Population Pyramid

Reading Time: 3 minutes

It is common to create a population pyramid in Excel, in order to represent the distribution of its customers or employees

Unfortunately, Excel doesn't have this template in the Chart's ribbon. But you can create easily a population pyramid chart by following these steps.

The data for a Population Pyramid

When you want to draw a population pyramid chart, your data are usually like this

Step 1: Put one column with negative values

Before to create your chart, you must perform a subtraction of one column with 0.

Women are generally on the left of a population pyramid so this column must have negative values.

  • In a new column, write this formula


  • And copy this formula for the others cells

Step 2: Recommended chart

The newest version of Excel proposes the option Recommended chart in the ribbon Insert

  • Select your data
  • In the ribbon select Insert>Recommended Charts
  • Then, choose the chart type 'Bar'
  • Finish by the option 2D Bar>Clustered bars.

Step 2: Choice of the chart

Now, it's time to select the chart

  • Select your data
  • In the ribbon select Insert>Charts
  • Then, choose the chart type 'Bar'
  • Finish by the option 2D Bar>Clustered bars.
Menu cluster chart 2D

Now, you have the following chart. Wow, it doesn't look like a population pyramid 😲😮😲

And also, you have 2 times a series Women

Step 3: Remove unnecessary data

We have a chart that has two columns for the women (a positive column and a negative). We will remove the positive column.

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

You will also have display this dialog box by doing a right click on your chart and choosing 'Select data ...'

In this dialog, you choose the first 'Women' and click on Remove. If ever you select the wrong series, do not worry, just click the cancel button and start again.

Step 4: Formatting bar

You can see that in your chart, the bars are thin and offset with respect to each other. It is very easy to remedy these two points.

After selecting the chart in the Layout tab, you select the series Women and then you click Format Selection. You can also right-click on your chart and select the menu Format data series.... The dialog box is displayed.

To reduce the gap between the bars, you will move the cursor to 0% (no gap) and the bars are not offset. Then you move the cursor to 100% (overlapped).

The graph becomes

Step 5: Changing axes

To complete our chart, we must reverse the vertical-axis so that the lowest values ​​at the bottom, set the horizontal-axis while the left and also change the number format to not display negative values.

Select the vertical axis (using the dropdown menu in the Layout tab is easier) and then you click Format Selection. In the Settings box, you select Axis Options and you select 'Categories in reverse order' (to return the chart) and also change the value 'Axis Labels' in the Low (to put axis labels on the left).

Then select the horizontal axis.


In the sub-menu Axis Options, change the Axis Label to High (to put the axis labels on the bottom of the chart)


In the sub-menu Number , choose the Custom category and write Standard, Standard in the 'Format Code' and click on the button Add

And the chart becomes :

Change the design

It only remains for you to use the various menus layouts for a very neat pyramid.

The chart is now finished

Related posts

Sunburst Chart

Frédéric LE GUEN

Add Emojis to your charts, formulas

Frédéric LE GUEN

Gantt Chart

Frédéric LE GUEN


Honor 22/11/2019 at 08:59

Thanks for this - this was just what I was looking for 🙂

Frédéric LE GUEN 26/11/2019 at 15:04
Mr LG Frost 12/01/2018 at 17:47

Males are usually on the left of the pyramid

Frédéric LE GUEN 12/01/2018 at 19:13

Good point. I will change the article soon


Leave a Comment