Error Bars in Excel

MS Excel or Microsoft Excel is powerful spreadsheet software and enables users to record data as well as present it graphically. It includes various built-in options that help present the data graphically in different ways. The chart is one such option using which we can display our data in a graphical view, which increases the readability of the data. There is a wide range of existing charts in Excel to display different sets of data in different styles or views. When using charts, Excel allows us to associate error bars with certain types of charts, such as line charts, bar charts, and scatter charts. We can present a more comprehensive view of the desired data set with error bars, allowing users to see margins or errors in the data.

This tutorial discusses the detailed description of Error Bars in Excel, including different types or use-cases. The tutorial also discusses relevant examples to help us better understand each type of Excel error bars.

What are Error Bars in Excel?

In Excel, Error Bars refer to built-in tools that help represent data variability and measurement accuracy with graphics. These error bars mainly help display how far the actual values are from the reported values. We can insert these errors bars in the two-dimensional bar, line, column, area, bubble charts and XY (scatter) plots. Among these graphical representations, the error bars can be displayed both vertically and horizontally in the bubble charts and scatter plots. Moreover, the error bars can display plus, minus, or both directions with Cap and without Cap style of bars.

Using error bars, we can represent a more comprehensive view of the desired data sets, allowing others to easily read or understand the margins or errors in the data.

There are typically three types of error bars in Excel:

Error Bars with Standard Error

This type of error bar helps to represent the standard error of the mean for all values. The standard error makes it easy to demonstrate how far the sample mean is likely to exist from the population.

Error Bars with Percentage

This error bar type helps to represent error bars of 5% value to our existing graph. The error bars of 5% is added by default. However, we can change the percentage value as per our choice. We can manually change the percentage by going to the ‘More Options’ section.

Error Bars with Standard Deviation

This type of error bar helps represent the amount of variability of the data, meaning how much the data deviates as a whole. The bars are graphed with one standard deviation for all data points in Excel. We can use Excel’s STDEV command to find the standard deviation in excel.

Apart from the above options, we also get ‘More Options’ that helps us access preferences for accordingly specifying the desired error bar values and creating custom error bars. In the following image, we can see all the above-discussed types of error bars:

How to add Error Bars in Excel?

Adding error bars in Excel is simple and easy. As discussed above, the error bars are available for certain charts, including the line chart, bar chart, and scatter chart or plot. However, the steps for adding error bars in corresponding charts are somewhat similar. They are as follows:

• First, we must select the Excel data or table.
• Next, we need to insert one of the supported charts with the selected data.
• After that, we must click or select the drawn graph, go to the Layout tab and select the Error Bars.
• After clicking the Error Bars on the ribbon, it displays a list of items to help us select and insert the specific type of the error bars. To customize the error bars further, we can click the More Options from the same menu list and customize the preferences accordingly.

Let us understand each type of error bars with the help of examples:

Example 1: Error Bars with Standard Error

The standard error refers to a statistical term used to measure the accuracy of a given data set. Standard error bars usually represent the exact standard deviation. In the context of statistics, a sample mean is a deviation from the true mean of the population, and that is why this deviation is usually called the standard error.

For example, consider the following excel sheet with some random month-wise sales data. The sheet displays actual and forecast sales data, and we need to add error bars with the relevant chart.

We must follow the below steps to insert excel error bars for our example data set:

• First, we must select the range from cell A1 to cell C13, including the month and their respective actual and forecast data.
  
• Next, we need to go to the Insert tab and select the Line Chart. We must click the ‘Line with Markers‘ from the window, as shown below:
  
• After selecting the chart, our selected data will be immediately plotted with the respective data. It will look like this:
  
• We must click and select the forecast bar or line in our chart. After selecting the forecast line/bar, we will see three new tabs on Excel’s ribbon. We must select the Layout tab and click the Error Bars
  
• As soon as we click the Error Bars option, Excel displays additional options related to error bars. We must click the second option named ‘Error Bar with Standard Error‘, as shown below:
  
• After clicking the standard error bar option, the plotted graph gets changed slightly. We can notice the changes in the following image with the standard error bar:
  

The chart above represents a statistical fluctuation measure of the actual and forecast sales data. Similarly, we can add or insert the error bars in other supported charts. After the error bars are added to the plotted chart, we can later adjust the formatting accordingly.

Example 2: Error Bars with Percentage

We learned to add the error bars with standard errors in the above example. Now, let us understand another type of error bars. This example discusses the steps to add error bars with a percentage in the graphical chart.

For example, consider the following excel sheet where we have some random test data. We need to draw a graphical chart for the data by calculating the average and adding error bars with the relevant chart.

We must follow the below steps to insert excel error bars for our example data set:

• First, we need to calculate the average for recorded test data. We need to apply the average formula =AVERAGE(B2:F2) on cell G2 and copy it to the below cells of the resultant average column. This will calculate the averages for our example data, as shown below:
  
• After getting the calculation results, we need to select the Concentration column data and the calculated Averages data by holding the Ctrl
  
• Next, we need to go to the Insert tab and select the Scatter option from the Charts In the next window, we must click the ‘Scatter with only Markers‘, which will help us create a respective chart like this:
  
• After that, we must click on any blue dot from the chart area to access new tabs on the ribbon. The blue dots create a graphical view of the average trails.
  
