Did you know your Excel calculations can be affected by filtering tables or hiding rows?

It’s one of those questions you may well answer with an emphatic yes. But if I dig beneath the surface, I know many of you wouldn’t be able to tell me the difference between the SUM, SUBTOTAL and AGGREGATE functions, a 9 and a 109, or a 4 and a 5.

If that sounded like gobbledygook, don’t worry, because all will be clear by the end.

When you work with numeric table data in Excel, there’s a good chance you’ll want to perform a calculation on at least one of the columns. Whether that’s summing, counting, or finding the maximum or minimum figure. It’s also likely you’ll want to filter the data or hide parts so you can home in on specifics.

When I started this experiment, I wasn’t a fish out of water by any means. However, I still wanted to perform a few tests so I could observe *exactly* how Excel handles unfiltered and hidden rows. Moreover, I was interested in how this impacted a column’s total, so I was equipped with the knowledge to choose the best method for a given scenario in the future.

Before you delve into the workbook, let’s just clear up the difference between an unfiltered and hidden row:

## Unfiltered

Rows that are part of a table, but don’t display because they fail to meet the chosen criteria. Conversely, the filtered ones are visible and have blue row numbers.

## Hidden

Worksheet rows that are out of sight. This can be done by selecting one or more rows, right-clicking on them and choosing **Hide**. **Unhide** makes them visible again.

# Sample Data

I’ve used sample sales data from contextures.com for the examples. The table is found at the bottom of the worksheet.

The **Total** row is a feature of Excel tables and can be toggled on or off. To do this, go to the **Table Design** contextual tab and check or uncheck the **Total Row** box. By clicking on any of the columns in that row, an in-cell dropdown can be accessed allowing you to perform a specific calculation for the column. The default options are SUBTOTAL’s 101–111 functions, although you can use custom formulas as well.

$19,627.88 is the total sum of **all** sales in the table. I have used this as the baseline figure for every example.

# Functions

The **Functions** section contains five examples of calculating totals, each using a different formulaic approach. Although they all focus on summation, these principles also apply to other methods of calculation.

## SUM

**=SUM(number1)**

*Used in **F1**, *** S1.F1** and

*S2.F1*The simplest way of including all rows in a calculation is to use the SUM function by defining a range for the `number1`

argument.

There is no catch — all rows in the **Total** column are summed, irrespective of being unfiltered or hidden.

## SUBTOTAL

**=SUBTOTAL(function_num,ref1)**

*Used in **F2**, **S1.F2**, **S2.F2**, **F3**, *** S1.F3** and

*S2.F3*SUBTOTAL has a `function_num`

argument consisting of a list of functions. Numbers 1–11 include hidden rows in their calculations, whereas 101–111 exclude them.

⚠️* I will caveat the previous paragraph by warning you of an issue with SUBTOTAL (9) if you filter first and hide rows second. I talk more about this in the **Scenarios** section.*

9 - SUM is used for the **F2 **examples, and for the **F3** ones it’s 109 - SUM.

For both, the `ref1`

argument references the **Total** column of the data table.

## AGGREGATE

**=AGGREGATE(function_num, options, array)**

*Used in **F4**, **S1.F4**, **S2.F4**, **F5**, *** S1.F5** and

*S2.F5*AGGREGATE also has `function_num`

, but it’s a little different. There are 19 distinct functions compared to SUBTOTAL’s 11. Additionally, there is an `options`

argument that has eight variations for how rows should be treated.

Choose option `4`

to calculate everything—no matter what. This will result in a formula that mimics SUM.

To achieve the same result as the 109 variant of SUBTOTAL, you must use `9`

for `function_num`

as there is no alternative. However, by specifying `5`

for `options`

, you can ignore hidden rows.

The `array`

argument contains the range to calculate.

# Scenarios

For you to understand how calculations may be impacted by your actions, I’ve included two scenarios:

**Scenario 1**is based on filtering first and hiding rows second**Scenario 2**is based on hiding rows first and filtering second

I’ve outlined each step and included the total, so you can see how the values change depending on the action taken.

For the most part, there are no real surprises with the results Excel pumps out. However, look at the differences between the scenarios. Apart from examples **S1.F1**/**S2.F1** and **S1.F4**/**S2.F4**, the others produce different results depending on whether the rows are filtered or hidden first.

For **S2.F3** and **S2.F5** — by hiding the first five rows of the table, three rows with **Central** as their region are taken out of the equation. Along with two others, they add up to $2,075.36. However, despite hiding these first, filtering for **Central** does not make a difference to the final total of $11,139.07.

## A Bug?

The most interesting observation is how Excel deals with SUBTOTAL (9) formulas. When you filter first and then hide, you would expect the latter to produce the same result as the former. After all, function 9 is meant to include hidden rows in calculations.

Why doesn’t it then?

In Bill Jelen’s YouTube video, he mentions at 1:11 this might be a bug. The video was posted on 28th February 2019 — and as of today — the behaviour is still present. That would suggest it’s a deliberate feature, or perhaps Microsoft just hasn’t got round to fixing it yet. I don’t know.

# Final Words

I hope with your newly acquired knowledge, you now have a greater understanding of how unfiltered and hidden rows are handled.

Most of the time you’ll depend on the in-cell dropdowns in the **Total** row to do the hard work for you. But there’s bound to be instances where you want multiple variations displayed simultaneously.

I hope you’ve had a chance to play about with filtering and hiding rows yourself, so you’ve picked up on the nuances and cemented your knowledge. If you’re still not familiar with the SUBTOTAL and AGGREGATE functions, it’s worth exploring them in-depth. Especially the latter, as the choices in the `options`

argument offer you a lot of control. I only showed you two, but there are others you might be interested in.

I’ll end by reiterating the point I made at the start: the majority of people could not explain the difference between an unfiltered and hidden row, but you can! **😇**