# Australian Bureau of Statistics

 ABS Home #search{vertical-align:-3.5px; }
 Education Services

 GeoQ 06B – Draw a population pyramid using Excel

 Please note: This task is designed to be completed using a computer. You can download this activity as a Word (.doc) file using the links at the bottom of the page.

Data:

Estimated Resident Population of Australia, age and sex, 30 June 2010.
 Years Males % Persons Females % Persons 0-4 749,322 3.4 711,435 3.2 5-9 701,176 3.1 664,543 3.0 10-14 719,775 3.2 683,954 3.1 15-19 770,551 3.5 729,803 3.3 20-24 850,180 3.8 798,065 3.6 25-29 843,634 3.8 818,706 3.7 30-34 767,118 3.4 765,257 3.4 35-39 801,784 3.6 813,374 3.6 40-44 771,081 3.5 780,356 3.5 45-49 780,525 3.5 794,015 3.6 50-54 726,190 3.3 743,127 3.3 55-59 655,407 2.9 669,617 3.0 60-64 603,728 2.7 608,057 2.7 65–69 449,957 2.0 458,975 2.1 70–74 343,680 1.5 366,513 1.6 75–79 256,049 1.1 295,493 1.3 80–84 188,941 0.8 250,340 1.1 85–89 98,996 0.4 168,059 0.8 90+ 39,010 0.2 92,054 0.4 Total Persons Australia 22,328,847
Source: Australian Demographic Statistics, Dec 2010. (ABS cat. no. 3101.0)

A population pyramid shows the structure of a population, that is the age and sex of the people. A population pyramid consists of two horizontal bar graphs drawn side by side, with the ‘y’ axis representing the age groups. The female population is shown on the right and the male on the left of the diagram. The pyramid may be drawn with either the number of people in each age category or the percentage of total persons for each age category. To make comparisons between pyramids use the percentage (%) value.

1. Use the table above and Excel to construct a population pyramid for Australia. Insert a horizontal bar chart for the female data. Do the same for the male data but double click the horizontal axis and select ‘Values in reverse order’, and overlap and align both charts as shown below.

Fig. 1 Frequency Table
 Years %Males Years %Female 0–4 3.4% 0–4 3.2% 5–9 3.1% 5–9 3.0% 10–14 3.2% 10–14 3.1% 15–19 3.5% 15–19 3.3% 20–24 3.8% 20–24 3.6% 25–29 3.8% 25–29 3.7% 30–34 3.4% 30–34 3.4% 35–39 3.6% 35–39 3.6% 40–44 3.5% 40–44 3.5% 45–49 3.5% 45–49 3.6% 50–54 3.3% 50–54 3.3% 55–59 2.9% 55–59 3.0% 60–64 2.7% 60–64 2.7% 65–69 2.0% 65–69 2.1% 70–74 1.5% 70–74 1.6% 75–79 1.1% 75–79 1.3% 80–84 0.8% 80–84 1.1% 85–89 0.4% 85–89 0.8% 90+ 0.2% 90+ 0.4%

Fig. 2 chart before axis is reversed

Fig. 3 Select 'Values in reverse order' from the Format Axis box

Fig. 4 Chart for males with axis reversed

Fig. 5 Finished Chart

2. Use your population pyramid to describe the structure of the Australian population. Comment on the following:

• width of the base of the pyramid
• width of the top of the pyramid
• shape of the sides
• at least one difference between the male and female populations.
Extension:

3. Create a population pyramid for your own State or Territory for the most recent population statistics. Go to the ABS Year Book Australia – make sure you have the latest publication. Select the ‘Data cube’ called ‘Population by age and sex’ and download the Excel spread sheet.

4. Explore data in the publication to find how many males over 100 years of age there are in your state or territory? Compare this with the number of females.

State or Territory:

Number of males over 100 years =

Number of females over 100 years =