Hello again, Central Office administrators. Today, it is all about how to filter data in excel. This is the function that I use most often when working with spreadsheets. The Filter function is by far my favorite, and I often turn filtering on as soon as I open a new worksheet.
Click below for my six (6) minute video on Filtering. I have included a link to the transcript below.
If you prefer text directions with screenshots, follow my step-by-step instructions below the video.
How to Apply and Use Excel Filters in a Data File
Step-by-Step Instructions With Screenshots for Using Excel Filters
Apply Filters
1. Open your spreadsheet. Your spreadsheet should include a header row.
2. Highlight the entire workbook by clicking in the upper left corner. This ensures that all your columns will be included.
3. On the ribbon, find and click the SORT & FILTER function.
4. Click FILTER. (Note that when you enabled the filters, drop down menus were added to the cells in your header row, as evidenced by the down arrows. See screenshot below).
Use Filters
Scenario: You need to see only students in grade 11.
5. Click on the arrow in the Grade field (row 1).
6. Check only grade 11 and click OK.
7. Note the count in the bottom left.
Scenario: Of these grade 11 students, you only want to see those that are in Dolls’ class.
8. Add an additional filter by clicking on the arrow in the Teacher_LN field.
9. Check Dolls and click OK.
Your sheet will now show all the students in grade 11 who have Mr. Dolls as a teacher.
In this sample data file that I am using, I would not need to apply the additional teacher filter since it is obvious that only two students have Mr. Dolls as a teacher.
Improve efficiency
However, most of my data files are not this small, and I could have had lots of grade 11 students enrolled in Mr. Dolls’ class. Applying the additional filter would have saved me a lot of time.
I am all about using technology to improve my productivity and to alleviate frustrating and tedious tasks. As I said earlier, as soon as I open a new spreadsheet, I typically turn on the Filter function.
Most often I am working with data files containing information similar to what you saw in my sample file in the video. The data file typically contains hundreds if not thousands of records, so filtering is a must.
One more example
Another way I use Filters is when I need to find missing information. For example, if I was missing a teacher ID number in a data file, I could filter for ‘blanks’ in the Teacher ID column. It would be obvious which teachers were missing their numbers, and it would be easy for me to add the missing data.
Resources
Step by Step Instruction Sheet with Screenshots PDF
Click on the image below for an infographic that you might find helpful on filtering data in spreadsheets.
How have you used filtering in your daily work? Tell me in the Comments section. I’d love to hear about it.
What other excel tutorials would you like to see? Send me a suggestion in the Comments section.
Have you signed up for my email newsletter yet? Get a free Blended Learning Resources mini-ebook when you do.
Recent Comments