How to Graph Uncertainty in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is among the most helpful software packages out there. It is feasible to do an endless number of actions on a dataset using the features and tools made available by Excel. In Excel charts, we need to display the amount of uncertainty routinely. The graph’s inclusion of uncertainty facilitates trust in the data and information. This article will look at two straightforward strategies for demonstrating errors in charts. Therefore, you should review these 2 easy ways to Graph Uncertainty in Excel.

Table of Contents hide

Download Practice Workbook

2 Easy Ways to Graph Uncertainty in Excel

1. Utilize Error Bar Option to Plot Uncertainty in Excel

2. Display Uncertainty in Chart Through Excel VBA

Conclusion

Download Practice Workbook

Please click on the link below this paragraph if you want a free copy of the example workbook we referenced during the presentation.

Graph Uncertainty.xlsm

2 Easy Ways to Graph Uncertainty in Excel

To demonstrate this, let’s have a look at a representative dataset. For example, the following dataset has columns labeled Sales, Profit, and Uncertainty of Profit. We will create a chart now that compares the number of sales to the amount of profit. Alongside the Y-Axis, the graph will represent the degree of uncertainty associated with the Profit column. The first method shown in this article is a demonstration of how to express uncertainty in a chart via the use of the Error Bar Option. In contrast, another creates a graph that depicts uncertainty using Excel’s Visual Basic for Applications (VBA) feature. In addition, I have been writing this essay with the help of Microsoft Excel 365. You are free to choose whatever version better suits your needs. Whichever one you want to use is OK with us.

1. Utilize Error Bar Option to Plot Uncertainty in Excel

Excel’s error bars offer a graphical portrayal of the variation in the data they contain. Error Bars are a helpful way to highlight estimated error or uncertainty and provide a more holistic understanding of the accuracy of a measurement. In this discussion, we will use the Error Bar Option to construct an illustrating graph uncertainty. To successfully do the project, please follow these directions closely and follow them carefully.

📌 Steps:

To begin, choose the working sheet as the Active Sheet.
Second, select the B5:C10 range.

Third, navigate to the Insert tab.
After that, from the Charts group, click the Buble Chart icon.
Later, tap the Scatter icon from the Scatter section.

Subsequently, a graph window will open that plots the values of our selected cells.
It is essential to note that the Scatter Chart plots the left column along the X-Axis and the column right with the Y-Axis.

Now, click anywhere in the Chart, followed by the Plus symbol.
Due to this, the Chart Elements bar will open.
Latterly, choose the Error Bars option.
After that, pick More Options.

Eventually, on the rightmost side, the Format Error Bars pane will appear.
Next, from the Vertical Error Bar section, check Both and Cap as the Direction and the End Style, respectively.
Later, check Custom from the Error Amount, then go to Specify Value.

As a result, the tiny window of the Custom Error Bars will open.
Presently, write the sheet name followed by an Exclamation mark and the range in the Positive Error Value box.
Likewise, input the same range in the Negative Error Value box.
Later, hit OK.

Consequently, the intended graph output will occur like the below.

2. Display Uncertainty in Chart Through Excel VBA

VBA stands for Visual Basic for Application and is abbreviated simply as VBA. Microsoft was the company that first developed the programming language known as VBA. Users can use the programming language known as VBA to access functions that are incompatible with Excel. Utilizing Excel’s Visual Basic for Applications (VBA) is an additional intriguing method of visualizing chart faults. In this part of the tutorial, we will use the Visual Basic for Applications (VBA) programming language to graph uncertainty in Excel. To do the assignment successfully, please follow the instructions below.

📌 Steps:

First, choose the intended sheet as an active sheet.
Second, navigate to the Developer tab, followed by Visual Basic.

After that, click the Insert option and then the Module.

Subsequently, an empty Module Box will appear.
Now, write the following code in the Module Box.

Sub AddUncertainty()
Dim ExcelVBA As Worksheet
Dim plot As Chart
Dim object As ChartObject
Set ExcelVBA = ActiveWorkbook.Worksheets("ExcelVBA")
Set plot = ExcelVBA.Shapes.AddChart.Chart
With plot
  .ChartType = xlXYScatter
  .SeriesCollection.NewSeries
  .SeriesCollection(1).XValues = "='ExcelVBA'!$B$5:$B$10"
  .SeriesCollection(1).Values = "='ExcelVBA'!$C$5:$C$10"
  .SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlBoth, _
      Type:=xlCustom, Amount:="=ExcelVBA!$D$5:$D$10", MinusValues:= _
      "=ExcelVBA!$D$5:$D$10"
 End With
End Sub

Importantly, remember to modify the worksheet name and the range as needed.
Next, press F5 or click the Run button.

As a result, the desired outcome will display in a graph that depicts the uncertainty.

Conclusion

Following the procedures we covered, you will soon be able to graph uncertainty in Excel. The ExcelDemy Website has many similar articles. Keep utilizing them, and please share any more suggestions or better methods. Remember to share your thoughts, questions, and recommendations in the space provided.