Concept
1. Practice data
pivot-practice.xlsx → Table tblSales (16 rows, Apr–Jun 2026):
| Date | Region | Category | Product | Salesperson | Qty | Amount |
|---|---|---|---|---|---|---|
| 05-04-2026 | North | Electronics | Laptop | Amit | 2 | 110000 |
| 08-04-2026 | South | Electronics | Mobile | Neha | 5 | 90000 |
| 12-04-2026 | West | Furniture | Chair | Ravi | 10 | 45000 |
| … | … | … | … | … | … | … |
Grand total of Amount = 10,70,000.
2. Create the pivot
Click inside the table → Insert → PivotTable → From Table/Range → New Worksheet → OK. Source = tblSales, so new rows are included on refresh.
3. The four boxes
Think of them as a sentence: "Show me [Values] by [Rows] and [Columns], only for [Filters]."
| Box | What it does | Example |
|---|---|---|
| Rows | one row per unique item | Region → North, South, West |
| Columns | one column per unique item | Category → Electronics, Furniture |
| Values | the number, summarised | Sum of Amount |
| Filters | limits the whole pivot | Salesperson = Amit |
Drag Region to Rows, Category to Columns, Amount to Values:
| Sum of Amount | Electronics | Furniture | Grand Total |
|---|---|---|---|
| North | 4,19,000 | 90,000 | 5,09,000 |
| South | 1,99,000 | 60,000 | 2,59,000 |
| West | 1,82,000 | 1,20,000 | 3,02,000 |
| Grand Total | 8,00,000 | 2,70,000 | 10,70,000 |
Swap Region and Category by dragging — same numbers, different view. That's the "pivot".
4. Text fields in Values = Count
Drop Product into Values and you get Count of Product (number of rows): North 6, South 5, West 5. Numbers default to Sum; if a number column has blanks or text, Excel may switch to Count — check the label.
5. Nesting
Put Region then Product in Rows → product rows inside each region with subtotals. Order in the box = order of nesting.
6. Make it look like a report
PivotTable Design tab:
- Report Layout → Show in Tabular Form — each field in its own column.
- Report Layout → Repeat All Item Labels — no blanks, copy-paste friendly.
- Subtotals → Do Not Show Subtotals when not needed.
- Number format: right-click a value → Number Format (not Format Cells — that's lost on refresh).
- Rename "Sum of Amount" by typing over the header (e.g. "Sales ").
7. Refresh
Pivots don't update automatically. Right-click → Refresh or Alt + F5; Data → Refresh All (Ctrl + Alt + F5) updates every pivot and query. PivotTable Options → Data → Refresh data when opening the file for reports others open.
Common mistakes
Building from a fixed range (A1:G16) instead of a Table — new rows are missed. Formatting with Format Cells (lost on refresh). Forgetting to refresh after changing data.