Excel Beginner Tip: The Power of Data Filters
The power of data filters is that they can help you find information quickly and increase efficiency. Hours of slowly scrolling a large dataset for a certain piece of information can be replaced by a few clicks.
A filter finds and displays all cell values that match a certain criterion. The data in your spreadsheet can be filtered by text, number or color.
Filter options are in the Sort & Filter section of the Ribbon’s Data tab.
Once the filter is turned on, columns can be filtered to find values that meet certain criteria by text, number, color or multiple criteria.
The filter option can be turned off by repeating steps 1 and 2 above. To remove any filters but keep the filter option, select the Clear button on the Data tab of the Ribbon next to the Filter button.
How to filter, sort and hide data?
Filtering is a topic we have covered before. If you’d like more information about how to filter, sort and hide data, read this previous blog post.
Use filters to check your data.
Things to check in your data:
- Filter for color. This could be helpful to flag cells of data with errors in them to correct later or know when all information is complete/submitted.
- Filter by text. This could be helpful to find cells that contain or do not contain a certain letter or word.
- Filter by number. This could be helpful to find cells that are equal, are not equal, or are greater than or less than a certain number.
- Multiple filter criteria. This could be helpful when searching for data that meet more than one criteria.
- Filter for blank or non-blank cells. This can be helpful if fields with blank data should not be counted for some reason or find them to update and add data.
Filter by color
If you take the time to color-code your data, it can then be filtered by color. This can be a helpful way to find specific records that you are looking for.
- Click on the filter arrow in the Name column (A1).
- From the drop-down menu, select Filter by Color.
- Then choose one of the options to either Filter by Color (A) or Filter by Font Color (B).
If the red color is selected in either 3A or 3B, this would be the outcome:
When would this be useful? Since Justin’s favorite color is blank, this record could be considered incomplete. Perhaps this record needs to be fixed or updated. By color-coding his name in red, he is flagged as having an incomplete record. By filtering this way, all rows with errors can be searched for and found easily.
If the green color is selected in 3A or 3B, this would be the outcome:
When would this be useful? Unlike the Justin example above, all green names have data for both age and favorite color fields. Perhaps these records are all considered complete. By filtering this way, all rows without errors can be searched for and found easily.
Filter by Multiple Criteria including by Text and Number
Now let’s add in a Text Filter. Suppose you are interested in finding the number of people with a complete record (green) and a favorite color that starts with the letter ‘b.’
From where we left off in the green example above, let’s add a new filter to the Favorite Color column that starts with a letter ‘b’ (a text filter).
- Click on the filter arrow in the Favorite Color column (A3).
- From the drop-down menu, select Text Filters.
- Then choose Begins With…
- In the Custom AutoFilter window, make sure that begins with is selected from the Favorite Color drop-down list and then type ‘b’ in the field next to it.
- Click OK.
The table then displays the result of the filter:
Now let’s add in a Number Filter. Suppose you are interested in finding the number of people with complete records under the age of 18. That is possible too!
From where we left off with a filter for words that start with a letter ‘B’ (a text filter), let’s add an age filter (a number filter).
- Click on the filter arrow in the Age column (A2).
- From the drop-down menu, select Number Filters.
- Then choose Less Than Or Equal To…
- In the Custom AutoFilter window, make sure that is less than or equal to is selected from the Age drop-down list and then type ‘18’ in the field next to it.
- Click OK.
The table then displays the result of the filter.
When would this be useful? As you can see, only one person meets this criterion, but imagine if your dataset had 5,000 people in it. It would take much longer to find them then! Filtering by multiple criteria can help you find specific data fast.
We hope this article helps you love your data a little more!
* * * * *
Are you interested in learning more Excel tips and running reports? Check out other articles on the HMIS blog.
Want to sign up for our newsletter? Go to the HMIS website, scroll down until you see the ‘HMIS E-mail Updates’ heading in the right column, add your email address, then click ‘Submit.’
