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.

10000+ Copies Already Downloaded

How To Create a Population Pyramid In Excel [Step By Step]

population pyramid in excelDo you know, you can create a Population Pyramid In Excel using three different ways?

And, all these three ways to create a population pyramid in excel are Super Fun?

Here my basic idea to create a population pyramid is to analyze the population of different age groups.

But, before we give it a shot, check these words from Wikipedia about population pyramid.

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

While creating a population pyramid in Excel, I have taken the same key points as mentioned in above definition.

I will use it to show population count for different age groups for both male and female.

Create Population Pyramid In Excel In Different Ways

Download this Quick PDF Guide to learn about how to create population pyramid in excel.

Bar Chart To Create a Population Pyramid in Excel

  • First of all, I have to prepare a data table for this. A data table with the total population, female population in negative values & male population in positive values.

Create Population Pyramid Excel Template Data Table

  • We need the female population in negative so that we can create a back to back bar chart.
  • Select age group, female & male data columns. And, create a Bar Chart. Go to Insert → Charts → 2D Bar → Clustered Bar.

Insert A Bar Chart To Create Population Pyramid Excel Template Data Table

  • Now, we need the following customization in it to make a pyramid population chart.
  • Change vertical axis to reverse in order. Format Axis → Axis Options  → Axis Position  → Category in Reverse Order.

Reverse label to Create Population Pyramid Excel Template

  • Change vertical label position to low. Format Axis → Axis Options  → Labels  → Label Position.

Change Label Position To Create Population Pyramid Excel Template

  • Change series overlap to 100% & gap width to 0%. Format Axis → Series Option.

Change Series Overlap & Gap Width To Create a Population Pyramid Excel Template

  • Once you add data labels, you have to change the female side data labels into positive numbers.
  • Select data labels, Go to Format Data Labels → Label Options → Number. Select custom from category & add to the “#,##0.00;#,##0.00” format code.
  • Now, population pyramid chart is ready.

Chart To Create Population Pyramid Excel Template

How does It work?

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

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

Using Conditional Formatting

You can also create a population pyramid by using conditional formatting. Follow these simple steps to create a population pyramid.

  • Create a table with male & female population data in age groups.

Use Conditional Formatting To Create Population Pyramid Excel Template

  • 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 & change bar direction (female & male bars should be in opposite direction).

Create Population Pyramid Excel Template Go to conditional formatting

  • Now, select the female data column & add data bars to it in the same the way. And, you’ll get a pyramid chart like below.

Final Population Pyramid Excel Template Using Conditional Formatting

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

Add Data Bars To Create Population Pyramid Excel Template Using Conditional Formatting

How does It work?

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

Use In-Cell Charting Method

I am a big fan of in-cell charting. So, to create an in-cell pyramid chart, these are the simple steps which you have to follow.

  • Create a data table like below.

Create Data Table To Create In-Cell Population Pyramid Excel Template

  • Now, add below formula in Female & 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).

Now, you pyramid chart is ready to use.

Final In-Cell Population Pyramid Excel Template

How does It work?

I have used REPT Function to repeat a text by the population data. You’ll get the data bar as per your data.

Download this sample file from here to try it yourself. And, don’t forget to download my Excel Productivity Guide. Worth $20, Absolutely Free.

Bottom Line

By creating a Population Pyramid, you will able to analyze population in different age groups.

And, you know three different methods to create a Population Pyramid in Excel.

You can use any of the above method mentioned which you think is perfect for you.

But tell me one thing.

Have you ever tried to create population pyramid in excel before?

And, which method you like most from above three.

Which Chart I Have to Learn Next