Excel 2010 Bible

Chapter 19: Learning Advanced Charting

475

On the CD
A workbook with this example is available on the companion CD-ROM. The filename is conditional
colors.xlsx.

This chart displays four data series, but some data is missing for each series. The data for the chart

is entered in column B. Formulas in columns C:F determine which series the number belongs to

by referencing the bins in Row 1. For example, the formula in cell C3 is

=IF(B3<=$C$1,B3,””)

If the value in column B is less than the value in cell C1, the value goes in this column. The formu-

las are set up such that a value in column B goes into only one column in the row.

The formula in cell D3 is a bit more complex because it must determine whether cell C3 is greater

than the value in cell C1 and less than or equal to the value in cell D1:

=IF(AND($B3>C$1,$B3<=D$1),$B3,””)

The four data series are overlaid on top of each other in the chart. The trick involves setting the

Series Overlap value to a large number. This setting determines the spacing between the series. Use

the Series Options tab of the Format Data Series dialog box to adjust this setting.

Note
Series Overlap is a single setting for the chart. If you change the setting for one series, the other series change
to the same value. n

Creating a comparative histogram

With a bit of creativity, you can create charts that you may have considered impossible. For exam-

ple, Figure 19.38 shows a chart sometimes referred to as a comparative histogram chart. Such charts

often display population data.

On the CD
A workbook with this example is available on the companion CD-ROM. The filename is comparative
histogram.xlsx.

Here’s how to create the chart:

1. Enter the data in A1:C8, as shown in Figure 19.38. Notice that the values for females
   are entered as negative values, which is very important.


2. Select A1:C8 and create a bar chart. Use the subtype labeled Clustered Bar.


3. Select the horizontal axis and display the Format Axis dialog box.


4. Click the Number tab and specify the 0%;0%;0% custom number format. This cus-
   tom format eliminates the negative signs in the percentages.