Analyze with pivot tables
Want more background?
The idea in everyday life
Start with one question: how much did each category earn from paid orders? Choose the groups, the numbers to add, and the orders to include. You can check each result against the original rows before using the finished sheet.
A pivot table summarizes records by a chosen category. Decide what to measure, how to group it, and which records to include.
Build a summary you can check
A pivot table groups rows and calculates a summary for each group. Start with one question: how much did each category earn from paid orders?
Open a blank spreadsheet and name its sheet Orders. Copy the table below, click A1 once, and paste. Each header belongs in its own column. Prices are in USD; enter the numbers without typing a dollar sign.
Six orders from a school fundraiser
Paste at A1.
| AOrder | BCategory | CStatus | DUnits | EUnit price |
|---|---|---|---|---|
| 101 | Books | Paid | 2 | 12 |
| 102 | Games | Paid | 1 | 30 |
| 103 | Books | Canceled | 1 | 12 |
| 104 | Books | Paid | 3 | 12 |
| 105 | Games | Paid | 2 | 30 |
| 106 | Books | Paid | 1 | 18 |
Copy as plain text
1. Prepare the values to summarize
Type Total in F1. Enter =D2*E2 in F2, then copy F2 into F3:F7. Select A1:F7, including the headers and all six orders. Leave any other summaries out of this selection.
You should see: The Total column is 24, 30, 12, 36, 60, 18. It includes the canceled order for now.
2. Create and set up the first pivot
Use the instructions for your spreadsheet app below. Put the summary on a new sheet.
- With A1:F7 selected, choose Insert → PivotTable. Choose New Worksheet, then OK. In Excel for the web, the pane may say New sheet.
- In PivotTable Fields, drag Category to Rows, Total to Values, and Status to Filters. If Values says Count of Total, open Value Field Settings and select Sum.
- Open the Status filter above the pivot, select Paid, and apply it. If the field pane closes, click inside the pivot to reopen it.
- After changing an order, right-click inside each pivot and choose Refresh. If you add orders beyond row 7, update the pivot’s data source first.
You should see: The new sheet shows a summary of paid sales by category. Check the setup and totals below.
3. Check the fields and result
Confirm that Rows is Category, Values is SUM of Total, and the Status filter includes only Paid.
You should see: Books: 78. Games: 90. Grand total: 168.
Rows names the groups; Values chooses what to calculate. SUM adds money. COUNT would show 3 Books orders and 2 Games orders, which answers a different question.
4. Make a second view
Keep the first summary. Create another pivot from Orders!A1:F7 on a new sheet. Use Status for Rows and SUM of Total for Values. Include both statuses.
You should see: Canceled: 12. Paid: 168. Grand total: 180.
The summaries agree: all orders (180) minus canceled orders (12) equals paid orders (168). Write those filters beside the summaries so someone else can compare them.
More practice and troubleshooting
If the totals differ
Check that the source is A1:F7, Values uses SUM, and the first pivot includes only Paid. Make sure Units and Unit price are numbers, not text. Compare totals after applying the same filter, then refresh in Excel.
What to finish
Two pivot summaries (paid sales by category and all order values by status), plus a note explaining the canceled-order test.
Watch for this
Using COUNT when you mean to add money, or comparing totals with different filters.
How can I tell I understand it?
You can build the two summaries, explain why 168 differs from 180, and check the result after changing an order.
Quick knowledge check
Answer all 3 questions. Get at least 2 right and the lesson completes automatically.
You’ll get an explanation for each answer. Signed in? Your results save to your account.
Pass the check. We’ll save the progress.
Answer all 3 questions above and get at least 2 right. You can review the explanations and try again.
Go to the knowledge checkOptional course overview video · same video as Lesson 1
Pivot Table Excel Tutorial
From Kevin Stratvert. Watch here or open it on YouTube .
Follow the exact pivot mechanics in the video, then change one field at a time and explain how the business meaning changes.Tools, templates, and lesson sources
Tools and templates
Tools open in a new tab so you can keep your place here.
Sources and further reading
Explore the references behind this lesson.
Report a problem with this lesson
Tell us what went wrong. Please leave out personal information and private project contents.