How Does Excel Handle Unfiltered and Hidden Rows?
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.
Example Workbook
Download from here:
⬇️ Unfiltered-v-Hidden-Rows.xlsb
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! 😇
