Skip to content
Tutorials Center
My learning

Topics

Lesson 61 of 93 ยท PivotTables and Data Analysis

PivotTable Basics

Learn Excel PivotTables from scratch: field areas, value field settings, source data rules, refreshing, GETPIVOTDATA and a pandas pivot_table equivalent.

You have 5,000 rows of sales and someone asks: "What did each region sell, split by product?" You could write dozens of SUMIFS formulas, or you could build a PivotTable in about thirty seconds. A PivotTable summarizes a list by dragging fields into four areas. You can rearrange it in a click, and it never changes your source data.

PivotTables are the most useful analysis tool in Excel. This lesson shows how to build one, what each area does, how to change the calculation, and what the source data must look like. Excel cannot run on this site, so the steps are text and the live examples use pandas pivot_table in Python as a stand-in. It does the same grouping and aggregating, and it makes a good mental model; the PivotTable engine in Excel is a different program.

A PivotTable has four areas

Rows One row per item.
Columns One column per item.
Values The numbers to add up.
Filters Pages you can switch.
1 / 2

Prepare the source data

A PivotTable is only as good as its source list. Check these rules first:

  • One header row, with a unique, non-empty name above every column.
  • One record per row, and one kind of fact per column (Date, Region, Product, Amount). Not one column per month.
  • No blank rows or columns, no subtotal rows, no merged cells.
  • Consistent values: "North" and "north " count as different items if there are stray spaces or typos (case differences are merged, but trailing spaces are not). Clean first; see Cleaning Messy Data.
  • Numbers stored as numbers and real dates, not text.

Turn the list into an Excel Table (Ctrl+T) before you build. A pivot built on a table picks up new rows after a refresh, while a pivot built on a fixed range such as A1:E500 silently ignores row 501.

Building the first PivotTable

Click a cell in the data and choose Insert > PivotTable. Excel suggests the table name as source; choose New Worksheet and press OK. (Insert > Recommended PivotTables offers ready-made layouts.) You see an empty report on the left and the PivotTable Fields pane on the right, with all your column names on top and four boxes below.

Tick a field and Excel guesses where it goes: text fields go to Rows, numeric fields to Values. Better, drag them yourself:

Area Purpose Example
Rows Each unique item becomes a row Region
Columns Each unique item becomes a column Product
Values Fields to calculate Sum of Amount
Filters A dropdown above the table Year

Drag Region to Rows, Product to Columns and Amount to Values and you have a cross-tab with row and column Grand Totals. Drag Product from Columns into Rows below Region and you get a nested report. Drag a field out of the pane to remove it. None of this changes the source.

Run it: the same cross-tab in pandas

pivot_table takes the same ideas: index is Rows, columns is Columns, values is Values and aggfunc is the calculation. margins=True adds the Grand Totals.

Python Rows, Columns and Values with pivot_table

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

When a combination does not exist in the data, pandas shows NaN (pass fill_value=0 to change it) and Excel shows an empty cell. In Excel you can type a value (such as 0) into PivotTable Options > Layout & Format > For empty cells show.

Value field settings

By default a numeric field is summed and a text field is counted. To change that, click the field in the Values box, choose Value Field Settings and pick another summary: Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, Var. You can also rename it ("Total sales" instead of "Sum of Amount") and set the Number Format there, which keeps the format when the pivot changes shape.

You can drag the same field into Values more than once, for example Sum of Amount and Average of Amount side by side, or Sum and Count. The Show Values As tab turns values into percentages and running totals; see Grouping, Calculated Fields and Show Values As.

A frequent surprise: a field shows as Count of Amount instead of Sum. That happens when the column contains at least one text value or blank cell (numbers stored as text, a "n/a"), so Excel plays safe and counts. Fix the source data, refresh, and the default returns to Sum.

Python Several summaries: sum, average, count

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Layout and sorting

