|home | about kurtosis | courses | course calendar | booking information | ideas | technique | work in progress | clients | contact us|
How to draw a population pyramid in Microsoft Excel
Step by step to a perfect pyramid
Here are two population pyramids. They show the UK population in 2008 (on the left) and the projection for 2020 (on the right). The colour-coding ought to be self-explanatory, if a little stereotyped: males are blue; females are pink. There are all sorts of interesting things going on here but the point of this webpage is to show you how to draw them.
Q. How old are people in the UK?
A. They're about 39.
When we want to know about the ages of people in a population we tend not to ask a question as simple as the one above. Instead, we'd probably ask how many people are over 65 or what percentage of the population is under the age of 18. And as for the answer to the earlier simple question, well, as it happens, 39 was—in 2008—the average age of people in the UK.
The conventional way of displaying population data is to show it graphically as a population pyramid. The UK's current (2008) population is shown on the left, as well as the latest projection for 2020.
Microsoft Excel doesn't have a tailor-made chart-type for a population pyramid. We have to use the horizontal bar chart and do some tweaking. The method is described here.
You'll need some population data. It's widely available on the web. A good place to go for UK data is the Office for National Statistics. For the example here we've taken the mid-2008 population estimates data for the UK. You can download the data from the ONS website as Excel workbooks but you might want to do a bit of cutting and pasting so that it takes the form of the example workbook here:
If you want to draw a pyramid for the 2008 population, it's tempting to just select the two data series (males and females) in columns C and D of the Data (1) worksheet, then plot them using a horizontal bar chart, but we need to do a tweak first. We need to pretend that the data on the left of each pyramid is composed of negative values. For example, we want the 2008 Males 0-4 age-group figure to be -1,895.0 instead of 1,895.0. So you need to create a column next to the existing data that changes the plus values to negative values. We've done this in blue ink in columns E and J of the worksheet called Data (2).
You can now plot the data. Taking the 2008 pyramid as our example, select the cells A8:A26, D8:D26 and E8:E26 then select a horizontal bar chart from the chart wizard. If you want to label the axes as you go through the wizard you need to remember that the horizontal axis is the y-axis and the vertical axis is the x-axis. The resulting chart is shown in the Chart (1) worksheet.
Two formatting changes are now needed. Right-click on either the males or females and choose Format Data Series..., then go to the Options tab and make the Overlap: 100 and the Gap width: 0. The result is the chart in the Chart (2) worksheet.
There are two final formatting changes you need to make.
The first is that you need to alter the labels on the horizontal axis so that the negative numbers display as positive numbers. To do this, right-click on the horizontal axis, choose Format Axis..., then choose the Number tab. You now want to pick a custom number style that is close to what you want so that there will be minimal editing required. Choose the custom format that displays as #,##0;-#,##0, and then simply delete the - sign immediately after the semi-colon.
The second change is that you need to move the labels on the vertical axis so that they are at the extreme left of the chart. To do this, right-click on the vertical axis, choose Format Axis..., then choose the Patterns tab and set Tick mark labels to Low.
The chart should now look like the one in the Chart (3) worksheet. If you want to adopt our other formatting preferences you'll end up with a chart like the one in the Chart (4) worksheet.
|© Kurtosis 2010 | 99 Giles Street, Edinburgh EH6 6BZ | Tel 0131 555 5300 | email email@example.com|