I have found Data Filters to be one of the most valuable Excel functions I have learned about in the past few years. Yes, it has been around for quite a while, but I don’t think that many people use it. You Should!
Here’s how it works. You have a table with column headers and one of the columns has dates in it. You turn on Data Filters and now, all of your column headers have a little drop-down arrow button in the right corner of each header label.
Click on the arrow button on a column full of dates and what you see in the filter box are the years, months, and days that are in the column. You can select one or more of the months or certain days from a month and have only the rows that match your selection listed. Excel hides all the rows that don’t match. You can filter any combination of columns to isolate the data you are looking for.
You can select specific values or filter on dates, numeric values, and matching the text. You can even filter based on color. This is particularly valuable when you have conditional formatting in use (and if you don’t, you should!). Weeding through a huge set of data looking for the one or two rows that are the outliers can be really tough to do manually. Yes, you could sort the data and find the high and low values. But with three keystrokes (see below), you can filter the data and scan through all the unique values in the list for those that seem unusual.
Allow you to filter the table by years, months, or days from values found in the table – or ranges, before or after a date, or periods like this week, last week, this month, next month – and much more.
Allow you to select specific values from the list of values found in the table or typical text filter values like, Begins with, Contains, Ends with and more.
Numeric and Color Filters
Numeric filters you are probably familiar with – Less than, Greater than, Equals, Not equals, etc.
Color Filters allow you to quickly find data by color and look at all the rows with the same color together in one place. Color can be very helpful with doing analysis. If you are not using conditional formatting and you are using Excel to do data analysis, you need to stop what you are doing and get familiar with that concept. It isn’t hard, but it is very powerful.
Copying Filtered Data: Once you have filtered a table, you can copy the filtered table and paste it to a new sheet or workbook. Only the filtered data is copied over, not all the hidden rows (careful, though. Formulas are lost in this copy, only the values are copied.) Something else important about filtered data. If you change the format or formula of the cell in a filtered selection and then copy it down the filtered list, only the VISIBLE cells are changed. This can be really helpful.
Using the Filter List Values
One of the best parts of using Data Filters doesn’t even involve filtering anything. I use it to find outliers – things in the list that are probably wrong. As soon as you turn on Data Filters and click the button in the column header, you get a sorted list of unique values found in that column. Scanning through it can give you a quick look at what might be messed up. Like a date with the wrong year, a negative value in a column that is supposed to have only positive numbers in it.
I use it to finding misspellings in text fields – example: you are looking at a list of hundreds of transactions that were manually created. One of the columns is department, but because the data wasn’t validated when it was created, instead of the 10 departments you expect to find, you find five versions of the Operations department:
Selecting the four incorrect versions in the filters list allows quick correction of the errant values by copying the correct value to all those on the filtered list.
Turning Filters On and off is really easy if you know the keyboard shortcut. Select a cell anywhere in your table. Control-Shift-L will toggle Data Filters on. Control-Shift-L turns filters off again. If you have to use the mouse, go to the Data tab, select any cell in the table, then click on Filter, the funnel icon.
So…I hope this helps you delve into Excel just a little more. As a knowledge worker, you and I have an obligation to constantly improve our skills. Learning new tools and techniques that make you more efficient is important.
Pillar #4 of CS3’s 18 Service Pillars is Engage in Knowledge Transfer. That’s where we as consultants share, train, answer questions, solve problems and then if you want to know, teach you how we figured it out. It’s one of the parts of my job I enjoy most.