Mirror bar chart is a type of bar chart that comparatively displays two sets of data side by side along a vertical axis. The chart resembles the reflection of a mirror, hence the name “mirror bar chart”. The advantage of a mirror bar chart is that it illustrates two data sets side by side and therefore makes it easy to make comparisons and spot any differences between them.

Note: This tutorial uses Excel 2013. In other Excel versions, there may be some slight differences in the described steps.

Watch the video below to see how to create a mirror bar chart in Excel. If you prefer written instructions, then continue reading.

To create a mirror bar chart in Excel, take the following steps:

1. Enter and select the data that you will use to create the mirror bar chart. An important point here is to write the values of one of the data sets as negative numbers so that they appear on the opposite side of the vertical axis.

2. On the Insert tab of the ribbon, in the Charts group, click on the Insert Bar Chart button and in the opened menu, click on the second option, which is Stacked Bar, among the 2-D Bar charts.

This inserts a mirror bar chart into the worksheet.

3. Move the vertical axis labels to the left of the chart. To do that:

• Click on the Chart Elements button. In the Chart Elements menu, hover your cursor over the Axes option and click on the arrow next to it.

• In the opened submenu, click on More options.

• This opens the Format Axis task pane. By default, the horizontal axis is selected. Click on the vertical axis to select it.

• In the Format Axis task pane, scroll down and click on the Labels option to expand it.

• In the Labels section, click the Label Position drop down menu and select Low to move the vertical axis labels to the left of the chart.

4. Remove the gaps between the bars. To do that:

• Click on the data series to select them. This activates the Format Data Series task pane.

• In the Format Data Series task pane, change the gap width to 0% by either typing 0 in the Gap Width box or moving the slider all the way to the left.

5. Add a border to the bars. To do that:

• Click on the data series for Product A and on the Format tab under Chart Tools choose White in the Shape Outline drop down menu.

• Then, click on the data series for Product B and again choose White for the Shape Outline.

6. Remove the horizontal axis, the gridlines and the chart title by unticking these options in the Chart Elements menu.

7. Add data labels to the chart by ticking the Data labels option in the Chart Elements menu.

8. Format the negative values for Product A so that they appear as positive numbers. To do that:

• In the Chart Elements menu, hover your cursor over the Data Labels option, click on the arrow next to it and in the opened submenu, click on More options.

• This opens the Format Data Labels task pane. Scroll down to the bottom of the task pane and click on the Number option to expand it.

• In the Number section, click the Category drop down menu and select Number.

• Put the Decimal places to zero.

• For Negative Numbers, choose the second option, which is red without parentheses.

• In the Format Code box, type Black in place of Red and click on the Add button.

9. Finally, change the text colors of the vertical axis labels, the legend and the data labels for Product B by clicking on the chart and on the Format tab under Chart Tools choosing Black in the Text Fill drop down menu.

So that’s it. Our mirror bar chart is ready.

What other charts do you use for comparison? Write in the comment section below.