Aggregate Excel Columns with Formulas and PivotTables

Formulas & functions · Updated November 18, 2026

To aggregate Excel columns, use a function such as SUM, AVERAGE, COUNT, MIN, or MAX across the cells you want to summarize. For example, with values in B2:D4, =SUM(B2:D4) adds them; use a PivotTable or Power Query when you need totals grouped by category rather than one overall result.

“Aggregate columns” can mean several things: calculate a summary from one or more columns, calculate a result across each row, or group records and summarize them by a field such as region or product. The right approach depends on which result you need, whether the source data changes, and whether filtered or hidden rows should count.

What aggregating Excel columns means

An aggregate is a compact summary of a set of values. A total is one kind of aggregation, but it is not the only one. A sales column might be summed to find revenue, averaged to find typical order value, counted to measure the number of entries, or examined for its highest and lowest values.

There is an important distinction between aggregating columns and combining columns. If January, February, and March sales are in separate columns, summing those columns produces a total. If you want to put records from several tables underneath one another, that is appending or combining data, not aggregation. If you want totals for each store or month, you need a grouped summary. PivotTables and Power Query are usually better suited to that task than a single formula.

Before choosing a method, decide whether you want one answer for the whole range, one answer per row, or a breakdown by category. Also check whether the cells contain numbers, text, blanks, or errors: those details can affect the result.

Use basic functions for direct summaries

For a straightforward calculation, Excel’s built-in functions are usually clearest. Suppose B2:D4 contains three months of sales for three products: 10, 15, and 20 in the first row; 7, 8, and 5 in the second; and 3, 2, and 4 in the third.

  • To total all nine cells, with those values in B2:D4, enter =SUM(B2:D4). The result is 74.
  • To total the first product’s row, with its monthly values in B2:D2, enter =SUM(B2:D2). The result is 45.
  • To calculate the average of all nine values, with the same values in B2:D4, enter =AVERAGE(B2:D4). The result is approximately 8.22.

These examples show two common ways to aggregate columns: summarize a rectangular block, or summarize one row across several columns. To get a separate total for every product, put the row formula beside the first product and copy it down. Excel adjusts the row references as the formula is filled. For a total per month instead, sum each month’s column, such as B2:B4, then repeat for columns C and D.

Other useful functions include COUNT for numeric cells, COUNTA for nonempty cells, MIN for the smallest value, and MAX for the largest. Choose COUNT when you need to count numbers, not every populated cell. A cell containing text is not included in COUNT, even though it is not blank.

When to use the AGGREGATE function

AGGREGATE is useful when a standard summary needs to ignore certain problems, such as error values, or when you want to calculate over visible rows. Its first argument selects an operation; 9 means SUM. Its options argument controls what to ignore. For example, option 6 ignores error values. With numbers 12, 8, and 10 plus an error in B2:B5, enter =AGGREGATE(9,6,B2:B5). The result is 30 because the error is ignored and the three numbers are added.

AGGREGATE is not a universal replacement for SUM. Its behavior depends on the operation and options chosen, and it does not make bad source data correct. If an error signals a missing or invalid value that matters to your analysis, ignoring it could produce a misleading total. Consider finding and fixing the source of the error instead.

For a simple total that should include every value, SUM is easier to read and maintain. Use AGGREGATE when you specifically need one of its supported operations or error- and visibility-handling options. In Excel for Microsoft 365 on Windows, both functions are available; AGGREGATE is also available in Excel 2010 and later desktop versions.

Summarize only visible or filtered rows

A normal SUM includes values in rows hidden by a filter. If the goal is a subtotal that changes with the filtered records, SUBTOTAL is often the appropriate function. With values 10, 20, 30, and 40 in B2:B5, and a filter that hides the row containing 20, enter =SUBTOTAL(9,B2:B5). The result is 80 because SUBTOTAL with function number 9 sums the visible rows and excludes rows removed by a filter.

SUBTOTAL can also be set to ignore manually hidden rows. Function number 109 means SUM while excluding both filtered-out rows and manually hidden rows. This distinction matters when someone hides rows using the row heading’s Hide command rather than filtering the table. If you want a total of every record regardless of what is hidden, use SUM instead.

SUBTOTAL is useful for a visible subtotal, not for every type of analysis. It does not automatically create a separate subtotal for each category, and nested SUBTOTAL results are ignored within a referenced range to reduce accidental double-counting.

Group totals with a PivotTable

