A pivot table summarizes a long data list without making you write formulas by hand. You drag fields into rows, columns, values, and filters, and Excel builds totals, counts, averages, and breakdowns that can be rearranged quickly.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, Excel 2019, Excel for Mac, and Excel for the web when you have raw data with one record per row and clear headers across the top.
Create one with Recommended PivotTables
This is the fastest way to start when you are not sure which layout you want.
- Click anywhere inside the source data.
- Make sure the data has headers and no blank rows or blank columns inside the range.
- Open the Insert tab.
- Choose Recommended PivotTables.
- Review the previews Excel suggests.
- Select the closest useful summary and click OK.
Excel creates the pivot table, usually on a new worksheet. You can still adjust the layout afterward.
Build a pivot table manually
Use this when you know the exact summary you need.
- Click inside the source data.
- Open Insert > PivotTable.
- Confirm the selected range.
- Choose New Worksheet and click OK.
- In the PivotTable Fields pane, drag a category field into Rows.
- Drag a numeric field into Values. Excel usually sums numbers by default.
- Optionally drag a field into Columns for a cross-tab view, or into Filters to limit the whole report.
Nothing is permanent while you are arranging fields. Drag fields in, out, or between areas until the summary answers the question you care about.
More control
Include new rows
If the source range does not include later rows, the pivot table will miss them. Convert the source data to a Table before building the pivot table, or adjust the source range and refresh.
Refresh the pivot table
Right-click the pivot table and choose Refresh after source data changes. If the workbook contains several pivot tables, use Data > Refresh All.
Change Sum to Count or Average
Click the field inside the Values area, choose Value Field Settings, and switch the calculation to Count, Average, Max, Min, or another option.
Group dates
Right-click a date field in the pivot table, choose Group, and group by Months, Quarters, Years, or another interval.
Fix numbers that only count
If Excel counts a numeric column instead of summing it, the source values may be stored as text. Convert the source column to real numbers, then refresh the pivot table.
Sources
- Microsoft Support – Create a PivotTable to analyze worksheet data (2025)
- Microsoft Support – Overview of PivotTables and PivotCharts (2025)
- Microsoft Support – Change the source data for a PivotTable (2024)