Questions About Excel Colors

To see what I mean, compare the two figures below. The report on the left uses Excel 2007's standard Median theme with the reporting workbook from from Plug-N-Play #1. The report at the right uses my adjusted version of the Median them.

To understand how the colors are assigned, take a look at the report below, which uses our custom WSJ color theme.

Notice in this figure that the backgrounds of the table and of three of the charts use the same color. Other themes in the PNP kit, like the Median theme in the dashboard at the right above, use different values for these two parts of the report.

In other words, we assign specific color positions to objects in a dashboard report; we don't merely assign colors. By associating color positions with objects we can change the color all charts, or all tables, or whatever, without changing any of the colors of other objects in the same report.

The figure below shows the specific color assignments we use for any dashboard in the PNP kit.

The figure contains two palettes with numbered labels. The table in the figure explains how the colors in each numbered position are used.

Setting Theme Colors	Applying Theme Colors
	0 Table Background 1 Figure Number Cells 2 Sheet Background 3 Chart Style 1: Background 4 Chart Style 1: Area Plot 5 Chart Style 2: Background 6 Chart Style 2: Area Plot

To illustrate, the color labeled zero in both palettes is used for table backgrounds. No matter which theme we use, the report workbook uses the color in the zero position for table backgrounds.

As an aside, notice that the palette in Excel's Edit Theme Colors dialog uses a dark-light, dark-light sequence for its first four colors. But the palette in the Fill Color control uses a light-dark, light-dark sequence for its first four colors. We Excel users must learn to adapt to that quirk in Excel 2007's user interface. We use the Light 2 color for table backgrounds, no matter where it's displayed in a New Excel dialog.

To launch the Edit Theme Colors dialog for a custom theme choose Page Layout, Colors. Right-click a theme and then choose Edit. To display the Theme Colors figure, choose Home, Font, and then click on the Fill Color icon.

Suppose we start with the Median theme above. We could choose a lighter version of one of the colors for the small area charts. However, when we switch to another theme, the same color setting probably wouldn't work. It would be too light or too dark or too red or too green or whatever. So we would need to adjust the color.

But that creates a problem.

When we use a variation of the base color, we can't adjust that variation directly. Instead, we must adjust the base color itself. So we must guess how to adjust one color so that another color looks the way we want it. And if we want to set the variation precisely -- to match a corporate color, for example -- we could spend ten minutes or more trying to get the exact color we need. That's not good.

Also, adjusting the base color adjusts all five of the colors linked to the base color. So if we like four of those five variations, but must adjust the fifth, we'll never have the colors we want. That not good either.

So the only practical way to set up themes for dashboards is to link each dashboard object to a base color position, as shown in the figures above, and then set the color in each position to just the hue and value that we want.

In Classic Excel, those 56 colors act very much like color styles. When you adjust one color in the palette of 56, that color changes everywhere it's used in your workbook. But New Excel offers only 12 colors with a similar style-like capability.

New Excel does allow you to assign any color to any cell, chart, drawing object, and so on. But those ad hoc colors can't be linked to a style setting. So when you need to change the ad hoc colors -- which you'll often need to do -- you'll need to change the colors one object at a time.

The net effect is that, for all practical purposes, Excel 2007 offers a selection of only 12 useful colors.

Suppose you create custom colors in Classic Excel and then open the workbook in New Excel. You'll see the colors, but they won't be linked to Excel 2007's color themes. They're ad hoc colors. Therefore, when you want to change any of those colors you'll need to change them one cell or chart object at a time.

On the other hand, suppose you save a New Excel workbook as a Classic Excel workbook. In this case, you lose all the New Excel colors. All you get is Classic Excel's default colors.

This is why I spent hours arranging the colors of the two Excel versions so they would be in sync.