Tips for Excel, Word, PowerPoint and Other Applications
AutoFiltering In Excel
Why It Matters To You
AutoFilter is one of my top 5 must know topics. When you have large data sets and don't need to summarized data, AutoFiltering is the fastest and easiest way of picking out exactly what you want to see. Want to better understand how many employees are in the Sales, in the Midwest, and have sales greater than x for 2006? You can AutoFilter your data set (assuming it already has that information) for each of those 4 criteria and see exactly who meets your parameters. This also highlights the importance of having a high quality data set to start with. If you have miscoded, missing, or inconsistent data, it will muddy your analysis.
How To ...
Open up the sample file, autofilter.xls. This is a table of registered voters and electoral votes by state. I created this during the 2004 election to better understand the effect of swing states, but it makes a good example file for AutoFilter.
Now, highlight the title row, go to the Main Menu and select Data: Filter: Autofilter (ALT + D, F, F). If you have a bunch of columns already filtered, Data: Filter: Show All (ALT + D, F, S), will show everything.
You should now see a set of filter arrows in the lower right hand corner of each column header. If you click on any one of these filter arrows, it will show you a unique list of every value in the list as well as couple of other options. Selecting an option will hide everything NOT meeting your criteria. If you filter on multiple columns, you will finally end with a set of entries that met all of your criteria.
- Sort Ascending/Descending - this works just like the sort options, it's just contained in one nice spot. Be careful though, because this changes the layout of your table.
- Specific value - use this if you're only looking to filter for a discrete criteria (e.g., states in the West region)
- Top x - This helps you focus on the top x items in a list. For example, I know I have a ton of employees, don't really know their ranges of sales, but want to see the top 20 sales reps.
- Blanks/NonBlanks - this is a very useful data cleaning tool. If I'm trying to assign regions to each state, I can filter by the Blanks to see what I've missed.
- Custom - use this option if you need to sort a single column according to multiple criteria, for example states with registered voters of more than 1,000,000 AND less than 2,000,000.
If you select a custom filter, you'll see the box below. You can sort by up to 2 criteria and make the result contingent on meeting either criteria (OR) or meeting both criteria (AND). You have quite a number of options for filtering. In addition to the standard inequalities (is greater than, is less than), you can also filter for Begins With, Ends With, Contains, Does Not Contain, etc. Say I'm looking for a specific employee and I can remember their name, but I'm pretty sure it started with an "mi", you could Filter by Starts With "mi". Try filtering the states for names beginning with "ne". If I just wanted states with "new" in the name, I would set my filter criteria for Contains "new".
Download the sample file, and try it out. Start by enabling AutoFilter (ALT + D, F, F) then give it a spin. You'll never go back.
Notes
| Last updated | 9/5/08 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | None |
| Related Tips | Data Validation in Excel |