Build a PivotTable and prove that its totals are right

Summarize six orders by month and region, distinguish money totals from record counts, and refresh after a controlled change.

Before you start

Windows desktop Excel 2016, 2019, 2021, 2024 and Microsoft 365. Mac and Excel for the web also provide PivotTables, but their insertion panes and field controls differ. This lesson uses a worksheet table, without the Data Model or recommended layouts.

Useful first: Turn an order range into a growing Excel table · Answer the same business question with a sum and a count

By the end, you can…

Place categories and measures in the correct PivotTable areas.
Reconcile a summary against the original six records.
Refresh deliberately after changing the source table.

YOUR PRACTICE FILE

Small data. A result you can check.

Synthetic data. Paste at A1 after formatting column B as Text. Amounts use arbitrary currency units.

Download practice data

View the raw practice data

OrderID	Month	Region	Amount
O101	2026-01	North	120
O102	2026-01	South	80
O103	2026-01	North	40
O104	2026-02	North	60
O105	2026-02	South	100
O106	2026-02	South	50

01Prepare one record per order

On a sheet named Orders, format column B as Text before pasting the sample into A1:D7. The month values are deliberate text keys, not dates to group automatically. Select A1:D7, choose Insert > Table, confirm the headers, and name the table OrdersData on Table Design.

Each amount belongs to one order. Do not add a subtotal row to these six records: a PivotTable would summarize that extra value again. Check that D2:D7 contains six numbers and sums to 450 before building any report.

=SUM(D2:D7)

Expected result: 450. Separately, =COUNT(D2:D7) must return 6; a correct-looking currency format does not establish numeric storage.

02Build the cross-tab deliberately

Click inside OrdersData, choose Insert > PivotTable, and place it on a new worksheet. Drag Month to Rows, Region to Columns, and Amount to Values. Open the value field settings and verify Sum, not Count. Keep both month keys and both regions visible.

Your labels and starting cell may differ from this layout. Compare the labeled intersections, not an assumed PivotTable cell address. The four detail totals must reconcile both horizontally and vertically.

Month	North	South	Grand total
2026-01	160	80	240
2026-02	60	150	210
Grand total	220	230	450

03Diagnose a count or an outdated result

A Count of Amount report answers a different question: January contains three orders and February contains three. It does not show revenue. If Sum gives incomplete results, inspect the original amount cells for text, fix their storage, and refresh; changing the summary label alone cannot repair missing numeric values.

A worksheet edit does not reliably mean the PivotTable already reflects it. Right-click the PivotTable and choose Refresh after updating the source. A table can include newly appended source records, but a fixed range ending at row 7 would need its source range expanded first. Before sharing, clear unintended report filters and compare the displayed grand total with the source again.

PUT IT INTO PRACTICE

Add one February order and explain every changed subtotal.

Enter O107, 2026-02, North and 30 in Orders!A8:D8. Preserve Month as text and confirm the table expands through row 8.
Check =SUM(OrdersData[Amount]) and =ROWS(OrdersData[OrderID]) in spare cells.
Refresh the PivotTable. Check February/North, the February total, the North total and the grand total.

Show the worked answer

The source contains 7 orders totaling 480.

February/North becomes 90; February totals 240; North totals 250; the grand total becomes 480. January stays 240 and South stays 230.

Check your work

A remaining 450 usually means stale results or an unexpanded source, not a rounding issue.
A filtered PivotTable can legitimately show less than the complete source; inspect filters before declaring a mismatch.

CHECK YOUR UNDERSTANDING

One question before you move on.

The value field says Count of Amount and shows 6. What does this establish?Six amount entries were counted; it does not establish revenue of 6.Revenue is 6 because the field came from Amount.The six orders must be duplicates.

Ready for the next step?

Mark this lesson when you can explain the idea and reproduce the practice result.

Your checklist stays on this browser.

NEXT LESSONCreate a chart that answers one clear question

Keep the skill close.

FIELD GUIDEExcel SUM Returns Zero or Misses Values: Convert Numbers Stored as Text FIELD GUIDEExcel SUMIFS Date Range: Include the Whole End Date, Even with Times WORKSPACE TOOLCSV import checker

Reference notes

These lessons use original examples. Check Microsoft’s documentation for details and platform-specific options.

Prepared September 13, 2026 · Column Harbor editorial team. Menu locations and available functions can vary by Excel version.