Pivot Table in Excel: How to Create One, Step by Step

A pivot table groups the rows of a table by a category, such as Region, and totals a number, such as Sales, for each group, without formulas. To create one, click a cell in the data, go to Insert > PivotTable, press OK, and drag Region to Rows and Sales to Values. The sheet below is not a pivot table: it builds the same summary with formulas, so you can watch the totals change.

FormulasReset

F2fx

=SUMIF($A$2:$A$9,E2,$C$2:$C$9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Sales	% of total
2	North	Apple	120		North	455	49%
3	South	Pear	85		South	305	33%
4	North	Pear	240		East	170	18%
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Click a cell to see its formula. Change a number or a formula and the sheet recalculates.

UNIQUE lists each region once and SUMIF totals it: North 455, South 305 and East 170, which is 49%, 33% and 18% of the 930 in all. Change C3 to 185 and the South total and all three percentages follow at once. A pivot table would show the same numbers, but only after you refresh it.

How to create a pivot table

Before you start, check the source data: one header row with a name in every column, one record per row, no blank rows or columns in the middle, and no subtotal rows.

1. Click any cell in the data.
2. Go to Insert > PivotTable (on some versions Insert > PivotTable > From Table/Range).
3. Excel fills in the range. Choose New Worksheet and press OK.
4. A blank pivot table appears with the PivotTable Fields pane on the right, listing your column headers.
5. Drag Region into the Rows box and Sales into the Values box. The pivot table shows each region once with Sum of Sales next to it, and a Grand Total row.
6. To change what is shown, drag fields between the boxes or out of the pane.

If you are not sure where to start, Insert > Recommended PivotTables shows a few ready-made layouts for your data. On a Mac the menu is the same: Insert > PivotTable.

Rows, Columns, Values and Filters

The PivotTable Fields pane has four boxes, and every pivot table is a choice of which column goes into which box:

• Rows: the categories down the left side, one row per distinct value (Region).
• Columns: categories across the top, one column per distinct value (Product).
• Values: the numbers to calculate for each combination. Sum is the default for a numeric column; Count, Average, Max, Min and others are in Value Field Settings.
• Filters: a field the whole pivot table is filtered by, shown as a drop-down above it.

With Region in Rows, Product in Columns and Sales in Values, the pivot table for the data above looks like this:

Sum of Sales   Column Labels
Row Labels     Apple   Pear   Grand Total
East              60    110           170
North            215    240           455
South            150    155           305
Grand Total      425    505           930

The formula version of that layout lists the regions down with UNIQUE, the products across with TRANSPOSE(UNIQUE()), and calculates every cell of the grid with one SUMIFS that takes the two lists as its criteria:

FormulasReset

F2fx

=SUMIFS(C2:C9,A2:A9,E2:E4,B2:B9,F1:G1)

	A	B	C	D	E	F	G
1	Region	Product	Sales			Apple	Pear
2	North	Apple	120		North	215	240
3	South	Pear	85		South	150	155
4	North	Pear	240		East	60	110
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Click a cell to see its formula. Change a number or a formula and the sheet recalculates.

E2 spills North, South and East down, F1 spills Apple and Pear across, and the SUMIFS in F2 fills the 3 by 2 grid between them: one total for each pair of a region and a product. Change B5 from Apple to Pear and both East cells change. The order here is the order the values first appear in; a pivot table sorts its labels alphabetically.

Count, average or percentage instead of sum

In the pivot table, click the field in the Values box and choose Value Field Settings. The Summarize Values By tab switches between Sum, Count, Average, Max and Min; the Show Values As tab turns the numbers into % of Grand Total, % of Column Total, a running total and others. Each formula has a direct counterpart:

FormulasReset

E2fx

=UNIQUE(A2:A9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Orders	Average
2	North	Apple	120		North	3	151.7
3	South	Pear	85		South	3	101.7
4	North	Pear	240		East	2	85.0
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Click a cell to see its formula. Change a number or a formula and the sheet recalculates.

North has 3 orders averaging 151.7, South 3 averaging 101.7, East 2 averaging 85.0. The % of Grand Total column is in the first sheet on this page.

Filter the summary by one product

The Filters box puts a drop-down above the pivot table. The formula version is a cell with a drop-down list and SUMIFS, which adds one more condition to SUMIF. Pick a product in F1:

FormulasReset

F1fx

	A	B	C	D	E	F
1	Region	Product	Sales		Product:	Apple▾ApplePear
2	North	Apple	120
3	South	Pear	85		Region	Sales
4	North	Pear	240		North	215
5	East	Apple	60		South	150
6	South	Apple	150		East	60
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Click a cell to see its formula. Change a number or a formula and the sheet recalculates.

With Apple chosen, North shows 215, South 150 and East 60. Pick Pear and they change to 240, 155 and 110. See SUMIFS for more conditions and the drop-down list page for how to add the list in Excel.

Refresh a pivot table

A pivot table keeps a copy of the source data (the pivot cache) and does not recalculate when a cell in the source changes. After editing the data:

• Right-click anywhere in the pivot table and choose Refresh, or press Alt+F5 on Windows.
• Data > Refresh All (Ctrl+Alt+F5) refreshes every pivot table in the workbook.
• To refresh every time the file opens, right-click the pivot table, choose PivotTable Options, and on the Data tab tick Refresh data when opening the file.

New rows added under the source range are not included, even after a refresh. Change the range under PivotTable Analyze > Change Data Source, or, better, turn the source into a table before creating the pivot table: select the data and press Ctrl+T, or use Insert > Table. A table grows when you add rows, and the pivot table picks them up on the next refresh.

Total every region with one formula

FormulasReset

F2fx

	A	B	C	D	E	F
1	Region	Product	Sales		Region	Sales
2	North	Apple	120		North
3	South	Pear	85		South
4	North	Pear	240		East
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Click a cell to see its formula. Change a number or a formula and the sheet recalculates.

Your turn: In F2, total the sales of every region listed in E2:E4 with one formula.

CheckShow answer

The answer spills 455, 305 and 170. Giving SUMIF the whole list E2:E4 as its criteria returns one total per region, so there is nothing to fill down. Excel 2019 and older have neither UNIQUE nor spilling: type the regions in E2:E4 and fill =SUMIF($A$2:$A$9,E2,$C$2:$C$9) down. Without the $ signs the ranges move down with each row and the totals come out wrong.

GROUPBY and PIVOTBY: a pivot table in one formula

Excel for Microsoft 365 has two functions that build a whole summary from one formula and recalculate like any formula, with no refresh. They need a current Microsoft 365 subscription. For the data above:

=GROUPBY(A2:A9,C2:C9,SUM)

East      170
North     455
South     305
Total     930

=PIVOTBY(A2:A9,B2:B9,C2:C9,SUM)

          Apple   Pear   Total
East         60    110     170
North       215    240     455
South       150    155     305
Total       425    505     930

GROUPBY takes the row field, the values and the function (SUM, COUNTA, AVERAGE, MAX, PERCENTOF). PIVOTBY adds a column field between them. Both sort the labels and add total rows, like a pivot table.

Pivot table or formulas: which to use

Use a pivot table to explore data and answer a question once; use formulas for a summary that sits in a report, feeds other formulas, and must always be current. To check a pivot table's numbers, rebuild one cell of it with SUMIFS: if the two disagree, the pivot table usually needs a refresh or its source range is too short.