Creation of a population pyramid

It is common to have to create a population pyramid in Excel, in order to identify the distribution of its customers. Unfortunately, Excel doesn't have this pattern in the Chart's ribbon. Nevertheless, you can create your own population pyramid chart by following these steps.


The data for a Population Pyramid

Data for a population pyramid are traditionally presented as follows.











Subtract a column

Before to create your chart, you must perform a subtraction of one of the columns (women are generally on the left of the graph, so it is this column that will be subtracted).


You write then in a new column the following formula


Copy this formula to all the series 'Woman' and also add a title in cell D3.








Choice of the chart

On the ribbon, go in the menu Insert> Chart.

Then, you choose the type 'Bars', and the first sub-menu 2D Bars> Clustered bars


Insert Bar Chart

So, you get the following graph (not existing for the moment :?)


Remove the second 'Women' column

For the moment, in our chart, we have 2 times the Women column (one for the positive value and the second with negative values). We must remove the positive column from the chart but keep it in our worksheet.

Select the chart and select the menu Design > Select Data. The following dialog box appears:


You could also display this dialog box by right-clicking on your graph and choosing 'Select Data ...'

Chart_Pyramide_Age_4In this dialog box, you select the first 'Women' and click on Remove. If ever you're in the wrong series, do not worry, simply click the 'Cancel' button and try again.





With Excel 2013, it's easier to remove a series from a chart; you just have to unchecked the series.

Format the bars

Let's focus on the bars now. The bars are narrow and not align for the same range of ages. But don't worry, It's easy to fix these two problems.

First, you select one series on your chart and you activate the menu (Chart Tool) Format > Format Selection. You can also right-click in your chart and select the menu Format Data Series. In the dialog box, you can reduce the gap between the bars of the chart by changing the Gap Width to 0 (no gap) and also, we want to create and overlap between the series so we change the value to 100% (Overlapped).


 The chart becomes


Change the axes

To enhance our chart, we must reverse the x-axis in order to have the youngest people at the bottom and also change the format of the numbers to not display negative values.

To do that, select the vertical axis (you can use the dropdown in the Format tab, it's easier) then you click Format Selection. In the dialog box you select the Axis Option label and you check Categories in reverse order' (to return the graph) and also change the position of the axis label to Low.


Then, select the horizontal axis and display the Format dialog box

Chart_Pyramide_Age_8In the left pane, select the Axis options and change the setting of the Axis label to High.










Chart_Pyramide_Age_9In the Number menu, select the category'Custom' and write Standard, Standard and then click Add









The graph becomes


Change the design

We have nearly finished. The data and the pattern are fine but the design is really not nice. Lets apply one of the layout of Excel.


And the chart becomes


Or if you change the color, you can have this


Related articles

Have a look at these other articles that could help you in your work

Permanent link to this article: https://www.excel-exercise.com/creation-of-a-population-pyramid/

Leave a Reply

Your email address will not be published. Required fields are marked *