Filtering Excel lists to find data

Office for Mere MortalsLast updated: 16 September 2014

When an Excel list gets big and complex, the issue of finding data in it, and extracting information from it, becomes important. We look at how to filter an Excel list to extract information from it.

By Helen Bradley

Excel is a great place to store lists and one reason is that the data is visible and not hidden behind a database form so you know what you have. If you need to, you can easily see exactly what’s in your list and you can edit it if necessary. However, when a list gets big and complex, the issue of finding data in it, and extracting information from it, becomes important. If you can’t find what you’re looking for and it this isn’t pretty easy to do, then you’re in trouble.

So, in the interests of making lists more accessible, this week we’ll look at how to filter an Excel list to extract information from it. The AutoFilter tool we’ll use has been around at least since Excel 97 which is the earliest version I have handy for checking these things.

START THE FILTER

Starting the filter is as simple as clicking in the list and choosing Data, Filter, AutoFilter. When you do, a small down pointing arrow appears to the right of each column heading in the area that Excel has identified as containing your list. Click one of these arrows and you’ll see a series of options including All, Top 10 and Custom as well as an entry for each unique item from that column. If there are lots of unique items, you will need to move up the list to see the Sort Ascending and Sort Descending options.

To put the filter to use, let’s assume you have a list of sales people you deal with including their company names and addresses and sales figures etc.. You can view all people in a particular state by selecting the state, for example CA, from the list – of course, this assumes that all states are entered in a column of their own.

Down the left of the sheet you’ll see that the data hasn’t moved and instead, the rows are hidden that contain information that doesn’t match the criteria we’re searching for. To return to viewing all the entries you can choose All from the list on the States column but this requires you to remember which column you used to filter the data. If you use multiple columns as we’re about to, this process is about as easy as untangling spaghetti. Instead, I suggest you choose Data, Filter, Show All each time as it works regardless of which column you filtered. In fact, if you work with AutoFilter a lot, I suggest you put a button for Show All on your toolbar to speed things up.

MORE COMPLEX FILTERING

You can filter on multiple columns by selecting the items you’re interested in from the various columns. For example, you can search for all sales people in California who work for a particular company. Select the company name from the company name list and the state from the state list to do this.

For numerical columns such as a column containing the amount a company purchased from you in the last year, you can extract the top or bottom performers. The Top 10 option is the one you use but, as you’ll see when you choose it, it’s actually much more than a top 10 selector. From the dialog options you can choose the Top or Bottom performers in the range so you could choose the Top 2 or the Bottom 5. You can also select the top or bottom performers by items or by percent – so you can extract the Top 6 values or the Top 6 percent of values.

So far so good. But we’re about to run headlong into some limitations. Say you’re heading to the east coast and you want a list of everyone in ME or NH as you’re visiting these states. You can’t do this from the menu because you can’t select both states – just one at a time. The solution is to use the Custom option for the State column and enter Equals; ME; NH; Equals; NH in the respective boxes. This creates a condition which reads something like “show all entries where State is ME or NH”.

You can also perform AND searches in this dialog so you can, for example, show all companies which purchased between 500 and 1000 from you last year. To do this, select the column with the data in it and choose Custom. Set the values to Greater than or equal to; 500; AND, Less than or equal to; 1000. You must use the AND operator because both conditions must be correct – i.e. the value must be 500 or more and it must also be 1000 or less.

Note too that, if you want to include any company that purchases exactly 500, you must use ‘Greater than or equal to’ as the condition. If you use ‘Greater than’, companies that purchase 500 won’t be visible but those that purchase 500.01 will be. It’s all a matter of choice – it’s just important that you know what information you want to extract and then you write a condition that does the job.

AUTOFILTER HITS A BRICK WALL

While everything so far has worked just fine, there are some situations where the filter will let you down and some questions it just can’t answer. Let’s step back to the example of extracting people who work in California and who work for a particular company. We selected California (or CA), from the State column and the company name from the Company name column and we got the results we wanted.

What if we want, instead, everyone in California as well as everyone who works for a particular company. It’s a classic OR search – you want to list everyone whose entry in the State column is CA or whose entry in the Company column is a particular company. Some people might match both criteria but others will match one or the other and we want to see their entries too. There’s no easy way to make the filter work for a search like this, so chalk this up as a limitation.

MAKING CALCULATIONS

When you have a filter in place, you may recall from last week’s edition, the SUM function can’t be used to total entries because it totals everything visible and not. Instead, click in the cell below the column in the filtered data that you want to calculate a sum for and click the AutoSum button on the toolbar. It creates a formula using the Subtotal function which works to sum the column of figures, but only those values that are visible. Configure a different set of filter conditions and it continues to sum only the visible values – just the result you want.