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
  • Modify the worksheet name and the range as needed.
  • Press F5 or click the Run button.

  • Here’s the result.

Output of Displaying Uncertainty in Chart Through Excel VBA

Read More: How to Add Custom Error Bars in Excel


Download the Practice Workbook


Related Articles


<< Go Back To Error Bars in Excel | Excel Chart Elements | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Lutfor Rahman Shimanto
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo