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 

Introduction Here are two population pyramids. They show the UK population in 2008 (on the left) and the projection for 2020 (on the right). The colourcoding ought to be selfexplanatory, 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 tailormade charttype for a population pyramid. We have to use the horizontal bar chart and do some tweaking. The method is described here.


Step One 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 mid2008 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: Step Two 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 04 agegroup 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). Step Three 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 yaxis and the vertical axis is the xaxis. The resulting chart is shown in the Chart (1) worksheet. Step Four Two formatting changes are now needed. Rightclick 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. Step Five 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, rightclick 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 semicolon. 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, rightclick 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 info@kurtosis.co.uk 