Population Age Pyramid Construction

Introduction 

A population pyramid provides a graphical representation of a country's age-sex distribution and may be used for different areas or sub-populations. Spreadsheet software such as Microsoft Excel, provide the mechanism to both manage the data and construct the actual graphical pyramid. 

1.1 Acquiring and Preparing the Data

Acquire country specific population data (in 5-year increments) for males and females for a given year. A good source of data is the US Bureau of the Census International Database.

• Choose Summary Demographic Data
• Select a country (example Trinidad and Tobago); choose Submit Query.
• A web page with demographic indicators, data, and other summaries appears.
• Data for the population age pyramid are provided for two time periods: 2000 and 2025.

Getting the data into Excel

Option 1: Manually enter the numbers and labels.

Option 2: The most efficient way is to copy the data into an ASCII editor, save the file as a text file, use Excel's Text Import Wizard, and perform some minor edits. To minimize the amount of editing, select only the 5-year interval population data for males and females.

Transferring Data from the Web to Excel

• Highlight the data and choose Edit > Copy (or control + c).
• Paste the data into an ASCII editor such as Microsoft Notepad; save the file (example c:\trinidad.txt).
  

  
  

  

  Figure 1

   
• From Excel, open trinidad.txt; the Text Import Wizard appears.
• Accept the defaults on the first screen as shown in Figure 1.
• Accept the defaults on the second screen as shown in Figure 2.

• Accept the defaults on the second screen as shown in Figure 2.
  

  
  

  

  Figure 2

   

• On the third screen, change the Column Data Format for Age to Text as shown in Figure 3. Choose Finish.
• The first four columns are for the year 2005. In order to construct the pyramid, only columns A (Age), C (Male), and D (Female) will be used. Delete columns B, E, F, and G. Select (highlight) the columns and choose Edit > Delete.
  

  
  

  

  Figure 3

   

  NOTE: To choose all four columns at once, hold down the control key and click on each one.

• The Excel worksheet appears as in Figure 4.
  

  
  

  

  Figure 4

   

Preparing the Data for the Population Age Pyramid

• Delete rows 2 and 3.
• Transform the male data into negative numbers (enclose each population value in parenthesis, or use the - operator).
• Change the numbers in the first two age categories to single numbers, i.e., 0-4 and 5-9.
• The spreadsheet appears as in Figure 5.
  
  

  

  Figure 5

  
  
  ^Top

  1.2 Creating the Population Age Pyramid

  • Select (highlight) the data in the range (A1:C18).
  • Choose the Chart Wizard button on the Standard toolbar, or Insert > the Chart Wizard appears.
  • In Step 1, choose Chart type: Bar, and clustered bar for the Chart sub-type (the default selection). Choose Next.
  • Accept the defaults in Step 2. Choose Next
  • In Step 3, enter Trinidad and Tobago 2005 for the Chart title, and Population (in thousands) for the Value (Y) axis. Choose the Gridlines tab to deselect the gridlines. Choose Next.
  • In Step 4, choose As New Sheet to have the chart appear as a standalone sheet (separate from the data) in the Excel workbook. Choose Finish.

  The resulting population age pyramid is shown in Figure 6.

  

  Figure 6

  
  
  ^Top

  1.3 Formatting the Population Age Pyramid

  There are many formatting options available in Excel. Personal preference will determine the final look of the chart. This step includes some suggested formatting.

  Remove the gap between the bars

  • Double click on a bar to open the Format Data Series dialog box.
  • Select the Options tab. Change Gap width to 0 and Overlap to 100.
  • Chose OK.

  Remove the gray background

  • Double click in the gray area to open the Format Plot Area dialog box.
  • Choose None under Area. Choose OK.

  Format the negative values for males

  • Double click on a male bar to open the Format Axis dialog box.
  • Choose the Number tab; select Number in the Category section. In the Negative numbers box, choose an option that removes the minus sign from the negative number (i.e., a red number with no parenthesis).
  • From the same Format Axis dialog box, choose Custom to change the color from red to black, and change the values to whole numbers.
  • In the Type box, change the value to 0;[Black]0 as show in Figure 7.
    
    

    

    Figure 7

  • Choose OK.

  Add data labels to the female bars

  • Double click on a female bar to open the Format Data Series dialog box.
  • Select the Data Labels tab; choose Show label.
  • Choose OK.

  To place the labels "inside the bar", double click on the labels to open the Format Data Labels dialog box.

  • Choose the Alignment tab; set Horizontal to Left, Vertical to Bottom, and Label Position to Inside Base.
  • Choose the Font tab; change the point size to 8.
  • Choose OK.

  Format the male bars

  • Double click on a male bar to open the Format Axis dialog box.
  • Choose the Font tab; change the point size to 8.

  The completed population age pyramid as it appears in Figure 8.

  

  Figure 8

  Additional Population Pyramid Resource

• Tutorial and exercise from the Population Reference Bureau.