Pivotdrill / Cheat sheet
Pivot table cheat sheet
Everything a pivot table can do, in the order you meet it: build, summarize, group, filter, show as a percent, calculate, lay out, refresh, and fix. Written for Excel for Windows with the Mac and Google Sheets differences noted, with a drill for most entries. Print the page or get the PDF by email.
The ones worth learning first
| What | How |
|---|
| Build one | Click in the data, Insert > PivotTable (Alt, N, V), New worksheet |
| Sum by category | Drag the category to Rows, the amount to Values |
| Count instead of sum | Values > Value Field Settings > Count (text fields count by default) |
| Grid of two dimensions | One field to Rows, another to Columns, the amount to Values |
| By month | Date field to Rows; right-click > Group > Months (and Years) |
| Percent of total | Right-click the value > Show Values As > % of Grand Total |
| Sort largest first | Right-click a value > Sort > Largest to Smallest |
| Filter the whole table | Field to Filters, or Insert > Slicer |
| Refresh after the data changes | Alt+F5 (one table), Ctrl+Alt+F5 (all) |
| Make the source a Table first | Ctrl+T on the data, so new rows are picked up on refresh |
Build and summarize
| Insert a pivot table | Alt, N, V (Windows). Mac: Insert > PivotTable. Sheets: Insert > Pivot table. |
| Source as a Table | Ctrl+T first; the pivot then grows with the data on refresh. |
| Revenue by region | Rows: Region. Values: Sum of Revenue. drill |
| Orders per product | Rows: Product. Values: Count of Order ID. drill |
| Average by department | Values > Value Field Settings > Average drill |
| Region by product grid | Rows: Region. Columns: Product. Values: Sum. drill |
| Max per group | Value Field Settings > Max drill |
| Min per group | Value Field Settings > Min drill |
| Two values side by side | Drag two fields to Values (Units and Revenue) drill |
| Count, total and average of one field | Drag the same field to Values three times, set each summary drill |
| Nested rows | Rows: Region, then Rep (order in the Rows box is the nesting) drill |
| Rows and columns both nested | Rows: Department. Columns: Level. drill |
| Three dimensions | Rows: Channel, Customer type. Values: Fees. drill |
| Distinct count | Add to Data Model when creating; Value Field Settings > Distinct Count |
| Rename a value header | Type over the header cell, e.g. 'Revenue' (cannot equal the field name) |
Sort and filter
| Sort by a value | Right-click a value > Sort > Largest to Smallest drill |
| Smallest first | Sort > Smallest to Largest drill |
| Report filter | Drag Status to Filters, pick Approved drill |
| Exclude an item | Row label dropdown, untick the item (Remote) drill |
| Two filters | Filters: Priority = High and Status = Open drill |
| Slicer | PivotTable Analyze > Insert Slicer; connect to several pivots with Report Connections |
| Timeline | Insert Timeline for a date field; drag the range |
| Top 10 | Row label dropdown > Value Filters > Top 10 |
| Label filter | Value Filters / Label Filters > contains, begins with, greater than |
| Keep sort after refresh | Sort via the dropdown > More Sort Options, not by manual drag |
| Search a long list | Type in the search box of the row label dropdown |
Dates and grouping
| By month | Right-click a date > Group > Months drill |
| By quarter | Group > Quarters (and Years so quarters of different years do not merge) drill |
| Year over year | Rows: Department. Columns: Years. drill |
| Tickets per month | Rows: Created (grouped by month). Values: Count. drill |
| One quarter only | Group by quarters, then filter the column to Q4 drill |
| Ungroup | Right-click > Ungroup |
| Number bins | Right-click a number row label > Group > start, end, by (e.g. 0 to 1000 by 100) |
| Custom groups | Select several row items > right-click > Group, then rename Group1 |
| Automatic date grouping | File > Options > Data > turn off if you prefer raw dates |
| Dates not grouping | The column holds text, not dates: convert with DATEVALUE or Text to Columns, then refresh |
Show values as
| % of grand total | Show Values As > % of Grand Total drill |
| % of row | Show Values As > % of Row Total (mix inside each region) drill |
| % of column | Show Values As > % of Column Total drill |
| % of parent | % of Parent Row Total (share within the group above) drill |
| Difference from | Show Values As > Difference From > previous (year over year change) |
| % difference from | Show Values As > % Difference From > previous |
| Running total | Show Values As > Running Total In > Month |
| Rank | Show Values As > Rank Largest to Smallest |
| Index | Show Values As > Index (relative importance of a cell) |
| Same field twice | Add the field to Values again and set one copy to a Show Values As |
Calculations
| Calculated field | PivotTable Analyze > Fields, Items & Sets > Calculated Field: = Revenue * 0.05 drill |
| Ratio of two fields | Calculated field = Profit / Revenue (computed on sums, which is what you want) |
| Average that is not an average of averages | Calculated field = Revenue / Units, not Average of Unit Price |
| Calculated item | Fields, Items & Sets > Calculated Item (a new row item from others, e.g. East + West) |
| Budget vs actual | Columns: Type (Budget, Actual). Add Difference From or a calculated field. drill |
| Resolution time by priority | Add a helper column in the source (= Closed - Opened), then Average drill |
| Share by location | % of Column Total on Headcount drill |
| Returning customers | Rows: Customer type. Values: Average order value (calculated field) drill |
| Country by year | Rows: Country. Columns: Year (grouped). Values: Sum. drill |
| Measures (Data Model) | Add to Data Model, then PivotTable Analyze > Measures: DAX like =DIVIDE(SUM(Sales[Profit]), SUM(Sales[Revenue])) |
| GETPIVOTDATA | =GETPIVOTDATA("Revenue", $A$3, "Region", "East") pulls one cell for a report |
| Turn GETPIVOTDATA off | PivotTable Analyze > Options > Generate GetPivotData |
Layout and format
| Tabular layout | Design > Report Layout > Show in Tabular Form (one column per field) |
| Repeat labels | Design > Report Layout > Repeat All Item Labels (for copying out) |
| Subtotals and grand totals | Design > Subtotals / Grand Totals |
| Blank rows between groups | Design > Blank Rows |
| Number format | Value Field Settings > Number Format (sticks after refresh, unlike cell formatting) |
| Show empty cells as 0 | PivotTable Options > For empty cells show: 0 |
| Keep column widths | PivotTable Options > Layout > untick Autofit column widths on update |
| Collapse and expand | Click the +/- buttons; right-click > Expand/Collapse Entire Field |
| Hide the +/- buttons | PivotTable Analyze > +/- Buttons |
| Pivot chart | PivotTable Analyze > PivotChart |
| Conditional formatting | Home > Conditional Formatting, apply to 'all cells showing Sum of Revenue values' |
Refresh, source and fixes
| Refresh | Alt+F5; Refresh All: Ctrl+Alt+F5 |
| Refresh on open | PivotTable Options > Data > Refresh data when opening the file |
| Change the source | PivotTable Analyze > Change Data Source |
| New rows missing | Source range is fixed: make the source a Table (Ctrl+T) or extend the range |
| Sum shows as count | The column has text or blanks: clean the source (VALUE, remove blanks), then refresh and set Sum |
| Old items still in the dropdown | PivotTable Options > Data > Number of items to retain per field: None, then refresh |
| Field list disappeared | PivotTable Analyze > Field List |
| Overlapping pivots error | Two pivots on one sheet grew into each other: move one or put each on its own sheet |
| Dates in text form | Convert in the source, refresh, then group |
| Duplicate rows for the same item | Trailing spaces or different case in the source: TRIM and PROPER, refresh |
| Copy the values out | Select the pivot, Ctrl+C, Paste Special Values; use Tabular Form with repeated labels first |
Shortcuts (Windows)
| Insert pivot table | Alt, N, V |
| Refresh / Refresh All | Alt+F5 / Ctrl+Alt+F5 |
| Select the whole pivot | Ctrl+A inside it |
| Group selected items | Alt+Shift+Right arrow |
| Ungroup | Alt+Shift+Left arrow |
| Field settings | Alt+Enter on a field cell (or right-click) |
| Hide a selected item | Ctrl+- |
| Toggle the field list | Alt, JT, L |
| Google Sheets | Insert > Pivot table; Values > Show as; dates group via Create pivot date group |
pivotdrill.com: practice every one of these with instant checking. 89 entries.
Questions
Why does my pivot show Count instead of Sum?
At least one cell in that column is text or blank, so Excel defaults to Count. Clean the source column (convert text numbers with VALUE, fill or remove blanks), refresh, and set the summary to Sum.
Why does the pivot not include my new rows?
The data source is a fixed range. Turn the source into a Table with Ctrl+T, point the pivot at the Table name, and every refresh picks up new rows.
Average of averages is wrong. Why?
Averaging a column of unit prices weights every row the same. Make a calculated field that divides the sum of revenue by the sum of units, so the ratio is computed on totals.
Pivot table or formulas?
A pivot for exploring and for one-off summaries: it is faster and does not break. Formulas (SUMIFS, COUNTIFS) for a report that other formulas depend on, since pivots only update on refresh.
A cheat sheet does not make them stick
Doing them with instant checking does. The free exercises need no account.
Start practicing
Pivotdrill