Pivot Tables

Excel Guides / Dashboard Building

Dashboard Building

Summarise hundreds of rows by month, product or channel in a few clicks, then slice the answer any way you like.

Updated 12 Oct 2026 · 7 min read · By Sky Dashboards

A pivot table summarises a list of records without a single formula. Drag one field into rows and another into values, and Excel totals, counts or averages the data for you. Change your mind, drag the fields somewhere else, and the summary rebuilds in a second. For most reporting it is faster and harder to get wrong than a page of SUMIFS.

Set up the source data as a Table

A pivot table is only as good as the list behind it. Check for one header row with a unique name in every column, one record per row with no blank or subtotal rows, one type per column (real dates, real numbers, not text) and no merged cells.

Then turn the range into an Excel Table: click inside it and press Ctrl + T. Name it on the Table Design tab, for example tblOrders. A pivot built on a Table picks up new rows the next time you refresh. A pivot built on a fixed range like A1:E13 ignores anything added below row 13.

Insert a pivot table

1. Click any cell in the Table.
2. Go to Insert > PivotTable and choose From Table/Range.
3. Check that Table/Range shows the Table name, tblOrders, rather than a cell address.
4. Choose New Worksheet, or Existing Worksheet and a cell, and click OK.

Excel adds an empty pivot (on a new sheet it starts at A3) and opens the PivotTable Fields pane. If the pane is missing, click inside the pivot; if it is still hidden, use PivotTable Analyze > Field List.

Rows, Columns, Values and Filters

The bottom of the Fields pane has four boxes. Drag a field into each to shape the report:

With Date in Rows (grouped by month, see below), Channel in Columns and Revenue in Values, the 12 orders become this:

Summarize Values By and Show Values As

A number field dropped into Values is summed and a text field is counted. If a number column contains even one blank or text cell, Excel counts it instead, which is why revenue sometimes turns up as Count of Revenue.

To change the calculation, right-click any value in the pivot, choose Summarize Values By and pick Sum, Count, Average, Max, Min or another option. Value Field Settings on the same menu has those choices plus Number Format and a Custom Name box for a cleaner heading.

Show Values As keeps the underlying sum but displays it another way. The ones you will use most:

• % of Grand Total: each cell as a share of the overall total.
• % of Column Total and % of Row Total: shares within each column or row, such as each channel's share of a month.
• Running Total In: a cumulative total along a base field. By month, the shop above runs $1,040, $2,853, $4,315.
• Difference From and % Difference From: the change against a base item, such as the previous month.

Drag Revenue into Values a second time, set the copy to % of Grand Total and rename it, and you get each value next to its share:

Group dates by month or quarter

Current versions of Excel often group a date field automatically when you drop it into Rows. To choose the grouping yourself:

1. Right-click any date in the pivot and choose Group...
2. In the Grouping dialog, select Months, Quarters or both. Click an item again to deselect it.
3. If the data covers more than one year, select Years as well. Without it, January 2025 and January 2026 are added together under one Jan.
4. Click OK. To reverse it, right-click a grouped item and choose Ungroup.

Refresh after the data changes

A pivot table works from a stored copy of the source, the pivot cache, and does not update when the source changes. After you add or edit orders, refresh it:

The same commands are under PivotTable Analyze > Refresh and Data > Refresh All. Two settings in PivotTable Analyze > Options are worth changing:

• On the Data tab, tick Refresh data when opening the file.
• On the Layout & Format tab, untick Autofit column widths on update, so a refresh does not resize columns you have set by hand.

Slicers and timelines

Slicers are filter buttons that sit on the sheet, so anyone can filter the report without opening a drop-down.

1. Click inside the pivot and choose PivotTable Analyze > Insert Slicer.
2. Tick the fields to filter by, such as Channel and Product, and click OK.
3. Click a button to filter. Hold Ctrl to pick several, or turn on Multi-Select in the slicer header. The Clear Filter icon resets it.

A timeline is a slicer made for dates. Choose PivotTable Analyze > Insert Timeline, tick the date field, and you get a bar you can drag across days, months, quarters or years. It needs a column of real dates.

One slicer can drive several pivots, as long as they come from the same source. Right-click the slicer, choose Report Connections... and tick every pivot it should filter.

GETPIVOTDATA, pivot charts and dashboards

Type = and click a value inside a pivot, and Excel writes a GETPIVOTDATA formula instead of a plain cell reference:

Because it finds the value by its labels, the formula keeps working when the pivot grows or is re-sorted. When you would rather have an ordinary reference to copy down a column, click the arrow next to Options at the left of the PivotTable Analyze tab and untick Generate GetPivotData. The same switch is in File > Options > Formulas as Use GetPivotData functions for PivotTable references.

Pivot charts

Select the pivot and choose PivotTable Analyze > PivotChart. The chart follows the pivot: filter, slice or regroup it and the chart redraws. For a cleaner look, hide the grey field buttons with PivotChart Analyze > Field Buttons > Hide All.

Frequently asked questions

Why doesn't my pivot table show new data?

Pivots do not update on their own. Press Alt+F5 to refresh it, or Ctrl+Alt+F5 to refresh everything. If new rows still do not appear, the source is a fixed range: point it at a Table with PivotTable Analyze > Change Data Source.

Why does my pivot show Count instead of Sum?

The value column contains at least one blank or text entry, so Excel defaults to Count. Right-click a value, choose Summarize Values By > Sum, and clean up the source column.

Can one slicer filter two pivot tables?

Yes, if both pivots use the same source. Right-click the slicer, choose Report Connections and tick both.

How do I stop GETPIVOTDATA appearing in my formulas?

Click the arrow next to Options on the PivotTable Analyze tab and untick Generate GetPivotData.

Related guides

All 32 Excel guides