OfficeTalk: Conditional Formatting: Adding Customized Color Scales to Excel 2007

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Summary: Color scales give you an opportunity to create visual effects in your data to see how the value of a cell compares with the values in a range of cells. Learn how to implement and manipulate color scales through the user interface and programmatically. (6 printed pages)

Frank Rice, Microsoft Corporation

May 2007

Applies to:   2007 Microsoft Office Suites, Microsoft Office Excel 2007

Contents

  • Overview

  • Color Scales and Data Bars—What's the Difference?

  • Easily Visualize Data Values Within a Range

  • Implementing Color Scales Programmatically

  • To Summarize

  • All About Conditional Formatting in Excel 2007

Overview

In my previous column on conditional formatting in Excel 2007, I talked about data bars that allow you to see the hierarchy of values within a range of values. By using data bars, you can quickly see which value is the largest, which is the smallest, and everything in between. Color scales are a close cousin to data bars in that they provide a comparison between a selected range of cells, they use visual effects to communicate the results to users, and they are configurable with respect to setting color and thresholds.

Color Scales and Data Bars—What's the Difference?

Color scales are visual guides that help you understand data distribution and variation. So how is a color scale different from a data bar? A color scale uses cell shading, as opposed to bars, to communicate relative values. This is extremely useful when you want to communicate something else about your data beyond the relative size of the value of a cell—for example, if high numbers are good and low numbers are bad, or if you want to understand variation in your data, color scales are a great choice. You can apply either a two-color or a three-color scale to a range of data, data in a table, or data in a Microsoft PivotTable report. For a two-color scale conditional format, you assign the value, type, and color to the minimum and maximum thresholds of a range. A three-color scale also has a midpoint threshold.

Easily Visualize Data Values Within a Range

To see color scales in action, let’s look at an example. Say you have some data about investment returns as shown in Figure 1.

Figure 1. A table of investment return values

If you select that range of values and apply a color scale, the range now looks like Figure 2.

Figure 2. Color scales applied to the table of values

It is now easy to see good returns, weaker returns, trends, and outliers. The values closer to red are bad, the colors closer to green are good, and yellow is in the middle. This works in a similar fashion to data bars in that Excel is comparing the values in each of the selected cells and assigning a background color based on a cell’s value relative to all other selected cells. The colors provide clear information to users of where individual cells fall in relation to other cell values. By default, when you apply color scales with one click, Excel uses the lowest, highest, and midpoint values in the range to determine the color gradients. As with data bars, you can specify the values that determine what colors to use—you can tell Excel to use the lowest/highest/middle value, or for each of the colors, you can specify a number, percent, percentile, or formula. Figure 3 shows the user interface (UI) used to change settings on color scales.

Figure 3. The color scale UI in Excel 2007

Implementing Color Scales Programmatically

As you might suspect, you can also implement color scales programmatically. The ColorScale object represents a color scale conditional formatting rule. You can create a formatting rule for color scales by using either the Add or AddColorScale method of the FormatConditions collection. There is also a ColorScaleCriterion object that represents the criteria for the minimum, midpoint, or maximum thresholds for a color format conditional format. There are associated methods and properties where you can set the color of the minimum, midpoint, or maximum thresholds. The type of threshold value for a data bar or color scale can be a number, percent, formula, or percentile. You can also set the priorities of the conditional formatting rules used with color scales. Let's see how this works.

Applying a Two-Color Scale to a Range of Data

The following code example creates a range of numbers and then applies a two-color scale conditional formatting rule to that range. The color for the minimum threshold is then assigned to red and the maximum threshold to blue by indexing into the ColorScaleCriteria collection to set individual criteria.

Sub CreateColorScaleCF(control As IRibbonControl)

    Dim cfColorScale As ColorScale
    
    'Fill cells with sample data from 1 to 10
    With ActiveSheet
        .Range("C1") = 1
        .Range("C2") = 2
        .Range("C1:C2").AutoFill Destination:=Range("C1:C10")
    End With
    
    Range("C1:C10").Select
    
    'Create a two-color ColorScale object for the created sample data range
    Set cfColorScale = Selection.FormatConditions.AddColorScale(ColorScaleType:=2)
    
    'Set the minimum threshold to red and maximum threshold to blue
    cfColorScale.ColorScaleCriteria(1).FormatColor.Color = RGB(255, 0, 0)
    cfColorScale.ColorScaleCriteria(2).FormatColor.Color = RGB(0, 0, 255)
        
