Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

Excel Population Pyramid Chart

We all agree on one thing that:

Charts are one of the best ways to compare data for different groups and time periods. A chart also helps a user to understand the data quickly.

Let’s get bit specific.

A Pyramid chart is one of those advanced Excel charts which can help you to present data in a specific way.

Yes, specific way.

Take an example of presenting population of different age groups and comparing both the genders with each other.

Yes, a population pyramid chart is a specific and targeted chart to present population data for better insights.

You know what? I have found 3, yes three different ways to make a population pyramid chart in Excel. And today, in this post, I’d like to share with you exact steps which you need to create it.

create population pyramid chart in excel in different ways

Steps to Create a Population Pyramid Chart using a Bar Chart

  • First of all, you need to prepare a data table for this. A data table having the total population, female population in negative values and male population in positive values.

Important Note: Female population should be in negative so that we can create a two sided bar chart.

  • Select age group, female and male data columns and insert bar chart. Go to Insert → Charts → 2D Bar → Clustered Bar.
  • You'll get a chart like below
  • Now, you need some customization in it to make a pyramid chart.
  • Change vertical axis to reverse in order. Format Axis → Axis Options → Axis Position → Category in Reverse Order.
  • Change vertical label position to low. Format Axis → Axis Options → Labels → Label Position.
  • Change series overlap to 100% and gap width to 0%. Format Axis → Series Option.
  • Once you add data labels, you have to change the female side data labels into positive numbers. For this, select data labels, Go to Format Data Labels → Label Options → Number. Select custom from category & add to the “#,##0.00;#,##0.00” format code.

Congratulations! your pyramid chart is ready.

How it works

Here you have used a simple bar chart in which we got two different sides by using male data in positive numbers and female data in the negative numbers.

And, then you have changed the female negative numbers into positive by using custom formatting.

Check this out == > Tornado Chart

Use Conditional Formatting to Make a Population Pyramid Chart

You can also create a pyramid chart by using conditional formatting. Just follow these simple steps.

  • Create a table with male and female population data in age groups.
  • Now select a single column either male or female. And, apply conditional formatting data bars.
    • Go to → Home → Styles → Data Bars → More Rules.
  • In the new formatting dialog box, tick mark the “Show Bar Only”, Add solid border and change bar direction (female and male bars should be in opposite direction).
  • Now, select the female data column and add data bars to it in the same the way. And, you’ll get a pyramid chart like below.
  • Now, for a final touch add two column at both sides of the diagram to add data labels. And, it will look like as below.

How it works

You have just applied conditional formatting data bars in the cell using data captured in it. Data bars will change when there is a change in data.

Check this out == > Funnel Chart

A Simple Population Pyramid Chart with REPT Function

I am a big fan of REPT function and the best thing is you can use it to create simple population pyramid.

Here are the steps.

  • First of all, create a data table like below.
  • Now, add below formula in female and male columns.
    • Female column: =IFERROR(REPT(“|”,C5*4),””)
    • Male Column: =IFERROR(REPT(“|”,F5*4),””)
  • Change the font style to Playbill (Font Size – 10) or you can also use Stencil (Font Size – 10).
  • In the end, change the font color of both columns.

Your pyramid chart is ready to use.

How it works

Here you have REPT function to repeat "|" as per the number you have in corresponding cell.

Animated Pyramid Chart

Click here to download this animated template.

Conclusion

You might be wondering that a pyramid chart is just for population data but the truth is you can use for all kind of data.

Whenever you need compare different data points for two different time periods pyramid chart is best to use, for example, sales data for different products for two years.

I hope this charting tip will help you to get better in Excel.

😃

Now tell me one thing. Have you ever used a pyramid chart? Or, do you any other way to create it? Please share with me in the comment section, I’d love to hear from you.

And, please don’t forget to share it with your friends.

  • Raymond Drayson

    Hi Punkte, 3 very good explanations. I was just wondering if it is possible to do this for 5 groups, like my teams, and one more question, can you make this with VBA?
    I think that your explanations are easy to follow and I like the way you give more than one way to achieve the goal, so I can choose which I understand best.
    Keep up the good work, as with you I am learning every day
    Ray