How To Create A Pivot Table In Excel (2026)
A PivotTable turns a long list of rows into a short summary table that you can rearrange in seconds.
You do not write any formulas, because Excel adds up, counts or averages the numbers for you.
This guide covers the desktop steps for Excel on Windows, based on Microsoft’s own support pages.
Quick steps to make a PivotTable
The whole job takes six clicks once your data is tidy.
- Click any cell inside your data, or select the cells you want to use.
- Open the Insert tab and select PivotTable.
- Check that the table or range shown in the dialog is the one you want.
- Choose New Worksheet, or choose Existing Worksheet and pick a cell for the top-left corner.
- Select OK.
- In the PivotTable Fields pane, tick the fields you want to summarize.
Prepare your data first
Most PivotTable problems come from the source data, not from the PivotTable.
Microsoft asks for clean, tabular data with the information arranged in columns rather than rows.
| Rule | Why it matters |
|---|---|
| One header row only | Every column needs a unique, non-blank label, because those labels become the field names. |
| No merged cells or double headers | Microsoft says to avoid both in the source data. |
| One data type per column | Do not mix currency values and dates in one column. |
| No blank rows or columns | The overview page says the range should not contain any. |
| No manual subtotals | Excel makes subtotals and grand totals itself, so remove subtotals added from the Data tab first. |
If your data is more complex or nested, Microsoft points to Power Query, which can reshape it (for example, unpivoting columns) so it has a single header row.
Format the list as an Excel table
Microsoft recommends turning the source data into an Excel table.
Select a cell in the data and choose Insert > Table.
Refreshing a PivotTable that is based on a table picks up new and updated rows.
A dynamic named range can do the same job if you prefer not to use a table.
What the Create PivotTable dialog asks
The dialog is short, but each choice matters.
| Option | What it does |
|---|---|
| Table/Range | Shows the source data, already filled in from your selection. |
| New Worksheet | Puts the PivotTable on a new sheet in the workbook. |
| Existing Worksheet | Puts it at a cell you choose on a sheet that already exists. |
| Add this data to the Data Model | Adds the source to the workbook’s Data Model. |
The down arrow on the PivotTable button also offers other sources: Get from External Data Source, Get from Data Model and Get from Power BI.
Build the layout in the Fields pane
Ticking a field box in the PivotTable Fields pane places it for you.
Excel sends non-numeric fields to Rows, date and time hierarchies to Columns, and numeric fields to Values.
You can drag any field to a different area if the default layout is not what you want.
Dragging a field from Rows to Columns is the "pivot" that gives the tool its name.
| Area | What goes there by default |
|---|---|
| Rows | Non-numeric fields, such as category names |
| Columns | Date and time hierarchies |
| Values | Numeric fields, which Excel summarizes |
Change the calculation from Sum to something else
Value fields show as SUM by default.
If Excel reads the data in a field as text, it shows COUNT instead, so do not mix data types in a value field.
- Select the arrow to the right of the field name in the Values area.
- Choose Value Field Settings.
- Under Summarize Values By, pick the calculation you want.
- Optionally edit the custom name, such as "Sum of Sales", and use Number Format to format the whole field.
Show values as a percentage
The Show Values As tab in the same dialog lets you display figures as a percentage, such as % of Grand Total.
To see both the total and the percentage, drag the same field into Values twice and give each copy a different setting.
Microsoft suggests renaming fields only after the layout is finished.
Its tip is to use Find and Replace (Ctrl+H) to strip "Sum of" from the names.
Refresh the PivotTable after the data changes
A PivotTable does not update itself when you add rows to the source.
To refresh one PivotTable, right-click inside it and select Refresh.
To refresh every PivotTable in the workbook, select a cell in one of them and open PivotTable Analyze.
Then open the arrow under Refresh and choose Refresh All.
For worksheet data, a refresh picks up new fields inside the original range or named range, and the new fields appear in the Field List.
Turn the summary into a PivotChart
A PivotChart is the graphical view of a PivotTable, and the two stay linked.
Layout or data changes in one appear in the other.
Excel offers any chart type except XY (scatter), stock and bubble.
You cannot change a PivotChart’s data range in Select Data Source, because it follows the PivotTable instead.
On refresh Excel keeps most formatting, but trendlines, data labels and error bars are not kept.
Common PivotTable problems and fixes
These problems all trace back to rules on Microsoft’s support pages.
| Problem | Likely cause and fix |
|---|---|
| Values show Count instead of Sum | Excel is reading the numbers as text. Convert them to real numbers first (see our guide to converting numbers stored as text). |
| New rows are missing | Refresh the PivotTable, and base it on an Excel table so new rows are included. |
| Field list is missing a column | The column probably has no header, so give every column a unique label. |
| Totals look doubled | Remove manual subtotals from the source data, because Excel adds its own. |
| Workbook file is large | Each new PivotTable stores a copy of its data. Building from an existing PivotTable in the same workbook shares that copy. |
Related Excel guides
Before you summarize a list, you may want to clean it with our guide to removing duplicates in Excel.
If your list is long, freezing the top row keeps the headers in view while you scroll.
If a formula in the sheet returns an error, see how to fix #N/A, #VALUE! and #REF! errors.