Microsoft Office Tutorials and References
In Depth Information
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 the value in column B is less than the value in cell C1, the value goes in this column. The
formulas 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:
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.
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.
Creating a comparative histogram
With a bit of creativity, you can create charts that you may have considered impossible. For
example, Figure 17-7 shows a chart sometimes referred to as a comparative histogram chart. Such
charts often display population data.
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 17-7.
Notice that the values for females are entered as negative values, which is very
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 following custom number format:
This custom format eliminates the negative signs in the percentages.