End Sub

Adding Color Scales from a Customized Ribbon UI

To implement this example, add a tab and button to the Ribbon in a macro-enabled Excel 2007 workbook. To do so:

  1. Add the CreateColorScaleCF procedure to the Excel document.

  2. Save the workbook as a macro-enabled file.

  3. Open the file as an Open XML Format file.

  4. Modify it by adding the ribbon customization XML file and updating the relationship part.

Begin by creating the workbook:

  1. Start Excel 2007.

  2. On the Microsoft Office button, point to Save As and click Excel Macro-Enabled Workbook. Save the file with the default name Book1.

  3. On the Developer tab, click Visual Basic.

    Note

    If the Developer tab does not appear in your workbook, do the following:

    1. On the File menu (the large Microsoft Office button), click Excel Options.

    2. On the Popular tab, select the Show Developer tab in the Ribbon option.

    3. Click OK to close the dialog box.

  4. In the VBA Project pane of the Visual Basic Editor, double-click the ThisWorkbook node.

  5. In the code window, paste the CreateColorScaleCF procedure shown previously.

  6. On the File menu, click Save Book1.

  7. Close the Visual Basic Editor and then close the workbook file.

Next, create the Ribbon customization file:

  1. On the Microsoft Windows Desktop, create a folder by right-clicking, pointing to New, and then clicking Folder. Name the folder customUI.

  2. Create a file in any text editor such as Notepad.

  3. Add the following XML markup to the file:

    <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> 
  <ribbon startFromScratch="false"> 
    <tabs> 
      <tab id="CustomTab" label="Color Scales Demo"> 
        <group id="SimpleControl" label="Conditional Format"> 
          <button id="Button1" imageMso="ViewBackToColorView" size="large" 
            label="Add Color Scales" 
            onAction="ThisWorkbook.CreateColorScaleCF" /> 
        </group> 
      </tab> 
    </tabs> 
  </ribbon> 
</customUI>

This code creates a tab, group, and button to the Ribbon in the workbook and assigns the CreateColorScaleCF procedure to the button.

  1. Save the file as customUI.xml in the customUI folder on the desktop.

Now, modify the Excel file to customize the Ribbon when it is opened:

  1. Navigate to the Book1.xlsm file and open the file as an Open XML Format file by adding the .zip extension to the end of the file name, and then double-clicking the file.

  2. Drag the customUI folder from the desktop to the open zipped folder.

  3. In the zipped folder, double-click the _rels folder, and then drag the .rels file to the desktop.

  4. Open the .rels file in a text editor.

  5. Add the following markup between the last <Relationship> tag and the </Relationships> tag:

    <Relationship Id="customUIRelID" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" 
Target="customUI/customUI.xml" />

  6. Save and close the file.

  7. Drag the .rels file from the desktop to the _rels folder.

  8. Navigate to the root folder of the zipped file and remove the .zip extension from the file name. This converts the file back to the Excel macro-enabled Excel file.

  9. Open the file in Excel.

  10. Click the Color Scales Demo tab to display the Add Color Scales button.

Click the Add Color Scales button. A range of numbers is displayed (see Figure 4) and the backgrounds are filled with colors ranging from red through blue to indicate the smallest to largest value.

Figure 4. A range of values and color scale

By using color scales, you can quickly see at a glance where each value fits within the range of values.

In the next column, I discuss another conditional formatting visualization available in Excel 2007: Icon Sets.

To Summarize

Color scales provide a quick way to compare values within a range. By using the improved UI in Excel 2007 or programmatically, you can easily implement color scales to increase the impact of your data.

All About Conditional Formatting in Excel 2007

For more in this series about Conditional Formatting in Excel 2007, see the following resources: