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.


Download Practice Workbook

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


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, you need to 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.

Prepare Dataset to Show Variance in Excel Bar Chart

  • Now use the following formula in cell C8 to transpose all the profit amounts from the source dataset.
=TRANSPOSE(C5:H5)
  • Then press ENTER.

Prepare Dataset to Show Variance in Excel Bar Chart

  • To create a variance column, use the following formula to retrieve the profit amounts from the Profit column.
=C9:C13
  • Then hit ENTER.

Prepare Dataset to Show Variance in Excel Bar Chart

  • To calculate the variance between the Variance Bar and Profit columns, type the following formula in cell E8.
=D8#-C8:C12
  • Then hit ENTER.

Prepare Dataset to Show Variance in Excel Bar Chart

  • Now filter out all the positive variances from the Variance column using the following formula.
=IF(E8#>=0,-E8#,"")
  • Then press ENTER.

Prepare Dataset to Show Variance in Excel Bar Chart

  • Then filter out all the negative variances from the Variance column using the following formula.
=IF(E8#<=0,E8#,"")
  • Then press ENTER.

Prepare Dataset to Show Variance in Excel Bar Chart

  • 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.

Prepare Dataset to Show Variance in Excel Bar Chart


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.

Insert Bar Chart to Show Variance in Excel Bar Chart


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.

Add Error Bars to Show Variance in Excel Bar Chart

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

Then 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.


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 FormatNo 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 LabelsMore Options.

Add Data Labels to Show Variance in Excel Bar Chart

  • 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.

Add Data Labels to Show Variance in Excel Bar Chart

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.

Show Variance in 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.


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. And please visit our website Exceldemy to explore more.

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo