This is the second post in a series intended to help nonprofit professionals develop confidence using Excel. The IllumiLab is excited to have one of our clients, Megan Ondr-Cooper of CASA of St. Louis sharing her Excel expertise with us.
In my last post, I shared my perspective on the value of Excel, a few steps to take before digging into your data, and keyboard shortcuts to save you time. In this post, I will cover filtering – something we all do everyday without even realizing it. When you shop online, looking for the perfect ___________, you filter your search by size, color, location, cost, etc. to find the right one.
What is Filtering?
Filtering allows you to hide certain records (or rows) so you can focus on a subset of your data or to help you find rows that meet certain criteria, making large datasets more manageable. Using filters with your data allows you to see your data in subgroups – by caseload, date range, service provider, demographic characteristics, etc. This saves you from having to endlessly eye-ball and tally or manually color code or cut and paste to arrange your data. I most often use the filter to preview my data and to get familiar with what’s in there. What type(s) of data are in each column? How many unique values do I have in each column? Is the data formatted consistently? Is any data missing? Filtering helps me answer all of these questions.
Filter Options
Different filters are available depending on the type of data in the column. You can filter:
- Text by looking for a particular word
- Numbers by looking for numbers that are smaller or greater than
- Dates by looking for dates within a certain range
You can add a filter to multiple columns to “drill down” into your data. For example, you can filter your dataset down to view only students that are in the 2nd grade that scored below reading level on their last assessment. Or you could filter to view only volunteers that applied last year that live in St. Louis County. In this way, filters can be mixed and matched to create the poor man’s query!
The filter can also be helpful to identify data entry errors and missing data. For example, it may be difficult to spot that a cell says “Esat Office” instead of “East Office” in a column of office locations. The filter menu includes a list of the values included in the column. You can select the incorrect value to filter down to only rows that contain that column value to correct the error. Also, if a column contains any blank cells, the filter list will include a “Blank” value.
Click here to learn to learn more about using the filter feature.
Try it out!
The next time you get started with a new set of data, try using the filter to get familiar with your data, identify errors, or examine subgroups. In the next post, I’ll illustrate the ways conditional formatting can help you “see” and make sense of your data.