How to Add Error Bars in Excel

Data often involves variability, making exact predictions impossible — like forecasting temperature or stock prices. Final values may vary slightly higher or lower.

You can use error bars in Excel charts to represent data with variability.

What are error bars?

Error bars are visual representations in charts that display the degree of variability, uncertainty, or error associated with a data point or measurement. They provide a graphical indication of the possible range within which the actual value is expected to fall, helping to assess the accuracy and reliability of the data.

Types of error bars

Excel offers different types of error bars. Let’s explore each one individually (with more details to follow).

1. Standard Error Error Bar
   This represents the 'standard error of the mean' for all values, indicating how far the sample mean is likely to deviate from the true population mean.

2. Percentage Error Bar
   A Percentage Error Bar represents variability as a fixed percentage of the data point's value. It shows how much the value may vary, higher or lower, based on the specified percentage.
   
   
   For example, in the chart above, a 10% percentage error bar means that for a data point value of 100, the error bar will range from 90 to 110.
3. Standard Deviation Error Bar
   A Standard Deviation Error Bar represents the spread of data around the mean. It shows how much individual data points typically deviate from the average, indicating variability within the dataset.
4. Fixed Value Error Bar
   A Fixed Value Error Bar represents variability using a constant value. It adds or subtracts the same specified amount to each data point, showing a uniform range of potential variation.
5. Custom Error Bar
   A Custom Error Bar allows you to define specific error values for each data point. You can set these values manually or use data from a range to represent variability tailored to your dataset.

How to add or remove error bars in Excel

In Excel, you can add error bars to 2-D line, bar, column, or area charts, as well as XY scatter and bubble charts. Additionally, error bars can also be applied to stock charts, making them versatile for representing variability across different chart types.

Let’s create a column chart using the temperature dataset.

Step 1:

Select any cell in the dataset, go to the Insert tab, and in the Charts group, click on the Insert Column or Bar Chart option.

Step 2:

Click anywhere on your chart to display the three icons shown below.

Step 3:

Click on the plus icon, also known as the Chart Elements icon.

Step 4:

Hover over the Error Bars option and click the black triangle icon that appears on its right.

Step 5:

Select one of the three options (Standard Error, Percentage, or Standard Deviation), or click More Options for additional choices. In this example, the Standard Error option is selected.

The steps above will add the standard error bars to all six columns in the chart.

Adding error bars in Excel: Windows vs. Mac

The process of adding error bars in Excel differs slightly between Windows and Mac due to variations in interface design and menu navigation.

Key differences

• Interface layout: Windows has a prominent Chart Elements (+) icon for quick access, whereas Mac relies more on the ribbon menu.
• Format Pane placement: In Windows, the format pane appears on the right; in Mac, it may integrate into the Format Sidebar.
• Terminology and tools: While functionality is similar, the placement of tools may differ slightly between platforms, requiring users to adapt to the layout. 

Both versions allow for detailed customization, but Mac users may need to rely more on the ribbon and sidebars, whereas Windows offers direct chart interaction through the Chart Elements icon.

Excel in Windows:

1. Select Chart: Click on the chart where you want to add error bars.
2. Chart Elements icon: Use the green Chart Elements (+) icon that appears near the chart. Check the Error Bars box.
3. Customize Error Bars: Hover over the Error Bars option and click the black triangle to expand options (Standard Error, Percentage, Standard Deviation, or More Options).
4. More Options: Selecting More Options opens a detailed formatting pane on the right, allowing you to customize the error bars further (e.g., custom values, direction, and end styles).
5. Alternative Method: Use the Chart Tools ribbon, go to the Layout tab, and click Error Bars in the Analysis group.

Excel on Mac:

