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]
Do 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.
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.
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.
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.
Change vertical label position to low. Format Axis → Axis Options → Labels → Label Position.
Change series overlap to 100% & 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.
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.
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.
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).
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.
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.