Error bars are used in charts to visually show the error that can be expected with that value.
For example, if I plot the forecasted sales value for the next four quarters, I can use error bars to show the expected variability that may happen.
If you’re wondering how it looks like, below is an example where I have a chart that shows the forecasted sales quantity for some items. The error bars show a possible variation of +/- 10%.
You can create these on columns charts, line charts as well as area charts.
In this tutorial, I will show you how to add error bars in Google Sheets charts.
So let’s get started!
Adding Error Bars To a Column Chart in Excel
Suppose you have a dataset as shown below and you want to create a column chart and show error bars on that chart:
The first step is to insert a column chart using this data.
For the purpose of this tutorial, I will be using a column chart (and then show you how to add error bars to this column chart).
Below are the steps to insert a column chart in Google Sheets:
- Select the dataset (including the headers)
- Click the Insert tab
- Click on Chart option (you can also click on the chart icon in the toolbar)
- By default, it would insert a Pie chart. Double click on the chart to open the Chart editor pane (opens in the right side)
- Click on the Chart Type drop-down and select Column Chart
The above steps would insert a column chart that would look at something as shown below.
Now, in this column chart, we need to add error bars.
Below are the steps to add error bars to a chart in Google Sheets:
- Double-click on the chart to open the Chart Editor pane (on the right)
- In the Chart Editor, click on ‘Customize’
- Click on the Series option
- Scroll down and you’ll find the ‘Error bars’ option. Check the option.
- In the Type drop-down, select Percentage
- In the Value field, enter 10
The above steps will give you the error bars on all columns of the chart. where these are located at the top of the column bar.
In this example, we have specified the error bar to cover +/- 10% of each value in the column. These are known as percentage type error bars.
Let’s have a look at all the different types of error bars that are available in Google Sheets charts.
Types of Error Bars in Google Sheets
In Google Sheets, you can three types of error bars.
This is the type of error bars we have added in the example below.
In this type, you need to specify the percentage value to use when creating the error bars. For example, if you specify 10%, the error bar will show you the upper and lower range of the value (i.e., 90%-110% range of each value).
As the name suggests, this will show the error bars where the value is fixed.
For example, if I specify the value to be 50, then it will show the error bar range in the +/50 value for each column bar.
In the example below, the error bar shows that the variation can be +/- 50 for each column (no matter what how high/low the value is)
Standard Deviation error bars are plotted at the center of the chart (instead of the top of each column as compared to the other two).
You need to specify the standard deviation value and it will be plotted in the chart as shown below (here I have specified the standard deviation value as 0.1)
Note that there is another option – None, which would remove any existing error bar that you already have in the chart in Google Sheets.
Hope you found this tutorial useful!
You may also like the following Google Sheets tutorials:
1 thought on “How to Add Error Bars in Google Sheets (in a few seconds)”
Thx for this article
Formatting error bars is an issue, but there is an other issue (difference with MS Excel) : it’s impossible to customize error bars values with a range. STDEV is not enough…