Today we are going to take a look at conditional formatting in Excel. Why spend time on conditional formatting. The simple answer is that it makes it easier to interpret your data. Management is busy (yes, really) so anything you can do to make it easy for them to interpret your data makes more likely they will interpret it correctly. Formatting is a major component to displaying business intelligence data. This time I’m going to show some of the formatting features of Excel that make interpretting the data to get at the real information easier.
I’m going to use an example with student test scores (Well, I do work at a school district so what did you expect?). Legal note: The student names and test score are fictitious.) The formatting concepts shown here will apply just as easily to the spreadsheet data at your organization.
The table below shows the initial list of 10 students and the results of three tests, each based on a maximum of 100 points.
Let’s suppose you wanted to find all of the student test scores that were less than 70. These are students that may need additional help. You can visually scan through the data to spot all the test scores less than 70, but it is easy to miss some of the scores, especially if you can imagine having a table of 25 students per class and 5 classes.
Conditional formatting provides an easier way to spot the low scores. To begin, select all test score cells that you want to check. In our figure that would be the range B3:E12. Then from the Home ribbon, find the Conditional Formatting button. This button should have a dropdown component to display more options. (Full Disclaimer Note: For those of you who may be wondering why my images look a little different than your images, I captured my images from Microsoft Office 2011 for the Mac).
Highlight Cell Rules
In the dropdown that appears, position your mouse over the Highlight Cells Rules option. Note the right pointing arrow which indicates that this menu option has another fly-out menu which automatically appears when you position your mouse over the menu option. From this list, let’s select the Less Than… option.
This option opens a dialog that less us specify how we want to change the formatting of cells that have a specific less than condition. The Mac version of Office displays several more options in this formatting rule (see image below) than the Microsoft Office 2010 for PCs version. In fact, the PC version only lets you enter the value to compare the cell values to and a dropdown list of pre-defined formatting options along with a custom option to let you define your own color formatting. Because we want to change the background color of those cells which have text score less than 70 to make them more visible, I entered the value of 70 and chose the format option: yellow fill with dark yellow text.
(A nice feature of the Mac version of Excel 2011 is the sample of the format.)
When you click OK, Excel formats only those cells in the highlighted range with test scores less than 70 as shown in the image below making it easy to spot the low test scores.
Now suppose that knowing the test scores below 70 are not enough. You also want to highlight test score below 60, perhaps showing them with a red background. Your first thought might be to go back to add another Highlighting Cells rule for cells that are less than 60 and color them red. On pressing Ok, you would see the following change to your spreadsheet. Everything is great, right?
While it make look ok, it is only correct because of the order in which you entered the rules. Had you entered the below 60 rule first and then entered the below 70 rule, your spreadsheet should have looked more like the following figure.
In other words, it would look like you had entered only the below 70 rule. The reason is that the order in which rules are entered is also the order in which they are applied to the spreadsheet. If you could slow the process down (or move at hyper speed mode like Superman), you would see that Brett S’s first test appears briefly in red before being changed to yellow.
Another way you can format the cells in a spreadsheet based on their numeric values is to use the Color Scales option in the Condition formatting dropdown. For this option, the fly-out displays different color combinations. These are gradients of either 2 or 3 colors. Excel does not ask how to apply the colors. It just acts over the range of numbers from the highest value to the lowest and defines the colors in a continuous gradient as shown in the next figure.
While a color gradient like this may be great when displaying your comfort level across daily temperatures, it may not be what you want for test scores. After all, a test score of 84, a ‘B’, for Phil C is almost yellow while a score of 89 which is more green. Furthermore, a score of 78, a ‘C’ is only slightly more orange than the score of 84.
What can you do? Select the cell range again and open the Conditional Formatting dropdown menu. At the bottom of the menu select Manage Rules.
In the manage rules dialog, you should see your rule. (If you have multiple rules defined already, you have to first select the rule you want to work with.) Click the Edit Rule button in the bottom left corner of the dialog. This opens the Edit Formatting Rule dialog shown below.
Notice that currently the color scale is defined as a continuous gradient from the highest value to the lowest value with the third color, yellow, at the midpoint (50%) of the value range. You can change this to use absolute values. For example, the following figure shows how to set the colors to specific values rather than percentages.
The nature of the 3-color scale will still create a gradient, but by managing the default rule definition, you can create your own custom gradient as shown in the following figure.
You can also display icons associated with the numbers in a set of cells. These icons are often used in KPI (Key Performance Indicator) found on management’s dashboards to help focus attention on the status of items. Let’s use the Avg Score column to display a set of icons to indicate how well each of the students performed over the three tests.
After selecting the data in the Avg Score column, open the Conditional Formatting dropdown menu and select Icon Sets. The flyout menu shows the different built-in icons that you can use. Icon sets contain from three to five icons. Let’s choose the 3 icon stoplight set. If we apply the icon set directly to the score range, Excel tries to divide the range into three evenly sized numeric ranges. In our case, each numeric range is (98.7-64)/3 or about 11.57. This explains why 88.7 is green while 83.7 is yellow since the green range is from 98.7 down to (98.7-11.57) or 87.13. There is no gradient in the icons like there was in the previous example. Therefore, every value in a range will display the same icon. The figure to the right shows the icons displayed on the left side of each cell in the selected range.
Again, if you don’t like the default, you can go into Manage Rules, select the rule you want to change and edit the rule as shown in the next figure. Let’s define any score above 80 to be green, scores between 60 and 80 to be yellow and scores below 60 to be red.
When you click OK, you can see the updated icons for Avg Score show no red icons because no student’s average score in this set falls below 60.
The last feature of formatting I will cover this time is Sparklines. Sparklines were ‘invented’ by Edward Tufte and was available as an add-in for Microsoft Office 2007 called TinyGraphs. As of Office 2010, Microsoft included Sparklines their version of Sparklines in Excel, not as an add-in, but as an option you can select out of the Insert dropdown menu.
The Insert Sparklines dialog lets you select the range of data you want to use when creating the Sparkline and the data range where you want to display the Sparkline. In this case, we want to select all of the test scores for all the students again as the data range for the Sparklines. We will place the Sparklines in the column to the immediate right of the Ave Score column. Excel figures out how to use the source data based on the relative orientation of the place where you are putting the Sparklines. Since we are defining a column in which to place the Sparklines, it automatically groups the value in each row of the data source to represent a single Sparkline in the output range. The figure to the left shows how I defined the ranges for my test score data.
In this last figure of this blog, you can see the Sparklines for each student.
The cool thing about Sparklines is that they show at a glance the data trend for the data. In this case, we can quickly identify students who have been improving (the Sparkline slants up as you look at it from left to right) and students which may be falling behind as shown by Sparklines that slant downward. The advantage of Sparklines is that they are ‘mini’ line charts that show the data trend. Imagine if you had to create a separate traditional Excel chart for each student to visually show the same type of chart. Not only would it take long, but it would take substantially more screen space. Sparklines are a great feature for dashboards to show trends in measured values over time among other uses.
Why is conditional formatting and Sparklines important? They are used in dashboards to help management quickly interpret the data displayed.
That’s it for this time. The next technical blog will begin a series looking at the DAX language as it relates to creating formulas for calculated columns in tables of the PowerPivot window.