Mrinmoy Roy
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.
- 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 Create a Bar Chart in Excel with Multiple Bars (3 Ways)
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 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.
Similar Readings
- How to Make a Bar Graph in Excel without Numbers
- How to Make a Diverging Stacked Bar Chart in Excel (with Easy Steps)
- What is the Difference Between Bar Graph and Histogram?
- How to Create Clustered Stacked Bar Chart in Excel
- How to Create Stacked and Clustered Bar Chart in Excel (with Easy Steps)
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 (2 Quick Solutions)
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: Excel Bar Graph Color with Conditional Formatting (3 Suitable Examples)
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 Change Bar Chart Width Based on Data in Excel (with Easy Steps)
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.
Related Articles
- Excel Bar Chart with Line Overlay (Create with Easy Steps)
- How to Add Vertical Line to Excel Bar Chart (3 Quick Ways)
- How to Create Stacked Bar Chart for Multiple Series in Excel
- Excel Stacked Bar Chart with Subcategories (2 Examples)
- How to Sort Bar Chart Without Sorting Data in Excel (with Easy Steps)
- How to Plot Stacked Bar Chart from Excel Pivot Table (2 Examples)
