How to Show Variance in Excel Bar Chart (with Easy Steps)

In Excel bar charts, data are represented with some vertical bars. Using those vertical bars, you can show the variance between multiple parameters. This representation of data helps to understand and compare data better in Excel. In this article, I will guide you to show the variance in an Excel bar chart step by step. So, without having any further discussion, let’s get started.

Steps to Show Variance in Excel Bar Chart

In the following dataset, I have an annual profit record. In the record, I have some annual profit amounts against their corresponding year. I will use this dataset to show you how to display variance in an Excel Bar Chart with ease.

Step-1: Prepare Dataset to Show Variance

To show variance in an Excel bar chart, prepare your dataset first.

Now, insert the following formula in cell B8 to transpose all the years from the source dataset.

=TRANSPOSE(C4:H4)&REPT(" ",13)

Then press ENTER.

Now use the following formula in cell C8 to transpose all the profit amounts from the source dataset.

=TRANSPOSE(C5:H5)

Then press ENTER.

To create a variance column, use the following formula to retrieve the profit amounts from the Profit column.

=C9:C13

Then hit ENTER.

To calculate the variance between the Variance Bar and Profit columns, type the following formula in cell E8.

=D8#-C8:C12

Then hit ENTER.

Now filter out all the positive variances from the Variance column using the following formula.

=IF(E8#>=0,-E8#,"")

Then press ENTER.

Then filter out all the negative variances from the Variance column using the following formula.

=IF(E8#<=0,E8#,"")

Then press ENTER.

Finally, calculate the variance in percentage using the following formula in cell H8.

=IFERROR((C9-C8)/C8,0)

Press the ENTER button.
Drag the Fill Handle icon from cell H8 to H12.

Step-2: Insert a Bar Chart

After preparing the dataset, it’s time to insert the Bar Chart.

To do that,

Select the entire Year, Profit, and Variance Bar columns.
Then go to the Insert tab.
From the Charts group, click on the Insert Column or Bar Chart drop-down.
From the 2-D Column section, select Clustered Column.

Step-3: Add Error Bars

Now, you need to add the error bars to show variance.

To add the Error Bars,

Click on the Orange column to select them all.
Then click on the plus icon at the top-right corner of the chart.
Now go to Error Bars ➤ More Options.

In the Format Error Bars dialog box, select Custom in the Error Amount section.
Then click on the Specify Value button.

Then, the Custom Error Bars dialog box will appear.

Insert the range of the Var (+ve) column in the Positive Error Value box.
After that, insert the range of the Var (-ve) column in the Negative Error Value box.
Then hit OK.

Step-4: Increase Bar Width

To increase the width of the blue column,

Click any one of the blue columns.
Then set the values of both Series Overlap and Gap Width to 0% in the Format Data Series dialog box.

Read More: Excel Chart Bar Width Too Thin

Step-5: Make Variance Bar Invisible

Now you have to make all the orange columns invisible.

To do that,

Select an orange bar.
Then go to Format ➤ No Fill.

Step-6: Show Variance Adding Data Labels

Finally, add some Data Labels to the Error Bars to show the variance in the bar chart.

To add the Data Labels,

Click on any error bars to select.
Then click on the plus icon at the top-right corner of the bar chart.
Then go to Data Labels ➤ More Options.

Select Value From Cells in the Format Data Labels dialog box.

Then the Data Label Range dialog box will appear.

Insert the entire range of the Variance (%) column, in the Select Data Label Range section.
Then press OK.

After that, you will get your desired chart. Where the variance is shown using the Error Bars.

So, this is how you can show the variance in an Excel bar chart.

Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the topics discussed in this article.

Download Practice Workbook

You can download the Excel file from the following link and practice with it.

Display Variance in Bar Chart.xlsx

Conclusion

To sum up, I have discussed steps to show variance in the bar chart in Excel. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.

<< Go Back to Excel Bar Chart | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!