Lesson 5 of 8 · 7 min · Pivot tables and charts
Pivot tables: summarise thousands of rows in a minute
What you will learn
- Prepare data so a pivot table works
- Build a pivot with Rows, Columns, Values and Filters
- Summarise by Sum, Count, Average and distinct count
- Show values as a percentage of total, row or column
- Sort, filter, refresh and fix common problems
In simple words
Imagine thousands of sales rows. A manager asks, "How much did each city sell in each month?" You could write dozens of SUMIFS formulas. A pivot table does it by dragging: drop City in one box, Month in another, Amount in a third, and Excel builds the whole summary grid. You can flip it, regroup it and filter it in seconds.
Words to know
- Pivot table: an interactive summary table that regroups data as you drag fields.
- Field: a column of your data that you can place in the pivot.
- Values area: the part of the pivot that holds the numbers being summarised.
- Refresh: update the pivot after the source data changes.
Prepare the data
A pivot table works best on a clean, tabular source:
- One header row, with unique, non-blank column names.
- One record per row; no blank rows or columns, no subtotal rows, no merged cells.
- One kind of data per column (all dates, all numbers; no text in number columns).
- Make it an Excel Table (Ctrl+T). Then new rows are included automatically when you refresh.
Build a pivot
- Click inside the table, then Insert > PivotTable. Choose a new worksheet.
- In the field list, drag fields into four areas:
| Area | What it does |
|---|---|
| Rows | each unique value becomes a row |
| Columns | each unique value becomes a column |
| Values | the numbers to summarise (sum, count, average) |
| Filters | a drop-down to restrict the whole pivot |
For example: City in Rows, Status in Columns, Amount in Values gives the total amount for each city split by status, with grand totals at the edges. Dragging City to Columns and Status to Rows turns it around.
Choose how to summarise
Numbers default to Sum; text defaults to Count. Change it: click the value field, choose Value Field Settings, then Sum, Count, Average, Min, Max, or Distinct Count (tick "Add this data to the Data Model" when creating the pivot to see it). If a number field is counted instead of summed, some cells are blank or text.
You can drop the same field in Values twice, for example Sum of Amount and Count of Amount, to show total and number of orders side by side. Rename the heading (for example "Total sales") by typing over it.
Show values as percentages
Right-click a value, choose Show Values As:
- % of Grand Total: each cell's share of everything.
- % of Column Total or % of Row Total: shares within a column or row.
- % of Parent Total: share within a group, useful with several row fields.
- Difference From and % Difference From: change versus the previous month or year.
- Running Total In: cumulative totals.
- Rank Largest to Smallest.
Sort, filter and layout
- Sort by value: right-click a number, Sort > Largest to Smallest.
- Top 10: row label drop-down, Value Filters > Top 10 (and change 10 to what you need).
- Filter with the row or column drop-downs, or drag a field to Filters.
- PivotTable Design tab: choose Report Layout (Tabular is easier to copy), turn grand totals and subtotals on or off, and apply a style.
- Number format: right-click a value, Number Format (this keeps its formatting when refreshed, unlike formatting the cells).
Refresh and keep it current
A pivot does not update by itself when the source changes. Right-click it and choose Refresh, or Data > Refresh All. If the source is an Excel Table, new rows are picked up; if it is a plain range, change the range under PivotTable Analyze > Change Data Source. Set Refresh data when opening the file in PivotTable Options for a report that is opened daily.
Getting data out of a pivot
- Double-click any number to see the underlying rows on a new sheet ("drill down"). This is how you check a figure.
GETPIVOTDATAformulas appear when you click a pivot cell from another cell; they are stable, but can be turned off in PivotTable options if you prefer ordinary references.- Do not type over a pivot; copy and paste values to another place to build a presentation table.
Common mistakes
- Blank cells or headers in the source, producing "(blank)" items or an error ("field name is not valid").
- Numbers stored as text, so they count instead of sum.
- Forgetting to refresh and reporting old numbers.
- Subtotal rows inside the source, which double-count.
- Formatting cells instead of using Number Format, losing the format on refresh.
Try it yourself
- Turn a sales list into a Table and insert a pivot with City in Rows, Product in Columns, and Amount in Values.
- Show the same pivot as percentage of column total.
- Add Count of orders beside Sum of amount and rename both.
- Filter to the top 5 cities by amount.
- Add rows to the source, refresh, and confirm they appear. Double-click a total to inspect its rows.
Check yourself
What are the four areas of a pivot table?
Rows, Columns, Values and Filters.
Why does a pivot count a numeric field instead of summing it?
Some cells in the column are text or blank, so Excel treats the field as text; clean the data.
Does a pivot table update when the source changes?
No, it must be refreshed. A source in an Excel Table includes new rows on refresh.
How do you see the rows behind a pivot number?
Double-click the number to drill down to the underlying records.
Interview questions
How do you prepare data for a pivot table?
Make it a tidy table: one header row with unique names, one record per row, no blanks, merged cells or subtotals, consistent data types, and ideally an Excel Table.
How do you count unique customers in a pivot?
Add the data to the Data Model when creating the pivot, then use Value Field Settings > Distinct Count.
Pivot table versus SUMIFS: when would you use each?
A pivot for fast exploration and flexible regrouping; SUMIFS for a fixed report layout inside a formula-driven sheet that must update automatically without refresh.
Summary
- Clean tabular data (preferably an Excel Table) is the foundation.
- Drag fields into Rows, Columns, Values and Filters; change Sum, Count, Average or Distinct Count.
- Use Show Values As for percentages and differences; sort and filter by value.
- Refresh after data changes, and drill down to verify numbers.
Going deeper
- Create a PivotTable to analyze worksheet data: the official guide.
- Show different calculations in PivotTable value fields: percentages and differences.
- Refresh PivotTable data: manual and automatic refresh.
See how much of this you can explain
A free mock interview asks you new questions on Excel for analysts for Data Analyst and scores every answer, with what to fix.