Population Site: Software: Population Pyramids 98

Population Pyramids 98

We created the Population Pyramids 98 utility back in the mid-1990's for some population projects we were working on.

Since it is now fairly easy to create population pyramids using modern spreadsheets, this utility is no longer distributed or supported.

Following is a example of how we can use a Microsoft Excel 2010 bar chart to create a population pyramid:

  1. Create a data table. Note that the values for male age groups are entered as negative numbers and female age groups are entered as positive numbers

    You will also want to format the cells containing age labels as text so that Excel does not try to convert the labels to dates. Select the age label cells and then click on: Home, Cells, Format, Format Cells, Text

  2. Select all cells in the data table (including the labels) and then click on: Insert, Bar, 2-D Stacked Bar

    This will initially create the following (not so good looking) pyramid - but don't worry, we will fix it up to look much better:

  3. The first thing we want to do is to reverse the order that Excel displays the bars so that the 0-4 age group is on the bottom, not the top

    To do the, make sure you have your bar chart selected, and then click on: Chart Tools, Layout, Axes, Primary Vertical Axis, More Primary Vertical Axis Options, Axis Options

    • Check the box that says: Categories in reverse order
       
    • Select: Horizontal axis crosses: At maximum category

    While we are here, let's also remove all the tick marks associated with the age axis and make sure we have each age group label displayed:

    • Select: Major tick mark type: None
       
    • Select: Minor tick mark type: None
       
    • Select: Interval between labels: Specify interval unit: 1
       
    • Click on Line Color (in the left-hand menu) and select: No line

    Close the Format Axis window

    This will result in a chart that looks a bit better:

  4. Now, let's clean up the bottom axis labels

    Make sure you have your bar chart selected, and click on Chart Tools, Layout, Axes, Primary Horizontal Axis, More Primary Horizontal Axis Options

    • Select: Major tick mark type: Outside
       
    • Select: Minor tick mark type: None
       
    • Let's get the age labels off to the left side. Select: Vertical axis crosses at axis value. Unfortunately, there is no option for minimum axis value, so we need to enter the minimum value ourselves. In our example, the minimum value is at -2000, so we enter -2000
       
    • Now, let's get rid of those negative numbers in the horizontal axis labels. Select: Number from the left-hand menu, select Category, Custom, and enter the following Format Code: #,##0;#,##0

    Close the Format Axis window

    This will result in a chart that looks like this:

  5. Finally, let's do some other minor fixes

    Make sure you have your bar chart selected, and click on Chart Tools, Layout

    Let's remove the grid lines:

    • Select: Gridlines, Primary Vertical Gridlines, None

    Move the legend to the top:

    • Select: Legend, Show Legend at Top

    And finally, let's make the bars bigger:

    • Click on one of the blue bars (males) to select them
       
    • Right-click and select Format Data Series
       
    • Set the Gap Width to 0% (No Gap)
       
    • Click on Border Color in the left-hand menu and select Solid line, Color: Black
       
    • If you would like to, you can also change the color of the male bars at this point by clicking on Fill in the left-hand menu
       
    • Close the Format Data Series window
       
    • Click on one of the red bars (females) to select them
       
    • Right-click and select Format Data Series
       
    • Click on Border Color in the left-hand menu and select Solid line, Color: Black
       
    • If you would like to, you can also change the color of the female bars at this point by clicking on Fill in the left-hand menu
       
    • Close the Format Data Series window

    Our final pyramid should now look like this:

Once you have your chart fully formatted, you can change the numbers in your data table and the pyramid will automatically adjust. A few notes:

  • If your male values (the negative numbers) becomes to large, you may need to re-adjust the position of the age labels (Layout, Axes, Primary Horizontal Axis, More Primary Horizontal Axis Options, Axis Options, Vertical axis crosses at axis value).
     
  • If your bottom axis does not have an equal number of tick marks on the male and female sides, you may need to set the values manually (Layout, Axes, Primary Horizontal Axis, More Primary Horizontal Axis Options, Axis Options, change the Minimum and Maximum from Auto to Fixed, setting the minimum value to the negative of the maximum value).

 

Please feel free to send questions or comments to support@hpntech.com.