How to Create a Population Pyramid Chart in Excel

Step-by-step

A population (or age) pyramid shows males and females side by side for each age group, with bars running in opposite directions from a central axis. The trick is simple: make one side’s values negative, then use a bar chart.

1. Start with the population table

The example lists age groups, the number of males (column B) and females (column C) in each group, and a total for each sex in row 23.

2. Convert the numbers to percentages, one side negative

Add two columns, Male % and Female %. Percentages make the two sides comparable, and making the male side negative is what pushes its bars to the left.

• Male % (D4): =0-(B4/$B$23)
• Female % (E4): =C4/$C$23

The dollar signs keep the totals fixed as you copy the formulas down. Format both columns as percentages (two decimals if you like). You can hide columns B and C once the percentages are in place.

3. Insert a bar chart

Select the age groups and the two percentage columns, and insert a clustered bar chart.

4. Move the age labels to the side

Right-click the vertical axis and choose Format Axis. Under Labels, set Label Position to Low, which moves the age groups to the left edge instead of the middle. Also tick Categories in reverse order so the youngest group is at the bottom.

5. Close up the bars

Delete the gridlines. Select one of the bar series and, in Series Options, decrease the Gap Width and increase the Series Overlap, so the male and female bars line up on the same row.

6. Finish the formatting

Colour the bars (the video uses blue for male and pink for female), adjust the horizontal axis label position, and change the chart title if you want. Your pyramid chart is ready.