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 the charts. Therefore, you should review these 2 easy ways to graph uncertainty in Excel.
How to Graph Uncertainty in Excel: 2 Easy Ways
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 article with the help of Microsoft Excel 365. You are free to choose whatever version better suits your needs.
1. Utilize Error Bar Option to Plot Uncertainty in Excel
Excel’s error bars show graphically the variation in the data they contain. Error Bars are a helpful way to highlight estimated errors or uncertainty and provide a better understanding of the accuracy of a measurement. In this discussion, we will use the Error Bar Option to construct an illustrating graph uncertainty. Please follow these directions to utilize error bars to plot uncertainty.
📌 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 right column 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.
Read More: How to Add Error Bars in Excel
2. Display Uncertainty in Chart Through Excel VBA
VBA stands for Visual Basic for Application and is abbreviated simply as VBA. In this part of the tutorial, we will use the Visual Basic for Applications (VBA) programming language to graph uncertainty in Excel. Please follow the instructions below.
📌 Steps:
- First, choose the intended sheet as an active sheet.
- Next, 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.
Read More: How to Add Custom Error Bars in Excel
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.
Conclusion
Following the procedures we covered, you will soon be able to graph uncertainty in Excel. Keep utilizing them, and please share any more suggestions or better methods. Remember to share your thoughts, questions, and recommendations in the space provided.
Related Articles
- How to Add Individual Error Bars in Excel
- How to Add Vertical Error Bars in Excel
- How to Add Horizontal Error Bars in Excel
- How to Add Standard Deviation Error Bars in Excel
- How to Remove Horizontal Error Bars in Excel
<< Go Back To Error Bars in Excel | Excel Chart Elements | Excel Charts | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!