Excel’s Conditional Formatting command allows for a simple and quick method to format a range or a table of cells based on the value of the data contained in those cells. This allows us to actually see certain features of the data. Over the next few Posts we are going to look at examples to show how the command can be used, along with a few of the options available.
The command is located on the Home Tab in the Styles Group:
When you click on the command a number of options will appear:
In the example that follows, we will explore the first option on the list, namely “Highlight Cells Rules”, (in the next post we will explore “Color Scales”).
Example: List of Students with Associated Test Scores
Following is a list of fictional student names and various test scores – also fictional. The first question to ask yourself is: What do we want to “see” within this data? There could be any number of answers, but what might be useful is a visual cue of all the test scores that are the highest, say those above 90. It also might be useful to see the scores that are the lowest, say those below 50. As an exercise we will work through the steps to achieve those two goals.
We will format the data in two parts: the first part we will work with the values over 90 and for the second part will work with the values under 50; and we will give each a different color.
Formatting the Data that is Greater Than 90
First select the data (although not strictly necessary it will streamline the process). Then click on the Conditional Formatting command and hover over the first option “Highlight Cell Rules”. A side menu will appear and, in that menu select “Greater Than …”
A pop-up box will appear, with a live preview of the default pre-set options:
The title and information line for the pop-up box is “Greater Than” which makes sense, since that is the option we chose. On the left side, the number 71 is automatically loaded for us. Excel is making an assumption that we might be interested in the data that is above the average. However, in our case we wanted to know which ones are above 90; so simply replace 71 with 90 (See Fig. 6B)
On the right side the default is “Light Red Fill with Dark Red Text”. Having the higher scores in red might be a bit misleading, so we will change it. Using the drop-down arrow, we can see other options. Let us simply use the second option “Yellow Fill with Dark Yellow Text”:
Click on OK to let Excel know that we are done with the pop-up box, and here is the result:
Formatting Data That is Less Than 50
Next, we wanted to “see” the data that is less than 50. Select the data again, if needed, then click on the “Conditional Formatting” command. Hover over “Highlight Cell Rules”, just as we did in the last part of this example. However, this time we are going to select the second option on the side menu, namely “Less Than …”.
The now familiar pop-up box seen in part 1 will appear with a live preview of the default options. Note that the title and information line in this dialog has changed to “Less Than”, which makes sense since we selected the Less Than option. Similar to the previous case, on the left side, the number 71 is automatically loaded for us. Again, Excel is making an assumption that we might be interested in the data that is below the average. In our case we wanted to know which ones are below 50; so simply replace 71 with 50. On the right side, the default is, again, “Light Red Fill with Dark Red Text”. Visually this seems appropriate, so we will leave that option untouched:
Click OK to commit the changes, and here is the final result:
We can now visually pick out the scores that are above 90 and those that are below 50 simply based on the color of the data and the cells. Highlighting these groups allows us to provide further analysis, or make adjustments, focusing on these sets of specific values. As data sets become larger, application of visual techniques becomes more valuable. We looked at the first couple of defaults available, and even with that, we were able to pick color values and easily get a visual representation that matched our goals.
In the next post we will look at a different data set and another option available on the Conditional Formatting command – Color Scales.