How to Aggregate Excel Table and Add Custom Columns in Excel Formula

To aggregate Excel table and add custom columns in excel formula, you need to use the AGGREGATE function and the calculated columns feature. Here is a brief overview of these tools:

• The AGGREGATE function allows you to apply different aggregate functions (such as SUM, AVERAGE, MAX, MIN, etc.) to a list or database with the option to ignore hidden rows and error values. You can use the reference form or the array form of the function depending on your input parameters. For more details, see AGGREGATE function.
• The calculated columns feature allows you to enter a single formula in one cell of a table column, and then that formula will automatically expand to the rest of the column by itself. You can use structured references to refer to table and column names in your formula. For more details, see Use calculated columns in an Excel table.

To illustrate how to use these tools, let’s create a scenario with some sample data and a desired result. Suppose you have a table that shows the sales amount and the commission rate for different salespersons in different regions, as shown below:

You want to add a new column that calculates the commission amount for each salesperson, and then aggregate the total commission amount by region. Here are the steps to do that:

• Create a table from the data range by selecting any cell within the data and pressing Ctrl+T. Make sure the My table has headers box is checked, and click OK. Excel will assign a default name to the table, such as Table1, but you can change it to a more meaningful name, such as SalesData, by typing it in the Table Name box on the Table Tools > Design tab.
• Insert a new column into the table by typing a column name, such as Commission Amount, in the cell immediately to the right of the table. Excel will automatically extend the table for you.
• Type the formula that you want to use in the first cell of the new column, and press Enter. In this case, the formula is =[@ [Sales Amount]]*[@ [% Commission]], which multiplies the sales amount by the commission rate for each row. Excel will automatically fill the formula into the rest of the column using structured references.
• To aggregate the commission amount by region, you can use the AGGREGATE function with the SUM function as the function_num argument. For example, to calculate the total commission amount for the North region, you can use the formula =AGGREGATE (9,5,SalesData [Commission Amount]* (SalesData [Region]=“North”)). This formula sums the commission amount column where the region column equals “North”, and ignores any hidden rows or error values. You can copy and paste this formula for other regions by changing the region name in the formula.
• Alternatively, you can use a pivot table to summarize the data by region. To do that, select any cell in the table, and go to Insert > PivotTable. Choose where to place the pivot table, and click OK. In the PivotTable Fields pane, drag the Region field to the Rows area, and the Commission Amount field to the Values area. Excel will automatically group the data by region and calculate the sum of the commission amount for each region.

Here is the result of using the AGGREGATE function and the pivot table:

I hope this helps you understand how to aggregate Excel table and add custom columns in excel formula.