3
How to construct a population pyramid in Excel
http://www.uvm.edu/~agri99/spring2004/Population_Pyramids_in_Excel.html
Thanks to Westminster School: http://homepages.westminster.org.uk/webgeography/word_excel/pyr.htm
Go to the US Census Bureau's Site on International Demographic Data: http://www.census.gov/ipc/www/idb/idbsprd.html and choose Table 094: Midyear Population by Age and Sex. Then choose a country, latest available year, and click the GO button at the bottom.
Copy the data on Age, Male, and Female population into Excel for the country of your choice. To get the first column to record the age range correctly, choose the column, format cells, and choose TEXT. Format the spreadsheet so it looks similar to the China example below.
To practice, you may download the China example below here .
In cell D4 enter the formula =B4+C4.
Copy Cell D4 down to the bottom of the column (cell D21 in example above)
Put the active cell in cell B22 above and click on the AutoSum and Enter. This will calcualte the total population of males.
Copy this cell across to row D (D22 in example above).
To calculate the percent males in cell E4, enter the formula =100*B4/$D$22 (notice the direct cell reference to the total population cell with the use of the $). Some of the demographic tables do not include ages 85 and older, so the total population cell reference may be different than the example above (for example D21 instead of D22).
Copy the formula from cell E4 down to the bottom of the column, E22 in the example above.
Format cells E4 to E22 to two decimal places by blocking that range and choosing Format-->Cells, Number Tab, Number, 2-decimal places.
To claculate the percent females in cell F4, enter the formula =100*C4/$D$22
Copy the formula from cell F4 down to the bottom of the column, cell F22 in example above.
Format Cells F4 to F22 to two decimal places by blocking that range and choosing Format-->Cells, Number Tab, Number, 2-decimal places.
If you have not done so already, Autosum the percent male and female columns in the cells E22 and F22 in the example above and below.
We must fool Excel by making the % male column negative, to make the population pyramid.
Alter the formula in cell E4 to be = -1*100*B4/$D$22 and copy this formula down to cell E21
The spreadsheet should now look like:
To build the pyramid, we need to choose a horizontal bar chart with two series of data (% male, and % female) and the age labels in column A as the Category X-axis labels. Block out the range A3 to A21 , hold down the CTRL key and block out the range E3:F21
Click on the chart Wizard button, select the default of clustered BAR chart with horizontal bars. Click Next.
Click Next again. In the Titles tab enter the title: Population Pyramid for China, 2000, Data Source: US Census Bureau
Enter Percent for the Y-Axis label, click next, and choose as an object in this sheet. Press Finish. You should have a graph that has two series of data.
To format the pyramid, grab a corner of the graph and expand it so you can see it clearly.
Put the tip of your mouse arrow on one of the bars on the left so it says "Category Axis" and double click.
Choose Patterns tab and set the major and minor tick marks to None , tick mark labels to Low, and from the Scale
tab set Number of Categories between tick mark labels to 1, click OK.
Double click on any of the bars in your pyramid, and in the Options Tab area set the Overlap to 100 and GapWidth to 0 . Click OK.
Double click the horizontal axis, in the Number Tab select Custom and for type enter 0;0 and click OK. Click on the scale tab and set
the major unit to 2, click OK.
Your pyramid should look something close to:
You should notice the percentage of males and females within each age class is not equal. Why might that be? Also why are there two bulges in the pyramid around 10-14 and 30-34 age groups?