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
Enter your email address below to download the sample 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.
- Start by selecting all the data and inserting a bar chart.
- Set the vertical axis to ‘categories in reverse order’.
- And the horizontal axis labels to position ‘high’.
- Set the gap width as desired. I like 50%.
- 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.
- Move legend to the top if preferred.
- Set a custom number format on the horizontal axis to hide the minus signs.
- Duplicate the chart (CTRL+D)
- Remove the Male series from one and the Female series from the other.
- In the Male series chart remove the vertical axis labels.
- Resize the charts as required.
- Remove the chart outline and fill colour from both charts.
- Ensure the minimum, maximum, major and minor units are the same in both charts. Fix them in the Chart Options if required.
- 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.
- 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:
- Set the Maximum to a formula. We do this to ensure the scale is the same for both Male and Female.
- Use a MAX formula referencing the values for both Male and Female.
- Change the bar colour as desired.
- 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:
Thanks for help me, but i cant make a chart with positif value. Can u help me please?
Hi James, please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Its very useful. We can show negative numbers positive in cells by using custom format for Option1:
Yes, indeed 🙂
Nice tutorial. I thought I had a way to get a chart like your option 2 but with a single chart to minimize problems with aligning two charts and two axis scales, using primary and secondary axis bars, but the axis labels wouldn’t work the way I wanted them to. I could still do it with data labels, but it becomes very tedious. I haven’t given up, though.
I’m not a fan of this kind of pyramid (aka tornado) chart, as I point out in my own ancient article, Tornado Charts and Dot Plots (https://peltiertech.com/tornado-charts-and-dot-plots/). Since the bars extend in opposite directions, you get a very neat near-symmetry, but you cannot easily compare the data, which you could with clustered bar charts or dot plots.
Thanks, Jon! I agree, it’s not my favourite chart for the reasons you cited. Probably should have mentioned those caveats. Let me know if you crack the secondary axis in the middle.