Excel Pyramid Charts

Excel pyramid charts are easy to build using bar charts or Conditional Formatting, but there are a few rules you must follow depending on the approach you choose.

Before we look at the steps, keep in mind that these charts aren’t limited to visualising gender splits. They’re also handy for any type of demographic data where you have two groups (typically male and female) split into categories.

Watch the Video

Download Workbook

Options for Excel Pyramid Charts

Option 1: Pyramid Charts using a Single Chart

The easiest way to create a pyramid chart is with a bar chart. The trick is to convert the values for the left-hand series to negatives, as you can see in my source data below:

Step 1: insert a bar chart:

Step 2: Set the series overlap to 100%:

Tip: also reduce the gap width for thicker bars. 50% is often ideal.

Step 3: Set the vertical axis labels’ position to ‘Low’, which will move it to the left:

Step 4: Set the horizontal axis label number format to hide the minus sign using a custom number format:

Tip: for large numbers you might also consider scaling the values to millions or billions with a custom number format.

Step 5: If you prefer the pyramid to run in ascending order, change the sort order of the source data, or set the vertical axis ‘categories in reverse order’:

Note: if you check this setting, you’ll also need to change the horizontal axis position to ‘High’:

Step 6: Finally, give the chart a title and change the colours as desired.

Option 2: Pyramid Charts using Two Charts

If you prefer the vertical axis labels in the centre of the bars you can use two charts:

Note: For visual instructions please watch the video above. If you’re confident with charts you can follow the written steps below.

Again, the source data requires the left-hand series to be negative values.

1. Start by selecting all the data and inserting a bar chart.
2. Set the vertical axis to ‘categories in reverse order’.
3. And the horizontal axis labels to position ‘high’.
4. Set the gap width as desired. I like 50%.
5. Enter a space in the chart title to delete the contents while retaining the space above the plot area for a manual title to be added later.
6. Move legend to the top if preferred.
7. Set a custom number format on the horizontal axis to hide the minus signs.
8. Duplicate the chart (CTRL+D)
9. Remove the Male series from one and the Female series from the other.
10. In the Male series chart remove the vertical axis labels.
11. Resize the charts as required.
12. Remove the chart outline and fill colour from both charts.
13. Ensure the minimum, maximum, major and minor units are the same in both charts. Fix them in the Chart Options if required.
14. Position the charts on top of one another and make sure the plot areas are the same size. This ensures they are both to the same scale.
15. Insert a text box for the chart title.

Option 3: Pyramid Charts using Conditional Formatting

Conditional Formatting data bars are another easy way to create Excel pyramid charts.

With this approach there is no need to convert one series to negative values, as you can see below:

Step 1: Select the first series (in column C) > Home tab > Conditional Formatting > Data Bars"

Step 2: Edit the rule: Home tab > Conditional Formatting > Manage Rules. Select the rule in the list and Edit:

In the Edit Formatting Rule dialog box make the following changes:

1. Set the Maximum to a formula. We do this to ensure the scale is the same for both Male and Female.
2. Use a MAX formula referencing the values for both Male and Female.
3. Change the bar colour as desired.
4. Set the bar direction to right-to left.

Step 3: Repeat step 1 for the Female series.

Step 4: Edit the rule for the Female series except you can leave the bar direction as is.

Step 5: Conditional Formatting data bars don’t have a horizontal axis therefore you may prefer to leave the numbers visible in the cells. If so, I like to algin them close to the centre line, but it’s important that the numbers themselves are still left aligned to one another. You can do this by clicking the Indent button to move the numbers to the left:

Tip: I also do this for the Male series to move it just off the centre line. And repeat for the column headers.

If you prefer the vertical axis labels in the centre of the bars, then it’s easily repositioned by moving them to the centre column in a Conditional Formatting pyramid chart: