Filters Make Excel Good To The Last Drop

Filters are a powerful tool in Excel that allow you to eliminate extraneous information from your view. For example, filters can be used to only show you the rows that have John Doe as the sales person or only show you rows that have blank cells in a certain column.

In order for filters to work properly, your range needs to have a column heading (a label at the top of each column). You turn on filters by clicking inside your range and then choosing Home-Sort & Filter-Filter. Voila, a drop down arrow appears next to each column heading. Clicking on any column’s drop down arrow provides a list of all entries in the column. Selecting any entry instructs Excel to show only the rows that match that entry.

Note that on the dropdown menu there are additional filters that can be applied, depending on what type of data is in the column. You may see Text Filters or Number Filters or Data Filters. From those dropdowns you can select additional filters to restrict your display even more.

After you have chosen a particular entry to be filtered, the dropdown arrow on that column changes its icon. To restore the view of all entries, click the dropdown and check Select All or click on Home-Sort&Filters-Filter to turn them off completely. More than one column can be filtered at a time, but they are cumulative. The second filter will only not overwrite the results of the first filter, but instead further restrict it.

There may be times however when you want to have even more filters applied to your data and have them all applied at the same time (essentially an OR command, instead of the AND command that filtering on multiple columns defaults too). In this case you need to use Excel’s Advanced Filter options.

You need to have at least three blank rows above your data range. Copy the column names to the first blank row so they are an exact match. Then in the second blank row, type the criteria you want Excel to check. There needs to be at least one blank row between the criteria and the data. 

If you type things in one row across, Excel makes it an “AND” filter. Each entry needs to be on its own line if you want an “OR” feature. For example, if you have columns with the headings Name, Age, Spouse and you create the following criteria:

It will only show rows where John is in the name field AND Mary is the Spouse.

On the other hand, if you create this:

Then it will find all rows where John is in the Name field OR Mary is in the Spouse field.

This example will find all rows where John OR Alice is in the name field OR Mary is the Spouse.

You can also use mathematical functions in criteria ranges.

This example would show all rows where the person was greater than or equal to 18 years old.

To run the advanced filter, click with your cursor on a cell within the range to be checked. Choose Data- Advanced Filter. At this screen you can choose to filter your list in place where it is or you can choose to keep your original data as is and put a copy of your filtered data in another place on your spreadsheet. In the List Range field, make sure that your list range is selected (not including those filter rows above your data range). Next you need to select the cells that contain your criteria (the column labels you copied to the first row and the information you typed in beneath them). If you want to copy your range to another location, you click on that option, then you click in the Copy To box and click the upper left corner of the place where you want the data to be pasted.

This is just the tip of the iceberg for Advanced Filters. You can put in multiple criteria for multiple columns or formulas and more.

I think you’ll find, as I do, that Filters make Excel Good to the Last Drop!

About the Author:
Marie Herman, CAP, OM, ACS, MOSM operates MRH Enterprises, LLC and has made her career helping others develop their skills. A master-level Microsoft Office Specialist, Marie is a corporate trainer, an author for Executive Secretary Magazine, and an expert on technology and professional development topics. She teaches online classes and certification study groups. She has more than two decades of experience as an administrative professional and efficiency consultant.