How to Create a Population Pyramid Chart in Excel

Population Pyramid Chart is a specific chart which helps us to present and analyze the population of both the genders using age groups.

Wikipedia says,

A population pyramid also called an age pyramid or age picture is a graphical illustration that shows the distribution of various age groups in a population, which forms the shape of a pyramid when the population is growing.

In Excel, we can create population pyramid chart using a bar chart, just like below one.

create population pyramid chart in excel in different ways

Insert a bar chart and then do a bit of customization, that’s it.

But here is the twist.

I have found that apart from this traditional method we can also use two different methods as well.

And today in this post, I’d like to share these three methods [exact steps] with you.

So let’s get started.

Simple Steps to Create a Population Pyramid Chart using Bar Chart

Before we create this chart we need to prepare data. 

A table with the total population, female population in negative values and male population in positive values.

data table to create a population pyramid chart in excel

Note: Column for total population is not compulsory but make sure to have female population in negative.

  • First of all, we need to insert a bar chart. For this, select age group, female and male data columns and insert a bar chart from Insert Tab ➜ Charts ➜ 2D Bar ➜ Clustered Bar.
  • Now we have a chart like below and further, we need to do some customization in it to make a population pyramid.
insert a bar chart to create a population pyramid chart in excel
  • From here, we need to make vertical axis in reverse order and from this, go to Format Axis ➜ Axis Options ➜ Axis Position ➜ tick mark “Category in Reverse Order”.
apply reverse order to create a population pyramid chart in excel
  • After that, go to Format Axis ➜ Axis Options ➜ Labels ➜ change label position to "Low".
change label to create a population pyramid chart in excel
  • Next, we need to change series overlap to 100% and gap width to 0%. For this, go to Format Axis ➜ Series Option.
change width to create a population pyramid chart in excel
  • In the end, we need to convert negative data labels for female data bar into positive. For this, select data labels and go to Format Data Labels ➜ Label Options ➜ Number select custom from category and add to the “#,##0.00;#,##0.00” format.
format data label as positive numbers in excel population pyramid chart

Congratulations! our pyramid chart is ready to rock.

ready to use population pyramid chart in excel

Use Conditional Formatting to Simple Create a Pyramid Chart in Excel

We can also create a pyramid chart by using conditional formatting and here are the steps for this.

  • First of all, create a table with male and female population data with age groups.
data table to create a population pyramid chart in excel with conditional formatting
  • And then, select a single column either male or female and apply conditional formatting data bars from Home Tab ➜ Styles ➜ Data Bars ➜ More Rules.
  • After that, 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).
show bars only to create a population pyramid chart in excel with conditional formatting
  • Now, select the second data column and add data bars to it in the same the way, and you’ll get a pyramid chart like below.
simple population pyramid chart in excel with conditional formatting
  • In the end, for a final touch, add two column at both sides of the diagram to add data labels.

Boom! our chart is ready.

final population pyramid chart in excel with conditional formatting

Create an In-Cell Population Pyramid Chart in Excel 

Here are the steps.

  • First of all, create a table like below.
data table to use to create population pyramid chart in excel using rept function
  • Now, add below formula in female and male columns and drag down.

Female Column:

=IFERROR(REPT(“|”,C5*4),””)

Male Column

=IFERROR(REPT(“|”,F5*4),””)

  • After that, 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 for both the columns and make female column's alignment to right and male's to left.

Chart is ready.

simple-population-pyramid-chart-in-excel-using-rept-function

Sample File

Download this sample file from here to learn more.

Conclusion

Have you ever used a pyramid chart? Or, do you any other way to create it?

Share your views with me in the comment section, I’d love to hear from you. And, please don’t forget to share this tip with your friends.

2017-11-28T12:01:10+00:00
  • Raymond Drayson says:

    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

  • >