Today we will read on this page Rows Hide by a Filter – Use the SUBTOTAL function in Excel.

Use the SUBTOTAL function in Excel instead of SUM, COUNT, MAX, etc. to ignore rows hidden by the filter or manually hidden rows.

Download the excel data file here

Rows Hidden by a Filter in Excel

The SUM function below calculates total sales.

Apply a filter –> The SUM function includes the rows hidden by the filter –> The SUBTOTAL function ignores the rows hidden by a filter and calculates the correct result  – It is not easy to remember which argument is of which function. Fortunately, the AutoComplete feature in Excel helps you with this

To better understand follow the snapshot steps

Rows Hidden by a Filter in excel

Instead of using 101 (AVERAGE) for the first argument, you can also use 1 (AVERAGE), instead of using 102 (COUNT), you can also use 2 (COUNT), etc. When filtering data, there is no difference in numbers. 101–111 and the number is 1–11. We recommend using the numbers 101–111 because Excel uses these numbers in the total rows at the end of the tables.

Manually Hidden Rows in Excel

The SUM function includes a series of cells below.

Hide row 2  –> The SUM function consists of manually hidden rows  –> The SUBTOTAL function manually ignores the hidden rows and calculates the correct result.

To better understand follow the snapshot steps

Manually Hidden Rows in Excel

Numbers 101–111 ignore manually hidden rows. However, the numbers 1–11 contain manually hidden rows!