Error Bars in Google Sheets

What Are Error Bars in Google Sheets?

Error bars in Google Sheets are used in charts to visually display any error that can be expected with that value. The addition of error bars significantly enhances your ability to analyse data and make informed decisions. Error bars are commonly used when working on scientific research, finance, and quality control. It is because in these fields it is important to understand the uncertainty and variability of data.

A student measures the height of a plant over three days as 8 cm, 11 cm and 15 cm. There is an error of ±1 cm each day. Here, the student uses a scatter chart and add vertical error bars with a constant value of 1. You can see how the uncertainty in each measurement can be seen visually, making the chart more accurate.

Types of Error Bars in Google Sheets

There are different types of error bars that can be added to a chart in Google Sheets.

Let us have a brief look at each one of them.

1. Standard Error Bars – These are the most used error bars based on the standard deviation of the data. They show the data’s variability about the mean.
2. Confidence Interval Bars =  These are like standard error bars but are based on the data’s confidence interval. You can see the range of values that the data may have taken for the given measurement error as well as the sample size.
3. Standard Deviation Bars – These bars show the variability in the data about the mean, just like standard bars. They are based on the actual standard deviation.
4. Custom Error Bars – These error bars allow users to specify their own values for the error bars, without using the built-in options. They are helpful in displaying specific types of data or for creating custom charts.

How To Add Error Bars in Google Sheets?

Adding error bars in Google Sheets is simple. To add error bars, you must go to the Customize tab of the Chart Editor. We will give you the step-by-step procedure. Below is some data based on the temperature of a city over a week.

Step 1: As a first step, let us highlight the data to add error bars. Open the spreadsheet to which you want to add the error bars. Then, select the data to create a chart.

Step 2: After selecting the data, click on the Insert in the menu bar and then click on Chart from the Insert menu. It will insert a chart based on the selected data.

Step 3: You get a Chart Editor on the right. Go to the Customise Tab in it and Click Series.

Step 4: Under Series, select the checkbox next to “Error bars” to enable the error bar in the chart.

Step 5: After enabling the Error bars, we choose the type of error bar and set its type and value. 3This option to set values will appear below the error bar checkbox after it is enabled.

The default error bar type is percentage. When you click on it, you will get a dropdown menu with four options: None, Percentage, Constant, and Standard deviation.

Step 6: Now you can see the chart with an error bar enabled in it.

Examples

Let us look at some simple examples on how to implement the error bars. We can show the to visually represent the error that can be expected

Example #1 – Add Vertical/Horizontal Error Bars in Google Sheets

Let us take an example of a scientist who is analysing his experimental data to show the variability of his measurements.  He conducted an experiment for recording the voltage and how it varied with time. He must now include error bars to visually represent the margin of error for each data point on the chart.

Step 1: Open a sheet to enter the data. Column A contains the input time intervals like 1 s, 2 s, 3 s and so on, while Column B contains the corresponding voltage values. Enter the error values, ±0.1 in this case, in Column C.

Step 2: Select the data in columns A and B and insert a chart by going to Insert → Chart. In the Chart Editor sidebar, choose “Scatter chart” as the chart type for a good visualization of data points.

Step 3: In the Chart Editor, click on the “Customize” tab, go to the “Series” section. Scroll down and check “Error bars.” One can select “Constant” to use a fixed value like 0.1.

The chart now shows vertical or horizontal error bars, giving a clear visual of data variability. This makes the chart more informative, especially in scientific or technical presentations.

Example #2

In a similar example, the students of a class display the uncertainty in their lab results using custom error bars.

Step 1: In a Google Sheet, enter the x-values, y-values, and their respective custom error values in three separate columns.

Step 2: Highlight the x and y columns. Go to Insert → Chart. In the Chart Editor, set the chart type to “Scatter chart” for data visualization.

Step 3: In the Chart Editor, go to the “Customize” tab and expand the “Series” section. Scroll down and check “Error bars,”

Currently, unlike Excel, Google does not support Custom error bars and we can only use constant error bars to approximate.

The chart will now display vertical error bars for each data point, clearly showing the uncertainty in each measurement.

Example #3 – Apply Percentage-Based Error Bars in Google Sheets

To apply percentage-based error bars in Google Sheets, we can consider an example, where we have some goals scored by a team across different tournaments. Let us add percentage-based error bars to them.

Step 1: Select the data for the chart. Go to Insert -> Chart. You get a chart with the Chart Editor opened on the right.

Step 2: Double-click on the chart you want to edit. In the editor, click on “Customize” and then “Series.”

Choose the specific series to which you want to add error bars.

Check the box next to “Error bars” to enable them.

Step 3: Under “Type,” select “Percent.”

Input the desired percentage value. This value will be applied to each data point in the selected series as the error range.

You can further customize the appearance of the error bars like colors within the “Error bars” section.

Important Things to Note

1. Error bars can only be added to specific chart types like scatter charts, line charts, and column charts. They won’t appear as an option for pie charts, area charts, or combo charts.
2. Only vertical error bars are supported natively. Google Sheets does not currently support horizontal error bars or asymmetric error ranges.
3. Google Sheets currently only supports symmetric error bars.
4. There is no native support for per-point custom error bars.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand Error Bars in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What is Error Bars in Google Sheets. We learn their types and how to add them to show margin of errors for a value with examples. You can learn more from the following articles. –

FORECAST.LINEAR in Google Sheets

Rotate Pie Chart in Google Sheets

COUPDAYSNC in Google Sheets