For business users of Microsoft Excel Free guides and templates

Managing Data

Use a 'Visible' Column in Formulas to Ignore Hidden Rows in Filtered Tables

Although Excel provides two worksheet functions that ignore filtered rows in a Table, nearly any function can ignore those hidden rows if you use this new trick.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

Excel Tables, introduced in Version 2007, give us the ability to use column filters to hide rows in a Table.

And slicers for Tables, introduced in Version 2013, make Table filtering much easier to do. (Excel 2010 introduced slicers for Pivot Tables.) With Table slicers, you can control your Table's filtering in a Report worksheet while your Table is parked out of the way in a Data worksheet in the same workbook. 

However, until now, we've had few ways to summarize the unhidden data in filtered Tables. But now, I've figured out how to expand our calculation options considerably with filtered Tables.

Summing All the Data in Filtered Excel Tables

The following figure shows a filtered Table. Because I created the figure in Excel 2013, I could include the slicers shown at the right of the figure. In Excel 2007 and 2010, I could filter the table only by using the filter dropdowns at the top of each column.

The list below the table illustrates different ways we can sum data in a filtered table. Formulas 1-3 ignore the fact that the table is filtered, and they return the grand total of the Sales data:

F13:  =SUM(Table1[Sales])

This formula is a simple sum of the Sales column in the table, which is named Table1.

F14:  =SUMIFS(Table1[Sales], Table1[Product], "*")

The syntax of the SUMIFS function is:

=SUMIFS(sum_range, criteria_range, criteria, ...)

Because the SUMIFS function can use wildcards, the asterisk ("*") tells the formula to return the sum of the Sales column for all Products. (I also could have specified Region. But I couldn't have specified the other columns because unlike the SEARCH function, the SUMIFS function can't use wildcards with numeric data.)

F15:  =SUMPRODUCT((Table1[Sales]))

The SUMPRODUCT function, can return the sum of a column, just like the SUM function.

Examples of eight formulas that return totals from a filtered Excel Table.

The remaining formulas in the list summarize only the visible data.

Built-In Ways to Sum Only Visible Data in Filtered Excel Tables

Formulas 4 and 5 use Excel functions with the built-in ability to ignore hidden rows.

F16:  =SUBTOTAL(9, Table1[Sales])

The SUBTOTAL function was designed to work with filtered data. It automatically ignores data in all filtered rows. It has this syntax:

=SUBTOTAL(function_num, ref1, ...)

In the formula in cell F16, the function number 9 tells the SUBTOTAL function to return the SUM of the Sales data.

F17:  =AGGREGATE(9, 5, Table1[Sales])

The AGGREGATE function works like an advanced version of the SUBTTOTAL function. It has two versions. This is the syntax for the reference-form version used here:

AGGREGATE(function_num, options, ref1, ...)

In cell F17, as with SUBTOTAL function, the 9 tells the function to return the SUM. Option 5 in the second argument tells the function to ignore hidden rows. And, of course, the ref1 argument in cell F17 tells the function to return the sum of the table's Sales column.

How to Set Up a Column that Reports Whether Its Row Is Visible

Look again at the table first shown above:

Examples of eight formulas that return totals from a filtered Excel Table.

The Visible column is key to how the last three formulas are calculated. The formulas in that column return 1 if the row is visible and 0 if it's not. 

The formula in this column is the trick that allows the remaining formulas to do their job. To my knowledge, this is the only way—without using macros—to write a formula that indicates whether its row is visible.

Here's the formula for the cell shown:

F13:  =(AGGREGATE(3, 5, [@Sales])>0)+0

Here's how it works:

The number 3 in the first argument tells Excel to use the COUNTA function. The number 5 in the second argument, tells Excel to ignore values in hidden rows. (This argument is key, because if the row is hidden, we want AGGREGATE to ignore any counts it might find.) And the third argument points to the values we care about...the Sales column, in this case.

The formula then tests whether the result is greater than zero. If the result is TRUE, the row is visible; if it's FALSE, it's not visible. Finally, the formula adds 0 to this logical result, which converts TRUE to 1 and FALSE to 0.

How to Use the Visible Column with Summary Functions

Here's the first formula that relies on the Visible column.

F18:  =SUMPRODUCT((Table1[Sales])*(Table1[Visible]=1))

This formula uses Method 2 of the SUMPRODUCT function to return the sum of the visible rows in Table1. It does this by multiplying each cell in the Sales column by the corresponding cell in the Visible column, then summing the result. Because hidden columns have a zero value, SUMPRODUCT returns the sum of only the visible cells.

Because SUMPRODUCT typically acts like an array formula, the (Table1[Sales]) section of this formula could be as sophisticated as you want. That is, you could perform many complex calculations on each cell in the Sales column. You could multiply it by other columns, square it, apply other worksheet functions to it, and so on...and then sum the results.

F19:  =SUMIFS(Table1[Sales],Table1[Visible],1)

This formula merely sums the visible cells in the Sales column. If your table has many rows, this is the preferred method, because SUMIFS calculates more quickly than SUMPRODUCT does.

F20:  {=SUM(IF(Table1[Visible],Table1[Sales]))}

This array formula that works about like the SUMPRODUCT version. You type it in as you normally would (without the braces), then hold down Ctrl and Shift before you press Enter. After you do so, the braces appear in your formula bar as shown.

The advantage of having an array formula available in your Excel toolkit is that you can calculate results on filtered data that you couldn't calculate any other way.

For example, suppose you want to find the growth rate in sales for the visible data. You could use either of these formulas to perform that calculation:

{=SLOPE(IF(Table1[Visible],Table1[Sales]),Table1[Date])}

{=SLOPE(Table1[Sales],IF(Table1[Visible],Table1[Date]))}

These formulas have two unusual characteristics. First, the IF functions have only two arguments, not three. Because we don't provide a value to return if the test is false, the functions return FALSE instead...which is just what we want.

Second, notice that the first version used IF in the first argument and the second used IF in the second argument. But they return the same result in all cases.

Here's one final example:

{=STEYX(Table1[Sales],IF(Table1[Visible],Table1[Date]))}

This array formula returns the Standard Error of the Estimate (essentially, the standard deviation around a trend line) for the visible cells.

In short, using the Visible column in your filtered tables gives your formulas a lot more power. Give it a try!

Take Your Next Steps

If you're looking for additional help with this topic, I can help you in three ways. To learn more, see Excel Training, Coaching, and Consulting.

 



Tags: #excel, #SUMIFS, #SUMPRODUCT, #SUM, #AGGREGATE, #STEYX, #SLOPE, #COUNTA, #visible, #visible column, #database, #Excel Table, #tables, #table slicers, #slicers, #filter
Free Excel Dashboards


Charley's SwipeFile charts