Filter Lists in Excel

Filtering an Excel List

Filtering is simple – and makes it easy to quickly find any record or records in your list with just a few clicks.

Like sorting, filtering in Excel can be done from the Home or Data tabs.

Starting on the Home tab, if I click anywhere in my data – so Excel knows where the data I want to filter is – I can click the Sort & Filter button, and then click Filter. Note that as soon as I do that, there’s a drop arrow next to each of my field names. That means I can filter by any field in the list.

At this point, I can click the drop arrow on the field I want to filter on, and see my filtering options. For example, here in the US Cities worksheet, if I want to see only cities with a Population of 1 million or more, I can choose Number Filters from that field’s Filter menu, and then choose Greater Than or Equal To from the pop-up menu.

In the resulting dialog box, all I have to do is type 100,000 in the box across from the Population box that shows the type of filter I asked for, and then click OK. Now you can see the cities that have a population of 1 million or more.

I can continue filtering, too. Now, if I want to see only cities with 100,000 people or more but only in warm climates, I can click the drop arrow on the State field and after unchecking all the states, go back through and check the states that have warm climates – California, Florida, Hawaii, Arizona, New Mexico to pick just a few – and then the list is filtered again.

Notice that the drop arrows on the 2 columns I’ve filtered now have a funnel symbol on them – that’s to remind me which columns I filtered on. I can clear one of the filters by clicking the drop arrow and choosing to Clear Filter on that field. Or, I can go to the ribbon and choose Clear from the Filter menu, and all filtering is removed.

The Data tab offers a Filter button, which turns the drop arrows on or off the same way the Filter command from the Sort & Filter button does.

Note that depending on the nature of the column’s data, your filter options change. For my Population, Longitude, Latitude, and Ranking columns, the Filter menu offers Number filters – based on values greater than, less than, or equal to a number you specify. You can also choose to filter for numbers that are an exact match, that fall between two values, or that fall into the top 10 values. You can also ask to see records that are Above or Below Average, which means Excel averages all the values in the column before filtering the data.

Columns containing text can be filtered to look for particular text. When you choose Equals or Does Not Equal, you’re asking Excel to filter for an exact string of text – a word, a phrase, or even just a few characters. Other options include Begins With, Ends With, and Contains or Does not Contain.

For example, I can say I want all the Cities with “field” in the name – choosing Contains and then typing “field” into the dialog box. I could further filter the County names for “field, ” too – so we end up with a list of cities with the same name as the counties they’re in.

As shown previously, I can then clear both filters in one fell swoop – and set about finding another set of records based on new criteria.