PivotTables - Summarize source records
Turn sales transactions into regional totals, compare products, and refresh a reliable summary.
By the end, you can
- Build a PivotTable from a sales Table.
- Choose rows, columns, and a numeric aggregation.
- Reconcile totals and refresh after adding records.
Before you start: Organize data with one header row and consistent column types. Understand Excel Tables and basic totals.
In this lesson
From transactions to a useful summary
A PivotTable groups source records and calculates summaries without rewriting the original data. Use it to answer questions such as revenue by region, attendance by department, or orders by product. Dragging fields changes the view; it does not rearrange the source records.
Sales transactions
A2fx
O101| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order ID | Region | Product | Revenue INR |
| 2 | O101 | North | Paper | 12000 |
| 3 | O102 | South | Pens | 8000 |
| 4 | O103 | North | Paper | 18000 |
| 5 | O104 | South | Paper | 10000 |
| 6 | O105 | East | Pens | 6000 |
ExampleBlue: input · Green: result
Build the regional sales report
- Select a source cell, press Ctrl+T, confirm the full range and My table has headers. On Table Design, name the Table SalesData.
- Choose Insert > PivotTable, confirm SalesData as the source, and select New Worksheet. Click OK.
- In PivotTable Fields, drag Region to Rows and Product to Columns.
- Drag Revenue INR to Values. Open Value Field Settings and select Sum. If Excel chose Count, inspect the source for text amounts; renaming Count to Sum does not change the calculation.
- In Value Field Settings > Number Format, choose Currency with the ₹ symbol. This keeps the values numeric.
- Sort Region A to Z and arrange the product columns Paper, then Pens. Choose Design > Grand Totals > On for Rows and Columns.
- For this example, open PivotTable Options > Layout & Format and set For empty cells show to 0. Without this setting, combinations with no records may appear blank.
- Compare the grand total with the source ₹54,000 before sharing the report.
Expected PivotTable
B3fx
30000| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Paper | Pens | Grand total |
| 2 | East | 0 | 6000 | 6000 |
| 3 | North | 30000 | 0 | 30000 |
| 4 | South | 10000 | 8000 | 18000 |
| 5 | Grand total | 40000 | 14000 | 54000 |
ExampleBlue: input · Green: result
Change the question without rebuilding
- Move Product below Region in Rows to see product details within each region.
- Filter Region to North: the displayed revenue is ₹30,000. A filtered report answers a narrower question than the full ₹54,000 source.
- With all regions visible, add Revenue INR a second time. Set Show Values As > % of Grand Total and format as Percentage: North is 30,000 ÷ 54,000 = 55.56% rounded to two decimals.
- Double-click a summary value to extract its supporting records to a new worksheet. Treat that extract as a snapshot, not an automatically updating source.
Refresh when source records change
- Append O106, East, Paper, 2000 inside SalesData.
- Right-click the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh.
- Check East Paper = ₹2,000, East total = ₹8,000, Paper total = ₹42,000, and the grand total = ₹56,000.
Common mistakes and fixes
- Count instead of Sum: convert numeric-looking text to numbers, refresh, and select Sum in Value Field Settings.
- Unexpected categories: standardize spellings and remove unwanted spaces before building the summary.
- Double-counted totals: exclude source subtotal and grand-total rows; use only detail records.
- Missing fields or records: check the source Table, field placements, and report filters before changing calculations.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.