How to Make a Population Pyramid with Projection Lines

A population pyramid is a side by side bar chart and very useful to compare categories over time. Please follow the steps to make a population pyramid with the data below (download example here):

Step 1: Prepare data and change one group of data to negative numbers (e.g., female in this example)

Step 2: Select the data columns "Age, 2000 – Female, 2000 – Male, 2020 – Female and 2020 – Male", then select Insert >> 2-D Bar >> Stacked Bar;

Step 3: You will get the chart similar to the one below.

Step 4: Move Y Axis to the left side

1. Right-click the Y Axis, and select "Format Axis" from the dialog box;

2. Under the "Labels" section, select "Low" in the "Label Position" box.

3. The Y-Axis is now on the left of the chart.

Step 5: Change chart type for data "2020-male and 2020-female" to line chart;

1. Right-click on the purple (2020-male), select change series chart type, in the dialog window, select "Scatter with smooth lines" for "2020 – Female and 2020 – Male";

2. Now you will find the 2020 – female and 2020 – male moved to the center, see below;

Step 6: Switch X and Y value for 2020 data

1. Right-click on the "2020 – male", the purple line, and click "Select Data", then click "Edit" in the new window;

2. Change X value to the value of "2020 – male", and Y value to the order value, see below;

3. Repeat and change the value "2020 – Female", and Y value with the order value. Now the chart should like below.

Step 7: Adjust secondary Y axis, change the minimum value to 0.5 and maximum value to 19.5, to move the line chart the same height as the bar chart.

Double click the Y Axis on the right, and change the minimum value to 0.5, and the maximum value to 19.5 in the Format Axis panel.