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

Get FREE Advanced Excel Exercises with Solutions!

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. Read More: How to Make a Simple Bar Graph in Excel

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

• 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 FormatNo Fill. Read More: How to Create a 3D Bar Chart in Excel

### Step-6: Show Variance Adding Data Labels

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

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Mrinmoy Roy

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 Advanced Excel Exercises with Solutions PDF  