
Tutorials
PivotTable Tutorial for Beginners: Summarise Data in 5 Minutes
Learn how to create an Excel PivotTable step by step: prepare your data, drag fields, group dates, refresh and avoid the common mistakes.
By ExcelPanda Team · Published · 5 min read
What a PivotTable does
Imagine a sales list with thousands of rows. You want total sales per region, or per product, or per month. Writing a formula for each is slow. A PivotTable builds that summary for you and lets you rearrange it by dragging fields around.
Get your data ready
PivotTables work best on tidy data. Before you start, check that:
- Every column has a heading in the first row, and no two headings are the same.
- There are no completely blank rows or columns in the middle of the data.
- Each column holds one kind of thing: all numbers, all dates or all text.
- There are no merged cells or subtotal rows inside the data.
Turning the range into an Excel Table first (select it and press Ctrl+T) is a good habit, because the PivotTable then grows with your data.
Build your first PivotTable
- Click any cell in your data.
- On the Insert tab, choose PivotTable.
- Check the range is right, choose a new worksheet, then select OK.
- In the field list, drag Region into Rows and Sales into Values.
You now have one total per region. Drag another field, such as Product, into Columns to split each region further.
The four areas of the field list
PivotTable field list areas
| Area | What it does | Example |
|---|---|---|
| Rows | Lists each item down the side | One row per region |
| Columns | Spreads items across the top | One column per year |
| Values | The numbers you total, count or average | Sum of sales |
| Filters | Lets you show part of the data | Only one sales rep |
Four tweaks you will use all the time
- Change Sum to Count or Average. Click the value field, choose Value Field Settings and pick the calculation.
- Group dates. Right-click a date in the PivotTable and choose Group to summarise by month, quarter or year.
- Sort. Right-click a value and sort largest to smallest to see your top performers first.
- Show values as a percentage. In Value Field Settings, use Show Values As to see each item as a share of the total.
Refresh after the data changes
If you change or add data, the PivotTable does not always update by itself. Right-click it and choose Refresh. If you added new rows outside a Table, you may also need to change the source range.
Common mistakes
- Blank cells or text in a number column. Excel counts the column instead of summing it.
- Forgetting to refresh. The numbers look right but are out of date.
- Typing over a PivotTable. Edit the source data, not the summary.
Want a plan for learning PivotTables alongside everything else? See our 30-day Excel plan.
Frequently asked questions
What is a PivotTable in Excel?
A PivotTable is a tool that summarises a large table of data. You drag fields into rows, columns and values, and Excel totals, counts or averages the data for you without writing formulas.
Do PivotTables change my original data?
No. A PivotTable reads your data and shows a summary. Your source table stays exactly as it was.
Why is my PivotTable not updating?
PivotTables do not always refresh on their own. Right-click the PivotTable and choose Refresh, or use the Refresh button on the PivotTable Analyze tab.
Why does my PivotTable count instead of sum?
Excel counts a field when the column contains text or blank cells. Clean the column so it holds only numbers, then set the value field to Sum.
PivotTable or formulas like SUMIFS?
Use a PivotTable when you want to explore and slice data quickly. Use SUMIFS when you need a fixed result in a specific cell that other formulas depend on.
Sources and disclaimers
Menu names can differ slightly between Excel versions. ExcelPanda is not affiliated with or endorsed by Microsoft. Microsoft and Excel are trademarks of the Microsoft group of companies.
.png)