Excel Factor Entry 2 – AutoFilter

If you use Excel Filters then you’ll love this time saving tip.

This tip was sent in by Mohammed Mustafa of Jeddah, Saudi Arabia.

Once you’ve set up your filters it requires at least a few clicks to apply them, but not anymore. With AutoFilter you can do it in two clicks!

Taking the example below we’ll do it the slow way and then the AutoFilter way:

Let’s say we want to filter on the item Binders.

The Slow Way to Filter

1. Click on the down arrow in the Item header.
2. Uncheck ‘(Select all)’.

 


3. Check ‘Binders’ and click ok.

 


4. Voila, 4 clicks later we’ve filtered our data and have this:

 

The Quick Way to Filter

The quick way requires you to first put the AutoFilter icon in your Quick Access Toolbar (QAT) or in a Custom Group in the Ribbon, but once that is done it’s there every time you open Excel, to use again and again.

Locating the AutoFilter Button

1. Right-click on either the QAT or the Ribbon and select Customize Quick Access Toolbar, or Customize Ribbon, depending on where you want your icon. Personally I think it’s perfect for the QAT as it doesn’t require any navigating through the ribbon to locate it.

 


2. Choose ‘Commands not in the Ribbon’ from the ‘Choose commands from:’ list.

 


3. Locate AutoFilter and click the ‘Add >>’ button to add it to your QAT.

Ok, now you’re ready to use your icon in the QAT.

Using the AutoFilter Button

1. Select a cell containing the criteria you want to filter on. I’ll select binders in cell C4:

 


2. Click on the AutoFilter button in the QAT

 

Voila, in a maximum of just two clicks you’ve filtered your data.

You can continue to apply additional filters in the same way. For example, if you wanted to then filter on the Rep, Gill you could click on any instances of ‘Gill’ and then click the AutoFilter button and your data will look like this.

Click here for more on Excel Filters

Thanks for your cool tip, Mustafa.

Mohammed Mustafa is from Jeddah, Saudi Arabia. He loves Excel and teaches it on a part-time basis. 

Vote for Mustafa

If you’d like to vote for Mustafa’s tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment….or all of the above 🙂