«

»

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

=0-C4

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

Chart_Pyramide_Age_1

Insert Bar Chart

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

Chart_Pyramide_Age_2

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:

Chart_Pyramide_Age_3

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

Chart_Pyramide_Age_5

 The chart becomes

Chart_Pyramide_Age_6

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.

Chart_Pyramide_Age_7

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

Chart_Pyramide_Age_10

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.

Chart_Pyramide_Age_11

And the chart becomes

Chart_Pyramide_Age_12

Or if you change the color, you can have this

Chart_Pyramide_Age_13

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 *