Knowledge hub

Pivot tables in Excel — useful guide

Pivot Tables are one of the fastest ways to summarise and analyse a large dataset from different angles. They're easy to set up, easy to edit, and can be refreshed with a single click — which makes them a natural pair with Power Query for automating regular reports.

How to create Pivot Tables

Click any cell in your table of data
Go to Insert > PivotTable
Choose where to place it — usually New Worksheet, or an existing one if you already have a destination. Double-check the selected data range before clicking Ok.
Click Ok

Setting up Pivot Tables in Excel

Creating a Pivot Table is the easy part — knowing how to navigate the four fields (Filters, Columns, Rows, Values) is where it gets confusing.

Filters

Drag one or more fields into Filters, and they'll appear at the top of the table for quick selection — e.g. filtering Status to only show "Cancelled" sessions.

You can also filter by clicking the small arrow next to the field:

Filtering directly from a Pivot Table field

Rows & columns

Straightforward once you see it in action — and you don't need both, one or the other is perfectly fine.

For more advanced tables, you can stack multiple fields in Rows or Columns — and the order matters. Putting State above Status groups statuses within each state; putting Status above State groups states within each status:

Values

Values controls what gets counted or calculated. For non-numerical fields, Excel simply counts — here it's counting members per state:

Counting members per state in a Pivot Table

For numerical fields you get more options — click the field and select Value Field Settings:

Value Field Settings for a numerical Pivot Table field

Calculation options for numerical Pivot Table values

You can count sessions, sum total hours, or calculate max, min, average, product, and standard deviation.

Refreshing the table

Click anywhere inside the Pivot Table
Go to PivotTable Analyze
Click Refresh

Different layouts for the Pivot Table

Right-click any heading and select Field Settings:

Or go to Layout & Print and experiment — here we've enabled "Show item labels in tabular form" and "Repeat item labels":

Layout and print settings for a Pivot Table

The new layout:

Pivot Table with updated layout formatting

Right-click and select PivotTable Options for more layout controls — including what to display for empty cells:

Setting empty cell display in Pivot Table options

The best part: once the layout is set, it stays set.

GETPIVOTDATA function

Because Pivot Tables adjust constantly as data and settings change, VLOOKUP isn't reliable against them — column counts shift. GETPIVOTDATA is built for exactly this:

=GETPIVOTDATA(data_field, pivot_table, [field 1, item 1], [field 2, item 2]...)

Getting the table grand total

Only the first two arguments are required — data_field (the table name, top-left of the table) and pivot_table (a cell reference to it). With just those two, the function returns the grand total, regardless of how the table's data changes:

Getting a row/column total

Add more arguments to target a specific row or column:

=GETPIVOTDATA(data_field, pivot_table, [field 1, item 1])

GETPIVOTDATA returning a row or column total

Field names must match exactly — not "Row Labels" or "Column Labels". If you're unsure, right-click the Pivot Table and select Show Field List. Also note: Grand Total isn't a valid field/item pair — =GETPIVOTDATA("Sum of Total Session Hours",A3,"State","Grand Total") won't work. Use the grand-total approach above instead.

Checking exact field names via Show Field List

Getting specific data by row and column

Add further field/item pairs to target an exact intersection:

=GETPIVOTDATA(data_field, pivot_table, [field 1, item 1], [field 2, item 2]...)

GETPIVOTDATA targeting a specific row and column

The same logic extends to tables with multiple row fields:

Stuck on a Pivot Table setting we haven't covered? Let us know and we'll add it.

Get in touch