Pivot Tables in Excel: A Complete Tutorial

Pivot tables are one of the quickest ways to summarise data in Excel. With a few drags they turn thousands of rows into a table of totals by region, product or month, and they recalculate as you rearrange them. Microsoft describes them as a tool to calculate, summarise and analyse data so you can see comparisons, patterns and trends (Microsoft Support: create a PivotTable). They are also easy to get subtly wrong. This tutorial builds a sales summary step by step and points out where errors creep in.

The example data is a sales list with columns for order date, region, product category, order ID, units and revenue, one row per order line.

Step 1: Prepare the data

Most pivot table problems start in the source data:

• One header row, with a unique name in every column.
• One row per record, with no subtotals, blank rows or merged cells inside the data.
• Consistent types in each column: dates that are real dates, numbers that are real numbers, not text that looks like numbers.
• Format it as a Table with Ctrl+T, so new rows are included automatically when the pivot table is refreshed.

Step 2: Create the pivot table

Click inside the table, choose Insert, PivotTable, and place it on a new worksheet. The PivotTable Fields pane lists your columns and four areas:

• Rows: categories listed down the side, such as region.
• Columns: categories across the top, such as quarter.
• Values: the numbers being summarised, such as revenue.
• Filters: fields that restrict the whole table, such as product category.

Drag Region to Rows and Revenue to Values. You now have revenue by region, a result that would otherwise need a SUMIFS formula per region.

Step 3: Choose the right summary

Excel sums numeric fields and counts text fields by default. Check every value field:

• Sum for amounts such as revenue and units.
• Count for the number of rows. With one row per order line, a count of order ID counts lines, not orders; one order with five products counts five times.
• Average for typical values, remembering that the average of order lines is not the average order value.

Change the calculation with Value Field Settings. The count-versus-orders trap is one of the most common sources of wrong numbers in pivot tables; if you need distinct orders, prepare an order-level table first or compute it another way and reconcile.

Step 4: Show values as percentages and running totals

The same numbers can be shown in different ways without new formulas. In Value Field Settings, Show Values As offers options such as % of Grand Total, % of Column Total, % of Parent Total and % Running Total In (Microsoft Support: show different calculations). Add Revenue to Values twice, once as a sum and once as % of Grand Total, to see each region's revenue and its share side by side.

Step 5: Group dates into months and quarters

Drag Order Date to Columns, then right-click a date and choose Group to group by months, quarters or years (Microsoft Support: group data). If the Group option is unavailable or produces odd results, some values in the date column are probably text, blanks or errors; fix the source column and refresh.

Step 6: Sort, filter and find the top items

Sort regions by revenue from largest to smallest from the row labels menu, and use Value Filters, Top 10, to show only the largest products. Filters on fields in the Filters area restrict the whole table, useful for looking at one product category at a time.

Step 7: Add slicers for easy filtering

Slicers are buttons that filter tables or pivot tables with a click, which makes a summary usable by colleagues who do not know the Fields pane. Insert a slicer for Region from the PivotTable Analyze tab. One slicer can control several pivot tables, but only pivot tables that share the same data source (Microsoft Support: slicers), another reason to build related pivots from one table.

Step 8: Refresh when the data changes

Pivot tables do not update automatically when the source data changes. After adding or editing data, right-click in the pivot table and choose Refresh, or refresh all pivot tables from the Data tab (Microsoft Support: create a PivotTable). A forgotten refresh is one of the most common reasons two reports disagree. When the source is a formatted Table, new rows are picked up on refresh without changing the source range.

Common mistakes and how to avoid them

• Counting lines instead of orders, as in step 3.
• Averaging averages. An average of regional average prices is not the overall average price; recompute from totals.
• Text that looks like numbers, which is counted instead of summed and silently excluded from totals.
• Blank or mistyped categories, such as "North " with a trailing space, which appear as separate rows.
• Forgetting to refresh.
• Hardcoding pivot results into other formulas by cell reference; the layout changes when fields move.

Cross-checking a pivot table's grand total against a simple SUM of the source column catches most of these. Excel formulas for data analysis covers SUMIFS and other formulas for fixed-layout summaries, and Excel Power Query covers cleaning the source data before it reaches the pivot table.

Pivot tables and pivot charts are part of the Excel module of the Data Analytics course.

Sources

• Create a PivotTable to analyze worksheet data (Microsoft Support) — Microsoft Support
• Show different calculations in PivotTable value fields (Microsoft Support) — Microsoft Support
• Group or ungroup data in a PivotTable (Microsoft Support) — Microsoft Support
• Use slicers to filter data (Microsoft Support) — Microsoft Support