How to Create a Population Pyramid Chart in Excel

Last Updated: June 22, 2023
puneet-gogia-excel-champs

- Written by Puneet

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

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.

Wikipedia
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, the female population in negative values, and the male population in positive values.

data table to create a population pyramid chart in excel

Note: A column for the total population is not compulsory but make sure to have a female population in negative.

  1. 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.
  2. 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
  3. 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
  4. After that, go to Format Axis ➜ Axis Options ➜ Labels ➜ change label position to “Low”.
    change label to create a population pyramid chart in excel
  5. 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
  6. 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.

  1. 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
  2. And then, select a single column either male or female and apply conditional formatting data bars from Home Tab ➜ Styles ➜ Data Bars ➜ More Rules.
  3. 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
  4. 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
  5. 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 

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

  1. First of all, create a table like below.
    data table to use to create population pyramid chart in excel using rept function
  2. Now, add the below formula in female and male columns and drag it down.
    Female Column: =IFERROR(REPT(“|”,C5*4),””)
    Male Column: =IFERROR(REPT(“|”,F5*4),””)
  3. After that, change the font style to Playbill (Font Size – 10) or you can also use Stencil (Font Size – 10).
  4. In the end, change the font color for both the columns and make female column’s alignment to right and male’s to left.

The chart is ready.

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

Sample File

Download this sample file from here

Conclusion

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

Whenever you need to 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.

It’s one of my lists of advanced Excel charts and graphs. I hope you found this tip useful, but do tell me one thing.

Have you ever used a pyramid chart? Or, do you have 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.

5 thoughts on “How to Create a Population Pyramid Chart in Excel”

  1. Hi,
    Your population graph tutorial was quite informative. However, I have observed, that in many Youtube videos there is no mention of the excel version in which the formula or graph can be obtained. A line on excel version would be quite useful for the readers.
    Thanks,
    M. Mohan

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

    Reply

Leave a Comment