Excel Pivot Tables: A Step-by-Step Guide for Beginners
A pivot table turns a long list of rows into a short summary you can rearrange with a few clicks. Instead of writing dozens of SUMIFS formulas to answer "How much did each city sell per month?", you drag fields into place and Excel does the totalling for you.
The best way to learn pivot tables is to build one. Below is a small sales dataset you can type into a blank workbook (it takes about five minutes), followed by step-by-step instructions. The steps use Excel for Microsoft 365 on Windows; Excel 2016 and later, and Excel for Mac, work in much the same way, though some menu names differ slightly.
The Sample Dataset
Imagine a small accessories distributor with sales staff in three cities. Each row is one sale. Type these columns into Sheet1 starting at cell A1:
| Date | Region | Product | Salesperson | Units | Revenue (PKR) |
|---|---|---|---|---|---|
| 06/01/2025 | Lahore | Wireless Mouse | Ayesha | 10 | 12000 |
| 09/01/2025 | Karachi | Keyboard | Bilal | 4 | 12000 |
| 15/01/2025 | Islamabad | Laptop Bag | Sana | 6 | 15000 |
| 22/01/2025 | Lahore | Keyboard | Ayesha | 5 | 15000 |
| 03/02/2025 | Karachi | Wireless Mouse | Bilal | 12 | 14400 |
| 11/02/2025 | Islamabad | Keyboard | Sana | 3 | 9000 |
| 18/02/2025 | Lahore | Laptop Bag | Ayesha | 8 | 20000 |
| 25/02/2025 | Karachi | Laptop Bag | Bilal | 5 | 12500 |
| 04/03/2025 | Islamabad | Wireless Mouse | Sana | 9 | 10800 |
| 12/03/2025 | Lahore | Wireless Mouse | Ayesha | 7 | 8400 |
| 19/03/2025 | Karachi | Keyboard | Bilal | 6 | 18000 |
| 27/03/2025 | Islamabad | Laptop Bag | Sana | 4 | 10000 |
Dates are shown in day/month/year format. If your Windows regional settings use month/day/year, type them in the format your system expects so Excel recognises them as real dates rather than text. Prices are fixed per product (mouse PKR 1,200, keyboard PKR 3,000, laptop bag PKR 2,500), which makes it easy to check the pivot table's totals by hand.
Step 1: Prepare the Data Properly
Most pivot table problems start with messy source data. Before inserting anything, check that your list follows these rules:
- One header row with a unique, non-blank name in every column.
- No blank rows or columns inside the data.
- No merged cells and no subtotal rows mixed in with the data.
- One type of data per column: numbers in Units and Revenue (not text that looks like numbers), real dates in Date.
- Consistent spelling: "Karachi" and "karachi " (with a trailing space) will appear as two separate regions.
Next, convert the range into an Excel Table. Click any cell in the data and press Ctrl+T (Cmd+T on Mac), confirm "My table has headers", and click OK. Then, on the Table Design tab, rename it to something meaningful like SalesData.
Why bother? A Table expands automatically when you add new rows, so your pivot table will include them on the next refresh. With a plain range, new rows below the original area are easily missed.
Step 2: Insert the Pivot Table
- Click any cell inside SalesData.
- Go to Insert > PivotTable.
- Check that the table/range shows SalesData.
- Choose New Worksheet and click OK.
You'll see an empty pivot area on the left and the PivotTable Fields pane on the right. If the pane disappears, click inside the pivot area to bring it back.
Microsoft's own walkthrough, Create a PivotTable to analyze worksheet data, is useful if your screen looks different from these steps.
Step 3: Understand the Four Areas
The bottom of the Fields pane has four boxes. Everything you build is a combination of these:
- Rows: the categories listed down the left side.
- Columns: the categories spread across the top.
- Values: the numbers being calculated (sum, count, average and so on).
- Filters: a drop-down above the pivot table that limits which records are included.
Now build your first summary: drag Region to Rows and Revenue (PKR) to Values. You should see:
| Row Labels | Sum of Revenue (PKR) |
|---|---|
| Islamabad | 44800 |
| Karachi | 56900 |
| Lahore | 55400 |
| Grand Total | 157100 |
Check one figure by hand: Islamabad has four sales, 15,000 + 9,000 + 10,800 + 10,000 = 44,800. Verifying a total like this is a good habit whenever you build a new report.
Step 4: Add a Second Dimension
Drag Product into Columns. The pivot table now becomes a cross-tab of region by product:
| Region | Keyboard | Laptop Bag | Wireless Mouse | Grand Total |
|---|---|---|---|---|
| Islamabad | 9000 | 25000 | 10800 | 44800 |
| Karachi | 30000 | 12500 | 14400 | 56900 |
| Lahore | 15000 | 20000 | 20400 | 55400 |
| Grand Total | 54000 | 57500 | 45600 | 157100 |
Already you can see a story: Karachi leads overall because of keyboard sales, while laptop bags are the biggest product line. Try swapping Region and Product between Rows and Columns to see the same data from the other angle.
To make the numbers readable, right-click any value, choose Number Format (rather than "Format Cells", which may be lost when the pivot table refreshes or changes layout), and pick Number with a thousands separator and zero decimals.
Step 5: Group Dates by Month
Remove Product from Columns and drag Date into Rows above Region. In recent versions of Excel, dates are often grouped automatically into Years, Quarters and Months. If they aren't:
- Right-click any date in the pivot table.
- Choose Group.
- Select Months (and Years if your data spans more than one year), then click OK.
Your monthly totals should be January 54,000, February 55,900 and March 47,200. If Excel says "Cannot group that selection", at least one value in the Date column is text or blank. Fix the source data and refresh.
Step 6: Show Values as a Percentage of the Total
Raw totals don't always tell you about share. Go back to Region in Rows and Revenue in Values, then drag Revenue into Values a second time. Right-click the second column, choose Show Values As > % of Grand Total. You'll get:
- Islamabad: 28.5%
- Karachi: 36.2%
- Lahore: 35.3%
Rename the column header to something clear like "Share of revenue" by clicking it and typing. Other useful options in the same menu include % of Column Total, Difference From (great for month-on-month change) and Running Total In.
Step 7: Change the Calculation
Sum isn't the only option. Click the drop-down on a field in the Values box, choose Value Field Settings, and pick:
- Count to see the number of transactions per salesperson.
- Average to see average units per sale.
- Max or Min to find the largest or smallest sale.
Try putting Salesperson in Rows and Units in Values with Average. You can quickly see who makes larger orders, not just more of them.
Step 8: Add Slicers for Easy Filtering
Filters work, but slicers are friendlier for anyone viewing your report. Click inside the pivot table, go to PivotTable Analyze > Insert Slicer, tick Product and Region, and click OK. Clicking a button in a slicer filters the pivot table instantly; hold Ctrl to select more than one.
For dates, Insert Timeline (on the same tab) gives you a sliding month selector. If you create several pivot tables from the same data, right-click a slicer and use Report Connections to make one slicer control all of them, which is the basis of a simple Excel dashboard.
Step 9: Refresh When the Data Changes
Pivot tables don't update automatically when the source data changes. After adding new sales rows to SalesData:
- Right-click the pivot table and choose Refresh, or
- Use Data > Refresh All to update every pivot table and connection in the workbook.
If you'd like the workbook to refresh itself whenever it opens, go to PivotTable Analyze > Options > Data and tick "Refresh data when opening the file".
Common Mistakes and How to Fix Them
| Symptom | Likely cause | Fix |
|---|---|---|
| Values show "Count of Revenue" instead of Sum | Some revenue cells are text or blank | Convert text to numbers, fill blanks with 0, then change to Sum |
| New rows don't appear after refresh | Source is a fixed range, not a Table | Convert data to a Table (Ctrl+T) and update the data source |
| Same city appears twice | Inconsistent spelling or extra spaces | Clean with Find & Replace or the TRIM function |
| Can't group dates | Text dates or blanks in the Date column | Convert to real dates and refresh |
| Number formatting disappears on refresh | Formatted cells instead of the field | Use Value Field Settings > Number Format |
| Figures don't match another report | A filter or slicer is still applied | Check filters and clear slicers before comparing |
Where to Go Next
Once you're comfortable with the steps above, the next skills to learn are calculated fields, pivot charts, the Data Model (for combining several tables) and Power Query for cleaning data automatically. If you're working through those with structured practice, Data Analysis for Beginners: Excel & Pivot Tables covers the foundations, and the Advanced Data Analysis Bootcamp with Excel moves on to multi-table models and dashboards.
Two other directions are worth knowing about. If you build the same report every week, Excel Automation with Macros & VBA shows how to automate the repetitive steps. And if you get stuck on formulas or cleaning steps, AI assistants can help; ChatGPT for Excel covers practical ways to use them, and our guide to prompt engineering basics explains how to ask clearer questions. When your reports outgrow a single workbook, read Power BI vs Excel: When to Use Each for Business Reporting.
Frequently Asked Questions
Do pivot tables change my original data?
No. A pivot table reads from your source data and creates a separate summary. You can rearrange, filter or delete the pivot table without affecting the original rows.
Why does my pivot table show "(blank)"?
There are empty cells in the field you're summarising, or the source range includes empty rows beneath the data. Fill in missing values or limit the source to the actual data, ideally by using an Excel Table.
Can I use a pivot table with data from several sheets?
Yes, but not by simply selecting multiple ranges. The usual approach is to combine the sheets with Power Query or to load related tables into the Data Model and create relationships between them.
Are pivot tables available in Excel for Mac and Excel on the web?
Yes, both support pivot tables, including the core Rows, Columns, Values and Filters areas. Some advanced features differ between versions, so check Microsoft's support pages for your edition.
What's the difference between a pivot table and a pivot chart?
A pivot chart is a chart linked to a pivot table. When you filter or rearrange the pivot table, the chart updates to match, which makes it a quick way to visualise your summary.
Conclusion
With a clean Table, four field areas and a few right-click options, you can answer most everyday business questions in minutes rather than hours. Practise on the sample data above, then try the same steps on a real export from your own work. For guided exercises that build these skills step by step, start with Data Analysis for Beginners: Excel & Pivot Tables.