How to Create a Pivot Table in Excel

A pivot table summarises hundreds or thousands of rows of raw data into a view you can adjust in a few clicks. You don't need to write a single formula. Below, you'll build one step by step, from preparing the data to formatting and refreshing after a change.

A pivot table groups and counts your data without formulas

A pivot table is an interactive summary that condenses a large list by grouping rows and summing, counting, or averaging values. You drag fields into zones and Excel calculates the summary live. Change the layout and the table recalculates instantly, without touching the source data.

Typical questions a pivot table answers in seconds: total sales per region, number of orders per month, or the average order value per salesperson. Instead of stacking VLOOKUP or SUMIF formulas, you let the pivot table do the grouping. For looking up individual values, formulas remain handier; read about the difference in VLOOKUP vs XLOOKUP.

Good source data is the foundation of a working pivot table

A pivot table only works reliably when the source data is well structured. Check your list against these points before you start.

• One header row. Each column has a unique, short header at the top; those headers become the fields in the pivot table.
• No blank rows or columns. An empty row breaks the contiguous range Excel recognises.
• One record per row. Each row is one observation, for example one order or one transaction.
• Consistent data. Numbers as numbers, dates as dates, no text like "12 pieces" in a number column.

Ideally, convert your list into an Excel table with Ctrl+T. If you add rows later, the table grows automatically and after a refresh the pivot table picks up the new rows without you having to adjust the range. A pivot table on an ordinary range won't pick up new rows automatically.

Also watch out for merged cells in your header row or sub-headings that break the list. A pivot table expects a flat list where every column contains the same type of data from top to bottom. If there are sub-headings or subtotals in the source data, remove them first; otherwise they'll end up as separate categories in your summary and the totals won't be correct.

You insert a pivot table from the Insert tab

With clean data, you create the pivot table in a few steps.

1. Click any cell inside your data list. As long as your cursor is within the list, Excel automatically recognises the whole contiguous range; you don't need to select anything.
2. Go to the Insert tab and click PivotTable in the top left. If you're using a Ctrl+T table, choose From Table/Range.
3. The window shows the range already filled in. Choose New Worksheet so the pivot table appears on its own clean tab.
4. Click OK. Excel opens an empty pivot table frame with the PivotTable Fields pane on the right.

If you're in a hurry or unsure about the layout, use Insert > Recommended PivotTables. Excel shows ready-made suggestions based on your data that you can place with one click.

The four zones determine the shape of your pivot table

On the right you see the PivotTable Fields pane: all your column headers at the top, four zones below. You build the summary by dragging fields into those zones.

A concrete example with a sales list. Drag the Region field to Rows and the Sales field to Values. Nothing more is needed: Excel now shows the total sales per region under the heading Sum of Sales. If you also drag Year to Columns, the summary splits by year. If you place two fields one below the other in the Rows zone, e.g. Region and then Customer, you get a nested summary with subtotals per region.

You choose whether Excel sums, counts or averages

By default Excel sums numbers (Sum of Sales). For text fields it counts the number of entries. You can change the calculation easily.

1. Right-click a value in the column, or click the field in the Values zone.
2. Choose Value Field Settings.
3. Under Summarize Values By select the calculation: Sum, Count, Average, Max or Min.
4. In the same window, click Number Format to immediately apply a tidy format, such as a currency symbol or thousands separator.

On the Show Values As tab you'll find another useful option. Choose % of Grand Total and the table shows each number as a share of the whole, ideal for a distribution by region or product group. You can drag the same field into the Values zone twice, e.g. once as Sum and once as % of Grand Total, so you see the amount and the share side by side.

Zoom in with filters, grouping and slicers

A pivot table really becomes useful when you can filter and group it.

• Group dates. Right-click a date in the table and choose Group. Select Months and Years, for example, to summarise individual dates into monthly totals.
• Add slicers. Go to PivotTable Analyze > Insert Slicer and tick a field. You get buttons that let you filter on e.g. a region with a single click.
• Add a timeline. For date fields, choose Insert Timeline, a slider that lets you quickly select a period.
• Sort and top 10. Click the arrow on a row label to sort or to use Value Filters to show only the top ten values.

Placing filters in the Filters zone adds a drop-down above the table; slicers do the same but remain visible as buttons, which works better in a dashboard.

Make the pivot table readable with the Design tab

By default Excel shows the pivot table in compact layout, where nested fields indent under each other. For a report, a different layout often works better.

1. Click inside the pivot table and go to the Design tab.
2. Open Report Layout and choose Show in Tabular Form if you want each field in its own column – handy for copying the data later.
3. Use Subtotals and Grand Totals to switch those totals on or off.
4. Pick one of the PivotTable Styles for colour and borders, and turn on Banded Rows for better readability.

If you also want to show the numbers visually, go to PivotTable Analyze > PivotChart. The chart is linked to the table and updates as you filter or switch layout.

Add your own calculation with a calculated field

Sometimes you need a number that isn't a column in the source data, like profit as sales minus cost. You create that with a calculated field, without changing your source data.

1. Click in the pivot table and go to PivotTable Analyze > Fields, Items & Sets > Calculated Field.
2. Give the field a name, for example Profit.
3. In the Formula box type the calculation using the field names, such as = Sales - Cost. Double-click a field in the list to insert it into the formula.
4. Click Add and then OK. The new field appears in the field list and in the Values zone.

A calculated field always calculates on the totals per group, not on each individual row. For simple ratios it works fine; if you need a calculation per separate row, add it as an ordinary column to the source data.

Double-click to show the underlying rows

If you want to know which records sit behind a number, double-click a value in the pivot table. Excel creates a new worksheet containing only the source data that builds that number. For example, if you see unexpectedly high sales for one region, a double-click immediately reveals the relevant orders.

This drill-down feature is handy for checking figures before you share a report. The new list is a snapshot; it won't update if you later refresh the pivot table, so delete such a temporary sheet when you no longer need it.

Quick fixes for common problems

If the pivot table isn't working as expected, it's usually one of these points.

• Excel counts instead of summing. There is text or a blank cell in the number column. Clean those up and, in Value Field Settings, set the calculation back to Sum.
• New rows don't appear. The pivot table is based on a fixed range. Use a Ctrl+T table or adjust the source via PivotTable Analyze > Change Data Source.
• A deleted source column causes errors. If a column the table references disappears, a calculation can break; how to fix such a reference error is explained in fix the #REF! error.
• Old items remain in the filter list. In PivotTable Options, set the number of items to retain per field to None and refresh.
• Date grouping fails. A date is stored as text in the column, or there is a blank cell. Convert the column to a true date type and remove blank rows; grouping will then work again.

Press Refresh after a change in the source data

A pivot table works with a copy of your data and doesn't update automatically. If you change or add data, you must refresh it.

1. Right-click anywhere in the pivot table and choose Refresh, or press Alt+F5.
2. To refresh all pivot tables in the file at once, go to PivotTable Analyze > Refresh > Refresh All (Ctrl+Alt+F5).

A handy habit is to turn on automatic refresh. Right-click in the table, select PivotTable Options, go to the Data tab and tick Refresh data when opening the file. That way your summary is current every time you open it without you having to remember.

If the email hangs when you try to send the report, see Outlook can't connect to the server: solutions. If you can't find a previously sent report, fixing the Outlook search function will help. For a thorough explanation of pivot table layout, see the Microsoft documentation.