Find the power of information right at your fingertips. KnowledgeDesk is a one stop nucleus for searching that vital piece of information, designed to make your search easier than ever before.
Keyword:
Category
Topic:
Previous  |   Random   | 
Filters Make Excel Good To The Last Drop by Marie Herman
Share/Save/Bookmark

Filters are a powerful tool built into 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 Data-Filter from the menu bar. Click on AutoFilter and voila, a drop down arrow appears next to each column heading. Clicking on any columns' 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 drop down menu there are additional choices. You can choose Top 10, Custom, a column entry or blanks or non-blanks. Top 10 will show the ten highest numerical or percentage matches in the column. Custom allows you to set two criteria, such as entries that are greater than 50 OR less than 12. Blanks can be used to easily fill in missing data or responses. Blanks will only be an option if there actually are blank cells within the column range. If there are no blanks, blanks and non-blanks will not be an option.

After you have chosen a particular entry to be filtered, the drop down arrow on that column changes color. To restore the view of all entries, click the colored drop down and change the option to All or click on Data-Filter-Show All. More than one column can be filtered at a time, but they are consecutive. The second filter will only fine-tune the results of the first filter.

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. 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. Type the column names (exact match) in the first blank row. 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, etc. and you create the following criteria:


Name Age Spouse
John Mary

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:


Name Age Spouse
John
Mary

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


Name Age Spouse
John
Alice
Mary

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.


Name Age Spouse
>=18

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-Filter-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. Your range should be selected with a dotted line, but if it isn't you can click on the cells manually to select them. 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 actually put in multiple criteria for multiple columns or formulas and more. Check out Help on Excel for even more information.

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



Bio: Marie Herman CPS/CAP, CIWD is an accomplished administrative professional. Marie also operates a successful business, MRH Enterprises (www.mrhenterprises.com), whose services include teaching computer and cooking classes, writing articles, and conducting workshops and other speaking engagements. She lives in the northwest suburbs of Chicago and squanders her free time learning nifty new computer tricks and tending her "backyard wildlife habitat"-certified yard. She can be reached at advertising@mrhenterprises.com.
Previous  |   Random   | 

Share this page with your friends