To create a PivotTable in desktop Excel, select your data and go to Insert > PivotTable. Here are the full steps, followed by the fix for a numbers column that shows a count instead of a total.
Build the PivotTable from your data
- Select your data. Microsoft recommends having it in an Excel table.
- Go to Insert > PivotTable. The Create PivotTable dialog opens with your range or table name already filled in.
- Under Choose where you want the PivotTable report to be placed, pick New Worksheet or Existing Worksheet.
- If you chose Existing Worksheet, select the cell where the PivotTable should go.
- Select OK. Excel creates a blank PivotTable and opens the PivotTable Fields list.
From the Fields list you drag fields into the layout to build your summary.
Before step 1, clean the source data.
Which versions of Excel these steps cover
Microsoft's Create a PivotTable to analyze worksheet data page applies to Excel for Microsoft 365 on Windows and Mac, plus Excel 2024, 2021, 2019 and 2016. That page has separate sections for Excel for the web and Excel on iPad. The steps above are for the desktop versions.
Why a numbers field shows a COUNT instead of a SUM
This is the problem people most often mistake for a PivotTable that "isn't working". You drop a sales or quantity column into the PivotTable and get a count of rows instead of a total.
According to Microsoft's support page, if Excel reads your data as text, it summarizes the field as a COUNT rather than a SUM.
The fix starts in the source data, not the PivotTable:
- Look at the column you expected to total. Numbers stored as text are the usual cause.
- Convert those cells to real numbers in the source table.
- Build the PivotTable again with Insert > PivotTable, following the steps above, and check that the field now sums.
If you only need one or two conditional totals and not a full summary, a formula can be simpler. See how to use SUMIFS in Excel to add values that meet several conditions.
Let Excel suggest a layout (Microsoft 365 only)
If you are not sure how to arrange the fields, go to Insert > Recommended PivotTables and Excel suggests layouts based on your data. Microsoft says this option is available only to Microsoft 365 subscribers.
Working in Google Sheets instead
Google Sheets has its own pivot tables with a different menu layout. The steps are in how to make a pivot table in Google Sheets.
