How to create a pivot table in Excel

Step 1: Get the data in shape

Your boss wants revenue by region and product for the last quarter, by 5. You have a sales log: one row per sale, with Date, Region, Rep, Product, Units and Revenue. That's exactly the shape a PivotTable wants.

Three things make or break it. Every column needs a header. No blank rows or columns in the middle. And each column holds one kind of thing, so Revenue has only numbers, with no "n/a" or totals mixed in.

Turn the data into a table first: click inside it and press Ctrl+T. When you add next week's sales below, the table grows and the PivotTable picks them up on refresh.

Step 2: Insert the PivotTable and drag fields

The Fields pane has four boxes. Rows lists one line per value, Columns makes one column per value, Values does the math and Filters narrows the whole table. Drag Region to Rows and Revenue to Values, and you have totals by region.

To check a pivot, compare one number with a formula. =SUMIF(B2:B13,"West",F2:F13) gives 4,105, the same as the West row. The other regions are East 3,644 and North 3,376, for a grand total of 11,125.

1. Click any cell in the data.
2. Go to Insert > PivotTable. Check the range or table name, choose New Worksheet, and click OK.
3. In the PivotTable Fields pane, drag Region to Rows and Revenue to Values.
4. Drag Product to Columns for a region by product grid. West shows 2,533 for Chair, 897 for Desk and 675 for Lamp.
5. Not sure where to start? Insert > Recommended PivotTables shows a few ready-made layouts for your data.

Step 3: Group dates by month

Drag Date into Rows and recent versions of Excel group it for you, into Years, Quarters and Months. If you see one row per day instead, right-click any date in the PivotTable, choose Group, select Months (and Years if the data crosses a year) and click OK.

Here September has 4,120, October 4,393 and November so far 2,612. Grouping only works when every cell in the Date column is a real date. One date stored as text and Excel refuses with "Cannot group that selection".

The formula check for one month uses SUMIFS with a date window: on or after the 1st, before the 1st of the next month.

Step 4: Sum vs Count, and % of total

Excel picks Sum when every cell in the field is a number, and Count when there's a blank or a bit of text in it. If your Revenue shows "Count of Revenue", right-click a value, choose Summarize Values By > Sum, then go back and clean the column.

The same menu gives Average, Max and Min: the average East sale is 728.80. For shares, right-click a value and choose Show Values As > % of Grand Total. East is 32.8% of revenue, North 30.3% and West 36.9%.

To sort, right-click a value and choose Sort > Sort Largest to Smallest. To change number formats for the whole field, use Value Field Settings > Number Format rather than formatting the cells, so the format sticks after refresh.

Step 5: Refresh, and the formula alternative

A PivotTable doesn't update on its own when the data changes. Right-click it and choose Refresh, or use Data > Refresh All. If you didn't use a table and added rows below the range, use PivotTable Analyze > Change Data Source to include them.

Typing = and clicking a pivot cell creates a GETPIVOTDATA formula instead of a plain reference. It keeps working when the layout moves, but it can't be filled down. To turn it off, go to PivotTable Analyze > Options (the arrow) and untick Generate GetPivotData.

Prefer formulas that update without refresh? In Excel 365, one cell can build the same summary. Newer Microsoft 365 builds also have GROUPBY and PIVOTBY, which do the same in one function.