Use a PivotTable when you want to aggregate a value column by one or more descriptive columns. For instance, a transaction list with Date, Region, Product, and Sales fields can be summarized as sales by region, or sales by region and product. A PivotTable can also switch between totals, counts, and averages without requiring a separate formula for every group.

  1. Make sure the source has one header row, no blank header cells, and one record per row. Convert the range to an Excel table with Insert > Table if you want the source range to expand more conveniently as rows are added.
  2. Select a cell in the source data and choose Insert > PivotTable. Confirm the table or range and choose where to place the PivotTable.
  3. In the PivotTable Fields pane, drag a category such as Region to Rows and a numeric field such as Sales to Values.
  4. If Excel shows Count instead of Sum, open the value field’s menu, choose Value Field Settings, and select Sum. This often happens when the source column contains text or numbers stored as text.

To add a second level of detail, drag another category, such as Product, beneath Region in Rows, or put a field in Columns to display categories across the top. Use the PivotTable to explore groupings; use formulas when you need a result in a specific cell or a calculation that is tightly tied to a fixed layout. After changing source data, refresh the PivotTable using PivotTable Analyze > Refresh, or right-click it and choose Refresh. Excel for Mac has PivotTable support too, though ribbon placement and pane layout can differ by release.

Use Power Query for repeatable grouped summaries

Power Query is a good choice when data must be imported, cleaned, or grouped repeatedly. It can combine steps into a refreshable process, so the same transformations can be applied when a new file or updated table arrives. For a simple one-off total, it is more setup than necessary; for recurring reports, the repeatability can be valuable.

In Excel for Microsoft 365 on Windows, select a cell in a source table and choose Data > From Table/Range. In the Power Query Editor, choose Home > Group By. Select the field to group on, give the new result column a name, select an operation such as Sum, and choose the numeric column to aggregate. Select OK, then Home > Close & Load to return the result to Excel.

For example, grouping transaction rows by Region and summing Sales produces a row for each region and its sales total. Group By can also calculate counts, averages, minimums, maximums, and other supported summaries. Power Query is especially helpful when the incoming data has inconsistent column names or needs cleanup before grouping. Exact commands and editor details can vary slightly between Excel releases and platforms.

Common problems and how to avoid them

If a total looks too small or returns zero, first check whether values are real numbers. Numbers imported as text may not behave as expected in calculations or PivotTables. Look for left-aligned values, warning indicators, or inconsistent formatting, and use Excel’s conversion options or clean the source data before summarizing. Formatting a text value as a number does not necessarily convert the underlying value.

  • Unexpectedly high totals: Check for duplicated records, repeated headers, or overlapping ranges. A formula that includes both detail rows and an existing subtotal may count values twice.
  • Errors in the result: SUM can return an error if its referenced range contains an error. Find the source error, or use an error-aware method only if ignoring that value is appropriate.
  • Blanks and zeros: A blank cell and a numeric zero can look similar in a report but mean different things. Decide whether a missing value should be excluded, treated as zero, or investigated.
  • Totals that do not update: Check the formula range or PivotTable source. A formula referring to B2:B20 will not automatically include a new value in B21 unless the range expands; an Excel table or a deliberately updated range can help.
  • Wrong PivotTable summary: Check Value Field Settings and inspect the source column for text or errors. Excel may count values when you expected a sum.

For reliable results, keep raw records separate from summaries, use clear field names, and avoid inserting subtotal rows into the middle of source data. This makes formulas easier to audit and gives PivotTables and Power Query a clean, consistent input.

Frequently Asked Questions

How do I aggregate multiple Excel columns into one total?
Use SUM over the full rectangular range containing the columns. For example, if the values are in B2:D100, a single SUM across that range adds all three columns and all included rows.

How do I total each row across several columns?
Enter a SUM formula for the first row, using that row’s cells, then fill the formula down. Excel adjusts the row references so each record gets its own total.

What is the difference between SUM and AGGREGATE?
SUM adds the referenced values directly. AGGREGATE offers several operations and options to ignore specified errors or hidden rows, but it requires more deliberate setup. Use SUM for an ordinary total.

Does SUM ignore filtered-out rows?
No. SUM includes values in filtered-out rows. Use SUBTOTAL when the result should reflect the visible rows, and choose its function number according to whether manually hidden rows should also be excluded.

Can Excel aggregate columns by category automatically?
Yes. A PivotTable can group a value field by categories such as region or product, while Power Query can create a repeatable grouped result. Choose a PivotTable for interactive exploration and Power Query for a refreshable data-preparation workflow.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.