In this example, we displayed two Average functions in the first row. Subtotals in the same row will always be of the result of the same function. It’s such a simple step, but the return is huge! The first shows two averages and the second shows the total number of units in stock. Uncheck the Replace Current Subtotals option. This next step is very important – do not skip it.Uncheck the Unit Price and Units In Stock in the Add Subtotal To list and check Units In Stock.This time, select Sum from the Function dropdown.In Excel 2003, choose Subtotals from the Data menu. Click anywhere inside the data and click Subtotal (in the Outline group).You sorted the data earlier by Category, so you don’t need to sort again.For instance, let’s sum the number of units on order – without deleting the existing row of averages: When you need to display different subtotaling functions, add another row of subtotals. What you can’t do is add a different function to the same subtotaling row. You could add more averages if you wanted to. How easy was that? With just a few clicks, you generated the average price and stock for each product category. Check the Replace Current Subtotals and Summary Below Data options.
CREATE MULTIPLE SUBTOTALS IN EXCEL HOW TO
Here’s how to get multiple functions: in the Add Subtotal To list, check Unit Price and Units In Stock.In the resulting dialog box, choose Category from the At Each Change In dropdown and Average from the Function dropdown.Click the Data tab and then click Subtotal in the Outline group. In this case, sort by the Category values to evaluate the many category groups. Because Subtotal evaluates group, you should always sort before executing the feature. First, sort the data by the column that contains the grouping value.Specifically, we’ll use this feature to return the average for both the Unit Price and Units In Stock fields in the data shown below: Multiple subtotaling functionsįirst, let’s use Subtotal to display multiple Average functions for the same group.
Two demo Excel files are available for you to download. In addition, it can evaluate the same group using a different function. What you might not know is that Subtotal can evaluate more than one column using the same subtotaling function.
You probably know that Excel’s Subtotal feature quickly generates subtotals for groups. You can add multiple subtotaling functions to the same row, and you can add multiple rows. There's more to Excel's Subtotal feature than you might realize. For example, if you're subtotaling sales figures, select the column that contains the dollar amounts.Display multiple subtotaling functions in Excel If you're breaking down sales figures by state, for example, select the state column.Ĭheck one or more of the column names under "Add Subtotal To." Select the columns that contains the numbers you're subtotaling. Select the same column you used for sorting earlier. Select the column you want to use as your subtotal category from the "At Each Change In" drop-down list. Select "Sum" from the "Use Function" drop-down list. To sort by a particular column, click anywhere in your data, select the "Data" tab and click "Sort." Change the "Sort By" setting to the name of the column, and then click "OK."Ĭlick anywhere is your sorted data, and then click "Subtotal" on the Data tab to begin creating your subtotals. To subtotal sales figures by state, sort the data by the state column. For example, if you have an inventory list that you want to subtotal by item type, you might sort the list based on the "Item Type" column. Sort the data based on the column you wish to use to define your subtotals.