How to Add Custom Error Bars in Google Sheets

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

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.

In the Chart editor panel that appears on the right side of the screen, click the Setup tab, then check the box next to Switch rows / columns:

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:

Google Sheets custom error bars

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

Google Sheets: How to Sort Bars in Bart Chart
Google Sheets: How to Create a Clustered Stacked Bar Chart
Google Sheets: How to Create a Stacked Bar Chart

Leave a Reply

Your email address will not be published. Required fields are marked *