Excel 2007 - Keystrokes - Applying a Basic Filter

Applying a One Column Single Match Filter in Excel Using Keystrokes

We normally associate Excel spreadsheets with columns and rows of numerical data but for a lot of people Excel is used to capture other text-based stuff, such as the output from surveys or even contact information. Now, I don't want to open the can of worms argument about whether a single Excel spreadsheet is the right or wrong place to store data like that but, the fact is, it happens a lot. And when you collate data like this in Excel, you're really going to want to know at least a little bit about filtering.

What is a Filter?

You'll probably hear about sorting and filtering at some point if you go anywhere near Excel. The basic difference between the two is that while sorting will reorder your data, filtering can actually hide data that doesn't meet the conditions you've specified. So, filtering is much more useful when you need to narrow down your data and work on a smaller set of information.

Let's run through an example but first we need to have some data to work with. I am going to use the following details in Excel and I have popped a few of the cell references in the heading cells so you can understand where the data sits in the spreadsheet. However, there really isn't much choice here because, when you are working with data lists, your heading row should always be in row 1, with your data starting immediately in row 2.

Have a bash at recreating my example data below in Excel. Alternatively, choose one of your own files or create your own set of data to use:

Surname (A1)	City (B1)	Type (C1)	Books (D1)	Reference (E1)
Wilson (A2)	Birmingham (B2)	Mail (C2)	2 (D2)	A1 (E2)
Smith (A3)	Glasgow (B3)	Email (C3)	5 (D3)	A3 (E3)
Jordan	London	Mail	1	A1
Franks	Glasgow	Email	4	A2
Dalton	London	Mail	2	A3
James	Birmingham	Email	5	A2
Barton	London	Mail	4	A3
Fulton	Glasgow	Email	3	A2
Harris	Birmingham	Mail	4	A1
Simons	Newcastle	Mail	3	A1
Ferris	Birmingham	Email	5	A2
Taylor	Newcastle	Email	4	A3
Francis	Glasgow	Mail	2	A2

Now that we have our data let's play with some filters in Excel.

Applying a One Column Single Match Filter in Excel

As I mentioned earlier, the whole point behind filtering is to hide the data that doesn't match the specific criteria you have set. In our example data table, I might want to filter out all of the data that doesn't match a specific city, or match a specific mail type or even filter out the Book column where the values are less than a specific amount. Let's go through applying a filter using the City column:

1. Tap the ALT key to move the focus to the upper ribbon in Excel.
2. ARROW RIGHT to the Data tab then DOWN ARROW to the lower ribbon.
3. Press the TAB key to move focus eventually into the Sort and Filter Toolbar. Keep pressing TAB until you have focus on the Filter button, which will currently be inactive.
4. With focus on the Filter button, press the SPACEBAR to activate it.

NOTE: When you activate the Filter button it places a small clickable arrow graphic in the bottom right corner of each cell in the title row. This is a small visual indication that the filter feature is currently active and these drop down menus can be accessed for each cell using ALT DOWN ARROW.

5. When you apply the Filter feature, the keyboard focus will return to the worksheet area. Use the ARROW KEYS as necessary to move to cell B1 and open the drop down menu for the title cell using ALT DOWN ARROW.
6. DOWN ARROW through the drop down menu and you will eventually arrive at a tree view object labelled Select All, which will be checked. Use your SPACEBAR to uncheck this item and, when you do this, all the other checkboxes in the tree view are automatically unchecked too.
7. Now DOWN ARROW again and move through the other options - notice how all of the individual Cities are available for you to choose from.
8. Pick a city by tapping your SPACEBAR to check the relevant box.
9. After you have checked one of the options then press the TAB key to move to the OK button and press the SPACEBAR to confirm.
10. Focus returns to the worksheet but this time you should notice some differences. If you are using a screenreader for example you will notice that the cell references suddenly make no sense and that is because a portion of the data is now hidden from view. Similarly, on visual read through of the data, a potentially huge chunk will now be missing. In my case, filtering on the one city, I only have two rows remaining in the Worksheet that match "Newcastle".

Well done! Your filter is set and active.

Turning the Filter Feature On and Off

Generally you have two options here, these are:

Turn the Filter Feature Off Completely

1. Tap the ALT key to move the focus to the upper ribbon in Excel.
2. ARROW RIGHT to the Data tab then DOWN ARROW to the lower ribbon.
3. Press the TAB key to move focus to the Filter button - which will be enabled - and press the SPACEBAR to deactivate it. The Filter feature is now completely disabled and your data is back to normal.

Leave the Filter Feature Active But Display All Data

1. Move to the cell column title that you are currently filtering by and access the drop down with ALT DOWN ARROW.
2. Keep pressing the DOWN ARROW until you have focus on the tree view section as before, specifically the Select All option. Use the SPACEBAR to check this.
3. With focus on the Select All checkbox, press the SPACEBAR to check it.
4. Press the TAB key to move focus to the OK button and press SPACEBAR again to confirm.
5. Focus will now return to your worksheet with all the data revealed again although the filter feature itself is still active.

So there you go, a one column, single match filter. In a future post we will take the filtering on a bit further and look at multiple columns, working with the filtered data in the worksheet and applying some custom text filters. Have fun!