A pivot table turns a long list of rows into a clear summary in a few clicks: total sales by month, orders by region, expenses by category. You do not need any formulas. This step-by-step guide takes you from raw data to a finished report.
Quick answer
- Click any cell inside your data.
- Go to Insert → PivotTable and click OK.
- Drag a field to Rows (for example, Region) and a number field to Values (for example, Sales).
That is a working pivot table. The rest of this guide shows how to make it useful.
Step 1: Prepare your data
Pivot tables work best with a clean, simple list:
- One header row, with a unique name in every column
- No empty rows or columns in the middle
- No merged cells
- One type of data per column (dates in a date column, numbers in a number column)
We will use this sample sales list (A1:E9):
| Date | Region | Salesperson | Product | Sales |
|---|---|---|---|---|
| 05/01/2026 | North | Sara | Laptop | 650 |
| 08/01/2026 | South | Omar | Monitor | 210 |
| 14/01/2026 | North | Sara | Monitor | 195 |
| 03/02/2026 | East | Lina | Laptop | 720 |
| 11/02/2026 | South | Omar | Laptop | 680 |
| 19/02/2026 | East | Lina | Printer | 150 |
| 02/03/2026 | North | Yousef | Printer | 140 |
| 15/03/2026 | South | Omar | Monitor | 230 |
Tip: press Ctrl + T to turn the list into an Excel Table first. When you add new rows later, the pivot table picks them up on refresh.
Step 2: Insert the pivot table
- Click any cell in the data.
- Go to Insert → PivotTable. (In some versions: Insert → PivotTable → From Table/Range.)
- Check that the range is correct, choose New Worksheet, and click OK.
A new sheet opens with an empty pivot table on the left and the PivotTable Fields pane on the right.
Step 3: Build the summary
The Fields pane has four areas:
| Area | What it does | Example |
|---|---|---|
| Rows | Groups data down the left side | Region |
| Columns | Groups data across the top | Product |
| Values | The numbers to calculate | Sum of Sales |
| Filters | Filters the whole report | Salesperson |
Drag Region to Rows and Sales to Values. You now see total sales per region:
| Row Labels | Sum of Sales |
|---|---|
| East | 870 |
| North | 985 |
| South | 1,120 |
| Grand Total | 2,975 |
Now drag Product to Columns to see each region split by product.
Step 4: Change Sum to Count or Average
- Click the Sum of Sales field in the Values area.
- Choose Value Field Settings.
- Pick Count (number of orders), Average (average order) or Max.
You can drag the same field into Values twice to show both the total and the count side by side.
Step 5: Show percentages
To see what share each region contributes:
- Right-click any number in the pivot table.
- Choose Show Values As → % of Grand Total.
Step 6: Group dates by month
Drag Date to Rows. Newer Excel versions group dates into years, quarters and months automatically. If yours does not, right-click a date in the pivot table, choose Group, select Months (and Years if your data covers more than one year), then click OK.
Step 7: Add a slicer for easy filtering
- Click inside the pivot table.
- Go to PivotTable Analyze → Insert Slicer.
- Tick Salesperson and click OK.
You now have clickable buttons that filter the report. This is the easiest way to make a report others can use.
Step 8: Refresh after the data changes
Pivot tables do not update by themselves. After you edit or add data, right-click the pivot table and choose Refresh, or press Alt + F5. To refresh every pivot table in the file, use Data → Refresh All (Ctrl + Alt + F5).
Common mistakes
- Numbers show as Count instead of Sum: the column contains blank cells or text. Clean the column, then change the setting to Sum.
- New rows are missing: the source range did not include them. Use an Excel Table (Ctrl + T), or update the range under PivotTable Analyze → Change Data Source.
- “(blank)” appears in the report: some rows have empty cells in that field. Fill them in or filter “(blank)” out.
- Dates will not group: some dates are stored as text. Convert them to real dates first.
FAQ
Can Excel suggest a pivot table for me?
Yes. Go to Insert → Recommended PivotTables and pick one of the previews.
Can I make a chart from a pivot table?
Yes. Click inside it and choose PivotTable Analyze → PivotChart. The chart follows your filters and slicers.
Do pivot tables work in Google Sheets?
Yes: Insert → Pivot table. The layout is slightly different, but the Rows, Columns, Values and Filters idea is the same.