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.

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

In 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)

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

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?

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..

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!!!