M$ Excel : How to Capture the Max of Only the Displayed Values of a Column in a Table

Back to list

MAX is not your friend. SUBTOTAL is. How should you know that? Don't ask me.

So, you have your beautiful data and, you've done the Format as Table trick.

Now, you can quickly do filtering on it - to see how good/bad things are under different conditions.

Thing is, can you have a cell somewhere up top that shows you the MAX of your table, and uses ONLY THE DISPLAYED VALUES. Else, what's the point. Right?

Thanks to these (https://exceljet.net/excel-functions/excel-subtotal-function) guys, here's what you do :

You start typing : =subtotal

You'll get prompted by Excel with SUBTOTAL. Hit Tab key to select that and now, you have to choose the function. If you refer above page, you'll find that the version of the function whose number has "10" in front (i.e., 101 vs 1) ignores hidden values. So, for MAX, it's 104. Thankfully, Excel gives you a gorgeous drop-down menu that makes life super easy.

So, if you've named your table (good idea:), you'll end up with something like

=SUBTOTAL( 104, myTable[@col_name])

And it'll work great!!