In a previous blog, we learned how to insert a clustered column chart in excel.
We have also discussed equations and functions, make Venn Diagrams, Stem and Leaf Plots, Supply and Demand Graphs, Burndown Charts, and many more in Excel.
These are all parts of a broad feature of visualizing data in excel. Graphing helps people grasp the potential impact of the data analysis even more. And excel makes it easy, efficient, and appealing to the eyes.
Today we are going to see another type of visualization that is different from most other graphs and charts in excel.
It’s called a population pyramid.
So, today let’s learn how to make a population pyramid in excel.
What is the Population Pyramid?
A population pyramid is a pyramid-shaped graph used by demographers to visualize the age and gender distribution of a given population.
It is also known as the age and gender chart.
The given population can be of a country, region, city, continent, etc.
This pyramid shows us proportions of the population by gender and researchers use it to forecast population growth, trends in the past, examine resident profiles, etc.
There is no built-in feature of the population pyramid in excel, but it is possible to make a population pyramid in excel.
So, let’s see how to make a population pyramid in excel.
Step#1 Arrange the Population Data in Excel
Population data can be manually typed in excel. However, you can go to the U.S. Census Bureau’s International Database and download your required population data.
As shown in the picture, select your country or region, select Five Year Age Groups from the Population by Age dashboard and select your year.
Now, you have to download the data. Select the download option at the top left corner of the table as shown in the picture. You will be given two choices of file formats. Choose the Excel format to download.
Now, open the excel file and you will see that you have your population data ready.
Step#2 Cleaning the Population Data
There are some unnecessary columns of data here, we need to delete the columns. We don’t need columns FIPS, GENC, country, year, % of the population, and gender ratio.
If you download or get your data from somewhere else, clean accordingly and follow the picture. We will need only 5 things for the population pyramid in excel- total population, male population, % of the male population, female population, and % of the female population.
Step#3 Calculating total and Percentage of Male and Female Population
Depending on where you get your population data from, the percentages of males and females might already be included but if not, I am going to show how to calculate them for convenience.
To calculate the percentages, first create a total column for all the population data.
For the total population, you can use the SUM formula. Type the following formula to get the total:
Now drag your fill handle to complete the other total columns.
Now, to calculate the percentage of males and females, type the following formula for males:
And for females:
Basically, divide an age group’s population by the total population of males or females.
Drag the fill handle down to complete the percentage for every age group.
Now, to convert the numbers into percentage form, select both the male and female percentage columns and press CTRL+1 or the Number Format option.
From the dialogue box, select Percentage and set the decimal places to 4 or according to your preference.
Press ok and you will see that the numbers have been formatted to show percentage.
Step#4 Creating Two Helper Columns for the Population Pyramid
Now we need two helper columns. One column should be labeled as Male% and the other Female%.
If you notice any population pyramid, you will see that one gender is at the left of the center line and the other is at the right of the centerline.
Usually, the XY graph has 4 quadrants. The upper left quadrant is negative, and the upper right quadrant is positive for both x and y values.
So, excel will need a negative value to plot the upper left quadrant. That’s why we will make the percentage of males negative to help excel make the population pyramid.
To make the male% negative, type the following formula:
The ampersand (&) joins two values in excel. We have multiplied it by 100 to show the percentage form without the % symbol.
And for the female%, type the following formula:
Drag the fill handle down to complete the calculation.
To format the female% properly, select the female% column and press CTRL+1 or the Number Format option, and select Number.
Now both the helper columns are ready to create a population pyramid in excel.
Step#5 Creating the Population Pyramid
To create the population pyramid, select both the helper columns (male% and female%) and from the Insert ribbon, select Recommended Charts.
You will see the population pyramid at the third option of the recommended charts. If you don’t see it, you can choose it from the Bar charts.
Press ok and create your population pyramid or age and gender chart in excel.
But we are not quite there yet. The pyramid needs to be formatted properly.
Step#6 Formatting the Population Pyramid
First, delete the gridlines in the back by selecting any of the gridlines and pressing delete.
- a. Formatting the Vertical Axis
Now, we need to fix the vertical axis. The vertical axis is on the graph, which doesn’t look good. To move to the far left, select the vertical axis, right-click on the vertical axis, and select Format Axis.
Go to the Label Position from the popup menu in the right and from the drop-down menu select Low.
You will see that the vertical axis has moved to the far left.
- b. Formatting the Data Bars
Now, to format the bars of the population pyramid, select any of the bars, right-click, and select Format Data Series.
From the pop-up menu in the right set Series Overlap to 100% and Gap Width to 0%.
Now, select the right sidebars (female% bars) and go to Fill & Line to give a border and change color.
Enable Solid Fill and Solid Line. You can choose your color according to your preference. I have chosen green for fill and black for the border.
Similarly, select the left sidebars (male% bars) and go to Fill & Line to give a border and change color.
Enable Solid Fill and Solid Line. You can choose your color according to your preference. I have chosen blue for fill and black for the border.
- c. Formatting the Horizontal Axis
The horizontal axis shows negative numbers on the left. We need to fix that.
Select the horizontal axis, right-click, and select Format Axis.
Set the minimum and maximum bounds to 10 and major units to 1.
From the Number options, select Custom category, and in the Format Code option type 0;0 and then select Add.
You can give a title to your population pyramid as well.
- d. Adding Data Labels
Adding data labels is optional. Adding data labels might also make the pyramid very clustered and unappealing. But if you still want to add data labels, select the bars, right-click and select Add Data Labels.
Now, select any of the data labels, right-click, and go to Format Data Labels.
From the popup menu on the right, disable Value, enable Value from Cells and select the % of Males column and finally enable Inside End.
Similarly, do the same process for the right side (female%).
There you go, your population pyramid in excel is ready.
The population pyramid is a very useful graph in presenting the population and gender of a certain region. You can use it professionally or present it in academic presentations as well.
So, there you go now you know how to make a population pyramid in excel.
Hi there, I am Naimuz Saadat. I am an undergrad studying finance and banking. My academic and professional aspects have led me to revere Microsoft Excel. So, I am here to create a community that respects and loves Microsoft Excel. The community will be fun, helpful, and respectful and will nurture individuals into great excel enthusiasts.