This is another in a series of Excel’s Conditional Formatting How-To’s. The pervious post looked at the Highlight Cell Rules option. (For Details See: Excel Conditional Formatting Highlight Cell Rules). In this article we are going to look at another option namely: Color Scales.

Using Color Scales is a popular formatting technique that lends itself very well to most spatial data – think of a map. Data with spatial significance simply means that there is a structural relationship with it and the data points around it.

Before we start, let us review the Conditional Formatting command. The command has groups of options that basically allow you to format cells based on the value of the data contained in those cells. The command is located on the Home Tab in the Styles Group:

Fig. 1 – Conditional Formatting Command

There are a number of options. In this article we will be focusing on one of the pre-set options, namely Color Scales. It is located in the center of that group (see Fig. 2):

Fig. 2 – Conditional Formatting Command Options

Color Scales:

A color scale assigns various colors to a range of values. Usually you assign colors that are related to each other. For a simple example, say we have a range of values between 1 and 10. We are going to assign various shades of grey to those values, changing from darker to lighter as we move through the range:

Fig 3. Example Grey Scale

This allows for an easy way to interpret the data by looking at the shades of colors. Note the gradual change from one shade to another. This gradient allows for finer detail than we saw in the last article. We can also use common interpretations to help us out. For example, a common convention, if we are using temperatures, would be Red for hot temperatures and Blue for cold temperatures. Doing this manually, with individual data points, would take some time; whereas “Color Scales” will do this automatically. We will use the following example to illustrate the process.

Example: Temperatures Over a Given Region

The data, see Fig 4, represents fictional temperature readings at given locations (also fictional):

Fig. 4 – Example – Data

Again, as in the pervious post, we ask ourselves a few questions. What do we want to “see” within this data? What would be meaningful? One answer might be that we want to see all the large numbers in varying shades of one color and the lower numbers in shades of a different color. Let us try a few of the Color Scales gallery of variations and see if they are useful.

First select your data (again, not strictly necessary but it streamlines the process) and click on the Conditional Formatting command. Hover over the “Color Scales” option and note the gallery of color scales that appears to the right:

Fig. 5 – Conditional Formatting – Color Scales – Options

Hover over the various options on the side gallery to get a live preview of the result. The first option, located at the upper left is generally the most popular. It formats the higher values in green and the lower values in red:

Fig. 6 – Details Conditional Formatting – Color Scales – Options

This will be misleading for our case since the convention is to use the warm colors (red, orange and/or yellow) for higher temperature values, and to use the colder colors (blue, green, and even purple) for the lower values. Therefore, let us try the color scale to the right of the one we just used. This option is much more intuitive since the larger, higher temperatures – are now in red.

Fig. 7- Details Conditional Formatting – Color Scales – Options

There are a number of other gallery options, and although many of the variations will work, the “Red-White-Blue” seems well suited for our example data set and our original goal:

Fig. 8 – Details Conditional Formatting – Color Scales – Options


Fig. 9 – Final Result

Without too much work, we were able to format the data using one command and a few mouse clicks. The result from using this color scale, for this example, is relatively intuitive – larger temperatures in red and lower temperatures in blue. The hot spots in the upper left area and in the lower middle area, now seem clear. The cooler area in the lower left and along the right side also seem much more obvious.

Color scales provide easy to interpret visual clues, making it a relatively simple to understand large amounts data – without being overwhelmed by all the raw numbers.

We will continue exploring the use of Conditional Formatting in future posts.