Microsoft KB Archive/114389

= Microsoft Knowledge Base =

Excel: Steps to Create a Population Chart Manually
Last reviewed: September 12, 1996

Article ID: Q114389

The information in this article applies to:


 * Microsoft Excel for Windows, version 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0

SUMMARY
Population charts (also called pyramid charts) compare the values from two series; the values from each series are plotted on either side of a middle axis. The following is an example of the basic structure of this type of chart

M    F   5      *****|**** 4       ***|***   3       ****|*****   2         **|***   1          *|** where M represents one series (male), and F represents another (female).

MORE INFORMATION
Microsoft Excel has a built-in function for creating population charts. However, you can also construct these charts manually.

To create a population chart manually
  In a new worksheet, enter the following data: A1: Age  B1: Male   C1: Female A2: 1    B2: -2     C2: 1 A3: 2    B3: -4     C3: 4 A4: 3    B4: -7     C4: 4 A5: 4    B5: -5     C5: 8 A6: 5    B6: -9     C6: 9

Note that data that is to be plotted on the left hand side of the chart must be entered on the worksheet in negative values.  Select the range A1:C6, and choose the ChartWizard button. In the ChartWizard Step 1 Of 5 dialog box, verify that range A1:C6 is selected, and choose the Next button. In the ChartWizard Step 2 Of 5 dialog box, select Bar Chart (Option 2), and choose the Next button. In the ChartWizard Step 3 Of 5 dialog box, select Stacked Bar By Value (Option 3), and choose Next.  In the ChartWizard Step 4 Of 5 dialog box, verify that the number in the First Column For Category (X) Axis Labels box is 1. In Microsoft Excel versions 3.0 and 4.x, this is achieved by choosing First Data Series under Use Column For (Step 4 of 5).  In the ChartWizard Step 5 of 5 dialog box, format the title and legend as desired.

To standardize your population chart
To adjust your chart to meet general standards, follow the appropriate procedure below.

To make this change          Follow this procedure Enable the values on the     1. Select the data series in the negative side of the y axis     worksheet. to appear as positive values

2. From the Format menu, choose Cells, and select the Number tab.

3. In the Code box, type "0.00;0.00" (without the quotation marks).

Note that the number format after the semicolon is a positive number.

The data will be displayed to two decimal places. For more information about formatting numbers, choose the Search button in help and type:

format codes, number

Move the x-axis labels away  1. Select the x axis, and choose Selected from the bars                   Axis (Patterns in versions 4.0 and                                 earlier) from the Format menu.

2. On the Patterns tab, under Tick-Mark Labels, select High or Low, depending on whether you want the labels to be                                on the left or right side (High is                                 left, Low is right). Remove the gaps between bars 1. From the Format menu, choose Chart

Type (Main Chart in 4.0).

2. Choose Options, and select the Options tab.

3. Set Gap Width to 0 (version 5.0).

Center your chart            Do the following to set the y axis maximum and minimum scales to equal, manual values:

1. Select the y axis (on bar charts this                                will be your horizontal axis).

2. From the Format menu, choose Selected Axis (Scale in version 4.0).

3. Select Scale and Adjust the maximum and minimum values to equivalent values. For example minimum = -10 & maximum = 10.