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.