Age Population Pyramid in Excel
An age population pyramid chart in Excel visualizes how many people exist at specific ages and their gender distribution. This pyramid chart in Excel shows males and females separately. Creating this pyramid chart in Excel is straightforward. Follow this simple step-by-step guide to build your age population chart.
Table of Contents
Pyramid Information Layout
Organize your data by age group and gender to create a pyramid chart in Excel. Include male and female population counts for each age range.
Select the columns you want to calculate. You need to include female and male columns. Calculate the total for all columns together. Click on three rows as marked in the table. Find AutoSum in the Home tab.
You could also go to the formulas, which is labeled number 2, and finally press AutoSum, labeled number 3, and just click on it.
Mathematical Workout
Mark the rows, beginning with the one marked in red, and then click on AutoSum to calculate the total of the rows. Do the same thing with all the rows.
Click on the column, as the one marked as number 1 has made, and then type in the same thing showing in one labeled as number 2. Click on the column beneath the one you have just calculated, and then type in =-1*100*B6/D24. Click on the small square showing beside number 3, the one marked in green, and drag it down to the last column.
If that does not work for you, continue doing numbers 1 and 2 in all the columns.
You are going to choose the column where you would like to place the result of the calculation. As it is written in number 1, type in the calculation, and choose the column you would calculate using the sumproduct function in Excel. You should continue doing the same thing in all the columns you would like to calculate using the same thing written in number 1 in the picture above.
Pyramid Chart Creation
Choose the age row, all the way down, as it is labeled as number 1. Then, you should press the CTRL button on your keyboard while clicking on the area that is marked as number 2, and choose the whole row, with your keyboard arrows and SHIFT button. You should not choose the total row, though.
Click on the insert tab, as marked in number 1, and then choose the 2D-Bar chart, as marked in number 2. Then choose the chart.
This will create the basic structure of the population pyramid.
Population Adjustment
Right-click on the axis. The axis has to be marked, as you’d see in the number 1 area. Then click the format axis, which is marked in red and labeled number 2.
In the format axis, you should choose the axis option (if there is another thing showing). This is the one labeled as number 1. Then you should choose the chart symbol, which is the one labeled number 2. Then click on labels, which is number 3, and finally choose low in the label position, which is labeled number 4.
Click on the lines, the one marked number 1. Clicking on it makes all the lines show that they have been marked.
Once you have clicked on the lines, your Microsoft Excel should show the major gridlines option, and if it does not, you should click on it. This is the one labeled as number 1. You should see the line in these options. Just click on no line, which is the one labeled number 2.
Click on the chart title and type in the title of your chart.
In the one marked as number 1, you should right-click with your mouse. This would mark that axis. Then you should click on number 2, which is “add chart element”, and then choose the “axis title”, marked number 3, and then choose “Primary Vertical”, marked number 4. The one labeled 5, is the place where you would be writing the axis title.
Designing the Chart
You should click on the chart you have created, and you will be able to see the design tab. If you do not, click on the design tab, marked in red, labeled number 1, and choose the desired design, which is marked as number 2. Finally, you could also decide to change the colors of the charts by using the one marked as number 3, “change colors”.
That is what the age population graph would look like when you are done.
You can download a free template of the age population pyramid here.
BHexKL
My pyramid looks a bit different but it is also fine