How to Create a Pivot Table in Excel: Beginner Guide
A pivot table summarizes thousands of rows in seconds without writing formulas. Here is how to build one from a small sales table.
The data
| A: Region | B: Rep | C: Amount | |
|---|---|---|---|
| 1 | Region | Rep | Amount |
| 2 | East | Ann | 1200 |
| 3 | West | Bob | 800 |
| 4 | East | Cara | 450 |
| 5 | North | Ann | 950 |
| 6 | West | Dan | 600 |
| 7 | East | Bob | 300 |
Your data should have one header row, no blank rows, and one record per row.
Create the pivot table
- Click any cell in the data.
- Go to Insert > PivotTable.
- Choose New Worksheet and click OK.
- In the field list, drag Region to Rows and Amount to Values.
The result:
| Region | Sum of Amount |
|---|---|
| East | 1,950 |
| North | 950 |
| West | 1,400 |
| Grand Total | 4,300 |
You can check it against SUMIF: East is 1,200 + 450 + 300 = 1,950.
Useful changes
- Add a second breakdown: drag Rep to Columns to see each rep’s amount per region.
- Count instead of sum: click the value field > Value Field Settings > Count.
- Show percent of total: Value Field Settings > Show Values As > % of Grand Total.
- Filter: drag a field to Filters, or insert a Slicer from PivotTable Analyze.
- Group dates by month or year: right-click a date > Group.
Refresh when data changes
Pivot tables do not update by themselves. Right-click the table and choose Refresh, or press Alt+F5. Convert your data to a table with Ctrl+T first so new rows are included automatically.
Common problems
- Amounts count instead of sum: some cells contain text. Clean the column.
- Blank row labels: the data has empty cells. Fill or filter them.
- Source range too small: change the data source under PivotTable Analyze.
Turn a pivot table into a chart with a graph in Excel, or start from our sales dashboard template that does the summaries with formulas.
Free pricing tips for your business
Get short, practical tips on pricing jobs and using our free calculators. No spam, unsubscribe any time.
Frequently asked questions
What is a pivot table used for?
Summarizing, grouping and comparing large lists, for example sales by region, month or product.
Do I need formulas?
No. You drag fields into areas. Excel calculates the totals.
Why is my pivot table not updating?
Refresh it manually with Alt+F5, or use a table as the source so new rows are included.
Can I make a pivot table in Google Sheets?
Yes. Use Insert > Pivot table.
Need a custom Excel sheet or automation?
Talk to usMore free tools: All free tools · SUMIF and SUMIFS · Excel dashboard template · Make a graph in Excel