Creating College Lists 101: Introduction to Using Filters

I’m not covering topics in the way most people are taught to use spreadsheets. I’m trying to provide enough information for you to be able to start using the DIY spreadsheets as soon as possible. And then I’ll start filling in other information that you need to know but wouldn’t be as useful in starting to use the spreadsheets.

That’s why in this lesson I’m covering using Excel filters before I talk about adding data, calculating equations, or any of the other things often covered before. Filters are the main way that you’ll be using the data so that’s what I think is the most useful for you to know starting out.

The spreadsheet tab in the workbook that I expect most people to work with is the Filters tab. Filters are the drop-down arrows you see at the top of columns.

If for any reason the filters disappear, there’s no reason to panic. You can easily recover the filters. Move your cursor to the top of one of the columns in the spreadsheet (It actually doesn’t make difference in this case but it can in the future so develop the habit now.). Select the Data Menu Tab and then select the Filters option, the Icon with the funnel.

The filters will reappear.

What Can You Do With Excel Filters?

In the last lesson, I used Excel filters to select only records that had a “yes” in the Use column I created. You can apply the same approach to any column with filters. In the following example, I’ll select only schools in Pennsylvania and New York. Click on the arrow at the top of the State column. Uncheck the Select All box.

Then check the states you want.

Now only schools from those states appear.

At the bottom left-hand corner you’ll see the number of rows, referred to as records, found. In the example, the filters selected for 255 of 1822 records found.

To clear the Excel filter and show all schools again, click on the filter icon which now has a funnel instead of an arrow. Select Clear filter for “State”.

You now see all of the records again.

Setting More Than One Excel Filter

You can set more than one filter at a time. In this example, I’ll select only for private schools in Washington and Oregon. As before, I’ll start with the State column and uncheck the Select All box and check the boxes for Oregon and Washington. At this point, Excel shows 53 records found.

Next I’ll click on the filter for the Type column and select just for Private schools. Now the filters for both the State and Type column show a funnel rather than arrows. Excel lists 23 or 1822 records found.

You can clear the filters on each column as demonstrated previously. Or you can select the Data Menu Tab and then the Clear option in the Sort & Filter group. This will clear all the filters in the current worksheet.

I often use this method when I have been setting multiple filters and don’t remember which filters I have actually set.

Setting Excel Filters for Numbers

Filters are also available for numbers. However, while selecting the actual values to display like I did for the states is possible, it is also pretty tedious. You’re better off using the Numbers Filter option from the drop-down menu. From here you can select the records based on their values such as equal to, greater than, and less than. In the following example I’m going to select schools with 5,000 or more full-time undergraduates.

Once I select Greater Than or Equal to… a Custom AutoFilter dialog box opens. I enter “5000” for my criteria and select OK.

Now Excel only displays the 437 records of schools with 5000 or more full-time undergraduates.

This should be enough on using Excel filters to get you started searching for schools that meet your specific criteria. I’ll be covering more on filters in later lessons.