A summary table in Excel turns a larger data set into a smaller view of totals, counts, averages, or other useful measures. The most flexible way to create one is usually a PivotTable. The Windows 11 version you use does not materially change the Excel process.
How to Create a Summary Table in Excel
Step 1: Prepare the source data
Put the data in columns with a single header row and avoid completely blank rows or columns inside the data range. Consistent dates, numbers, and category names produce better summaries.
Step 2: Select a cell in the data
Click anywhere inside the source range. If you have formatted the source as an Excel Table, the range can expand more reliably when new rows are added.
Step 3: Insert a PivotTable
Select Insert > PivotTable. Confirm the source range, choose where the PivotTable should be placed, and select OK.
Step 4: Add fields to the summary
Use the PivotTable Fields pane to drag category fields into Rows or Columns and numeric fields into Values.
Step 5: Choose the calculation
If Excel uses the wrong summary, open the value field settings and choose Sum, Count, Average, or another appropriate calculation.
Step 6: Format and review the results
Apply number formats, sort categories, and check totals against the source data. A summary is only useful if the categories and calculations mean what you expect.
Refresh the Summary After Data Changes
A PivotTable does not necessarily update the moment the source data changes. Use Refresh after editing the source. If new rows fall outside the original range, using an Excel Table as the source can make ongoing maintenance easier.
When a Formula-Based Summary Is Better
For a small fixed report, formulas such as SUMIFS, COUNTIFS, and AVERAGEIFS can be easier to control. PivotTables are especially useful when you want to rearrange categories or explore the data without building many separate formulas.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.