Select any cell and open PivotTable Design to change the look: Report Layout (Compact, Outline, Tabular), Subtotals, Grand Totals, Blank Rows and Styles. Tabular layout is best if you plan to copy the result elsewhere, because each field gets its own column. Sort by clicking the dropdown on a row label (for example sort Region by Sum of Amount, largest first). Filter row items with the same dropdown, or use the Filters area to pick a value like one year.

To get the top 5, use the dropdown on the row labels > Value Filters > Top 10 and change the number.

Refreshing

A PivotTable does not update when the source data changes. It keeps a snapshot (the pivot cache). After editing or adding rows, right-click the pivot > Refresh, or use PivotTable Analyze > Refresh (Alt+F5), or Refresh All (Ctrl+Alt+F5) for every pivot and query in the workbook. If you type in the source data and the pivot looks wrong, an unrefreshed cache is the first suspect.

Other facts worth knowing:

  • Do not type inside a pivot's body; Excel overwrites your cells at the next refresh. Put notes and formulas beside it.
  • Preserve cell formatting on update and Autofit column widths on update are in PivotTable Options. Untick autofit if the column widths keep jumping.
  • Saving a workbook with Save source data with file ticked makes the file bigger but allows a refresh without the original source.
  • Changing which range the pivot reads from: PivotTable Analyze > Change Data Source.

GETPIVOTDATA

Click a cell outside the pivot and type =, then click a number in the pivot. Instead of =B5 you get something like:

TEXT
=GETPIVOTDATA("Amount",$A$3,"Region","North","Product","Pens")

GETPIVOTDATA reads a value by field and item names, not by position. That is safer: if the pivot is rearranged or sorted, the formula still finds the number for North and Pens, whereas =B5 would then point at a different item. If you dislike it, turn it off with PivotTable Analyze > Options > Generate GetPivotData, and Excel writes plain cell references instead. If the requested item is not visible in the pivot (for instance after you filter it away), the formula returns #REF!.

Run it: GETPIVOTDATA by names

This stand-in builds a pivot as a dictionary and looks up values by names, like the function does.

Python Looking up a pivot value by names, not position

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Troubleshooting, date grouping and formatting

PivotTables are forgiving, but a handful of problems account for most questions.

Common errors and their fixes

Message or symptom Cause Fix
"The PivotTable field name is not valid" A header cell in the source is blank Give every column a unique, non-empty header
"A PivotTable report cannot overlap another PivotTable report" Refresh or expansion would run into another pivot or data Move the pivots apart, or leave empty columns between them; set PivotTable Options > Layout > Preserve cell formatting and watch for tables side by side
"Cannot group that selection" The field holds text, blanks or errors mixed with dates/numbers Fix the source column so every cell is a real date or number, then refresh
"Data Model" load error ("We can't load the Data Model") Add-in or memory problem, or a very large model Close other workbooks, update Office, or switch to a normal pivot if you did not need relationships
Numbers count instead of sum The column has text-numbers or blanks, so Excel defaults to Count Convert text to numbers (Text to Columns, or multiply by 1) and set Sum in Value Field Settings
Old items still listed after data changed The pivot cache keeps deleted items PivotTable Analyze > Options > Data > Number of items to retain per field: None, then refresh
Same item appears twice ("Pen" and "Pen ") Trailing spaces or inconsistent spelling in the source Clean the text (Cleaning Messy Data) and refresh
New rows are missing The source is a plain range that did not expand Base the pivot on a Table (Excel Tables) or change the source range

Always check that you pressed Refresh (Alt+F5, or Alt+A then R+A for all). A pivot never updates itself when source cells change, unless you tick Refresh data when opening the file.

Stop automatic date grouping

Excel 2016 and later group dates into Years, Quarters and Months automatically when you drop a date field in. To undo it for one pivot, right-click a date > Ungroup. To turn it off for all new pivots: File > Options > Data > Disable automatic grouping of Date/Time columns in PivotTables. To group dates deliberately, right-click a date > Group and choose the units (Months and Years, or Days with a number of days such as 7 for weeks). A source column that contains any text date prevents grouping, so convert those to real dates first.

