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

  1. 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.

  2. Select the range A1:C6, and choose the ChartWizard button.

  3. In the ChartWizard Step 1 Of 5 dialog box, verify that range A1:C6 is selected, and choose the Next button.

  4. In the ChartWizard Step 2 Of 5 dialog box, select Bar Chart (Option 2), and choose the Next button.

  5. In the ChartWizard Step 3 Of 5 dialog box, select Stacked Bar By Value (Option 3), and choose Next.

  6. 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).

  7. 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.

KBCategory: kbusage
KBSubcategory:

Additional reference words: 3.00 4.00 4.00a 5.00



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 12, 1996
©1997 Microsoft Corporation. All rights reserved. Legal Notices.