This is a guest post from Greg Matoka with the Excelgrm blog.
Pop quiz: Look at the table below in Figure CF- 1 and identify all the salespersons whose monthly sales were one standard deviation above average for that month. You have 2 seconds to answer.
Sorry, times up. Did you know the answer? I didn?t think so. Now try the same question on the following table in Figure CF-2.
Better? The point of this little exercise is to show the time savings that can be achieved with visual clues, or in this case, a bright yellow light bulb going off in the face. Because Excel spreadsheets are often a huge collection of data that can continue to grow larger over time, the user of the spreadsheet needs help in identifying key data in as short a time as possible. Requiring users to wade through rows and rows of data and perform mental comparisons and calculations is not a good approach to presenting information. You can see this in my video at the end of the post.
As I described in my article on data validation, where a goal of good spreadsheet development is to limit the possibility of errors by restricting what the user can enter into a cell, there is another rule that I like to follow: a user should be directed to what they want/need to see and not be distracted or confused.
There are different ways that the preceding information could have been obtained, but the most straight-forward in this case would be to conditionally format the cells so that the sales that are one standard deviation above average are highlighted against the rest. Sure, you could have filtered the data on the same condition, but you wouldn?t be able to see the other sales which are important as references or comparisons. Excel provides the wonderful conditional formatting feature to guide the spreadsheet user to the essentials.
How does it work
Formatting of Excel data has been around as long as Excel, i.e., set the color of a cell or cells so that they catch the reader?s attention. And even the ability to restrict or control the formatting of cells based on specified conditions (conditional formatting) has also been around since the beginning. However, with the introduction of the ribbon in 2007 and enhancements made in 2010, conditional formatting became easier and more powerful. To go through all the details and variations of conditional formatting is beyond the scope of this article. However, I believe that with a few examples of the fundamentals as well as conditionally formatting’s more expanded functionality, the reader can understand how it can benefit them and/or their clients.
Ribbon Access to Conditional Formatting
Here are the steps to use for applying conditional formatting to a cell or a range of cells:
- You must first understand what it is that you want to have stand out, and what the condition is that is needed. I.e., be clear on the data and the condition that?s to be applied to it. In the previous example, the data was the set of monthly sales data, and the condition was to identify those salespersons whose sales were one standard deviation above the average. This is a realistic scenario because that information can be used for monthly bonus payouts.
- Select the data to which you want a condition applied, and then select Home->Styles->Conditional Formatting. See FigureCF-3 below.
The drop downs displayed from the Conditional Formatting tab selection offer an unlimited number of ways to format and specify the conditions. The first two selections, Highlight Cells Rules and Top/Bottom Rules provide some quick and standard conditions and format types that can be applied. Data Bars, Color Scales and Icon Sets provide slightly more sophisticated formats. Figure CF-4 uses the table from Figure CF-1 but changes the condition to group the sales folks to show those sales people that were above 75% of average, those between 50-75%, and the laggard D?Antonio whose sales were below average for the month.
But wait, there?s more. Although Excel provides dozens of standard conditions that you can set against selected data that oftentimes is not enough, in which case you?ll need to create your own formula (rule) to control the formatting. The custom condition is generated by navigating through either the Conditional Formatting drop down box selection ?New Rule? (see Figure CF-3 above), or as a ?More Rules? selection in any of the drop downs from the standard drop down set. See Figure CF-5.
I’ve found that one of the best, but by no means the only, place to use custom conditional formatting is with reminders. By using the Excel date functions, you can set up your own little custom reminder system for many different types of tasks. Yes, I know there are gazillions of reminder systems available, many of which are free. But again, it?s the ability to create customized solutions to meet specific situations that make Excel so useful. Here?s an example. Suppose you have a little ticket broker business and you sell your tickets on one of the national ticket marketing sites. If you don?t sell your tickets by so many days before the event, then they?re removed from the exchange. So, you need to pay close attention to the ticket status so you can adjust your price and don?t get left having to steeply discount on game day by standing in front of the arena trying to get anything. Figure CF-6 shows a conditionally formatted spreadsheet that determines which color to fill the cell with, based on the ticket status, the last date available to sell the ticket, and the current date.
If the tickets are sold, then the transaction goes green. If there are between 11 and 12 days before the sale cutoff date then the transaction is on an orange alert status. If it?s between 6 and 10 day, then a yellow warning is indicated. Once day 5 is reached, it?s too late and the tickets are removed by the listing agency. You can see from this example that multiple rules can be specified for one range of cells.
Figure CF-7 shows the different conditions set for this rule that?s applied. This box is generated from the ?Manage Rule?? drop down selection (See Figure CF-5). Note also that the rules are evaluated in the order shown, and if the condition evaluates to TRUE and the ?Stop If True? checkbox is checked, then the formatting is applied and no more rules are evaluated.
Note: This spreadsheet is available for downloading off my website.
One more thing, if you want to quickly find all the cells on a spreadsheet that have conditional formatting applied to them, go to Home -> Editing -> Find & Select -> Go To Special? This will generate a selection box (Figure CF-8). Select Conditional Formats and All -> OK. This will highlight all the cells on the page that are conditionally formatted.
What to Watch For
- The rule must evaluate to True or False or the results are unpredictable.
- If you copy another cell into a conditionally formatted cell, the rule(s) are lost.
This post is from Greg Matoka: I created the Excelgrm blog in order to provide information and assistance regarding Microsoft Excel. I would like to use this blog to provide insights and instructions into the features that I have found useful, and which from my experience, I believe are too often ignored or overlooked by the average Excel user today.