Insert the pivot table
Select one cell inside your data so Excel can detect the whole range, then go to Insert > PivotTable. Choose to place it on a new worksheet and click OK. An empty pivot frame and a field list appear.
Build the layout with the field list
Drag the field you want to group by into Rows (for example Region), and drag a numeric field such as Sales into Values. Excel sums the sales for each region automatically.
Change how values are summarised
By default Values shows a sum. Click the field in the Values box, choose Value Field Settings, and switch to Count, Average, Max, or another function to answer a different question.
Common mistakes / tips
- Give every column a header. Pivot tables use the top row as field names, so blanks there cause problems.
- Avoid blank rows. A gap can stop Excel from selecting the full range.
- Refresh after edits. Changing the source data does not update the pivot until you right-click and choose Refresh.
Frequently asked questions
How do I refresh a pivot table?
Right-click anywhere inside the pivot table and choose Refresh, or press Alt+F5. This pulls in any changes from the source data.
Why is my pivot table counting instead of summing?
Excel counts when a Values column contains text or blanks. Make sure the field holds only numbers, then set the value field to Sum.
Pivot tables are great, but grouping millions of rows is a job for code. Go further with our free Pandas course.