Creating a population pyramid in Excel
Part of my job involves working with demographic data. One of the common ways in which to represent population statistics is through charts, the most common being the population pyramid, which provides a consise overview of the structure of a population according to gender and age.
I also love Excel, but one of the niggling frustrations about Excel is that it does not provide the population pyramid as a chart option. But all is not lost, as it is possible – with just a bit of tinkering – to manually create a population pyramid in Excel. There are useful guides on the internet on how to build one from scratch (see here, for example), but what I’ve done is attach to this post an Excel file I created that can be easily used to create population pyramids for presentations and reports. You are welcome to download this file and use it for your own work.
Download: Excel – Population Pyramid (85+).xlsx
The file above consists of two worksheets: Data and Chart. If you look at Data, you will see that I’ve already added population figures for the City of Johannesburg, from Statistics South Africa’s 2007 Community Survey. In order to create your own population pyramid, simply replace (by pasting or typing in) the male and female figures in the white cells below the headings of Male and Female.
Once you have added the figures, click on the Chart worksheet to see the resulting pyramid. You can then right click on the pyramid and copy it into another application, such as Word or PowerPoint.
The age groups in this pyramid end at 85+. If you have data where the age ranges end at 80+ or even 100+, please let me know and I will create other Excel files to represent more (or less) groups.
If this has been useful to you, please let me know by leaving a short comment.
__________
Update: (22 Feb 2012): for those of you who want to play around with the actual table and graph settings, the password to unprotect the Excel file is pyramid. I also want to thank Helen North, who originally introduced me to an earlier version of an Excel population pyramid.
I was hoping to be able to type in all data, such as the age groups too.
thanks for doing this very easy process to construct a population pyramid. it works like a charm. I will share this with students.
thanks, this was really useful for a project, props for putting time into this
hey can you give out the password you set for you excel so i can unlock the protected worksheet and edit or remove cells
could you leave the age groups in differences of 9. Like 0-9, 10-19 and so on?
Hi
Thanks it works very well. I would like to change the data and graph to include ages up to over 100. You have protected the worksheet and I cannot guess your password, or must I start form scratch.
Thanks
Mariëtte
Hi there. I’ve uploaded the file again. Just use the password ‘pyramid’ to unprotect the worksheet.
Best of luck!
thank you very much! am using this for a project.
The age groups for pyramid that i want to create end at 75+. Can u create other Excel files to represent less groups for me? Thank you ^_^
Hi Hazlina
I will see what I can do 🙂 Please email me your email address, so that I can send you the file directly.
Great! Yes it is a surprise that ecel does not have these chart type
Claire
Thank you for making this so easy. I am trying to draw an outline of past population pyramid over the current pyramid. Any suggestion how I can plot to compare the two in one plot?
This was ABSOLUTELY incredible! I found this after three different failed template attempts. Thank you SO much for your help!
Thank you for creating this and THANK YOU for sharing it.
this is super useful! thanks!
Hi Kevin, I was very excited to find this posting because I had been using an APP called pyramid generator but it is gone. However, I wonder…does this work on a Mac? I am using excel 2011 for Mac. I replaced my numbers in your table, but I can’t get a chart…am I doing something wrong?
I’m an idiot. It works. thanks so much!
How can I add a comparative population as a line chart on to the graph
Great stuff…you saved me a lot of trouble. Much appreciated!
Thanks! I was looking for this chart and your work is just perfect!