Pivotdrill

Pivot tables, one page per job

How to do every pivot table job people search for, in Excel and Google Sheets: build one, group dates, show percentages, calculated fields, slicers, refresh problems, each with the direct answer, the field layout, a worked example, the usual mistakes, and the Pivotdrill exercises that practice it.

40 topics, each with the direct answer, syntax, worked examples, the mistakes people make and the drills that practice it. Updated November 2026.

Basics

  • Create a pivot tableCreate a pivot table in Excel from a range or table, pick a new worksheet, then drag fields into Rows and Values. Steps, shortcut, data rules and fixes.
  • Rows, Columns, Values and FiltersThe four pivot table areas: Rows make the row labels, Columns spread categories across the top, Values hold the numbers, Filters restrict the whole table.
  • Sum, Count and Average in a pivot tableChange a pivot table value from Count to Sum or Average in Value Field Settings, show total, count and average side by side, and fix a column that counts.
  • Sort a pivot tableSort a pivot table largest to smallest by a value, A to Z by label, or in your own order by dragging rows. More Sort Options and why a sort does not stick.
  • Filter a pivot tableFour ways to filter a pivot table: the row label dropdown, the Filters area above the table, Value Filters on totals, and slicers. Examples and catches.
  • Top 10 filterUse Value Filters, Top 10 to keep only the top 5, the bottom 3 or the items that make up 80 percent of a total. Steps, the Items, Percent and Sum modes.
  • Value filtersFilter pivot table rows by their totals: only regions with revenue over 10,000, counts below 5, or totals between two numbers. Steps and what gets tested.
  • Pivot table shortcutsThe keyboard shortcuts that matter for pivot tables: insert, refresh, refresh all, group and ungroup, open the field list, select the whole pivot.
  • Unpivot dataUnpivot a wide table (one column per month) into a long list a pivot table can use. Power Query Unpivot Columns steps and a Google Sheets formula approach.

Layout and values

  • Two row fieldsPut two or more fields in Rows to nest categories, choose which field is outer and inner, collapse groups and switch to tabular layout for separate columns.
  • Multiple value fieldsAdd two or more fields to Values, decide whether they sit side by side or stacked with the Sigma Values item, and show one field twice as a sum and a count.
  • Tabular layoutSwitch a pivot table to Tabular Form so every row field gets its own column, compare Compact, Outline and Tabular, and make Tabular the default layout.
  • Repeat row labelsFill the blank cells under an outer row label so every row shows its region: Design, Report Layout, Repeat All Item Labels, plus the per-field option.
  • Remove grand totals and subtotalsTurn grand totals off for rows, columns or both and remove subtotals from nested fields on the Design tab. How to bring them back and show subtotals on top.
  • Blank cells in a pivot tableReplace empty pivot cells with 0 or a dash in PivotTable Options, and get rid of the (blank) row label by filtering it out or filling gaps in the source data.
  • Text in the Values areaA pivot table cannot sum text, so the Values area shows a count. Ways to show text: put the field in Rows with tabular layout, or a Data Model CONCATENATEX.
  • Distinct countCount unique customers or products per region. In Excel tick Add this data to the Data Model and pick Distinct Count; in Google Sheets choose COUNTUNIQUE.
  • Pivot from multiple sheetsBuild one pivot table from several worksheets by appending them in Power Query or relating them in the Data Model. Steps for both and the old wizard limits.
  • Data Model pivot tableWhat Add this data to the Data Model does: relate several tables without lookups, get Distinct Count and write DAX measures. Also what you lose by using it.

Dates and grouping

  • Group dates by month, quarter and yearGroup a date field in a pivot table by months, quarters and years with right-click, Group, and get year by month breakdowns. Auto grouping, ungrouping, weeks.
  • Group numbers into bandsGroup a numeric field into ranges such as 0 to 99 and 100 to 199 with the pivot table Group dialog. Set the start, end and step, and relabel the bands.
  • Dates not groupingCannot group that selection means the date column has text, blanks or mixed values. Find the bad cells, convert text dates to real dates, refresh and group.
  • TimelineInsert a timeline to filter a pivot table by year, quarter, month or day with a drag slider. Where the button is, connecting several pivots, and real dates.

Calculations

  • Percent of grand totalShow each row as a share of the overall total with Show Values As, % of Grand Total. Add it next to the raw numbers, format it, compare with parent totals.
  • Percent of row and column totalUse Show Values As with % of Row Total or % of Column Total in a region by product grid. Which direction adds up to 100 percent and how to read each one.
  • Difference from previousCompare periods inside a pivot table with Show Values As, Difference From and % Difference From. Pick the base field and (previous); why period one is blank.
  • Running totalAdd a cumulative column to a pivot table with Show Values As, Running Total In, using the month field as the base. Includes % Running Total and yearly resets.
  • Calculated fieldInsert a calculated field with Fields, Items & Sets to compute margin, commission or price per unit from other fields. Formula rules, why averages go wrong.
  • Calculated itemCreate a calculated item to combine or compare row items, for example Americas = East + West. Where the command hides, why it is grayed out, double counting.
  • GETPIVOTDATAGETPIVOTDATA(data_field, pivot_table, field1, item1) pulls one value out of a pivot table by its labels, so it survives rearranging. Syntax and examples.

Formatting and refresh

  • Refresh a pivot tableRefresh a pivot table after the source changes with right-click Refresh or Alt+F5, refresh every pivot with Ctrl+Alt+F5, and set the file to refresh on open.
  • Change data sourceUpdate the range a pivot table reads from with PivotTable Analyze, Change Data Source, or convert the source to an Excel Table so it grows by itself.
  • Pivot table not updatingA pivot table does not update on its own. The usual causes: no refresh, new rows outside the source range, retained old items, text numbers. Checks and fixes.
  • Number format in a pivot tableFormat pivot table values so the format survives a refresh: use Value Field Settings, Number Format rather than the Home ribbon. Currency and percent.
  • Conditional formatting in a pivot tableApply color scales, data bars and highlight rules to pivot values so they stay attached to the field after a refresh. The three scope options explained.
  • Copy a pivot table as valuesTurn a pivot table into plain cells with Copy, Paste Special, Values and Number Formats, keep the look with Keep Source Formatting, set tabular layout first.
  • SlicersInsert a slicer to filter a pivot table with clickable buttons, connect one slicer to several pivots with Report Connections, select several items, clear it.
  • Pivot chartMake a chart that stays linked to a pivot table with PivotTable Analyze, PivotChart. Choose the type, hide field buttons, sort bars, filter from the chart.

Google Sheets

  • Pivot table in Google SheetsCreate a pivot table in Google Sheets with Insert, Pivot table, use the editor to add Rows, Columns, Values and Filters, change SUM to COUNTA, group dates.
  • Calculated field in Google SheetsAdd a calculated field to a Google Sheets pivot table: Values, Add, Calculated field, then =Revenue-Cost summarized by SUM, or =SUM(Revenue)/SUM(Units).

Reading is not practicing

Every topic here links to drills that check your answer on live data. All of them are free to try.

Start practicing