Before you start
You will be able to
- Prepare source data for a PivotTable, and explain why an Excel table is the best source.
- Build PivotTables with fields in the Rows, Columns, Values and Filters areas, choosing Sum, Count, Average, Max or Min.
- Use Show Values As to give percentages of the grand total, the column total and the row total, and interpret each correctly.
- Use date grouping, sorting and filtering on a PivotTable, and refresh it after its source data change.
- Compare a PivotTable with SUMIFS and COUNTIFS formulas, reconcile one against the other, and choose the right tool for a report.
Review first
- Conditional sums, counts and averages — Every cell of a PivotTable is a conditional sum or count; SUMIFS and COUNTIFS are how you check one.
- Excel tables and cleaning data — A PivotTable should be built on an Excel table of clean data.
- Transforming and reshaping data — Aggregation, and the reshaping B02 calls a pivot, are what a PivotTable does.
- Summarising data: changes, averages and spread — Choosing a sum, a count or an average depends on the question, as B01 explains.
Fictional case A fictional coffee business founded by Ana Lim. It starts as a catering and barista-training service, opens its own cafés and an online shop for beans, and plans a canned cold brew.
The idea
Fictional case
Ana wants to know how the spring’s $9,096.15 of online orders moved from month to month, and how much of it came through the app. With SUMIFS she would write a grid of formulas, one per month and channel, and rewrite it the moment she asked the next question: by region instead, or only for subscriptions. She needs a summary she can rearrange in seconds, built from the Orders table.
A PivotTable is Excel’s interactive summary of a list of records. It groups the records by the values of the fields you place in its rows and columns, and in each cell aggregates a field you choose: the sum of order_total, the count of orders, the average invoice. B02 used “pivot” for reshaping long data into a wide table and defined aggregation as combining many values into one; a PivotTable does both at once, and redoes them every time you move a field.
To build one, click a cell in the table and choose Insert › PivotTable, check the source (the table Orders) and choose a new worksheet. The PivotTable Fields pane lists each column header of the source as a field, and below it are four areas. Fields in Rows become row labels down the left; fields in Columns become column headings across the top; fields in Values are summarised in the body; and fields in Filters become report filters above the table. Tick a field and Excel places it for you: Microsoft notes that non-numeric fields usually go to Rows and numeric fields to Values. Drag a field to move it.
Read the result as a set of conditional aggregates. The May and web cell, $2,159.05, is the total of order_total over the orders placed in May through the web shop: exactly what =SUMIFS(Orders[order_total],Orders[channel],"web",Orders[order_date],">="&DATE(2026,5,1),Orders[order_date],"<"&DATE(2026,6,1)) computes. The PivotTable writes every such cell at once, adds the row and column totals, and rebuilds them all when Ana swaps channel for region.
The first view already raises a question. May has the most order value, $2,929.75, but the fewest orders, 49. A sum can’t say why; the records can. One web order in May, LC-10144 on 14 May, was the 120-bag office order worth $1,512.00: without it May’s value would be $1,417.75, the lowest of the four months. A PivotTable is the fastest way to find where a total comes from, and the start of the questions, not the end.
In depth
Source data a PivotTable can use
Microsoft’s advice for source data comes down to the shape B01 called structured data: one record per row and one field per column.
- A single header row of unique, non-blank labels, one per column. Two header rows, or a blank heading, leave a field without a usable name.
- Records in rows, fields in columns. A sheet with one column per month is already a summary; reshape it into rows of month and value first (B02’s transforming-data lesson).
- One type per value field. Don’t mix text and numbers in a column you will sum.
- No blank rows or columns. Excel takes the source as the block of data around the selected cell, so a blank row or column cuts the data short.
- No totals inside the data. A subtotal row typed between the records would be summed with them and counted twice.
- Ideally an Excel table. The table grows when rows are added, so the PivotTable sees them at the next refresh.
Values: Sum, Count, Average and the rest
Right-click a value and choose Summarize Values By to pick the aggregation: Sum, Count, Average, Max, Min, Product, StdDev, StdDevp, Var or Varp (the last four belong to B04). A numeric field in Values starts as Sum. Microsoft states the rule for the exception: if the field contains blanks or non-numeric values (text, dates or Booleans) when you place it in Values, the PivotTable uses Count. So a column of order totals stored as text arrives as “Count of order_total”, and a cleaning problem shows up as a summary problem.
Count counts non-empty values, as COUNTA does, not records. Lantern’s rating column is blank for orders that weren’t rated, so Count of rating gives 154, not 231; to count orders, count a field that is never blank, such as order_id. Average ignores blanks the same way: the average rating, 4.10, is the average of the 154 ratings given, and says nothing about the 77 orders without one.
Several summaries can sit side by side: drag order_total into Values twice and set one to Sum and one to Average. By channel that gives $3,656.20 and an average order of $36.93 for the app, against $5,439.95 and $41.21 for the web shop, the average order value of each.
Counting different things is another matter. Count of customer_id counts orders (231), not customers. Microsoft’s Distinct Count summary counts each value once, but it works only when the PivotTable uses the Data Model (Excel for Windows): tick “Add this data to the Data Model” when you create it. For Lantern it gives 112 customers. Without the Data Model, =ROWS(UNIQUE(Orders[customer_id])) from the last lesson gives the same number.
Show Values As: shares instead of amounts
Right-click a value and choose Show Values As to replace each amount with a calculation on it. Microsoft lists, among others: % of Grand Total, % of Column Total, % of Row Total, % Of (a chosen base item), Difference From, % Difference From, Running Total In and the Rank options. The three percentage-of-total options answer different questions, and the formula cards below give each.
| Cell: May, web | Divided by | Shows | Answers |
|---|---|---|---|
| % of Grand Total | the grand total, $9,096.15 | 23.7% | What share of the spring’s value was web orders in May? |
| % of Column Total (months across) | May’s total, $2,929.75 | 73.7% | What share of May’s value came through the web shop? |
| % of Row Total (months across) | the web total, $5,439.95 | 39.7% | What share of the web shop’s spring value came in May? |
Which one is “% of column” depends on the layout: the same question needs % of Row Total if months run down the rows instead. Before you choose, say the question as “what share of X was Y” and check that the option divides by X. The wrong option still gives a tidy column of percentages that add up to 100% somewhere, which is why it goes unnoticed.
Grouping dates and numbers
A date field in Rows can show one row per day, which is rarely what you want. Right-click a date and choose Group, then under By choose Months (or Quarters, or another period); recent versions of Excel detect related time fields and group them for you. If the data span more than one year, select Years as well as Months, so that each row is one month of one year.
Numbers group the same way, into equal intervals: right-click an order_total row label, Group, Starting at 0, By 20, and the rows become $0–$20, $20–$40 and so on, the equal-width binning of B02. Bands of unequal width, such as Ana’s order-size bands, need a helper column with an approximate-match lookup (the lookups lesson), which then goes into Rows like any other field.
Sorting and filtering
Use the arrow on Row Labels or Column Labels to sort A to Z or Z to A, or right-click a cell and choose Sort, which sorts all the items at that level. To rank items by their totals, select a value in the Grand Total column and sort it. In a PivotTable, month and weekday names typed as text sort in calendar order by default, because PivotTable Options › Totals & Filters › Use Custom Lists when sorting is on, and Excel’s custom lists include the months and the days of the week. In an ordinary sort, or with that option off, they sort alphabetically (Apr, Jun, Mar, May). When a PivotTable doesn’t sort as you expect, Microsoft suggests Sort › More Sort Options, or unchecking that option.
Filters limit which records count. A field in the Filters area filters the whole PivotTable (channel = app, say), and the arrows on the labels filter rows or columns. A slicer does the same job with a panel of buttons that also shows what is selected: click in the PivotTable and choose Insert › Slicer. Slicers work on tables too, and one slicer can drive several PivotTables built from the same source through Report Connections.
Refresh and the pivot cache
A PivotTable doesn’t read the table cell by cell. It summarises a copy of the source data that it holds in memory, the pivot cache. When the source changes, the PivotTable keeps showing the old figures until it is refreshed: right-click it and choose Refresh, press Alt+F5, or use Refresh All on the PivotTable Analyze tab for every PivotTable at once. PivotTable Options › Data › “Refresh data when opening the file” refreshes on opening.
Microsoft is adding an Auto Refresh option for PivotTables on data in the same workbook, but when this lesson was checked it was available only to members of the Microsoft 365 Insider programme; don’t count on it. And refresh can only see what the source range covers. A PivotTable built on A1:M232 ignores orders pasted into row 233, refresh or not; built on the table Orders, it includes them, because the table grows. An old PivotTable can be pointed at a table with Change Data Source.
PivotCharts and GETPIVOTDATA
A PivotChart (Insert › PivotChart) charts a PivotTable’s summary, and the charts lesson covers which chart to choose. To use a PivotTable’s number in an ordinary formula, type = and click the value cell: Excel writes a GETPIVOTDATA formula such as =GETPIVOTDATA("order_total",$A$3,"channel","web"), which finds the value by its labels rather than its position, so it survives a rearranged layout. It returns only data the PivotTable shows, and #REF! if a filter hides it. You can switch the feature off with PivotTable Analyze › PivotTable › Options › Generate GetPivotData.
PivotTable or SUMIFS?
| PivotTable | SUMIFS, COUNTIFS and AVERAGEIFS | |
|---|---|---|
| Building and exploring | Seconds; drag to ask the next question | One formula per layout; slower to change |
| New categories in the data | Appear by themselves at refresh | Need a new row or column of formulas |
| Updating | Only when refreshed | Immediately, like any formula |
| Layout and formatting | Excel’s layout, within limits | Exactly the report you design |
| Shares, ranks, running totals | Show Values As | Formulas you write |
| Counting different customers | Distinct Count, with the Data Model | UNIQUE (Microsoft 365, 2021, 2024) |
| Categories with no records | Not shown (a blank) | Shown as 0 |
Use a PivotTable to explore, to answer one-off questions and for summaries whose categories may change. Use a summary table of formulas for a fixed report or dashboard that must update the instant the data do, that must show a category even when it has no records, or whose layout matters. Whichever you use, reconcile: the PivotTable’s grand total must equal =SUM(Orders[order_total]), $9,096.15 over 231 orders, and any cell you are about to quote should match a SUMIFS. A mismatch means a stale cache, a source range that stops short, or a filter you forgot.
Formulas
% of Grand Total
- The value in row i and column j of the PivotTableas the data
- The grand total of all the valuesas the data
- The cell’s share of the whole table%
Assumes. Every cell is divided by the same number, so all the cells add up to 100%.
% of Row Total
- The value in row i and column jas the data
- The total of row ias the data
- The cell’s share of its row%
Assumes. Each row adds up to 100%. Use it when the question is a share of a row item.
% of Column Total
- The value in row i and column jas the data
- The total of column jas the data
- The cell’s share of its column%
Assumes. Each column adds up to 100%. Use it when the question is a share of a column item.
Worked examples
Fictional figures
Ana builds a PivotTable on Orders with channel in Rows and three value fields: Count of order_id, Sum of order_total and Average of order_total. She then shows a second copy of the sum as % of Grand Total. What does it show?
| channel | Count of order_id | Sum of order_total | Average of order_total | % of Grand Total |
|---|---|---|---|---|
| app | 99 | $3,656.20 | $36.93 | 40.2% |
| web | 132 | $5,439.95 | $41.21 | 59.8% |
| Grand Total | 231 | $9,096.15 | $39.38 | 100.0% |
- Count the orders with order_id, a field that is never blank: 99 app and 132 web orders, 231 in all.
- Sum order_total: $3,656.20 and $5,439.95, which add up to the grand total $9,096.15: the reconciliation check passes.
- Average order_total gives each channel’s average order value: $3,656.20 ÷ 99 = $36.93 and $5,439.95 ÷ 132 = $41.21.
- % of Grand Total divides each sum by $9,096.15: 40.2% app and 59.8% web.
Answer. The app took 99 orders worth $3,656.20 (40.2% of the value, average $36.93); the web shop 132 orders worth $5,439.95 (59.8%, average $41.21).
What it means. Four numbers per channel from one drag-and-drop table, and a total that matches the source. The web shop’s higher average includes the $1,512.00 office order; the challenging example shows how much one order can move a PivotTable’s figures.
Fictional figures
Ana puts channel in Rows, order_date grouped by Months in Columns and Sum of order_total in Values, then wants each month’s split between app and web. Which Show Values As option answers that, what does it show, and how should she read May?
| channel | Mar | Apr | May | Jun | Grand Total |
|---|---|---|---|---|---|
| app | $910.75 | $1,012.65 | $770.70 | $962.10 | $3,656.20 |
| web | $1,065.10 | $1,162.90 | $2,159.05 | $1,052.90 | $5,439.95 |
| app, % of column | 46.1% | 46.5% | 26.3% | 47.7% | 40.2% |
| web, % of column | 53.9% | 53.5% | 73.7% | 52.3% | 59.8% |
- Say the question as a share: “what share of each month’s value came through the app?” The base is the month’s total. Months run across, so each month is a column: % of Column Total.
- Each cell is divided by its month’s total: in March $910.75 ÷ $1,975.85 = 46.1%; in May $770.70 ÷ $2,929.75 = 26.3%. Each column adds up to 100%.
- May looks like a collapse of the app’s share, from about 47% to 26%. Check the records first: the web column includes LC-10144, $1,512.00. Without it May’s web value is $647.05 and the app’s share of May is $770.70 ÷ $1,417.75 = 54.4%, higher than in any other month (46–48%), so the app had if anything a good May.
- Reconcile one cell before quoting it:
=SUMIFS(Orders[order_total],Orders[channel],"web",Orders[order_date],">="&DATE(2026,5,1),Orders[order_date],"<"&DATE(2026,6,1))returns $2,159.05, the same as the PivotTable.
Answer. % of Column Total. The app’s share was 46.1% in Mar, 46.5% in Apr, 26.3% in May, 47.7% in Jun. May’s low figure is one office order, not a shift in how customers buy: without it the app’s May share is 54.4%, its highest of the spring.
What it means. The PivotTable did the arithmetic; the analyst chose the base and checked the records behind the surprise. % of Row Total would have answered a different question (how the app’s spring value was spread over the months) and looked just as tidy.
Fictional figures
Ana is deciding how to staff and market catering for 2027 and builds a PivotTable on her 76 events of 2026: event type in Rows, with Count of event_id, Sum of invoice and Average of invoice. A second PivotTable has the event date grouped by Months. Which types matter most, and when is she busiest?
| Type | Events | Invoices | Average invoice | % of invoices |
|---|---|---|---|---|
| Breakfast | 13 | $12,060.00 | $927.69 | 19.4% |
| Conference | 13 | $14,540.00 | $1,118.46 | 23.3% |
| Launch | 11 | $8,690.00 | $790.00 | 13.9% |
| Meeting | 28 | $18,200.00 | $650.00 | 29.2% |
| Team day | 11 | $8,830.00 | $802.73 | 14.2% |
| Grand Total | 76 | $62,320.00 | $820.00 | 100.0% |
- Reconcile: 76 events and $62,320.00 of invoices, the totals F02 records for the year.
- Meetings are the most frequent type (28 events) and bring in the most, $18,200.00 or 29.2%, but at the lowest average, $650.00.
- Conferences have the highest average, $1,118.46, but the cell includes Meridian Partners’ two-day conference at $4,200.00. Without it the other 12 average $861.67, below breakfasts at $927.69.
- By month, events build up through the year, apart from a summer dip (6 in July, 5 in August): 3 in March, 10 in October, 11 in November and 13 in December. October to December hold 34 of the 76 events (44.7%).
Answer. Meetings are the volume business and breakfasts the best regular earners per event once the one two-day conference is set aside; 44.7% of events fall in the last quarter, with December the busiest month (13).
What it means. Ana books extra staff for October to December 2027 and promotes breakfasts to the clients who already order meetings. The PivotTable gave the counts and sums; the decision came from reading the averages with their outlier in mind. Whether the quotes cover their costs is a question for the what-if lesson.
Explore
Common mistakes
“The PivotTable shows the latest data.”
It shows its cache as of the last refresh. After correcting or adding data, refresh (Alt+F5, or Refresh All), and build it on an Excel table so that new rows are inside the source range.
“Count of a column counts the rows.”
Count counts non-empty values, as COUNTA does. Count of rating counts rated orders only. Count a field that is never blank, such as an id, to count records, and use Distinct Count (with the Data Model) to count different customers.
“My totals show as Count, so the PivotTable is broken.”
A value field with any text or blanks in it starts as Count. Usually the numbers are stored as text: clean the column (VALUE, as in the last lesson), refresh, and switch the field to Sum.
“Any percentage option will do.”
% of Grand Total, % of Row Total and % of Column Total divide by different bases and answer different questions. Say the question as “what share of X is Y”, and check that the option divides by X in your layout.
“A big number in a PivotTable is a trend.”
A sum can be one record. May’s high value is one office order. Check the records behind a surprising cell (filter the table, or sort by value) before you explain it.
“If the PivotTable is right, I don’t need to check it.”
Reconcile its grand total with a SUM of the source and spot-check a cell with SUMIFS. Stale caches, short source ranges and forgotten filters all produce totals that look perfectly reasonable.
Guided practice
Work through these with hints when you need them. Every hint and every worked solution is there to be used.
Loading four questions…
On your own
No hints this time. Answer, check, and read the feedback; the solution appears once you have answered or if you ask for it. Your first answers count toward mastery.
Loading six questions…
In practice
Lantern’s monthly review. Ana keeps one PivotTable sheet on the Orders table, with slicers for channel, region and subscription. At each month-end she pastes the cleaned orders into the table, clicks Refresh All, and checks that the grand total equals the SUM of order_total before she reads anything else. Questions that come up in the meeting (“what about the app in the North?”) are answered by clicking a slicer, not by writing new formulas.
A bookshop’s buying decisions. Cobble Lane Books, a fictional bookshop, exports its till data each week. The buyer’s PivotTable puts category in Rows and weeks in Columns, with Sum of sales and % of Row Total, to see whether each category’s sales are rising through the term; categories that appear for the first time (a new author’s series) show up at the next refresh without anyone editing a formula.
From exploration to a fixed report. Once the questions settle, an analyst often rebuilds the few figures that matter with SUMIFS on a report sheet: fixed layout, every category shown even when it is zero, instant updates. The PivotTable stays as the exploring tool and as the check: each month, the report’s SUMIFS figures must equal the PivotTable’s. The dashboards lesson builds on this.
Customers, not orders. A coffee-subscription start-up (fictional) reports “active customers” each month. Its analyst builds the PivotTable with “Add this data to the Data Model” ticked, puts the month in Rows and the customer id in Values as a Distinct Count, and puts Count of order id beside it. The gap between the two columns is the repeat buying the founders want to see, and it would vanish if someone counted ids without the Data Model.
Knowledge check
Six questions, one attempt each, no hints. Score 80% or more to complete the lesson; you can take it again at any time.
Loading six questions…
Summary
- A PivotTable groups the records of its source by the fields in Rows and Columns and aggregates a field in Values; Filters limit which records count.
- Build it on an Excel table of clean data: one header row of unique labels, one record per row, no totals inside the data.
- Numeric fields start as Sum; a field with text or blanks starts as Count, which counts non-empty values, like COUNTA. Distinct Count needs the Data Model.
- Show Values As gives % of Grand Total, % of Row Total and % of Column Total; choose the one whose base matches the question’s “share of what”.
- Group dates by months (and years), group numbers into equal intervals, and sort by labels or totals.
- A PivotTable summarises its cache: refresh after the data change, and build it on a table so new rows are included.
- Every value cell is a conditional aggregate: reconcile the grand total with SUM and spot-check cells with SUMIFS; use formulas for fixed, always-live reports.
Key formulas
Terms introduced
- PivotTable
- Excel’s interactive summary table: it groups the records of a source by the fields placed in its Rows and Columns areas and aggregates a field placed in its Values area, rebuilding when fields are moved.
- Pivot cache
- The copy of the source data that a PivotTable summarises; the PivotTable shows new or corrected data only after it is refreshed.
- Slicer
- An Excel panel of buttons that filters a table or PivotTable and shows which items are selected; one slicer can control several PivotTables that share the same data source.
- Distinct count
- The number of different values in a field, counting each value once however many records share it, such as the number of customers rather than orders.
Sources
- Microsoft Support. Create a PivotTable to analyze worksheet data. support.microsoft.com/office/18fb0032-b01a-4c99-9a5f-7ab09ed…
- Microsoft Support. Use the Field List to arrange fields in a PivotTable. support.microsoft.com/en-us/Excel/get-started/use-the-field-…
- Microsoft Support. Sum values in a PivotTable. Lists the summary functions, the Count default and Distinct Count. support.microsoft.com/en-us/excel/sum-values-in-a-pivottable
- Microsoft Support. Show different calculations in PivotTable value fields. support.microsoft.com/en-US/Excel/show-different-calculation…
- Microsoft Support. Refresh PivotTable data. support.microsoft.com/office/refresh-pivottable-data-6d24cec…
- Microsoft Support. GETPIVOTDATA function. support.microsoft.com/en-us/Excel/functions/getpivotdata-fun…
- Microsoft Support. When PivotTables don’t sort as expected. support.microsoft.com/en-gb/office/when-pivottables-don-t-so…