To aggregate data in an Excel PivotTable, select a clean source range, choose Insert > PivotTable, and place a field in Values. Excel summarizes that field—usually with Sum for numbers or Count for text—and you can change the method through Value Field Settings. A PivotTable is a flexible way to group and summarize records without writing a formula for each group.
What aggregation means in an Excel PivotTable
Aggregation means combining multiple records into a useful summary. A sales table might contain one row for every order line; a PivotTable can combine those rows to show sales by product, month, or salesperson. Instead of manually adding matching records, you assign fields to areas that describe how the summary should be organized.
The source table supplies the records and fields. Rows and Columns determine the categories displayed across the report, while Values contains the field or fields Excel calculates. Filters can limit which records appear. You can arrange more than one field in these areas, so a report can show sales by region and then by product, for example.
“Aggregate” can also refer to Excel’s AGGREGATE worksheet function. That is a separate tool for calculating a result such as a sum or average from a range, with options for ignoring hidden rows or errors. A PivotTable, by contrast, builds a grouped summary from a set of records and lets you rearrange the grouping interactively.
Create a basic aggregated PivotTable
In Excel for Microsoft 365 on Windows, start with a rectangular dataset that has one header row and no completely blank rows or columns within the data. Click a cell in the range, then select Insert > PivotTable. Check the proposed range, choose whether the PivotTable should appear on a new or existing worksheet, and select OK. If the data is an Excel Table, Excel can use that table as the source.
In the PivotTable Fields pane, drag fields into the appropriate areas. For example, put Region in Rows and Sales in Values. Excel creates one row per region and calculates a sales total for each. Add Product below Region in Rows to see a product breakdown within each region. The grand total summarizes the records represented in the report, subject to any filters.
To change the calculation, select a number in the Values area or its field name in the pane and open Value Field Settings. In the Summarize Values By tab, choose a calculation and select OK. The exact dialog arrangement can vary slightly with the Excel interface. On Mac, the PivotTable tools and dialog layout differ, but the same basic process—insert a PivotTable, assign fields, and change its value summary—applies. Excel for the web also supports PivotTables, though some advanced features and controls may differ from the Windows desktop app.
Choose the right summary calculation
Sum is appropriate for additive measures such as sales, units, or expenses. Count is useful for counting nonblank records, such as the number of order lines associated with each customer. Average reports the mean of the underlying values; it is not the same as averaging the displayed group totals. Other available calculations include Minimum, Maximum, Product, Count Numbers, and statistical options such as standard deviation and variance.
Choose the calculation based on what each source row represents. If every row is an order, counting rows can answer how many orders are recorded. If one order occupies several rows because it contains several products, that same count answers how many order lines there are, not how many distinct orders. The PivotTable cannot infer the business meaning of a field from its name alone.
- Use Sum for amounts or quantities that should be added.
- Use Count when the question concerns records or nonblank entries.
- Use Average when each source value should carry equal weight.
- Use Min or Max to find the smallest or largest source value in each group.
For example, an average price across all transaction rows gives each row equal weight. It may not equal total revenue divided by total units, particularly when transaction sizes vary. If you need a weighted average, define the measure carefully rather than assuming the PivotTable’s ordinary Average option will produce it.
Worked example: summarize sales by region and month
Suppose a sales list has columns named Date, Region, Product, Units, and Sales, with one row per product line sold. Put Region in Rows and Sales in Values to compare regional totals. Put Date in Columns to split the summary by date, then group the date field into months if the report should show monthly rather than daily results. Add Product under Region in Rows to investigate which products make up each regional total.
Check that Sales is summarized by Sum. If Excel displays Count of Sales instead, it may have detected text or inconsistent values in that source column. Correct the source data, refresh the PivotTable, and then choose Sum in Value Field Settings. Also confirm that the values represent amounts on a consistent basis; adding sales recorded in different currencies without conversion would produce a number that looks precise but is not a meaningful total.
This report is a summary of the source rows, not a new transaction ledger. Double-clicking a value cell in many desktop PivotTables opens a new worksheet containing the records behind that value, which can help verify a result. The detail sheet is a snapshot of the records included at that time; it does not replace the source data or automatically become a permanent audit trail.
Group dates and other categories
Date fields can often be grouped into useful periods. Right-click a date label in the PivotTable and choose Group, then select intervals such as Months, Quarters, or Years as appropriate. For a report spanning multiple years, group by both Years and Months if you need January in one year to remain separate from January in another. Grouping changes the display and organization; it does not rewrite the original dates.
Grouping may fail if the source date column contains blanks, text that resembles dates, or invalid date values. Make the source values genuine Excel dates and resolve blanks or errors before trying again. You can also group suitable numeric fields into ranges, such as age bands or price brackets. For labels such as product names, use the original categories or create a separate category field in the source when a custom grouping needs to be reused consistently.
Count distinct items when duplicates matter
Ordinary Count includes repeated entries. If a customer appears on ten transaction rows, Count of Customer can count ten nonblank entries, not one customer. For a unique-customer total, use Distinct Count when it is available. In the Windows desktop PivotTable creation dialog, the option is associated with adding the source to the Data Model; select Add this data to the Data Model when creating the PivotTable, then look for Distinct Count in Value Field Settings.
Data Model features and their availability depend on the Excel version and platform, so the option may not appear in every interface. If it is unavailable, other approaches include preparing a deduplicated customer list, using Power Query to group or remove duplicates before loading the results, or using a suitable worksheet method. Be clear about whether you want unique customers overall or unique customers within each region: those are different summaries, and the latter can count the same person in more than one region.
Prepare the source data for reliable summaries
A PivotTable is only as dependable as its input. Keep each field in its own column, use one consistent header row, and put one record on each row. Avoid merged cells, subtotals inserted between records, and blank header names. Use consistent data types: dates should be dates, quantities should be numeric, and category labels should not vary through stray spaces or spelling differences.
For an expanding dataset, format the source range as an Excel Table with Insert > Table, or use Home > Format as Table. Tables can expand when new records are added, which makes them a practical PivotTable source. Give the table and its columns clear names so you can identify the source later. Formatting a value to look like a number does not necessarily convert text-stored digits into numeric values; clean the underlying data if calculations behave unexpectedly.
Refresh and update the PivotTable
A PivotTable does not always update immediately after you edit its source. Right-click inside it and choose Refresh, or use PivotTable Analyze > Refresh on the Windows ribbon. To refresh multiple PivotTables that share a workbook connection, PivotTable Analyze > Refresh > Refresh All is a convenient option; Excel’s Data tab also provides Refresh All for workbook connections.
If you add rows outside the original source range, refresh alone may not include them. Check the source through PivotTable Analyze > Change Data Source, or base the PivotTable on an Excel Table so added rows can be included as the table grows. Refreshing recalculates from the source; it does not fix incorrect source values, repair inconsistent categories, or guarantee that a manually chosen range has been expanded.
Common aggregation problems and fixes
If a numeric field appears as Count rather than Sum, inspect the source column for text, error values, or mixed types. Convert entries to genuine numbers, correct problematic cells, and refresh. If the PivotTable shows unexpected extra categories, look for inconsistent spelling, spaces, or blanks in the source labels. If a total seems too large, check whether the source contains duplicate records and whether the same business event is represented on multiple rows.
Another frequent misunderstanding is comparing a PivotTable’s average with an average calculated from subgroup averages. Those values can differ because groups may contain different numbers of records. Likewise, a grand total for an average is calculated from the underlying records, not by simply averaging the visible group averages. Confirm the desired definition before using the result in a report.
Finally, a PivotTable can preserve formatting or cached items that make old labels appear after a refresh. If removed items continue to show in filters, check the PivotTable options for retained items and clear or refresh the report as appropriate. Do not assume that a visible label proves the source still contains that record; inspect the current source and report settings.
PivotTable aggregation versus the AGGREGATE function
Use a PivotTable when you want to group records by one or more categories, change the layout, filter the report, or compare several measures. Use the AGGREGATE worksheet function when you need a calculation from a range as part of a worksheet model and want options such as ignoring hidden rows or errors. The function does not automatically create a category-by-category report like a PivotTable.
For a simple total of sales by region, a PivotTable is usually straightforward and easy to inspect. A formula-based solution can be useful when a result must sit in a fixed report layout or feed other calculations. Power Query is another option when the main task is repeatable data cleaning or reshaping before analysis. These tools solve related but distinct problems; choose according to whether you need a grouped report, a cell-level calculation, or a repeatable transformation process.
Frequently Asked Questions
How do I change Sum to Count in a PivotTable?
Select the value field, open Value Field Settings, choose Count under Summarize Values By, and select OK. Count generally counts nonblank entries, so confirm that this matches the question you are asking.
Why does Excel show Count instead of Sum?
Excel may have detected text or mixed data types in the source field, so it treats the field as nonnumeric. Check the source cells, convert intended amounts to numbers, refresh the PivotTable, and select Sum if needed.
Can a PivotTable count unique customers?
Yes, with Distinct Count when the PivotTable uses the Data Model and the option is supported in your Excel interface. Otherwise, create a deduplicated source or use a data-preparation approach that produces unique customer records.
How do I aggregate dates by month?
Add the date field to Rows or Columns, right-click a date label, select Group, and choose Months. Include Years as well when the source covers multiple years and you need to distinguish the same month in different years.
Does a PivotTable update when I change the source data?
Not necessarily. Refresh the PivotTable after edits, and check its source range if you added records beyond the original boundary. Using an Excel Table as the source can make expanding datasets easier to manage.
Related
- Excel Not Responding When Copying and Pasting? Fixes
- Excel Not Updating Links From Other Workbooks? Fixes
- Excel Not Keeping Formatting? Causes and Fixes
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Cumulative Line Graph: How to Create OneTo make a cumulative line graph in Excel, add a running-total column beside your values, then chart the…
- Column Sparklines in Excel: A Step-by-Step How-ToTo insert column sparklines in Excel for Microsoft 365 on Windows, select the cells where you want the…
- Pivot Chart: Remove Filter Labels in ExcelTo remove the filter labels from an Excel PivotChart, select the chart and choose PivotChart Analyze > Show/Hide…
- Excel Trendline Tutorial: Add, Format, and Choose OneTo add a trendline in Excel for Microsoft 365 on Windows, select a chart, then choose Chart Design…
- Activate Power Pivot in Excel: Windows Setup GuideTo activate Power Pivot in Excel for Microsoft 365 on Windows, select File > Options > Add-ins, choose…
- Excel School Dashboard: What It Shows and How to BuildAn Excel school dashboard is a visual summary of school data—such as attendance, grades, enrollment, or staffing—organized so…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.