Color code Excel values using criteria
Excel can filter a list by criteria using AutoFilter or Advanced Filter. For instance, you can use either feature to filter a list of orders to a particular product or salesperson. Or you can use both features to filter a list of orders to a particular product sold by a specific salesperson. The results are similar, but as you might suspect, AutoFilter automates the process.
What you might not realize is that you can use both filtering features to color code a list of values. I combined the AutoFilter feature and the Fill Color formatting tool to quickly color code the list of cities below.
First, you have to enable AutoFilter, which is simple to do. Just highlight the values you want to color code. In this case, that's E1:E10. Then, choose AutoFilter from the Data menu. Excel displays a small drop-down arrow in the column's header cell. Click the arrow to see a list of unique city values.
The next step is to color code by city as follows:
- Click the AutoFilter arrow and choose a city.
- Select just the resulting city cells. For instance, there are just two Seattle records, so select just those two cells. Be careful, because this step is easy to omit.
- Use the Fill Color tool to color the selected cells.
- Choose All from the City column's AutoFilter list to display the entire list.
Repeat this process until you've color coded all the city values. If you have several values to code, the process can become a bit awkward. I recommend that you work through the AutoFilter's list from beginning to end instead of jumping around. Also, assign colors beforehand, so you don't accidentally apply the same color to more than one value. You can disable AutoFilter when you're done -- the cells will retain their color codes.
The Advanced Filter criteria method requires a bit more work, as you must set up a criteria range and change the criteria values as you color code the list.
Now, the bad news is that you must update the colors as you add records. If you add a new record to the example's city list, Excel will assign yellow as the new City value's Fill Color, regardless of the actual value. This technique isn't terribly efficient for a list you constantly update.