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
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.
Read More: How to Create Bar Chart with Error Bars in Excel
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.
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.
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.
Read More:Â How to Create a Bar Chart with Standard Deviation in Excel
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.
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.