Flat layout: tabular, repeated labels

The default Compact layout puts all row fields in one indented column, which is hard to reuse as data. For a flat table you can sort, filter and copy: Design > Report Layout > Show in Tabular Form, then Report Layout > Repeat All Item Labels, Subtotals > Do Not Show Subtotals and Grand Totals > Off for Rows and Columns. Now the pivot looks like a normal table with one value per cell. To change the layout for every new pivot, use PivotTable Options > Layout & Format or File > Options > Data > Edit Default Layout.

Formatting that survives a refresh

  • Set the number format through Value Field Settings > Number Format, not Home > Number, so it sticks when values change.
  • Tick PivotTable Options > Layout & Format > Preserve cell formatting on update and untick Autofit column widths on update if you size columns by hand.
  • Use a PivotTable Style (Design tab) instead of manual cell fills.
  • For conditional formatting in a pivot, select a value cell, apply the rule, then click the Formatting Options icon (lightning) that appears and choose All cells showing "Sum of ..." values so it follows the field when the pivot changes. Color scales and data bars work well on a flat layout. See Conditional Formatting.
  • Show blanks as zero or a dash: PivotTable Options > Layout & Format > For empty cells show.

Duplicate items and relational tables

If you need to relate two tables (Orders and Customers) in one pivot, do not VLOOKUP everything in first: tick Add this data to the Data Model and create a relationship (Data Model and Power Pivot). If the lookup column has duplicates on the "one" side, relationships fail with an error about duplicate values; remove the duplicates or choose the right key (Removing Duplicates). Distinct count is available only for Data Model pivots (Value Field Settings > Distinct Count).

Small habits that save time

  • Alt+N+V inserts a PivotTable; Alt+F5 refreshes; Ctrl+Shift+* (with a cell selected) selects the whole pivot.
  • Double-click a value to see the rows behind it on a new sheet ("drill through"). Delete that sheet afterward to keep the file small.
  • To delete a PivotTable, select the entire pivot (PivotTable Analyze > Select > Entire PivotTable) and press Delete. Deleting only the sheet or part of the pivot leaves the cache behind; a pivot that shares the cache is unaffected.
  • To keep a static copy, select the pivot, copy, then Paste Special > Values.
  • Use GETPIVOTDATA when you want a stable formula reference; turn the auto-generation on or off with PivotTable Analyze > Options > Generate GetPivotData.

Run it: why a pivot goes stale

JS Stale pivot cache and the trailing-space duplicate

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which area of the PivotTable Fields pane holds the numbers you want to calculate?

    Values holds the fields that are summed, counted or averaged. Rows and Columns create the labels, and Filters adds a dropdown above the report.
  2. 2. You add 50 new rows to the source data, but the PivotTable is unchanged. What is the most likely reason?

    A PivotTable works from a cached snapshot. Refresh it (Alt+F5), and make sure the source is a Table or the range was extended.
  3. 3. A numeric field shows up as Count of Amount instead of Sum of Amount. Why?

    Excel sums a field only if it is entirely numeric. Text values or blanks make it fall back to Count. Clean the source and refresh.
  4. 4. What is the advantage of GETPIVOTDATA over a plain cell reference to a pivot cell?

    GETPIVOTDATA looks up the intersection of named items, so changes in position do not break it. It returns #REF! if the item is not visible.

Summary

  • A PivotTable summarizes a clean list with four areas: Rows, Columns, Values and Filters.
  • The source needs one header row, one record per row, no blanks or subtotals and real numbers and dates; make it an Excel Table so it grows.
  • Value Field Settings change the calculation (Sum, Count, Average, Max...), the name and the number format.
  • Pivots do not update by themselves: use Refresh (Alt+F5) or Refresh All (Ctrl+Alt+F5).
  • GETPIVOTDATA reads pivot values by names; pandas pivot_table is a handy stand-in for the idea.

Next: Grouping, Calculated Fields and Show Values As.