↑ Return to Charts

Population Pyramid

It is common to create a population pyramid in Excel, in order to identify the distribution of its customers. Unfortunately, Excel doesn’t have this pattern in the Chart’s ribbon. Nevertheless, you can create your own population pyramid chart by following these steps.

 

The data for a Population Pyramid

 

 

Data on a population pyramid is traditionally presented shown against.

 

 

 

 

 

 

 

 

Subtraction of a column

 

Before to create your chart, you must perform a subtraction of one of the columns (women are generally on the left of the graph, so it is this column that will be subtracted).

You write then in a new column the following formula

=0-C4

Copy this formula to all the series of ‘Women’ and also add a title in cell D3.

 

 

 

 

Choice of the chart

On the menu Insert > Charts, you will choose the chart type ‘Bar‘, then the first sub-menu 2D Bar> Clustered bars.

You get the following chart (wow, it doesn’t look like a population pyramid 😕 )

Remove unnecessary data

We have a chart that has two columns for the women (a positive column and a negative). We will remove the positive column.

Select the chart and select the menu Design > Select Data. The following dialog appears:

You will also have display this dialog box by doing a right click on your chart and choosing ‘Select data …’

In this dialog, you choose the first ‘Women’ and click on Remove. If ever you select the wrong series, do not worry, just click the cancel button and start again.

 

 

 

 

Formatting bar

You can see that in your chart, the bars are thin and offset with respect to each other. It is very easy to remedy these two points.

After selecting the chart in the Layout tab, you select the series Women and then you click Format Selection. You can also right-click on your chart and select the menu Format data series…. The dialog box is displayed.

To reduce the gap between the bars, you will move the cursor to 0% (no gap) and the bars are not offset. Then you move the cursor to 100% (overlapped).


The graph becomes

Changing axes

To complete our graph, we must reverse the vertical-axis so that the lowest values ​​at the bottom, set the horizontal-axis while the left and also change the number format to not display negative values.

Select the vertical axis (using the dropdown menu in the Layout tab is easier) and then you click Format Selection. In the Settings box, you select Axis Options and you select ‘Categories in reverse order’ (to return the chart) and also change the value ‘Axis Labels’ in the Low (to put axis labels on the left).

Then select the horizontal axis.

Chart_Pyramide_Age_8In the sub-menu Axis Options, change the Axis Label to High (to put the axis labels on the bottom of the chart)

 

 

 

 

 

 

 

 

 

 

Chart_Pyramide_Age_9In the sub-menu Number , choose the Custom category and write Standard, Standard in the ‘Format Code‘ and click on the button Add

 

 

 

 

 

 

 

 

And the chart becomes :

 

Change the design

It only remains for you to use the various menus layouts for a very neat pyramid.

The chart becomes

And if you change the color of the format Series (Gradient fill)





31 comments

Skip to comment form

  1. Anonymous

    Thank you sir!!

  2. Anonymous

    did not help at all

  3. Anonymous

    This was a humungous help! I pretty know nothing about computers and this guy gave a really nice procedure of how to do this. Great Job!

  4. Anonymous

    when I do this there is no change in my graph. Help?

    1. sabreenalam

      hmm…i am not sure that worked fine for me

  5. vani

    thank u this will help me wid my assignment for 2morrows work…

  6. yatanar

    Thank you, This is very useful for us

  7. Isaac Torres

    Hi, I’m using Excel 2011 and the graph doesnt change when i type Standard;Standard neither 0;0 or General;General. Can anyone help me. Thanks

  8. Anonymous

    Thank you, this helped me with my assignment!

  9. Anonymous

    I cannot Add the ‘Format Code’ in the ‘Custom category’,

    1. joshuanair

      Use this code #,##0_);#,##0

  10. Anonymous

    Truly useful and easy to use

  11. give

    hiiiiiiiiiiiiiiiiiiiiiiiii HAVE your comment is rude

  12. have

    fuck you watermalondrea
    I lied I love you

  13. Anonymous

    m k’/
    \\\

  14. Helpmeee

    I’m wanting to make an age – sex distribution using a population pyramid. Importantly, I want to display this distribution for 3 different regions on the same chart for comparison. Finally, it must have the axis in the centre. Can anybody please help me? I am following the standard instructions in Excel, but having problems because the axis are overlapping.

  15. Man chun

    Improvements:-

    Firstly “standard;standard” deosn’t seem to work in my Excel 2007, so you can just use 0;0 instead.

    How to add labels to the leftside while removing the negative value lables.

    In the steps it says to remove the Female(positive) column, keep it in, and do all the steps.

    Select Female(positive) data, and rigth click and “Add Labels”, ensure the Female(Negative) lablels are not added. Then just manually move the Female(positive) labels across to the left hand side.

    Select Female(positive) data in the chart and rigth click, and choose “Format Data Series”, goto Fill and select “No Fill”, which will make this column invisible.

    Next ensure this column is at the top within the legend table as else this one will overlap the Male column if not the case.

    Now you have labels at left and right handside of the chart.

  16. Anonymous

    this did nothing for me

  17. SUSocProf2

    This is very helpful, thanks much. However, I also have a couple remaining problems. I’m using Excel 2013 and using the pyramid to show a community’s age structure. How do I move the labels for the segments (e.g., ‘<5 yrs', or '10 to 14 yrs,' to the leftmost side of the chart, so that it's not overlapping the bar segments? Also, now removing the minus sign from the numbers on the left side of the axis?
    Thanks
    AParis

    1. Anonymous

      Under Axis Options, click the far right symbol (which is also axis options), then choose the Labels dropdown. From there you can choose the label position (High, low, next to axis or none)

  18. Abi

    Hi,

    I am running Office 365 and this is wonderful – however when I try to change the negative numbers to positive numbers, they do not change. Do you have any suggestions for how to remedy this?

  19. Anonymous

    Thanks so much! Do you have suggestions for doing this for two time snap shots, such that the 2013, for example, appears above 2010 at each age interval?

  20. G

    I’m using Excel 2013 and I can’t change the axis options to Custom: Standard;Standard – any help?

    1. Anonymous

      Instead of “Standard;Standard” type “General:General”

  21. willem

    How do I centre the age categories in the middle of the graph for a better display

  22. Rustham

    Thanks so much

  23. Anamika

    Man, This helped a lot in my geo project. Thanks a lot! I have a problem. I tried to change my negative numbers to positive but it doesn’t work on my mac, it changes every number to 0. Just need help on that..

    1. Warzone Codger

      I can help you with that query

    2. engjellushezenelaj

      can someone help to show how can I change my negative numbers to positive? i am trying but nothing change..please if someone now it tell me

    3. Zoe

      I don’t know what software you’re using, but it just depends on the wording that the program uses. In OpenOffice instead of typing Standard;Standard it is General;General.

      When you go to format cells, look in the bottom box that says format code, whatever word they use, type that in place of standard or general.

  24. Gerry

    Thankyou so much this helped me heaps!!!

Leave a Reply

%d bloggers like this: