Age Population Pyramid in Excel

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

Pyramid Information Lay out

You should lay out the data. Don’t bother about the rest at the moment. You will need data on the number of individuals in each age group and gender.

Age Population Pyramid 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 of them at the same time. Click on three rows, just as it is marked in the one labeled as number 1. You can find the 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.

Age Population Pyramid 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.

Age Population Pyramid 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.

See also  Chart with a single x-axis but two different ranges

If that does not work for you, continue doing numbers 1 and 2 in all the columns.

Age Population Pyramid 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, 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.

Age Population Pyramid write formula

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.

Age Population Pyramid mark columns

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.

Age Population Pyramid insert bar chart

The chart should look like this.

Age Population Pyramid 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 the format axis, which is marked in red and labeled number 2.

Age Population Pyramid format axis

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.

See also  How to Make Dynamic Chart From Static Chart

Age Population Pyramid axis options

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

Age Population Pyramid 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 these options. Just click on no line, which is the one labeled number 2.

Age Population Pyramid format major gridlines

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

Age Population Pyramid chart title

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.

Age Population Pyramid primary vertical axis

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”.

Age Population Pyramid design chart

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

Excel age population pyramid

Interpretation of the Age Population Pyramid Chart

Interpreting an age population pyramid involves understanding the data that is being displayed and what the graph is trying to convey. Here are some steps to interpret an age population pyramid in Excel:

  1. Identify the age groups: The X-axis of the pyramid displays the age groups, usually in five-year increments, such as 0-4, 5-9, 10-14, and so on.
  2. Determine the gender distribution: The height of the bars represents the number of individuals in each age group, and the color of the bars represents the gender. Usually, males are shown on the left side of the graph and females are shown on the right side of the graph.
  3. Analyze the shape of the pyramid: The shape of the pyramid can provide important information about the population. A pyramid with a wide base and a narrow top indicates a growing population, while a pyramid with a narrow base and a wide top indicates an aging population. A balanced pyramid, with a similar width at the base and the top, indicates a stable population.
  4. Evaluate the population growth: By comparing the height of the bars for different age groups, you can evaluate the population growth over time. For example, if the height of the bars decreases as you move up the pyramid, this indicates that the population is aging, while if the height of the bars increases as you move up the pyramid, this indicates that the population is growing.
  5. Determine the population structure: By analyzing the shape of the pyramid, you can determine the population structure of the area. For example, a pyramid with a narrow base and a wide top may indicate an aging population with a low birth rate, while a pyramid with a wide base and a narrow top may indicate a young population with a high birth rate.
See also  How to flip a chart in Excel

You can download a free template of the age population pyramid here.