• We must click on the Layout tab on the ribbon, go to Error Bars, and select the option named ‘Error Bars with Percentage‘.
  
• As soon as we select the error bars to add, the graph changes like the below image:
  

The above image shows the changes representing the Error Bars with percentage measurements. The bars reflect the error amount by the five percentages. It is the default percentage amount in Excel. However, we can change the Error Bars percentage amount as desired by adjusting the formatting via the ‘More Error Bars Options‘.

Example 3: Error Bars with Standard Deviation

In this example, we discuss and add another essential type of error bars. Let us consider the same example where we have some random test data.

Since we will add Error Bars with Standard Deviation for the above data, we must first calculate the Standard Deviation. After that, we need to draw a graphical chart for the data based on the calculated standard deviation and add error bars with the relevant chart.

We must follow the below steps to insert excel error bars for our example data set:

• First, we need to apply the formula =STDEVA(B2:F2) on cell G2 and copy it to the below cells of the corresponding column. This will calculate the standard deviation for our example data, as shown below:
  
• After getting the standard deviation results for the respective data, we must select the Concentration column and the calculated standard deviation data by holding the Ctrl
  
• Next, we must navigate the Insert tab and select the Column option from the Charts In the next window, we must click the ‘Clustered Column‘ under the 2-D Column, which will help us create a respective chart like this:
  
• After that, we must click on any blue colour bar from the chart area to get new tabs on the ribbon. We must select the Layout tab, click the Error Bars and choose ‘Error Bars with Standard Deviation‘.
  
• As soon as we select the desired error bars to add, the existing graph changes like the below image:
  

The above image shows the changes representing the Error Bars for the standard deviation measurements. In our example, the error bars displays a standard deviation of 0.5, 0.1, 0.2, 0.4 and 1.0. We can change the colours of these added error bars by adjusting the formatting, which will help us to highlight them.

Creating Custom Error Bars in Excel

Apart from Excel’s existing error bars, such as the error bars with standard error, error bars with percentage and error bars with standard deviation, we can also use the custom error bars. Although error bars with standard error fulfil our needs in most cases, we can try creating custom error bars to display specific data series.

We can perform the following steps to add the custom error bars in our Excel sheet:

• First, we need to insert one of the supported charts in our Excel sheet. For example purposes, we can create a small data series and create a respective chart like Clustered Column.
• Next, we must select one of the chart elements, i.e., data series, which will enable new tabs on Excel’s ribbon. After that, we need to navigate the Layout tab, access the ‘Error Bars’ tool and select ‘More Error Bars Options‘. This will launch the ‘Format Error Bars’ window.
  
• We navigate the Vertical Error Bars and select the Custom under the Error Amount section in the next window. We must click the ‘Specify Value‘ button.
  
• After clicking the Specify Value, we will get a small ‘Custom Error Bars‘ window where we have to specify the positive and negative error values. We can enter any desired values in the corresponding boxes without equal signs or brackets. Also, we can select the data from the sheet. After supplying the data, we must click the OK button, and the corresponding error bars will be added to the selected chart.
  

If we do not need to display positive or negative custom error bars, we must enter the values as zero in the respective box under the ‘Custom Error Bars’ window. We must ensure that none of the boxes is blank or empty; otherwise, Excel automatically retains the last used values, thinking we forgot to specify the value.

Formatting Error Bars in Excel

Excel allows us to add/ insert the error bars and modify the formatting as per our choice. We can access the Format Error Bars window by going to Layout > Error Bars > More Error Bars Options. However, we must first select the object/element from the plotted chart to get the Layout tab on the ribbon.

In addition to this, we can also press the right-click on any added Error Bars in the chart area and select the ‘Format Error Bars‘ option from the list.

Another option to access the Format Error Bars window is to double-click on any existing error bars from the chart area.

Once the ‘Format Error Bars’ window is displayed, we can go through the different categories and adjust the preferences accordingly. The window allows us to modify preferences for the error bars, such as the type, direction, style, colour, transparency, width, Cap, arrow type, percentage amount, etc.

Deleting Error Bars in Excel

When we need to delete the error bars from our worksheet, we don’t need to use any tool or option from Excel. It is easy to delete the error bars using the mouse and keyboard. It is just two steps process, as listed below:

• Select the error bar to delete.
• Press the Delete key on the keyboard.

The above steps delete all the error bars for the selected data series. If we have both the vertical and horizontal error bars in the sheet, we must delete them separately. However, the process to delete the error bars is the same.

Note: We can also delete the existing error bars by selecting them and going to Layout > Error Bars > None. This also helps remove or delete the Error Bars for the selected series of data or all the existing Error Bars if none are selected.

Important Point to Remember

• In Excel, the error bars can only be added for charts formats.
• The error bars typically follow the quantitative scale axis and run parallel to it. It implies that the error bars can be added either vertically or horizontally based on the position of the quantitative scale, be it X-Axis or the Y-Axis.
• It is essential to note that Excel supports only vertical error bars for most existing charts. However, we can add horizontal error bars using the bar charts, bubble charts, and XY scatter plots.
• The uncertainty of data points is revealed or calculated by the lengths of the corresponding error bars.
• If the supplied series has skewed data, the length of created error bars may be unbalanced on both sides of its axis.
• When adding error bars in Excel, we must use the full axis, which means the numeric values will start at zero.