Table of Contents
To add custom error bars in Google Sheets, you can select the data series for which you want to add the error bars and then click on the “Insert” tab. From there, choose “Chart” and select the type of chart you want to use. Once the chart is inserted, click on the chart to open the “Chart editor” panel on the right side. In the “Customize” tab, click on “Series” and then select “Add error bars.” From there, you can customize the error bars by choosing the type (standard deviation, standard error, or custom) and the value. You can also choose to display the error bars for specific data points or the entire series.
Often you may want to add custom error bars to a bar graph in Google Sheets.
Fortunately this is easy to do and the following step-by-step example shows how to add the following custom error bars to a graph in Google Sheets:
Let’s jump in!
Step 1: Enter the Data
First, let’s enter the following dataset that shows the mean plant growth, standard deviation of plant growth and sample size of three different fertilizers that were applied to a field:
Step 2: Calculate the Half-Width of the Confidence Intervals
Next, we will calculate the half width of a confidence interval for a mean for each group, which we will later use for the length of the error bars in the graph.
To do so, type the following formula into cell E2:
=CONFIDENCE.T(0.05, C2, D2)
Then click and drag this formula down to the remaining cells in column E to calculate the half width of the confidence interval for each group:
Note: We chose to use an alpha value of .05 to calculate the half width for a 95% confidence interval.
You can find the complete documentation for the CONFIDENCE.T function in Google Sheets .
Step 3: Create Bar Chart with Custom Error Bars
Next, highlight the cell range B2:B4, then click the Insert tab along the top ribbon, then click Chart.
At this point, the bar chart should look like this:
The height of the bars represent the mean plant growth for each fertilizer.
Next, click the Customize tab in the Chart editor, then click the dropdown arrow next to Series, then choose Column1:
Then check the box next to Error bars, then click Constant under the Type dropdown menu, then type in 1.404, which represents the half width of the confidence interval that we calculated earlier for Fertilizer A:
Repeat this process for Fertilizer B and Fertilizer C.
The end result will be that each bar in the graph has its own custom error bar length that represents the 95% confidence interval for the mean:
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets: