How to Create a Pivot Table in Excel

Excel builds a PivotTable in a few clicks: click inside your data, go to Insert > PivotTable, then drag fields into the Rows, Columns, and Values boxes in the PivotTable Fields pane. From there you swap the default Sum for Count or Average, and refresh the table any time the source numbers change. If Insert > PivotTable stops with the error “The PivotTable field name is not valid,” the cause is nearly always a blank cell in the header row of your source, so every column needs a unique, non-empty heading before you start.

Insert the PivotTable#

Start with clean data: one header row, no blank columns, no merged cells. A drop-down list on any text column keeps entries spelled consistently, which avoids rows splitting into near-duplicates later. Then follow these steps.

Step	What to do
1	Click any single cell inside your data range, or select the full range including the header row.
2	Go to the Insert tab and select PivotTable.
3	Confirm the range Excel picked, then choose New Worksheet or Existing Worksheet for where the PivotTable goes.
4	Select OK. Excel opens a blank PivotTable and the PivotTable Fields pane on the right.

Drag fields into Rows, Columns, and Values#

The PivotTable Fields pane lists every column header as a checkbox, with four boxes underneath: Filters, Columns, Rows, and Values. Checking a box places that field automatically — text and dates to Rows, numbers to Values — but dragging a field into any box overrides that. For a sales-by-region sheet, drag Region to Rows, Month to Columns, and Sales to Values, and the numbers cross-tabulate instantly.

Switch the summary from Sum to Count#

Excel sums numeric fields and counts text or blank fields by default. To change that, right-click any number in the PivotTable and choose Summarize Values By, then pick Sum, Count, Average, Max, or Min. Value Field Settings, on the same menu (or from the PivotTable Analyze tab’s Active Field group), adds more functions and lets you rename the field.

Refresh the PivotTable when data changes#

A PivotTable does not update on its own. After editing the source data, right-click anywhere inside the table and select Refresh, use the Refresh button on the PivotTable Analyze tab, or press Alt+F5. Refresh updates one table; Refresh All, from the same tab, updates every PivotTable in the workbook.

Situation	Fix
Values changed, same rows/columns	Refresh (right-click, or Alt+F5)
New rows added below a plain range	Extend the range via Change Data Source on the PivotTable Analyze tab
New rows added to a formatted Excel Table	Just Refresh — the table’s range grows automatically

That last row is why it pays to convert source data to a formatted Table before building the PivotTable: rows added later get picked up on refresh with no manual range editing. If your workflow also pulls matching values from another sheet before summarizing them, see our guide to using VLOOKUP in Excel.

Keep the source data and the PivotTable on separate sheets so a stray edit does not land in your raw numbers, and refresh right after every paste or import. That habit alone heads off most “my totals don’t match” complaints.

Common questions#

Why do I get “The PivotTable field name is not valid”?#

Excel needs a label for every column, so this error means the header row has a blank cell, a duplicate heading, or the selected range reaches into an empty column. Give each column a unique, non-empty header and reselect the exact data range, then insert the PivotTable again.

Why is my PivotTable counting instead of summing?#

Excel only sums a field when every cell in it is numeric; a single blank or a number stored as text makes it fall back to Count. Clean that column so all entries are real numbers, then right-click a value and choose Summarize Values By > Sum.

Why don’t new rows show up in my PivotTable?#

A PivotTable does not expand its source range on its own. Either point it at the larger range with Change Data Source on the PivotTable Analyze tab, or convert the source to a formatted Excel Table first so rows added later are picked up automatically on the next refresh.

Why does my PivotTable show (blank)?#

A “(blank)” entry means some source records have no value in that field, usually from empty cells inside the data range. Fill or remove those cells at the source, or use the field’s filter to hide the blank item from the layout.