How to create a pivot table in Excel
Last updated
A pivot table summarises a list in a few clicks, without writing formulas. Click inside your data, go to Insert > PivotTable, confirm the range and where to put it, then drag fields in the PivotTable Fields pane: Region to Rows and Sales to Values gives Sum of Sales for each region, with a Grand Total at the bottom. A pivot doesn’t update by itself: press Alt+F5 to refresh it after the data changes.
Where to find it in Excel
Insert > PivotTable > PivotTable Fields (Filters / Columns / Rows / Values)
- Table/Range
- The source data, headers included, such as
A1:F21. Every column needs a header: it becomes the field name. Turn the list into a table first (Ctrl+T) and the pivot picks up new rows on the next refresh. - Rows
- The field whose items become the row labels, for example each region. Items are sorted A to Z by default, under the Row Labels header.
- Columns
- An optional second field spread across the top, such as the quarter. Rows and Columns together give a cross table, with a Grand Total for each row and each column.
- Values + Summarize value field by
- The field to calculate. Excel picks Sum for a column of numbers and Count as soon as it contains a blank or a text cell. Change it in Value Field Settings: Sum, Count, Average, Max, Min.
- Filters
- A field shown above the pivot with a drop-down, for example Channel. Pick an item such as Online and the whole pivot only counts those rows.
- Refresh
- PivotTable Analyze > Refresh (Alt+F5) recalculates the pivot from its source; Refresh All (Ctrl+Alt+F5) does every pivot of the workbook. Until then, changes in the data don’t show.
Practise pivot tables here
Select the data with its headers and click PivotTable… above the formula bar, as on the Insert tab in Excel, then pick the fields. Unlike Excel, the grid recalculates the pivot as soon as the data changes.
Total sales by region
Build a PivotTable from A1:C13 that shows the total sales of each region. Select the table with its headers, click PivotTable, keep the location E1, then put Region in Rows and Sales ($) in Values.
| Region | Product | Sales ($) |
| East | Laptop | 1,200.00 |
| West | Monitor | 450.00 |
| North | Laptop | 1,350.00 |
| East | Keyboard | 80.00 |
| South | Monitor | 520.00 |
| West | Laptop | 1,180.00 |
| North | Keyboard | 95.00 |
| East | Monitor | 610.00 |
| South | Laptop | 1,250.00 |
| West | Keyboard | 110.00 |
| North | Monitor | 480.00 |
| East | Laptop | 1,300.00 |
How to create a pivot table, step by step
- Click any cell of your data, or select it with its header row, for example
A1:F21. - On the Insert tab, click PivotTable. In the dialog, check the Table/Range and choose New Worksheet, or Existing Worksheet with a Location such as
H1. - In the PivotTable Fields pane, drag a field to Rows (or tick it: text fields go to Rows, number fields to Values), then drag the field to calculate to Values.
- If the header says Count of instead of Sum of, open Value Field Settings and pick the calculation you need. Add a field to Columns or Filters to go further.
Pivot table examples
Total sales by region
Rows: Region · Values: Sum of Sales
One row per region, sorted A to Z, and a Grand Total at the bottom. Right-click a total, then Sort > Sort Largest to Smallest to rank the regions.
Number of orders by product
Rows: Product · Values: Count of Order
Order numbers are numbers, so Excel offers Sum of Order, which adds them up and means nothing. Switch to Count in Value Field Settings.
Region by quarter
Rows: Region · Columns: Quarter · Values: Sum of Sales
A cross table: each cell is the total of one region in one quarter, with totals for every row and column.
Share of the total
=I3/I7
One region’s total divided by the Grand Total, formatted as a percentage. For the whole column at once, use Show Values As > % of Grand Total instead.
A formula that reads the pivot
=GETPIVOTDATA("Sales",$H$1,"Region","North")
This is what Excel writes when you click a pivot cell while typing a formula. It keeps finding North’s total even if the pivot moves or is re-sorted, but it doesn’t shift when copied.
Common problems and how to fix them
| Problem | Cause | Fix |
|---|---|---|
| Count of Sales instead of Sum of Sales | The Sales column contains a blank cell or a number stored as text, so Excel defaults to Count. | Fill or fix the cells, then set Value Field Settings > Sum. |
| A huge, meaningless total of order numbers | The order number column is numeric, so Excel sums it. | Summarise it by Count to get the number of orders. |
| The pivot shows old figures | A pivot doesn’t recalculate when the source changes, and new rows below the range aren’t included. | Press Alt+F5. For new rows, use Change Data Source, or build the pivot on a table (Ctrl+T). |
| A “(blank)” row appears | The source range includes empty rows or cells, for example whole columns A:F. | Select only the data, use a table, or untick (blank) in the row labels filter. |
| “The PivotTable field name is not valid.” | A column of the source has no header, or the selection is a single cell outside the data. | Give every column a header and select the data with its header row. |
| The formula next to the pivot doesn’t copy down | Clicking a pivot cell wrote GETPIVOTDATA with fixed items instead of a reference like I3. | Type the reference by hand, or untick Generate GetPivotData under PivotTable Analyze > Options. |
Pivot table or SUMIFS?
A pivot table builds a full summary in seconds, and you can rearrange it by dragging fields, but it only changes when you refresh it. SUMIFS, COUNTIFS and AVERAGEIFS formulas update instantly and let you place each result exactly where you want it, at the cost of writing one formula per cell. Use a pivot to explore and report, and formulas for a fixed layout that has to stay live.
Frequently asked questions
Does a pivot table update automatically?
No. After changing the data, click inside the pivot and press Alt+F5, or use Refresh All (Ctrl+Alt+F5). You can also tick Refresh data when opening the file in PivotTable Options.
How do I show percentages instead of amounts?
Right-click a value, choose Show Values As > % of Grand Total. % of Column Total and % of Row Total work the same way for cross tables.
How do I group dates by month or quarter?
Put a date field in Rows: recent versions group it into years, quarters and months by themselves. Otherwise right-click a date, choose Group and pick Months, Quarters or Years.
Can I put the same field in Values twice?
Yes. Drag Sales to Values twice, then set one to Sum and the other to Average or Count to see both side by side.
How do I delete a pivot table?
Click inside it, go to PivotTable Analyze > Select > Entire PivotTable and press Delete. The source data stays untouched.
Keep practising
All 10 pivot table exercises, with explanations and progress tracking.
Open the pivot table exercises