Microsoft spreadsheets allow you to create complex tables and perform different types of calculations with them. Excel functions help us in many situations and are rarely influenced by the layout and appearance of your tables: do you want to hide certain cells to get an overview? Nothing happens, the functions will continue to count the filtered values. However, it may not be what you want. If you want your calculations to take filters into account and exclude hidden values , the SUBTOTALS function will be very useful. This function combines many calculation operations and allows you to better control which cells the program has to consider in its operations..
Especially when working with large data sets, we don't need all ranges to be displayed. When we hide rows or filter specific values from being shown, large tables can be more easily understood. In this way, only essential data is visible and this makes work easier. However, hidden values are not lost, they are still part of the workbook, and therefore functions will fall back on them when they perform their calculations . This is a great advantage of Excel.
However, this advantage can also be annoying. If, for example, we want the Excel functions to respond to the current display of the data and adjust their results to it, we will not be able to do so with the usual calculation methods: we would have to create a new table in which the values would not appear. we have left out of the filter. With the SUBTOTALS function you have an alternative, because it can be adjusted in such a way that it does not include hidden cells in the calculations ..
SUBTOTALS combines eleven different functions in one. Users have to select what type of calculation should be used in the function and whether hidden cells should be taken into account or not. The function does not consider the values that are currently hidden due to the filter. Therefore, SUBTOTALS can be applied as an additional result alongside a final result. Comparing the two values usually provides additional information.
The full range of SUBTOTAL functions is only available if the table orientation is portrait. In this case, if a row is hidden, it can be taken into account in the calculation if you wish. However, if you distribute the values horizontally, that is, within a row, and then hide an entire column, this change will never be reflected in the result..
In theory, the SUBTOTALS function only requires two pieces of information: first, you enter which calculation should be carried out; the second argument, and all arguments after it, contain information about the cell range on which it applies . At least one piece of information is required, but in total up to 254 ranges can be included in the formula.
=SUBTOTALES(núm_función;ref1;[ref2];...)
With the first parameter, the function acquires the characteristics of another function (Excel provides a list to indicate which one). Enter the correct number and SUBTOTALS will perform the corresponding calculation. Each function has two numbers. Values between 1 and 11 indicate that SUBTOTALS will also apply to hidden values. By entering a value from 101 to 111, the function ignores hidden cells in the entered range. Here is a summary.
The hidden cells continue to form part of the calculation:
Hidden cells are not taken into account in the calculation:
All of these functions will ignore filtered values.
The classic example of the SUBTOTALS function in Excel is the calculation of a sum . With the function we can obtain a result that omits the filtered or hidden values, together with the final result, for which all the values are taken into account.
=SUBTOTALES(109;A2:A10;B2:B10)
In this example, we take values from two columns and add them together. As we have entered the value 109 for the first parameter (SUM), only the values that are visible at that moment will be taken.
The same works for other types of calculation as well. In this way you can, for example, calculate the average .
=SUBTOTALES(1;A2:A10;B2:B10)
However, it is also possible to combine SUBTOTALS with other functions . In this way, the result of the function can be transmitted to other calculations directly, without the need to create auxiliary cells for it. Thus, for example, several partial results can be added.
Sometimes it is useful to combine SUBTOTALS with the IF function . In this way, you can establish a row in which you indicate the desired type of calculation, for example, in the form of a drop-down menu. Although the formula will be a bit more extensive, once you have created it you will not have to apply modifications. Let's take as an example that you want to frequently switch between the three calculation forms SUM, COUNT, and AVERAGE.
=SI(A12="SUMA";SUBTOTALES(109;B2:B10);SI(A12="CONTAR";SUBTOTALES(102;B2:B10);SI(A12="PROMEDIO";SUBTOTALES(101;B2:B10))))
With the automatic filter, you adjust the data to be taken into account, while in the drop-down list you select the calculation you want. IF queries will always select the correct SUBTOTAL function.
In addition to the function that you can easily integrate into the formula, Excel offers a tool with the same name . This is in the tab? Data? and the button? Subtotal ?. This tool is useful if you use a multi-column list in Excel, for example. In a column is a fixed set of generally non-numeric values, such as names. These inputs, in turn, are mapped to numerical values: sales, scores, metrics, and the like. The tool helps you determine partial grouping results: all identical entries in one column are collected in one group and the corresponding values in the other column are calculated against each other.
This tool, which you can start with a button, also uses the SUBTOTALS function in the background. The tool covers the formulas for you and applies them to the correct cells.
However, in order to use this feature, you need to have made some preparations beforehand. The tool accesses an ordered list . The sort function can be found by right-clicking on the list range. Also, the columns in your list need headings in the first row.
If you check the list now and start the native tool, a new menu opens in Excel. In that menu you first select the column to be used for grouping. In this regard, the title of the columns is also important, as the corresponding range is selected by name and not by a cell reference. In the next step, you decide what type of calculation should be carried out. You have the same functions at your disposal that you can select with the SUBTOTALS function . Finally, you select which values should be included in the calculation. Here you can also select various options. Also, you have the possibility of making three aesthetic changes.
If you apply these options, Excel will modify the design a bit. In the left margin you have an additional menu with the interlacing of your list . With the corresponding buttons you can show and hide groups, and in this way adjust the design to your needs.
With SUBTOTALS, either with the function or with the tool, Excel offers you many options for handling tables and lists. Especially with large volumes of data, you will be able to get more order and get interesting information.