📎 This article includes 1 downloadable practice file ↓
In this article
“Total sales by region.” “Orders per customer.” “Each product’s sales by month.” With formulas each of these takes several SUMIFS. With a pivot table each takes about ten seconds of dragging, and you can rearrange it as fast as people ask new questions.
Create the pivot
- Click any cell in your data (a clean list or, better, an Excel Table).
- Insert › PivotTable › New Worksheet › OK.
- A blank pivot appears on the left and the PivotTable Fields pane on the right, listing your column headers.
The four areas
| Area | What goes there | Example |
|---|---|---|
| Rows | Categories down the side | Region |
| Columns | Categories across the top | Product |
| Values | Numbers to summarise | Amount |
| Filters | A filter for the whole pivot | Customer |
Drag Region to Rows and Amount to Values. You now have sales by region with a grand total. Drag Product to Columns: a region × product grid. Drag it back out to remove it. Nothing you do here changes your data.
Sum, Count, Average
Numbers are summed by default. Click the field in Values › Value Field Settings to choose Count (how many orders), Average, Max or Min. Drag the same field into Values twice to show both Sum and Count side by side.
Group dates by month
Drag Date to Rows. Recent Excel groups it into Months automatically; if not, right-click a date › Group › tick Months (and Years if your data spans more than one). Now you have monthly sales in one step.
Show % of total
Value Field Settings › Show Values As › % of Grand Total. Each region now shows its share. Other options: % of Column Total, Difference From (vs last month), Running Total.
Sort, filter and slicers
- Right-click a number › Sort › Largest to Smallest to rank regions or customers.
- Use the arrow next to Row Labels › Value Filters › Top 10 for the top 5 customers.
- PivotTable Analyze › Insert Slicer › Product: clickable buttons that filter the pivot. One slicer can control several pivots (right-click › Report Connections).
Refresh
Pivots don’t update by themselves. After changing data, right-click the pivot › Refresh (or Data › Refresh All). If your source is an Excel Table, new rows are included automatically; with a plain range you’d have to change the source.
Where beginners go wrong
| Problem | Cause |
|---|---|
| “Count of Amount” instead of Sum | Some amounts are text or blank; fix the data or change to Sum |
| New data missing | Not refreshed, or source range too small; use a Table |
| (blank) row appears | Empty cells in the field; fill or filter them out |
| Can’t group dates | Some dates are text; convert them |
The full guide: pivot tables complete guide.
Practice
Download this lesson’s workbook below. The Sales sheet has 120 orders from July to September 2026; each question is a number your pivot should show. Type your answers in the yellow column; the Check column turns green when you’re right, and the Answers sheet shows a working formula for every task.
📎 Practice files for this article
- 📗Lesson 17 practice workbook120 orders over three months: build pivots by region, customer, product and month, and check the numbers.⬇ XLSX · 19 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.