Now that we’ve been through filtering dates, text, and numbers in tables, Excel Video 36 demonstrates how to filter by color. Filtering by color is new in Excel 2007 and allows you to filter data by either the font color or the cell color. Video 36 uses three separate examples of how to filter by color.
We’ll start with a simple example of selecting all of the red cells in a column, which happens to be all of the patients from Florida. It’s easy to select only the red cells, and it’s just as easy to select all of the cells that aren’t red. The second example filters dates. Before the video, I selected all of the February 2010 dates and formatted them with a blue background. Then I selected all of the dates in a given week in February, and made the font color white. All of the February dates have a blue background. Some of the February dates have black text and some February dates have white text. It’s easy to select just the blue background cells or to select blue background and white text cells, all from the same menu. The last example has multiple background colors in the same E&M Code column. When you filter a column by color, Excel goes through and selects of the colors you have used to format the column and includes the colors in the filtering menu, making it easy to filter by each of the different colors.
One way I’ve seen filtering by colors used in practice is a group I do some consulting for. They download their over 120 day A/R from a Pivot Table to a table in Excel 2007. The administrator assigns each old claim to staff members and colors the background of the cells to track who is assigned to each claim. Filtering by color allows them to easily keep track of who is assigned to each claim and then filter to count the number of claims assigned to each employee, total the outstanding balance by employee, etc. using the totals feature we discussed in Excel Video 32.
Once we’ve covered tables, I’ll create some Excel Videos explaining conditional formatting, another powerful feature in Excel 2007. Filtering by color also works with conditional formatting, so keep that in mind as you keep watching this series.
