A Pivot Table is the single most powerful analytical tool in Excel. With a few mouse clicks, it can transform thousands of rows of raw data into a concise, interactive summary — totals by region, averages by department, counts by category, with instant drill-down and the ability to switch perspectives in seconds. Where a formula-based report takes hours to build and is difficult to modify, a Pivot Table takes minutes and can be completely restructured by dragging and dropping fields. This module covers creating, formatting, customising, and interrogating Pivot Tables, as well as the Pivot Chart that brings them to life visually.
A Pivot Table is a dynamic summary table that groups, aggregates, and reorganises data from a source dataset. It "pivots" your data — the same underlying figures can be summarised in completely different ways depending on which fields you place in which areas.
| Area | What It Does | Example Fields |
|---|---|---|
| Rows | Each unique value in this field becomes a row label on the left side of the Pivot Table | Region, Department, Salesperson, Product, Month |
| Columns | Each unique value becomes a column header across the top | Quarter, Year, Status, Product Category |
| Values | The numeric field to summarise (Sum, Count, Average, Max, Min, etc.) — displayed at each row/column intersection | Revenue, Salary, Quantity, Units Sold, Hours |
| Filters | A dropdown filter at the top of the entire Pivot Table — filters the entire table to show only selected values | Year, Country, Business Unit, Product Line |
When any cell inside a Pivot Table is selected, the PivotTable Analyse contextual tab appears alongside the Design tab. PivotTable Analyse controls the data, layout, and tools of the Pivot Table.
| Group | Key Tools | What They Do |
|---|---|---|
| PivotTable | PivotTable Name, Options | Name the Pivot Table for reference; access all PivotTable Options settings (layout, formatting, totals) |
| Active Field | Field Settings, Expand/Collapse | Open Field Settings for the selected field (change aggregation, number format, name); expand/collapse row groupings |
| Group | Group Selection, Ungroup, Group Field | Group dates into months/quarters/years; group numbers into ranges (bins); group selected items into a custom group |
| Filter | Insert Slicer, Insert Timeline, Filter Connections | Add Slicers and Timelines for visual filtering; connect filters to multiple Pivot Tables |
| Data | Refresh, Change Data Source, Clear | Refresh (updates the Pivot Table after source data changes); Change Data Source (point the Pivot Table to a different range/table); Clear (remove all fields or all filters) |
| Actions | Clear, Select, Move PivotTable | Clear filters or entire table; select only values or labels; move the Pivot Table to a different location |
| Calculations | Fields, Items & Sets; Calculated Field; Calculated Item; Solve Order | Add calculated fields (formulas using other field values); add calculated items; manage calculation order |
| Tools | PivotChart, Recommended PivotTables | Create a linked PivotChart; get AI-suggested Pivot Table layouts |
| Show | Field List, +/- Buttons, Field Headers | Show/hide the Field pane; show/hide expand/collapse +/- buttons; show/hide the Rows/Columns headers row |
By default, numeric fields dragged to the Values area are summarised by Sum. Text fields are summarised by Count. You can change the aggregation function and control how the values are displayed.
| Function | What It Calculates | SA Use Case |
|---|---|---|
| Sum | Total of all values | Total revenue, total salary, total units sold |
| Count | Number of non-blank entries (including text) | Count of invoices, count of employees per department |
| Average | Mean of all values | Average salary, average order value, average score |
| Max | Largest value in the group | Highest invoice per region, maximum salary per band |
| Min | Smallest value in the group | Lowest stock level per product, minimum order value |
| Product | Multiplied product of all values | Rarely used; compound growth calculations |
| Count Numbers | Count of cells containing numbers only (ignores text) | Count numeric entries vs all entries for completeness check |
| StdDev / StdDevp | Standard deviation (sample / population) | Salary dispersion analysis, quality control |
| Var / Varp | Variance (sample / population) | Statistical analysis of performance data |
The "Show Values As" tab in Value Field Settings allows you to display each value as a calculation relative to others — without adding any formula columns to your source data.
| Show Values As Option | Displays | Use Case |
|---|---|---|
| % of Grand Total | Each value as a % of the overall grand total | Each region's share of total company revenue |
| % of Column Total | Each value as a % of its column subtotal | Each product's share within each quarter |
| % of Row Total | Each value as a % of its row subtotal | Each quarter's share of the region's annual total |
| Running Total In | Cumulative total within a field (e.g., Jan, Jan+Feb, Jan+Feb+Mar) | Year-to-date revenue cumulation |
| % Running Total In | Cumulative % through the series | Pareto analysis — top products by cumulative % |
| Rank Smallest to Largest | Rank (1 = smallest) | Rank regions by revenue |
| Rank Largest to Smallest | Rank (1 = largest) | Leaderboard ranking of salesperson performance |
| Difference From | Difference between current value and a reference (e.g., previous month) | Month-on-month change in sales |
| % Difference From | % change from a reference value | Month-on-month % growth rate |
The Design contextual tab controls the visual appearance, layout, and subtotal/grand total settings of the Pivot Table.
| Layout | Appearance | Best For |
|---|---|---|
| Compact Form (default) | All row fields are in one column, indented. Labels are compact. Row field names do not show as headers. | General use; saves horizontal space; good for drilling down into hierarchies |
| Outline Form | Each row field is in its own column. Shows field names as column headers. Subtotals appear above each group. | When you need to see which field is in which column; when copying to another spreadsheet |
| Tabular Form | Each row field in its own column. Subtotals appear below each group. The closest layout to a flat table. Easiest to read and use as a data source. | When you want to copy and use the Pivot Table data elsewhere; for reports that must look like a standard table |
| Setting | Options |
|---|---|
| Subtotals | Do Not Show Subtotals / Show all Subtotals at Top / Show all Subtotals at Bottom |
| Grand Totals | Off for Rows and Columns / On for Rows and Columns / On for Rows Only / On for Columns Only |
Grouping automatically consolidates individual values into summary groups. This is one of the most powerful Pivot Table features — it allows you to summarise daily data by month, weekly data by quarter, or continuous numbers into bands.
| Filter Method | How |
|---|---|
| Row/Column filter dropdown | Click the dropdown ▼ on the Row or Column field → untick values to hide, or use Label Filters / Value Filters / Date Filters for more options |
| Report Filter (Filters area) | Drag a field to the Filters area → a dropdown appears above the Pivot Table → select a value to filter the entire table to that value |
| Slicer | PivotTable Analyse → Insert Slicer → select fields. Clickable button panels that filter visually and can connect to multiple Pivot Tables. |
| Timeline | PivotTable Analyse → Insert Timeline → select date field. A continuous date range selector for time-based filtering. |
| Top 10 Filter | Row/Column dropdown → Value Filters → Top 10… → set top/bottom N items by any value field. Shows only the top N rows/columns by a selected measure. |
A Calculated Field creates a new virtual field in the Pivot Table using a formula that references other fields. It appears as a new column in the Values area and is calculated on the summarised data.
= Revenue - Cost or = Revenue / Units)A Pivot Chart is a chart that is directly linked to a Pivot Table. When you filter, sort, or change the Pivot Table, the Pivot Chart updates automatically. It has filter buttons built into the chart itself, making it interactive without needing separate slicers.
— or —
A Pivot Chart automatically shows field filter buttons at the bottom of the chart. These are interactive — clicking them filters both the chart and the connected Pivot Table. To hide the filter buttons for a cleaner chart appearance: right-click any filter button on the chart → Hide All Field Buttons on Chart.
| Aspect | Regular Chart | Pivot Chart |
|---|---|---|
| Data source | Fixed cell range | Connected Pivot Table (dynamic) |
| Filtering | No built-in filter controls | Built-in filter buttons; responds to Slicers |
| Flexibility | Static; must manually change source data to update | Drag-and-drop fields to change what is charted; always reflects the current Pivot Table |
| Complexity | Can plot any data arrangement | Limited to the Pivot Table's current structure |
Q1: You have a flat table with 5,000 rows of sales transactions: Date, Region, Product, Revenue, Quantity. Using a Pivot Table, what fields would you put in which areas to show total revenue by product for each region?
✓ Rows area: Region (each region becomes a row). Columns area: Product (each product becomes a column header). Values area: Sum of Revenue (the total revenue appears at the intersection of each region/product combination). Optionally: Filters area: Date or Year (to filter by time period). The result is a cross-tabulation (matrix) with regions in rows, products in columns, and revenue totals in each cell — showing at a glance which products perform best in which region. With 9 SA provinces and say 20 products, you get a 9×20 summary matrix from 5,000 raw rows in about 30 seconds.
Q2: Your Pivot Table shows Revenue by Region but you want to also see what percentage of the total revenue each region represents, without adding any formulas to the source data. How do you do this?
✓ Drag the Revenue field into the Values area a second time (so it appears twice). Then for the second Revenue field: right-click any value in that column → Show Values As → % of Grand Total. The second column now shows each region's revenue as a percentage of the overall total. Format the second column as Percentage (right-click → Number Format → Percentage). Rename the second field header to "% of Total" by clicking Value Field Settings → Custom Name. The Pivot Table now shows both the absolute revenue AND the percentage share side-by-side without any source data changes.
Q3: Your source data has individual transaction dates. After creating the Pivot Table with Date in the Rows area, you see individual dates (01/01/2025, 02/01/2025, etc.) instead of a monthly summary. How do you group them by month and year?
✓ Right-click any date value in the Pivot Table Rows area → Group… → in the Grouping dialog, hold Ctrl and click both Months and Years in the list (so both are selected/highlighted) → click OK. The Pivot Table now groups daily dates into Year → Month hierarchy. Clicking + next to a year expands it to show the months within that year. If you only want months (not the year level), select only Months in the dialog. Note: if the grouping option is greyed out, it usually means there are text values or blank cells in the date column of the source data — fix those first.
Q4: You update 200 new rows in the source data table. The Pivot Table still shows the old totals. What do you do to see the updated data?
✓ The Pivot Table must be refreshed manually. Right-click anywhere inside the Pivot Table → Refresh. Or: PivotTable Analyse tab → Data group → Refresh. Or press Alt+F5. To refresh all Pivot Tables in the workbook at once: PivotTable Analyse → Data → Refresh All, or Ctrl+Alt+F5. If the source data is an Excel Table (Ctrl+T), only a Refresh is needed — the Pivot Table will include all rows in the Table. If the source is a plain range, you must also verify that the data range in "Change Data Source" includes the new rows, otherwise the Pivot Table will not see them even after refresh.
Q5: You need a Pivot Table that shows Revenue by Region, but you also need a "Profit Margin %" column calculated as (Revenue - Cost) / Revenue for each region. Revenue and Cost are both fields in your source data. How do you add the Profit Margin calculation to the Pivot Table?
✓ Use a Calculated Field: click inside the Pivot Table → PivotTable Analyse → Calculations → Fields, Items & Sets → Calculated Field. Name: "Profit Margin %" and Formula: =(Revenue-Cost)/Revenue. Click Add → OK. The new field appears as an additional column in the Values area. Format it as percentage: right-click values in the new column → Number Format → Percentage → 2 decimal places. Note that this calculated field operates on the aggregated values (total revenue minus total cost, divided by total revenue for each region) rather than on individual row values, which is the correct behaviour for a regional profit margin summary.
Q6: What is the main advantage of using an Excel Table (Ctrl+T) as the source for a Pivot Table, compared to using a plain cell range?
✓ When the Pivot Table source is an Excel Table, any new rows added to the bottom of the Table are automatically included in the Pivot Table the next time you Refresh — no need to update the source range. With a plain cell range (e.g., $A$1:$F$5000), if new rows are added below row 5000, they fall outside the defined range and the Pivot Table misses them even after Refresh. You would have to go to PivotTable Analyse → Change Data Source and manually extend the range to include the new rows. The Table approach eliminates this maintenance overhead entirely. Additionally, Table column names are always used as Pivot Table field names regardless of position, so inserting or reordering columns in the source does not break the Pivot Table field references.