If you use Excel much, especially with large amounts of data, and aren’t using the AutoFilter function, stop right now and go learn about it. (You’ll be glad you did!) If you’re already using it, good for you – you know how useful it can be.
This week we’ll see a cool formula that will clearly show you and/or the people looking at your spreadsheets, if the data has been filtered or not. And if it is, how many records are being shown.
Warning this may register a bit high on the ‘geek meter’. But don’t let that scare you. If you’re still with me take a look below to see what this looks like in action.
The formula to make this work uses an IF statement in combination with the COUNTA and SUBTOTAL functions. ( If this is making your head hurt already, go ahead and skip down to the bottom, I won’t be offended. )
If you’re still with me, here is the formula:
=IF(COUNTA(B4:B3000)>SUBTOTAL(3,B4:B3000), “list filtered [“&SUBTOTAL(3,B4:B3000)&” items]”,”Not filtered”)
Now that is a pretty ugly looking beast, but don’t let that intimidate you. Here is the logic translated for normal humans.
What do you use AutoFilter for in Excel?