By
Like most programs these days, Microsoft Excel is far more powerful than most folks realize. While you may not use 90% of its power, I will share with you what I think are two of the most power features that beginning Excel users can master right away. They are easy to access and understand and they can be used in a wide variety of circumstances. These two functions are also very effective when used together.
Sample Data:
The data that we will be using is a typical row report that includes different types of data. It includes dates (Date Opened), text (Gender, Race/Ethnicity) and it includes dollar amounts (Financial Outcome). Autofilters provides some special functions for each of these.
Part One: Autofilters
Enabling Autofilters
The first tool for your Excel toolbox is the Autofilters function. To turn this feature on, highlight the row that has your column heading in it by clicking on the row number in the left most column. In this example, click on 3 to highlight the third row:
Then, go to the Data ribbon and select Filter:
You will now see drop down indicators in the cell of each column heading.
These drop downs allow you to filter this large data set so that you see only those records that may be of interest to you, or to quickly count certain cases, or to total certain columns.
Autofilter on Text
What you see when you press the drop-down filter on the Gender field is a list of every unique entry in the entire list. If any of the cells are blank, you will have an option to filter the (Blanks) as well. By selecting and de-selecting the check-boxes, you can choose which values to show or to hide.
Because the drop-down shows you every unique entry, this is a quick and easy tool to find misspellings in text fields. Note the entries for both “Female” as well as for “Femal”. To fix those records, simply filter to show only the “Femal”s, then edit the spelling to correct it. Likewise, this will highlight other inconsistencies that may go unnoticed in a list that is too long to review each entry. Another example is with the county name. “Montgomery”, “Montgomery Co”, “MontoCo”, “Montgomery County”, and “Montgomery Coounty” [sic] because they are all different, will all appear as separate entries on the Autofilter drop-down highlighting an issue to be addressed. This type of inconsistency suggests that a change in the data entry process may be needed.
Autofilter on Dates
Autofilters on Date fields work slightly differently than they do with text. You can select and de-select which records to in-/exclude based on the year, the month, or the specific date. So, if you only want to see (or to quickly count) only the records that were opened in July of 2017, you can do that.
Also note that in the middle of the Autofilter menu there is a flyout menu for Date Filters which gives you quick access to additional date filtering functions such as Before, After, Between, This Month, Next Month, etc.
Autofilter on Numbers
Again, auto filtering on numbers is slightly different than for text or dates. You have a listing for each unique entry. This is a great way to quickly spot numbers that are “out of range”. It will also list zeros separate from blanks which may be helpful for finding records which are incomplete.
Again, there is a flyout menu for number filters so it is easy to filter based on functions such as Greater Than, Less Than, Between, Equal To, etc.
Stacking Filters
So you might start out with a report that has 2,000 records, but as you filter on each column, the filtering effect is cumulative and the number of records that meet the criteria will drop. For example, you may start out with 2,000 total records, but once you filter on Gender = Female the number of results will drop to, say, 1,200 records. When you add additional filter for Date Opened = March 2017, your results may drop even lower, to, say, 300 records. And when you then filter on Race = Hispanic, you may be down to a smaller working list that is easy to review or to export or otherwise work with. This cumulative filtering effect is called “stacking filters”.
Additionally, the entries in the Autofilter list are only displayed if there is a record. So if every female served is Hispanic, after you filter on Female, if you select the Race/Ethnicity Autofilter, there will be no option of African American or anything else. You will only see Hispanic. Again, just looking at what is presented in the Autofilter can be telling information.
[In the next article in this series, we’ll see how the Subtotal function can amplify the power of filters.]
