How to Graph Uncertainty in Excel (2 Easy Ways)

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. The graph will represent the degree of uncertainty associated with the Profit column.

how to graph uncertainty in excel

Method 1 – Utilize the Error Bar Option to Plot Uncertainty in Excel

Steps:

Choose the working sheet as the Active Sheet.
Select the B5:C10 range.

Utilize Error Bar Option to Plot Uncertainty in Excel

Navigate to the Insert tab.
From the Charts group, click the Bubble Chart icon.
Select the Scatter icon from the Scatter section.

A graph window will open that plots the values of our selected cells.
The Scatter Chart plots the left column along the X-Axis and the right column on the Y-Axis.

Click anywhere in the Chart, then click on the plus icon on the top-right. The Chart Elements bar will open.
Choose the Error Bars option.
Pick More Options.

Utilize Error Bar Option to Plot Uncertainty in Excel

The Format Error Bars pane will appear.
From the Vertical Error Bar section, check Both and Cap as the Direction and the End Style, respectively.
Check Custom from the Error Amount, then go to Specify Value.

The window for the Custom Error Bars will open.
Select the sheet name followed by an Exclamation mark and the range in the Positive Error Value box.
Input the same range in the Negative Error Value box.
Hit OK.

Here’s the result.

Output of Utilizing Error Bar Option to Plot Uncertainty in Excel

Read More: How to Add Error Bars in Excel

Method 2 – Display Uncertainty in a Chart Through Excel VBA

Steps:

Choose the intended sheet as an active sheet.
Navigate to the Developer tab and click on Visual Basic.

Display Uncertainty in Chart Through Excel VBA

Click the Insert option and select Module.

An empty Module Box will appear.
Insert 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