How to Graph Uncertainty in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

how to graph uncertainty in excel


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.

Utilize Error Bar Option to Plot Uncertainty in Excel

  • 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.

Utilize Error Bar Option to Plot Uncertainty in Excel

  • 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.

Output of Utilizing Error Bar Option to Plot Uncertainty in Excel

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.

Display Uncertainty in Chart Through Excel VBA

  • 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.

Output of Displaying Uncertainty in Chart Through Excel VBA

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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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