1. Select Chart: Click the chart to enable editing.
2. Chart Design tab: Navigate to the Chart Design tab in the ribbon, then click Add Chart Element and select Error Bars from the dropdown menu.
3. Customize Error Bars: Similar to Windows, you can choose from Standard Error, Percentage, Standard Deviation, or access More Options for detailed customization.
4. Formatting Pane: Error bar settings on Mac may appear in a smaller Format Error Bars pane or directly in the Format sidebar, depending on your version.

Adding custom error bars in Excel

Custom error bars in Excel allow you to define specific error values for each data point, giving you flexibility in representing variability or uncertainty. Here's a step-by-step guide:

Step 1: Create your chart

1. Prepare your data: Ensure your dataset includes values for the chart and custom error margins. (Download the exercise file.)
2. Insert a Chart:

• Select your data.
• Go to the Insert tab.
• Choose a suitable chart type (e.g., column, line, or scatter chart).

Step 2: Add error bars

1. Click anywhere on the chart to activate it.
2. If using Windows:

• Click the green Chart Elements (+) icon near the chart.
• Check the Error Bars box.
• Click the black triangle next to Error Bars and choose More Options.

3. If using Mac:

• Go to the Chart Design tab in the ribbon.
• Click Add Chart Element > Error Bars > More Options.

Step 3: Customize error bars

1. Open the Format Error Bars pane:

• Click on any error bar in the chart.
• The Format Error Bars pane or sidebar will open.

2. Choose Custom Error Bars:

• In the pane, find the Error Amount section.
• Select Custom and click the Specify Value button.

3. Input Custom Values:

• A dialog box appears with two fields: Positive Error Value and Negative Error Value.
• Enter the cell range for your custom error values (e.g., C2:C7 for error margins in the example).
• Click OK to apply.

Custom error bars provide detailed insights into data variability, making your charts more informative and precise.

How to insert horizontal error bars

So far, we've focused on vertical error bars, which are the most commonly used in Excel charts (applicable to column charts, line charts, area charts, and scatter charts). However, horizontal error bars are also available and can be used with bar charts and scatter charts.

Here’s an example where quarterly data is plotted in a bar chart with horizontal error bars.

The process of adding horizontal error bars is identical to that of vertical error bars discussed earlier.

How to put error bars for a specific data series

Excel allows you to tailor error bars to highlight variability in a specific part of your data.

Let’s illustrate this with a Combo Chart example. Here, sales values for 2022 are displayed as columns, while sales values for 2023 are shown as a line chart, with error bars applied only to the line chart. (Use Products_Brands_Sales Excel File)

Here are the steps to add error bars to a specific series:

1. Select the data series where you want to apply error bars.
2. Click the plus icon (Chart Elements).
3. Click the black triangle next to the Error Bars option.
4. Select the type of error bars you want to add.

If you like, you can also add error bars to all series in the chart by repeating these steps, starting with selecting the specific series in the first step.

Formatting error bars

You can customize error bars by adjusting their color, thickness, and shape. To format an error bar, right-click on any bar and select **Format Error Bars**. This will open the **Format Error Bars** pane on the right side of the screen.

Here are the elements you can customize or modify in an error bar:

You can adjust the color and width of the error bar by selecting the Fill & Line option and modifying the color and/or width settings.

You can display error bars on both sides of a data point (positive and negative) or choose to show only the positive or negative error bars. These settings can be adjusted using the Direction option in the Format Error Bars pane.

Conclusion

Error bars are a powerful tool for representing variability or uncertainty in your data. They can be easily added and customized to enhance the clarity and accuracy of your charts. Whether you’re working with column, line, scatter, or combo charts, Excel provides flexible options for adding and formatting error bars to suit your needs.

Free lesson from Excel Charts and Data Visualization by Deb Ashby

To deepen your knowledge, explore these helpful resources:

• Excel Charts: The Complete Guide 
• Advanced Excel Charts Techniques

For a more structured approach, consider enrolling in the Excel Charts and Data Visualization Course to master chart creation and visualization techniques.

Ready to become a certified Excel ninja?

Start learning for free with GoSkills courses

Start free trial