What pivot tables do
A pivot table summarizes a data list without formulas. You drag fields into four areas and Excel counts, sums or averages the data in the arrangement you choose. You can change the arrangement instantly, which makes pivots ideal for exploring questions: revenue by region, orders by month, average spend by customer type.
| Pivot area | What it does | Example |
|---|---|---|
| Rows | Creates one row for each distinct value | Region |
| Columns | Creates one column for each distinct value | Quarter |
| Values | The numbers to summarize and how | Sum of revenue |
| Filters | Limits the whole table to a subset | Product category |
Prepare the data first
Most pivot problems come from messy source data. Before you insert a pivot, check these points.
- One header row Every column has a unique, non-blank heading.
- One record per row Each row is a single transaction or case, with no totals mixed in.
- No blank rows or columns A gap in the data can split the range.
- No merged cells Merged cells break sorting and grouping.
- Consistent data types Numbers stored as numbers, dates as true dates, and categories spelled the same way.
- Format as a Table Select the data and press Ctrl + T. The pivot then grows automatically when you add rows.
Clean spelling differences such as North, north and N. before pivoting, or they will appear as separate rows. Our guide on data analysis reports covers documenting your cleaning steps.
Build a pivot table step by step
- Click inside the data table and choose Insert, then PivotTable. Pick a new worksheet.
- Drag Region to Rows, Quarter to Columns and Revenue to Values. Excel sums by default for numbers.
- Check the Value Field Settings to choose Sum, Count, Average, Max or Min, and set a number format.
- Add a field such as Product to Filters to look at one product at a time.
- Sort rows by value, and turn off any subtotals you do not need.
Result: revenue by region and quarter (hypothetical, $000)
| Region | Q1 | Q2 | Q3 | Q4 | Total |
|---|---|---|---|---|---|
| North | 120 | 135 | 150 | 180 | 585 |
| South | 90 | 95 | 110 | 120 | 415 |
| East | 60 | 80 | 70 | 100 | 310 |
| Total | 270 | 310 | 330 | 400 | 1,310 |
Quick reads from the pivot: North is the largest region with 585, which is 44.7 percent of the total. Q4 is the strongest quarter at 400, which is 48 percent higher than Q1 (400 divided by 270). East dipped in Q3.
Show values as percentages, growth and running totals
The same pivot can answer different questions by changing how values are shown. Right-click a value and choose Show Values As.
| Setting | What it shows | Example in the table above |
|---|---|---|
| % of Grand Total | Each cell as a share of everything | North total is 44.7 percent, South 31.7 percent, East 23.7 percent |
| % of Row Total | Each cell as a share of its row | North's Q4 share is 180 / 585, about 30.8 percent |
| % of Column Total | Each cell as a share of its column | North's share in Q1 is 120 / 270, about 44.4 percent |
| Running Total In | Cumulative total across a field | Cumulative revenue by quarter: 270, 580, 910, 1,310 |
| Difference From | Change versus a chosen item or previous period | Q4 versus Q3: +70 |
| % Difference From | Percent change versus a baseline | Q4 versus Q1: +48.1 percent |
A calculated field lets you add your own measure, such as profit as revenue minus cost, and a calculated item works on categories. Use them sparingly, since they can behave unexpectedly in totals.
Grouping dates and numbers
If your data has transaction dates, drag the date field to Rows. Excel can group it into months, quarters and years. Right-click a date, choose Group and select the levels you need. For numbers, grouping creates bins, such as order sizes in steps of 50. This is how you build a quick frequency table without formulas.
If grouping is unavailable, the usual cause is blank cells or text in the date column. Clean the data and refresh.
Working on this assignment now? Get a price for help with your paper.
Get an instant quoteSlicers, timelines and pivot charts
Slicers are buttons that filter a pivot. Timelines do the same for dates. Select the pivot, choose Insert, then Slicer and tick the fields. Connect one slicer to several pivots by right-clicking it and using Report Connections, so one click filters the whole dashboard. A pivot chart is a chart linked to a pivot, which changes when you filter or rearrange the pivot.
- Refresh after data changes Right-click the pivot and choose Refresh, or use Data, then Refresh All.
- Use a table as the source Then new rows are included automatically.
- Keep pivots on a separate sheet Do not type next to a pivot, as it can resize.
- Clear or limit the cache Large pivots can slow a workbook, so avoid copying the data repeatedly.
Design a one-page dashboard
A dashboard answers a few questions at a glance. Decide the questions before you design, such as are we growing, where, and what changed. Then follow a simple layout.
| Zone | Contents | Notes |
|---|---|---|
| Top | Key figures (KPIs) in large type | Total revenue, growth versus last period, best region, number of orders |
| Middle | Two to four charts, each answering one question | A line chart for trend, a bar chart for comparison |
| Side or top strip | Slicers and timelines | Group them together and label them |
| Bottom | A small table or notes | Definitions and the data source and date |
| KPI | How to calculate it |
|---|---|
| Total revenue | Sum of the revenue field, linked to a cell with =GETPIVOTDATA or a direct formula |
| Growth versus previous period | (This period minus last period) divided by last period |
| Share of top region | Top region revenue divided by total revenue |
| Average order value | Revenue divided by number of orders |
Put the data on one sheet, pivots on another and the dashboard on a third that only references the pivots. This keeps the dashboard clean. Turn off gridlines on the dashboard sheet, use two or three colors consistently, and align charts to a grid.
Designing a one-page dashboard
| Area | Contents | Tip |
|---|---|---|
| Top row | Three to five key numbers (revenue, margin, orders, growth) | Compare each with last period or target |
| Middle | One trend chart and one breakdown chart | Same colors for same categories |
| Bottom | A detail table or top and bottom lists | Sort so the point is obvious |
| Side | Slicers for region, product and period | Connect them to every pivot table |
- Start with the question What decision does this dashboard support?
- Use fewer charts Five clear charts beat fifteen cluttered ones.
- Refresh and test Check totals against the source after refreshing.
- Label units and periods Currency, percent and date range.
Common mistakes
- Messy source data Blanks, merged cells and inconsistent labels are the main cause of broken pivots.
- Numbers stored as text They show Count instead of Sum. Convert them to numbers.
- Forgetting to refresh Pivots do not update automatically when the data change.
- Too many charts A dashboard with ten charts says nothing. Choose the few that matter.
- Unlabeled figures Always include units, the period and the data source.
- Hard-coded numbers Link dashboard figures to pivots or formulas so they stay current.
If you want help building a pivot analysis or dashboard, you can order Excel assignment help.