Excel: Subtotals for your calculations
Microsoft’s spreadsheet programme lets you produce comprehensive tables and complex calculations. Functions in Excel can assist in many situations and are rarely affected by the design and appearance of your tables. Want to hide certain cells for clarity? No problem: The functions still count the filtered values. However, you may not always want that. If you want your calculations to react to filters and hidden cells, the SUBTOTAL function is quite useful. It combines multiple calculation operations and gives you more control over the cells Excel includes.
What do you need Excel’s SUBTOTAL function for?
You may not always want to see all cells, especially if you are working with large volumes of data. You can hide rows or certain values in order to make it easier to work with comprehensive tables. This reduces tables to a size that only shows the essential data, which makes it far simpler to work with them. However, the hidden values are not lost – they are still part of the worksheet and are therefore still used for calculation functions. That is a major advantage of Excel.
But what is generally an advantage can also be a problem in some situations. For example, if you want the Excel functions to react to what is currently visible and adapt your results accordingly, you can’t do so with the normal calculation methods. You would have to create a new table that does not include the values that are filtered out. The SUBTOTAL function gives you an alternative: You can configure the function to prevent hidden cells being included in the calculation.
SUBTOTAL combines no less than eleven different functions: Users choose which calculation method is to be used in the function, and whether to include hidden cells or not. The function does not include values currently hidden by a filter in the calculations. Accordingly, SUBTOTAL is ideal as an additional result to complement the final result. Comparing the two values often gives additional information.
SUBTOTAL can only utilise its full range of functions if the values are arranged vertically. If a row is hidden in a case like this, that can influence the calculation if required. However, if the values are arranged horizontally, i.e. in one row, and a complete column is hidden, this change never affects the result.
SUBTOTAL syntax
Theoretically, the SUBTOTAL only needs two items of information. First, specify which calculation is to be performed. The second and all following arguments contain information on the range of cells used. One entry is required, but you can add up to 254 ranges to the formula.
=SUBTOTAL(Function;Reference1;[Reference2];…)The first parameter transfers the properties of another function to the SUBTOTAL function – Excel provides a list of functions. You enter the corresponding number and SUBTOTAL performs the corresponding calculation. However, there are two numbers for every function. Values between 1 and 11 configure SUBTOTAL to include hidden values. If you enter 101 to 111, the function ignores hidden cells in the specified range. Here is an overview.
Hidden cells remain part of the calculation:
- 1: AVERAGE
- 2: COUNT
- 3: COUNTA
- 4: MAX
- 5: MIN
- 6: PRODUCT
- 7: STDEV
- 8: STDEVP
- 9: SUM
- 10: VAR
- 11: VARP
Hidden cells are not included in calculations:
- 101: AVERAGE
- 102: COUNT
- 103: COUNTA
- 104: MAX
- 105: MIN
- 106: PRODUCT
- 107: STDEV
- 108: STDEVP
- 109: SUM
- 110: VAR
- 111: VARP
No matter which group you choose a parameter from, the values currently hidden in a table by the filter are never included in calculations.
SUBTOTAL in practice: Examples of the function
Calculating a sum is the best example of the SUBTOTAL function. Besides the final result including all values, the function also lets you calculate only the values not affected by filters or hidden values.
=SUBTOTAL(109;A2:A10;B2:B10)
In this example, we take values from two columns and add them. As we entered 109 for the first parameters, only values that are currently visible are included.
The same settings work with other calculation methods. For example, you can calculate the average.
=SUBTOTAL(1;A2:A10;B2:B10)
You can also combine SUBTOTAL with other functions. Accordingly, the result of the function can be passed on directly to other calculations – without requiring auxiliary cells. For example, you can add up multiple subtotals.
It can also be helpful to combine SUBTOTAL with the IF function. You can set up a cell in which you specify the required calculation type – for example using a drop-down menu. While that makes the formula itself somewhat longer, once you write it you never have to edit it afterwards. For example, if we assume that you want to switch between the three calculations SUM, COUNT and AVERAGE regularly.
=IF(A12="SUM";SUBTOTAL(109;B2:B10);IF(A12="COUNT";SUBTOTAL(102;B2:B10);IF(A12="AVERAGE";SUBTOTAL(101;B2:B10))))
Use AutoFilter to set the data that to be incorporated, and choose the required calculation from the dropdown list. The IF queries then always run the correct SUBTOTAL function.
Calculating subtotals using the Excel tool
Besides the function, which is easy to integrate in formulas, Excel offers another tool of the same name. You can find it in the “Data” tab under the “Subtotal” button. This tool is useful if you have a multi-column list in Excel, for example. You can include a fixed set of values (generally non-numerical) – for example names – in one column. In turn, these entries are assigned to numerical values: revenues, point scores, measurements and similar data. The tool helps you calculate subtotals from groups – all identical entries in one column are grouped and the corresponding values in the other column are calculated.
This tool, which you can launch at the push of a button, uses the SUBTOTAL function in the background. The tool completes the formulas for you and enters them in the right cells.
Before you can use the Subtotal feature, you have to do some preliminary work. The tool accesses a sorted list. You can find the sort function by right-clicking the list area, for example. The columns in your list also require labels in the first row.
If you select the list and open the Subtotal tool, Excel opens a new menu. First, select the column to be used for grouping there. (You need to label the columns for this, as you select the corresponding range using the name rather than a cell reference.) In the next step, you choose the calculation to be performed. You can choose from the same functions available in the SUBTOTAL function. Finally, choose the values to which the calculation is to apply. You can specify multiple values. You can also make three changes which are more cosmetic in nature.
- Replacing existing subtotals: Do you want the list to show subtotals from various calculations, or only to show the last calculation? You can specify that here.
- Insert page breaks between groups: There is a page break after each group list.
- Show results under the data: The subtotal for every group is shown directly under the respective group.
If you confirm your entries, Excel changes the view a little. You now also have an additional menu on the left margin, reflecting the nesting of your list. You can show or hide the groups using the corresponding buttons and adapt the view to your needs.
Excel gives you more options for working with tables and lists in Subtotal – whether as a function or as a tool. Especially with complex data volumes, this helps you clear things up and gives you interesting information.
Related articles
Rawpixel.comShutterstock Excel: ROUNDDOWN – an explanation of this handy function
When performing complex calculations in tables, you can quickly produce values that are not fit for everyday use because they have far too many decimal places. The ROUNDDOWN function in Excel can help with this. It uses a simple formula to simplify your workflow. You can use it to round down any number to the desired number of decimal places.
ChinnapongShutterstock Deleting Duplicates in Excel: How it Works
The Microsoft program Excel makes storing and processing data a piece of cake. But the more entries are added to a table, the greater the chance that some values are duplicated in the process. Excel offers a function for removing duplicates to solve this problem with ease. This allows you to delete redundant double entries with just a few clicks.
fizkesShutterstock What are if-then statements in Excel and how to use them
Excel’s if-then statement is one of Excels most helpful functions. In many situations, you can create a logical comparison: if A is true, then B, otherwise C. To use the if-then function in Excel, you first need to understand how it works. For example, which syntax rules does the IF function follow, and how can you add operators and other functions to an if-then statement in Excel?
Practical functions in Excel: COUNTIF explained
What is COUNTIF? With Excel, Microsoft provides a helpful spreadsheet program. The application combines numerous functions – most of which many users are unaware of. It therefore makes all the more sense to learn more about it and use the program for more than just creating tables. The COUNTIF function helps you create statistics, for example. We explain clearly how to use COUNTIF correctly in…
Rawpixel.comShutterstock Excel: What can you do with the SUMPRODUCT function?
Besides its clear tables and informative diagrams, Excel impresses in its ability to perform complex calculations automatically. Microsoft’s spreadsheet software offers many different tools to help you do just that. For example, you can use Excel’s SUMPRODUCT function to add and multiply values in a single step. We’ll explain how it works.
