How to Create a Pivot Table in Excel: A Step-by-Step Guide for Beginners
A pivot table summarizes detailed data by rep, month, or any other field without writing formulas. Here's how to make one, step by step, and how to avoid the usual snags.
2026-11-10
Key points
- 1Source data needs headers in row 1 and one record per row
- 2Drag fields to Rows, Columns, and Values to summarize
- 3Click Refresh after changing the source data
What a pivot table does
This example totals sales by rep and month from detailed records. A summary that would take dozens of SUMIFS formulas takes only a few drags.
| Date | Rep | Amount |
| 4/3 | Smith | 12,000 |
| 4/8 | Johnson | 8,000 |
| 5/2 | Smith | 15,000 |
| 5/9 | Johnson | 9,000 |
| 5/20 | Smith | 6,000 |
| Sum of Amount | Apr | May | Grand Total |
| Smith | 12,000 | 21,000 | 33,000 |
| Johnson | 8,000 | 9,000 | 17,000 |
| Grand Total | 20,000 | 30,000 | 50,000 |
Prepare the source data
- Row 1 has column headers, with no blanks or duplicates
- One record per row
- No blank rows or columns and no total rows in between
- No merged cells
- No text or blanks in the amount column
See how to make a table in Excel for tidying data. If you turn the source data into a table with Ctrl + T, the pivot range grows when you add rows.
How to create a pivot table
- 1
Select one cell in the source data
- 2
Click PivotTable on the Insert tab
On Windows you can also press Alt → N → V. When the menu appears, choose From Table/Range. - 3
Check the range, choose New Worksheet, and click OK
- 4
In the PivotTable Fields pane on the right, drag fields into the four areas below
Example: Rep in Rows, Date in Columns, Amount in Values.
Note
If the field list disappears, click inside the pivot table or click Field List on the PivotTable Analyze tab.
Rows, Columns, Values, and Filters
| Area | What goes there | Example |
|---|---|---|
| Rows | Fields to list down the side | Rep, product |
| Columns | Fields to spread across the top | Month, branch |
| Value | Numbers to summarize | Amount, quantity |
| Filters | Conditions that filter the whole table | Fiscal year, region |
To swap rows and columns, just drag the fields between areas and the table rebuilds itself.
Switch between Sum, Count, and Average
- 1
Click "Sum of Amount" in the Values area and choose Value Field Settings
- 2
Choose Sum, Count, Average, Max, or another function
- 3
Click Number Format at the bottom left, add thousands separators, and click OK
Count shows up instead of Sum
If the amount column contains even one blank or text value, it is summarized by Count from the start. Fix the source data and switch to Sum with the steps above.
Refresh after changing the data
A pivot table doesn't update by itself when the source data changes.
| Action | Method |
|---|---|
| Refresh this pivot table | Right-click inside → Refresh, or Alt + F5 |
| Refresh everything in the workbook | Ctrl + Alt + F5 |
| Extend the source range | PivotTable Analyze tab → Change Data Source |
Group dates by month
Dates placed in Rows or Columns are sometimes grouped by month automatically. If they aren't, or you want quarters or years, follow these steps.
- 1
Right-click a date cell in the pivot table and choose Group
- 2
Choose Months, Quarters, or Years and click OK
If you see "Cannot group that selection", the date column contains text or blanks.
Filter with slicers
A slicer filters the summary with buttons. It's handy for switching views live in a meeting.
- 1
Select a cell in the pivot table and click Insert Slicer on the PivotTable Analyze tab
- 2
Check the field to filter by (for example, Branch) and click OK
- 3
Click a slicer button to show only that branch
Hold Ctrl to select more than one.
Troubleshooting
QA "(blank)" row appears.
The source data has empty cells. Fill them and refresh, or clear "(blank)" from the Row Labels ▼ filter.
QReferencing a cell creates a GETPIVOTDATA formula.
Excel inserts this function when you click a pivot table cell in a formula. For normal references, clear Generate GetPivotData under the Options ▼ on the PivotTable Analyze tab.
QI want to sort from largest to smallest.
Right-click a value cell and choose Sort → Sort Largest to Smallest.
QI want to chart the summary.
Select the pivot table and click PivotChart on the PivotTable Analyze tab. See also how to make a chart in Excel.