How to make a pivot table in Excel (beginner guide)
Updated 17 November 2026
A pivot table turns a long list into a short summary in seconds: total sales per month, number of orders per customer, spending per category. You don't write any formulas.
Before you start: tidy the data
- One header row at the top, every column with a name.
- No completely blank rows or columns inside the data.
- One type of information per column (dates in one, amounts in another).
- Tip: click inside the data and press Ctrl + T to make it a Table. New rows you add later are then included automatically.
Make the pivot table in 5 steps
Click inside your data
Any single cell in the list is enough.
Insert › PivotTable
On the Insert tab, choose PivotTable (in recent versions, From Table/Range). Check the range, keep New Worksheet, and click OK.
Drag fields into the boxes
In the PivotTable Fields pane, drag a category (for example Region) to Rows, and a number (for example Sales) to Values. Excel adds them up for you.
Change Sum to Count or Average
Click the field in Values › Value Field Settings, then pick Count, Average, Max and so on. Text fields are counted automatically.
Refresh when the data changes
Pivot tables don't update on their own. Right-click the pivot table › Refresh, or press Alt + F5.
Three quick upgrades
- Columns: drag another field (for example Year) to Columns for a side-by-side comparison.
- Filters: drag a field to Filters to show one region or product at a time.
- Group dates: right-click a date in the pivot table › Group › Months or Years.
Questions
Why doesn't my pivot table show new data?
Pivot tables don't update automatically. Right-click it and choose Refresh. If you added rows below the original range, turn the data into a Table (Ctrl + T) so new rows are included.
Why does my pivot table count instead of sum?
Excel counts when the column contains text or blank cells. Clean the column so it holds only numbers, or change it in Value Field Settings.
More: VLOOKUP in Excel: a simple guide with examples · 20 Excel keyboard shortcuts every beginner should know