Excel 2010 Bible

Part III: Creating Charts and Graphics

474

The other two slices are apportioned based on the value in cell B1. The formula in cell A4 is

=MIN(B1,100%)/2

This formula uses the MIN function to display the smaller of two values: either the value in cell B1

or 100 percent. It then divides this value by 2 because only the top half of the pie is relevant. Using

the MIN function prevents the chart from displaying more than 100 percent.

The formula in cell A5 simply calculates the remaining part of the pie — the part to the right of the

gauge’s “needle”:

=50%-A4

The chart’s title was moved below the half-pie. The chart also contains a text box, linked to cell B1,

that displays the percent completed.

Displaying conditional colors in a column chart

You may have noticed the Vary Colors by Point option on the Fill tab of the Format Data Series

dialog box. This option simply uses more colors for the data series. Unfortunately, the colors aren’t

related to the values of the data series.

This section describes how to create a column chart in which the color of each column depends on

the value that it’s displaying. Figure 19.37 shows such a chart (more impressive when you see it in

color). The data used to create the chart is in range A1:F14.

FIGURE 19.37

The color of the column depends varies with the value.