Population Pyramid Chart is a specific chart which helps us to present and analyze the population of both the 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.
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.
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.
- 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”.
- After that, go to Format Axis ➜ Axis Options ➜ Labels ➜ change label position to "Low".
- Next, we need to change series overlap to 100% and gap width to 0%. For this, go to Format Axis ➜ Series Option.
- 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.
Congratulations! our pyramid chart is ready to rock.
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.
- 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).
- 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.
- 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.
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 simple population pyramid.
- First of all, create a table like below.
- Now, add below formula in female and male columns and drag down.
- 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.
Download this sample file from here to learn more.
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 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 list of the advanced Excel charts and graphs.I hope you found this tip useful, but no tell me one thing.
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.
More Advanced Charts and Graphs
- Step Chart: A step chart is an advanced version of line chart which make easy to check variation and changes in the trend [...]
- Milestone Chart: It's a simple chart which helps to track stages of a project in a simple and understandable way [...]
- Waffle Chart: You can also call it a square pie chart. It's a simple square with 100 small boxes which [...]
- Tornado Chart: It looks like a tornado where you can compare data for different categories for two different periods [...]
- Gantt Chart: It's one of the most popular charts for project managers to track project with details and [...]
- Bullet Chart: It's a perfect chart to track single point target and achievement, all the step to create it are [...]
- Thermometer Chart: This chart is also perfect for tracking single point target Vs. achievement [...]
- Speed-O-Meter: It's also called gauge chart which you can use in your dashboards to present some specific KPIs [...]
- Pictograph: In pictograph we add a image/icon in a chart instead of bar, column or line. [...]
- People Graph: It's just like a pictograph where you can use icons to create a cool info-graphic [...]