How to Create Custom Error Bars in Google Sheets

You can use error bars in graphs to represent the confidence level for the data points. The wider the error bars, the less confidence a particular data point has associated with it. This can help to represent your data more accurately and show the variability of the data. By the end of this tutorial, you will know how to add error bars to your graph, customize your error bars, and use alternatives to represent your data in Google Sheets.

Steps to create custom error bars in Google Sheets

Follow these steps to create error bars in Google Sheets:

• Open your Google Sheet.

• Highlight your data.

• Go to Insert > Chart.

• Choose your chart.

• Go to Customize > Series.

• Click Error bars.

Now, let’s go further into each step.

1. Open Google Sheets.

The first step is to open the Google Sheets spreadsheet with your data in your web browser. If you don’t have data but would like to follow along with this guide, you can open a Google Sheets template or type in temporary values.

2. Organize your data.

In your sheet, organize your data into columns or rows. Next, choose the range of cells you want to include in your chart.

3. Navigate to the ‘Insert’ menu and click ‘Chart.’

Click on the Insert menu at the top and click the Chart option. The Chart editor will open on the right side of your screen.

4. Choose your chart within the ‘Chart editor.’

Within the Chart editor below the Setup submenu, you can choose the Chart type you prefer for your data. Click on the chart to select it.

5. Go to the ‘Customize’ tab in the ‘Series’ section.

In the Chart editor, navigate to the Customize tab. Under the Customize > Series section, click on the drop-down menu next to the series you want to add error bars to.

6. Add error bars.

Select the Error bars option in the Customize > Series section.

How to customize your error bar appearance

To customize your error bar appearance, choose the type of error bars you want to add from the available options: Standard deviation, Percent, or Constant. You can adjust the settings to match your data requirements.

For custom error bars, you can select a range of cells containing the error values. You can customize the appearance of the error bars by modifying options such as point size, point shape, and error bar color. Experiment with different settings until you achieve the desired visual effect.

Alternatives to Google Sheets custom error bars

If custom error bars don't meet your needs or if you're working with chart types that don't support error bars, consider these alternatives to show variation in your data set:

• Box-and-whisker plots: Consider using box-and-whisker plots to display the distribution and variability of your data. These plots provide a comprehensive overview of data ranges, quartiles, and outliers.

• Advanced statistical software: If the limitations in Google Sheets are too restrictive for your analysis, you may consider using specialized data visualization tools or statistical software that offer more advanced options for custom error bars, such as SAS or R.

How to do error calculations

When you want to understand the accuracy of forecasting results, calculating the error determines the difference between the value you predicted and the actual value seen in the results. Follow these steps to identify the error value:

1. Subtract the actual value from the value you predicted.

2. Divide that number by the actual value.

3. Multiply the resulting value by 100.

For example:

1. 500 (predicted value) - 150 (actual value) = 350

2. 350/500 = 0.7

3. 0.7 x 100 = 70 percent

Learn more about Google Sheets on Coursera

Build your data analytics skills on Coursera with top-rated courses from universities and industry leaders. Consider the Google Data Analytics Professional Certificate to learn data organization and management skills like debugging, cloud computing, Google Sheets, and more.