Home > A cookbook of tips & tricks, Having fun with data > Creating a population pyramid in Excel

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.

Copy the data into the table

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.

Population pyramid for City of Johannesburg

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.

  1. jocelyn
    November 10, 2011 at 5:07 am | #1

    I was hoping to be able to type in all data, such as the age groups too.

  2. November 19, 2011 at 11:31 am | #2

    thanks for doing this very easy process to construct a population pyramid. it works like a charm. I will share this with students.

  3. Carlos
    December 11, 2011 at 6:47 pm | #3

    thanks, this was really useful for a project, props for putting time into this

  4. Mike
    February 12, 2012 at 10:23 pm | #4

    hey can you give out the password you set for you excel so i can unlock the protected worksheet and edit or remove cells

  5. Tara
    February 19, 2012 at 12:31 pm | #5

    could you leave the age groups in differences of 9. Like 0-9, 10-19 and so on?

  6. Mariëtte
    February 19, 2012 at 6:34 pm | #6

    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

    • Kevin Parry
      February 22, 2012 at 8:29 pm | #7

      Hi there. I’ve uploaded the file again. Just use the password ‘pyramid’ to unprotect the worksheet.

      Best of luck!

  7. Jen
    March 13, 2012 at 6:24 am | #8

    thank you very much! am using this for a project.

  8. hazlina
    May 20, 2012 at 9:52 am | #9

    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 ^_^

    • Kevin Parry
      June 11, 2012 at 5:49 pm | #10

      Hi Hazlina

      I will see what I can do :-) Please email me your email address, so that I can send you the file directly.

  9. claire cosgrove
    June 28, 2012 at 12:54 pm | #11

    Great! Yes it is a surprise that ecel does not have these chart type

    Claire

  10. SA
    July 17, 2012 at 10:34 pm | #12

    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?

  11. Alex
    September 18, 2012 at 3:46 pm | #13

    This was ABSOLUTELY incredible! I found this after three different failed template attempts. Thank you SO much for your help!

  12. Mona
    October 30, 2012 at 9:51 am | #14

    Thank you for creating this and THANK YOU for sharing it.

  13. edwinatmi
    February 5, 2013 at 5:20 am | #15

    this is super useful! thanks!

  14. Pam Small
    March 14, 2013 at 12:25 am | #16

    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?

  15. Pam Small
    March 14, 2013 at 12:31 am | #17

    I’m an idiot. It works. thanks so much!

  16. SM060689
    January 7, 2014 at 11:36 am | #18

    How can I add a comparative population as a line chart on to the graph

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: