Population Pyramid with Excel

Population Pyramid with Excel
Last Updated on 12/05/2024
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

To learn how to draw a population pyramid chart in Excel, let's take this file with the population of Korea.

Percentage of the Population of Korea

Step 1: Convert the Women's values with negative values

Before creating your chart, you must perform 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

=0-C4

  • And copy this formula for the other cells
Convert the womens value to negative

Step 2: Create a chart for your data

  1. Select your Data
  2. Select Insert > Recommended chart
Menu Recommended chart
  1. Select the third option
Select the best chart to display your data

Or, if you prefer to select this type of chart from the chart gallery

  1. Go to Insert
  2. Select the bar chart type
  3. And the 2-D Bar option (horizontal representation)
Menu cluster chart 2D

You have created this chart but there is still work to do to have a perfect population pyramid chart.

Chart returns by the recommended chart tool

Step 3: Remove the first Women's Series

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

  1. Select the chart
  2. And select the menu Design > Select Data.
Menu Chart Select Data
  1. In the list of series, unchecked the first Women series
Unchecked the first Womens series

If you don't have unchecked the correct series, you can't reopen the same dialogue box and select the correct series this time 😉

Step 4: Format the bars of the chart

You can see that in your chart, the bars are thin and offset with respect to each other. Let's correct these 2 points

Format the bars of the charts
  1. Double-click to any bars to display the Chart pane
  2. Then select the Series options
  3. Change the Series Overlap to 100%
  4. Change the Gap Width to 0%
Change the setting of the Data Series

The chart becomes

Chart with no overlap between the series

Step 5: Reverse the chart

Next, we must reverse the vertical axis so that the lowest values ​​are at the bottom.

  1. Select the Vertical axis, from the pane, with the dropdown menu
Select the vertical axis with the dropdown menu
  1. Select the Axis Option icon
  2. Check the option Categories in reverse order
Option Categories in Reverse Order

Step 6: Format the numbers

Here, we have 2 actions

  • Remove the negative sign for the values of the Women
  • Display the number of the ages on the left side of the chart

Remove negative numbers

  1. Select the Horizontal Axis
  2. Then, select the Axis Option icon
  3. And the Number format option
  4. Here is the tricks, in the format Code, you write General;General (the delimiter is a semi-colon)
Remove the negative sign for the women

Display ages on the left side of the chart

  1. Select the Vertical Axis
  2. Then, select the Axis Option icon
  3. And the Label section
  4. In the label position, select the Low option
Change the position of the axis labels

And the chart becomes

Chart of the population pyramid without specific design

Change the design of the population pyramid

It only remains for you to use the various designs menus for a very neat pyramid. The chart is now finished 😀😎👍

Chart Population Pyramid

6 Comments

  1. Marzia
    06/04/2023 @ 10:40

    THANKS SO MUCH FOR THIS. Big help!!

    Reply

  2. Mzee
    02/03/2023 @ 18:33

    Wow, this was of tremendous help to me, I am so excited to have made my first pyramid and it is perfect.
    Thank you!

    Reply

  3. Honor
    22/11/2019 @ 08:59

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

    Reply

    • Frédéric LE GUEN
      26/11/2019 @ 15:04

      Reply

  4. Mr LG Frost
    12/01/2018 @ 17:47

    Males are usually on the left of the pyramid

    Reply

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

      Hello,
      Good point. I will change the article soon

      Reply

Leave a Reply

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

Population Pyramid with Excel

Reading time: 3 minutes
Last Updated on 12/05/2024

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

To learn how to draw a population pyramid chart in Excel, let's take this file with the population of Korea.

Percentage of the Population of Korea

Step 1: Convert the Women's values with negative values

Before creating your chart, you must perform 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

=0-C4

  • And copy this formula for the other cells
Convert the womens value to negative

Step 2: Create a chart for your data

  1. Select your Data
  2. Select Insert > Recommended chart
Menu Recommended chart
  1. Select the third option
Select the best chart to display your data

Or, if you prefer to select this type of chart from the chart gallery

  1. Go to Insert
  2. Select the bar chart type
  3. And the 2-D Bar option (horizontal representation)
Menu cluster chart 2D

You have created this chart but there is still work to do to have a perfect population pyramid chart.

Chart returns by the recommended chart tool

Step 3: Remove the first Women's Series

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

  1. Select the chart
  2. And select the menu Design > Select Data.
Menu Chart Select Data
  1. In the list of series, unchecked the first Women series
Unchecked the first Womens series

If you don't have unchecked the correct series, you can't reopen the same dialogue box and select the correct series this time 😉

Step 4: Format the bars of the chart

You can see that in your chart, the bars are thin and offset with respect to each other. Let's correct these 2 points

Format the bars of the charts
  1. Double-click to any bars to display the Chart pane
  2. Then select the Series options
  3. Change the Series Overlap to 100%
  4. Change the Gap Width to 0%
Change the setting of the Data Series

The chart becomes

Chart with no overlap between the series

Step 5: Reverse the chart

Next, we must reverse the vertical axis so that the lowest values ​​are at the bottom.

  1. Select the Vertical axis, from the pane, with the dropdown menu
Select the vertical axis with the dropdown menu
  1. Select the Axis Option icon
  2. Check the option Categories in reverse order
Option Categories in Reverse Order

Step 6: Format the numbers

Here, we have 2 actions

  • Remove the negative sign for the values of the Women
  • Display the number of the ages on the left side of the chart

Remove negative numbers

  1. Select the Horizontal Axis
  2. Then, select the Axis Option icon
  3. And the Number format option
  4. Here is the tricks, in the format Code, you write General;General (the delimiter is a semi-colon)
Remove the negative sign for the women

Display ages on the left side of the chart

  1. Select the Vertical Axis
  2. Then, select the Axis Option icon
  3. And the Label section
  4. In the label position, select the Low option
Change the position of the axis labels

And the chart becomes

Chart of the population pyramid without specific design

Change the design of the population pyramid

It only remains for you to use the various designs menus for a very neat pyramid. The chart is now finished 😀😎👍

Chart Population Pyramid

6 Comments

  1. Marzia
    06/04/2023 @ 10:40

    THANKS SO MUCH FOR THIS. Big help!!

    Reply

  2. Mzee
    02/03/2023 @ 18:33

    Wow, this was of tremendous help to me, I am so excited to have made my first pyramid and it is perfect.
    Thank you!

    Reply

  3. Honor
    22/11/2019 @ 08:59

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

    Reply

    • Frédéric LE GUEN
      26/11/2019 @ 15:04

      Reply

  4. Mr LG Frost
    12/01/2018 @ 17:47

    Males are usually on the left of the pyramid

    Reply

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

      Hello,
      Good point. I will change the article soon

      Reply

Leave a Reply

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