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

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

  • Transpose the years from the source dataset:
    • In cell B8, use the formula:
=TRANSPOSE(C4:H4)&REPT(" ",13)
  • Press ENTER.

Prepare Dataset to Show Variance in Excel Bar Chart

  • Transpose the profit amounts from the source dataset:
    • In cell C8, use the formula:
=TRANSPOSE(C5:H5)
  • 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
  • Press ENTER.

Prepare Dataset to Show Variance in Excel Bar Chart

  • Create a variance column by retrieving profit amounts from the Profit column:
    • In cell E8, use the formula:
=D8#-C8:C12
  • Press ENTER.

Prepare Dataset to Show Variance in Excel Bar Chart

  • Filter out positive variances:
    • In cell F8, use the formula:
=IF(E8#>=0,-E8#,"")
  • Press ENTER.

Prepare Dataset to Show Variance in Excel Bar Chart

  • Filter out negative variances:
    • In cell G8, use the formula:
=IF(E8#<=0,E8#,"")
  • Press ENTER.

Prepare Dataset to Show Variance in Excel Bar Chart

  • Calculate variance in percentage:
    • In cell H8, use the formula:
=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

  • Select the entire Year, Profit, and Variance Bar columns.
  • 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, choose Clustered Column.

Insert Bar Chart to Show Variance in Excel Bar Chart

Read More: How to Make a Simple Bar Graph in Excel


Step 3: Add Error Bars

  • Click on the Orange columns to select them all.
  • Click the plus icon at the top-right corner of the chart.
  • Go to Error Bars More Options.

Add Error Bars to Show Variance in Excel Bar Chart

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

  • Insert the range of the Var (+ve) column in the Positive Error Value box.
  • Insert the range of the Var (-ve) column in the Negative Error Value box.
  • Press OK.

Read More: How to Create Bar Chart with Error Bars in Excel


Step 4: Increase Bar Width

  • Click any blue column.
  • Set Series Overlap and Gap Width to 0% in the Format Data Series dialog.

Read More: Excel Chart Bar Width Too Thin


Step 5: Make Variance Bar Invisible

  • Select an orange bar.
  • Go to FormatNo Fill.

Read More: How to Create a 3D Bar Chart in Excel


Step 6: Show Variance with Data Labels

  • Click any error bars to select.
  • Click the plus icon at the top-right corner of the bar chart.
  • 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.

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

Add Data Labels to Show Variance in Excel Bar Chart

Your desired chart will now show variance using Error Bars.

Show Variance in Excel Bar Chart

Read More: How to Create a Bar Chart with Standard Deviation in Excel


Practice Section

You’ll find an Excel sheet resembling the screenshot below at the end of the provided Excel file. This sheet allows you to practice all the topics covered in the article.


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo