Age Population Pyramid in Excel

The age population is usually a good way to know how many people are in a specific age, and how many of them were girls, and how many were boys.
It is also very easy to create the age population, using Excel. You are going to learn how to insert age pyramid graph with this very simple step-by-step instruction.

Pyramid Information Layout

You should layout the data. Don’t bother about the rest at the moment.

table data

Mark the columns that you are going to calculate. There are female and male, and we are going to do the total calculation of all at the same time. Click on three rows, just as it is marked in the one labeled as number 1. You could find the AutoSum in the “home” tab. You could also go the formulas, which is labeled number 2, and finally press AutoSum, labeled number 3, and just click on it.

formulas autosum

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.

autosum columns

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 the number 1, and number 2 on all the columns.

drag down column

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, and then 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.

write formula

Pyramid Chart Creation

Choose the age row, all the way down, as it is labeled as number 1. Then, you should press CTRL button on your keyboard, while clicking on the area that is marked as number 2, and choose the whole rows, with your keyboard arrows, and SHIFT button. You should not choose the total row though.

mark columns

Click on insert tab, as marked in the number 1, and then choose the 2D-Bar chart, as marked in number 2. Then choose the chart.

insert bar chart

The chart should look like this.

chart inserted

Population Adjustment

Right click on the axis. The axis has to be marked as you’d see in the number 1 area. Then click format axis, which is marked in red, and labeled number 2.

format axis

In the format axis, you should choose the axis option (if it 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 the number 3, and finally choose low in the label position, which is labeled number 4.

axis options

Click on the lines, the one marked number 1. Clicking on it make all the lines showing that they have been marked.

click line

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 this options, just click on no line, which is the one labeled number 2.

format major gridlines

Click on the chart title, and type in the title of your chart.

chart title

In the one marked as number 1, you should right click with your mouse, this would marked that axis. Then you should click on the 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 number 5, is the place where you would be writing the axis title.

primary vertical axis

Designing the Chart

You should click on the chart you have created, and you would be able to see the design tab, and if you do not, click on the design tab, marked in red, labeled number 1, then choose the desired design, which is marked as number 2. Finally, you could also decide to change the colors of the charts, using the one marked as number 3, “change colors”.

design chart

That is how the age population graph would look like when you are done.

Excel age population pyramid


You can download the Template here – Download