Filtering: Basic How-To

If you have an inventory or other detailed list in a spreadsheet, chances are that sooner or later you’ll want part of the list, and not the rest. Looking at the example of the book inventory, imagine that there was a sale on a particular publisher’s books, or that you wanted to get a list of all the books you own by a particular author. Most spreadsheet programs make it easy to do this, by letting you filter your list to show only what you want it to show.

As a side effect, the filtering dialog can tell you what all the unique entries in a given column are. For example, if you had 30 books by “Robb, J.D.”, and 50 books by “Roberts, Nora”, then , it might be hard to spot that you also have 1 book by “Robbins, Alan” tucked in between them. However, when you open the filter dialog you only see one entry for each author, so “Robb, J.D.”, “Robbins, Alan”, and “Roberts, Nora” all appear exactly once.

Or let’s say that you’re looking at a Regency romance novel, and want to enter it, but don’t remember how you’ve entered other Regency romances. Did you just say “Regency”? How about “Historical romance”? Or maybe “Romance, Historical”? The filtering dialog lets you quickly scan through all of genres you’ve previously entered. It also lets you spot when you might have put something in two different ways, or mis-typed an entry (Fantsay instead of Fantasy, for example).

Of course, the details of how to filter depend on which program you’re using.

For any of these, you’ll want to have your selected cell somewhere within the text you want to filter. You can filter my more than one column — for example, if you want a list of all your hardbound fantasy novels you could filter by binding and by genre simultaneously. Chances are good that it will automatically treat the first row as column headers, so if you don’t already have column headers you might want to add them before doing this!

In Excel 97:

1. Click on the Data header (or type Alt-D)
2. In the menu that appears, click on ‘Filter’ (or type F)
3. In the sub-menu that appears, click on ‘AutoFilter’ (or type F)
4. Little arrows have appeared in each of the upper right corners of the columns that you can filter. Click on the one in the column you want to filter by.
5. Click the option that represents the value you want to remain. If you want more than one option for that row, or you want advanced options such as “does not equal”, “begins with”, “ends with” or “less than”, click on (Custom…)
6. If you want to filter by another column, go back to step four and repeat for the new column
7. When you are done with your filtered list, you can un-hide all the hidden rows and remove the little filter arrows by repeating steps 1-3.

In Excel 2007:

1. On the Home tab, look for the “Editing” group. For me it’s the last one on the right.
2. In the “Editing” group, click on “Sort & Filter”. You’ll get the same result whether you click on the little downward-pointing arrow or not.
3. In the menu that appears, click on “Filter” or type F.
4. Little arrows have appeared in each of the upper right corners of the columns that you can filter. Click on the one in the column you want to filter by.
5. In the menu that pops up, remove the checkmark next to “(Select All)”, and then add a checkmark next to the items you want to remain visible.
6. If you want to filter by another column, go back to step four and repeat for the new column
7. When you are done with your filtered list, you can un-hide all the hidden rows and remove the little filter arrows by repeating steps 1-3.

Note: You can also do some advanced filtering. Just above the box with the checkbox-list, there’s an option for either “Text Filters” or “Number Filters”, depending on the values in the column. The text filters let you choose options like “contains”, “does not contain”, “begins with”, etc. The number filters let you select items that are greater than, less than, or between values you specify, above or below average, or even simply a “top 10” list where it selects the 10 (or 5, or 100) biggest (or smallest) values.

In Google Spreadsheet:

1. Switch to list view by clicking on “View”
2. Over each of the columns you’ll notice a drop-down menu. At first they’ll all say “(All)”. If you click on one of the menus you’ll see the list of all the entries in each column. Select the entry you want shown for each column. You can only select one value per column.
3. When you’re done looking at the filtered lists, there’s a link at the top to return you to the normal view.

In OpenOffice Calc:

1. Click on “Data” or hit Alt-D to open the Data menu.
2. Click on “Filter” or type F to open the Filter sub-menu.
3. Click on “Standard Filter…” or type S to open the standard filter dialog box
4. In the dialog box, select the first column you want to filter, then the condition you want it to fulfill, then the value you want it compared to. For example, “Column A”, “=”, and “9” would show all the rows that had the value 9 in column A. “Column B”, “Largest”, and “5” would show the five rows with the greatest values in column B.
5. If you want to have another criteria, first choose either AND or OR, depending on whether you want both rules to be true for an item to appear, or are happy as long as at least one rule is true. Then follow the instructions in 4), and repeat as often as desired.
6. If you want the results pasted somewhere else, click on the “More Options” button, make sure the box next to “Copy results to…” is checked, then enter the cell reference where you want the results pasted in the box to the lower right.
7. Click the “OK” button.
8. If you did not paste the values elsewhere, but are done working with the filtered list, simply unhide the rows which the filtering process hid for you.

Note: OpenOffice has the most robust filtering functionality I’ve found. It can do everything Excel 2007 can do, and more. One thing it can do that Excel 2007 can’t is filter using OR across multiple columns — Excel can only use the OR within a single column. For example, if you were keeping a spreadsheet of classes taught, and classes could have both teachers and assistant teachers those would be two different columns. In most programs you can only do AND relationships between columns. That is to say, an item has to meet the criteria for both column 1 and column 2 for it to be shown. So you could filter to limit to all the classes where TeacherA was the main teacher, or you could filter to limit to all the classes where TeacherA was the assistant teacher, but if you tried to do them both at once you’d get no results because TeacherA would never be both the main teacher and the assistant teacher for the same class. However, OpenOffice lets you do an OR relationship between columns. With this an item only has to meet the criteria for column 1 or the criteria for column 2 in order to be included.

In Microsoft Works Spreadsheet:

You can’t. However, you can sort, which can make up for some of the missing functionality. I’ll discuss sorting later.