Excel - Using Gradients to Correlate Data

Using gradients in Excel is not just to make your spreadsheet pretty. This type of conditional formatting makes it easy to see correlations and meaning in your data. You can see that put to use in my article on RAM Latency VS Clock Speed. In this followup article, I show exactly how that gradient was created.

click for larger view.

Conditional Formatting is a very powerful spreadsheet feature that is more than just for making pretty charts. I use it primarily for displaying correlations in huge datasets where such connections would be difficult to discern otherwise, and manual formatting would be a poor use of time at best.

In this short article, I will show you how to use conditional formatting to make the gradient used in the image above. If you are curious about that image, it comes from my article on RAM Latency VS Clock Speed.

A useful aspect of gradients (a.k.a. color scales) is that cells with the same color contain the same or similar values. In the chart above, that fact was used to make it easier for overclockers to find the correct initial latency for a new clock speed (the value in the chart needs to be the same for each combination).

To duplicate the gradient of the chart above, follow these steps:

1. Select the cells containing the data to be formatted.
2. On the home tab, select the Conditional Formatting drop-down button and then select Color Scales. There are some predefined scales you can use. I chose the second one in the image below and then modified it.
3. To modify the formatting, go back to the Conditional Formatting dropdown and select Manage Rules. The dialog that is displayed defaults to showing only rules that apply to the currently selected cell(s). So, either select one of the gradient cells or change the dialog to “Show formatting rules for: This Worksheet.”
4. Select the rule to be modified and click on the Edit Rule button.
5. The only change I made was to select a darker green and red for the minimum and maximum colors.

That is it. Powerful and easy.

Some additional things to note.

1. When I start on a spreadsheet, I usually set the background color for the entire sheet to white. This will cause all the borders to disappear. So, where I need to see borders, I add them manually. The result of this practice causes the data to stand out as there are no borders in the parts of the spreadsheet that contain no data. The overall look is very pleasing and more professional.
2. The colors you choose can convey meaning. In the example chart above, the more desirable the number, the darker the green and the less desirable the number, the darker the red. Most people already associate green with good and red with bad so work with that.
3. If you are curious about the diagonally split corner label in the chart above, it is not a built-in feature of Excel. To create this in your own spreadsheets, see my article on Dual-label Corner Cell (splitting a cell diagonally)