AutoFilter is a real life saver. Hardly a day goes by where I don’t use it. It is super quick and easy to use and can help you to make sense of big data, quickly.
What is AutoFilter?
Excel cleverly adds little drop-down arrows to the headings (top row) of your dataset, which you can then use to filter the information. Here’s a quick example.
Say you have a set of data showing various orders placed by sales reps. Each line represents one type of item, how many items were ordered, cost per unit, cost for the entire order, etc.
Assume you would like to review one rep’s orders only. It would be a waste of time to wade through the entire dataset to only look at one person’s orders. AutoFilter helps us to zero in on the stuff we’re actually interested in. The more lines of data, the more useful the filter becomes.
Step 1: Select the dataset, ensuring that you include the row containing the headers.
Step 2: From the DATA tab, select FILTER:
That’s it! You will now see drop-down arrows in each of the headers.
To review a specific rep’s orders, click the drop-down in the relevant column. Let’s have a look at Botha’s orders. Untick (Select All) and only pick Botha.
Everything else is filtered out and only Botha’s entries remain:
You can select multiple items, filter multiple columns, or use it to sort data by name, date, or even colour. Play around with it.
To clear all filters and start again, press the Clear button, and to remove the AutoFilter completely, just click on the big Filter button again:
Productivity tips:
It is important to do a few quick spot checks before creating the auto-filter
- Ensure there are no empty lines in your data
- Ensure that you don’t select any totals at the bottom when selecting the auto-filter range
- Be aware of any hidden columns that may interfere with your range selection.
Pro tip:
The cool thing is, when you have filters in place and you use AutoSum (position the cursor below the totals and press Alt = then Enter) the formula changes from SUM to SUBTOTAL, ensuring that only the filtered selection is added up:
Any other cool AutoFilter tips? Let us know in the comments!