#172: AutoFilter Formula

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.

Read this as…If the total number of rows is greater than the number of rows being displayed, show “List Filtered” and the number of items displayed. Otherwise show “Not Filtered”

What do you use AutoFilter for in Excel?

One thought on “#172: AutoFilter Formula

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s