Error bars and standard deviation excel



Add, change, or remove error bars in a chart

Error bars in charts you create can help you see margins of error and standard deviations at a glance. They can be shown on all data points or data markers in a data series as a standard error amount, a percentage, or a standard deviation. You can set your own values to display the exact error amounts you want. For example, you can show a 10 percent positive and negative error amount in the results of a scientific experiment like this:

You can use error bars in 2-D area, bar, column, line, xy (scatter), and bubble charts. In scatter and bubble charts, you can show error bars for x and y values.

Note: The following procedures apply to Office 2013 and newer versions. Looking for Office 2010 steps?

Add or remove error bars

Click anywhere in the chart.

Click the Chart Elements button next to the chart, and then check the Error Bars box. (Clear the box to remove error bars.)

To change the error amount shown, click the arrow next to Error Bars, and then pick an option.

Pick a predefined error bar option like Standard Error, Percentage or Standard Deviation.

Pick More Options to set your own error bar amounts, and then under Vertical Error Bar or Horizontal Error Bar, choose the options you want. This is also where you can change the direction, end style of the error bars, or create custom error bars.

Note: The direction of the error bars depends on the type of chart you’re using. Scatter charts can show both horizontal and vertical error bars. You can remove either of these error bars by selecting them, and then pressing Delete.

Review equations for calculating error amounts

People often ask how Excel calculates error amounts. Excel uses the following equations to calculate the Standard Error and Standard Deviation amounts that are shown on the chart.

Uses this equation

s = series number

i = point number in series s

m = number of series for point y in chart

n = number of points in each series

yis = data value of series s and the ith point

ny = total number of data values in all series

s = series number

i = point number in series s

m = number of series for point y in chart

n = number of points in each series

yis = data value of series s and the ith point

ny = total number of data values in all series

M = arithmetic mean

Add, change, or remove errors bars in a chart in Office 2010

In Excel, you can display error bars that use a standard error amount, a percentage of the value (5%), or a standard deviation.

Standard Error and Standard Deviation use the following equations to calculate the error amounts that are shown on the chart.

Uses this equation

s = series number

i = point number in series s

m = number of series for point y in chart

n = number of points in each series

yis = data value of series s and the ith point

ny = total number of data values in all series

s = series number

i = point number in series s

m = number of series for point y in chart

n = number of points in each series

yis = data value of series s and the ith point

ny = total number of data values in all series

M = arithmetic mean

On 2-D area, bar, column, line, xy (scatter), or bubble chart, do one of the following:

To add error bars to all data series in the chart, click the chart area.

To add error bars to a selected data point or data series, click the data point or data series that you want, or do the following to select it from a list of chart elements:

Click anywhere in the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the chart element that you want.

On the Layout tab, in the Analysis group, click Error Bars.

Do one of the following:

Click a predefined error bar option, such as Error Bars with Standard Error, Error Bars with Percentage, or Error Bars with Standard Deviation.

Click More Error Bar Options, and then under Vertical Error Bars or Horizontal Error Bars, click the display and error amount options that you want to use.

Note: The direction of the error bars depends on the chart type of your chart. For scatter charts, both horizontal and vertical error bars are displayed by default. You can remove either of these error bars by selecting them, and then pressing DELETE.

On a 2-D area, bar, column, line, xy (scatter), or bubble chart, click the error bars, the data point, or the data series that has the error bars that you want to change, or do the following to select them from a list of chart elements:

Click anywhere in the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the chart element that you want.

Читайте также:  Error handler mysql php

On the Layout tab, in the Analysis group, click Error Bars, and then click More Error Bar Options.

Under Display, click the error bar direction and end style that you want to use.

On a 2-D area, bar, column, line, xy (scatter), or bubble chart, click the error bars, the data point, or the data series that has the error bars that you want to change, or do the following to select them from a list of chart elements:

Click anywhere in the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the chart element that you want.

On the Layout tab, in the Analysis group, click Error Bars, and then click More Error Bar Options.

Under Error Amount, do one or more of the following:

To use a different method to determine the error amount, click the method that you want to use, and then specify the error amount.

To use custom values to determine the error amount, click Custom, and then do the following:

Click Specify Value.

In the Positive Error Value and Negative Error Value boxes, specify the worksheet range that you want to use as error amount values, or type the values that you want to use, separated by commas. For example, type 0.4, 0.3, 0.8.

Tip: To specify the worksheet range, you can click the Collapse Dialog button , and then select the data that you want to use in the worksheet. Click the Collapse Dialog button again to return to the dialog box.

Note: In Microsoft Office Word 2007 or Microsoft Office PowerPoint 2007, the Custom Error Bars dialog box may not show the Collapse Dialog button, and you can only type the error amount values that you want to use.

On a 2-D area, bar, column, line, xy (scatter), or bubble chart, click the error bars, the data point, or the data series that has the error bars that you want to remove, or do the following to select them from a list of chart elements:

Click anywhere in the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the chart element that you want.

Do one of the following:

On the Layout tab, in the Analysis group, click Error Bars, and then click None.

Tip: You can remove error bars immediately after you add them to the chart by clicking Undo on the Quick Access Toolbar or by pressing CTRL+Z.

Do any of the following:

Express errors as a percentage, standard deviation, or standard error

In the chart, select the data series that you want to add error bars to.

For example, in a line chart, click one of the lines in the chart, and all the data marker of that data series become selected.

On the Chart Design tab, click Add Chart Element.

Point to Error Bars, and then do one of the following:

Apply the standard error, using the following formula:

s = series number
I = point number in series s
m = number of series for point y in chart
n = number of points in each series
y is = data value of series s and the I th point
n y = total number of data values in all series

Apply a percentage of the value for each data point in the data series

Apply a multiple of the standard deviation, using the following formula:

s = series number
I = point number in series s
m = number of series for point y in chart
n = number of points in each series
y is = data value of series s and the I th point
n y = total number of data values in all series
M = arithmetic mean

Express errors as custom values

In the chart, select the data series that you want to add error bars to.

On the Chart Design tab, click Add Chart Element, and then click More Error Bars Options.

In the Format Error Bars pane, on the Error Bar Options tab, under Error Amount, click Custom, and then click Specify Value.

Under Error amount, click Custom, and then click Specify Value.

In the Positive Error Value and Negative Error Value boxes, type the values that you want for each data point, separated by commas (for example, 0.4, 0.3, 0.8), and then click OK.

Note: You can also define error values as a range of cells from the same Excel workbook. To select the range of cells, in the Custom Error Bars dialog box, clear the contents of the Positive Error Value or Negative Error Value box, and then select the range of cells that you want to use.

Add up/down bars

In the chart, select the data series that you want to add up/down bars to.

On the Chart Design tab, click Add Chart Element, point to Up/Down Bars, and then click Up/down Bars.

Depending on the chart type, some options may not be available.

Источник

How to add error bars in Excel: standard and custom

by Svetlana Cheusheva, updated on October 4, 2022

Читайте также:  Exception file что это такое

The tutorial shows how to make and use error bars in Excel. You will learn how to quickly insert standard error bars, create your own ones, and even make error bars of different size that show your own calculated standard deviation for each individual data point.

Many of us are uncomfortable with uncertainty because it is often associated with lack of data, ineffective methods or wrong research approach. In truth, uncertainty is not a bad thing. In business, it prepares your company for the future. In medicine, it generates innovations and leads to technological breakthroughs. In science, uncertainty is the beginning of an investigation. And because scientists love quantifying things, they found a way to quantify uncertainty. For this, they calculate confidence intervals, or margins of error, and display them by using what is known as error bars.

Error bars in Excel

Error bars in Excel charts are a useful tool to represent data variability and measurement accuracy. In other words, error bars can show you how far from the reported values the actual values might be.

In Microsoft Excel, error bars can be inserted in 2-D bar, column, line and area graph, XY (scatter) plot, and bubble chart. In scatter plots and bubble charts, both vertical and horizontal error bars can be displayed.

You can put error bars as a standard error, percentage, fixed value, or standard deviation. You can also set your own error amount and even supply an individual value for each error bar.

How to add error bars in Excel

In Excel 2013 and higher, inserting error bars is quick and straightforward:

  1. Click anywhere in your graph.
  2. Click the Chart Elements button to the right of the chart.
  3. Click the arrow next to Error Bars and pick the desired option:
    • Standard Error — displays the standard error of the mean for all values, which shows how far the sample mean is likely to be from the population mean.
    • Percentage — adds error bars with the default 5% value, but you can set your own percentage by choosing More Options.
    • Standard Deviation — shows the amount of variability of the data, i.e. how close it is to the average. By default, the bars are graphed with 1 standard deviation for all data points.
    • More Options… — allows specifying your own error bar amounts and creating custom error bars.

Picking More Options opens the Format Error Bars pane where you can:

  • Set your own amounts for fixed value, percentage and standard deviation error bars.
  • Choose the direction (positive, negative, or both) and end style (cap, no cap).
  • Make custom error bars based on your own values.
  • Change the appearance of error bars.

As an example, let’s add 10 % error bars to our chart. For this, select Percentage and type 10 in the entry box:

  • To add standard error bars in Excel, you can simply select the Error Bars box without picking any option. The standard error bars will be inserted by default.
  • To customize the existing error bars, double-click them in the chart. This will open the Format Error Bars pane, where and you change error bars type, choose another color and do other customizations.

How to do error bars in Excel 2010 and 2007

In earlier versions of Excel, the path to error bars is different. To add error bars in Excel 2010 and 2007, this is what you need to do:

  1. Click anywhere in the chart to activate Chart Tools on the ribbon.
  2. On the Layout tab, in the Analysis group, click Error Bars and choose one of the following options:

How to add custom error bars in Excel

The standard error bars provided by Excel work fine in most situations. But if you wish to display your own error bars, you can easily do that too.

To make custom error bars in Excel, carry out these steps:

  1. Click the Chart Elements button.
  2. Click the arrow next to Error Bars and then click More Options…
  3. On the Format Error Bars pane, switch to the Error Bars Options tab (the last one). Under Error Amount, select Custom and click the Specify Value button.

A small Custom Error Bars dialog box appears with two fields, each containing one array element like = <1>. You can now enter your own values in the boxes (without equality sign or curly braces; Excel will add them automatically) and click OK.

If you do not want to display positive or negative error bars, enter zero (0) in the corresponding box, but don’t completely clear the box. If you do that, Excel will think you simply forgot to input a number and it will retain the previous values in both boxes.

This method adds the same constant error values (positive and/or negative) to all data points in a series. But in many cases, you will want to put an individual error bar to each data point, and the following example shows how to do this.

How to make individual error bars in Excel (of different lengths)

When using any of the inbuild error bars options (standard error, percentage or standard deviation), Excel applies one value to all data points. But in some situations, you may want to have your own calculated error values on individual points. In other words, you wish to plot error bars of different lengths to reflect different errors for each data point on the graph.

In this example, I’ll show you how to make individual standard deviation error bars.

To begin with, enter all the error bar values (or formulas) into separate cells, usually in the same columns or rows as the original values. And then, tell Excel to graph error bars based on those values.

Tip. Optionally, you can fill two separate rows/columns with your error values — one for positive and the other for negative.

Supposing, you have 3 columns with sales numbers. You have calculated an average (B6:D6) for each column and plotted those averages in a chart. Additionally, you found the standard deviation for each column (B7:D7) by using the STDEV.P function. And now you wish to display those numbers in your graph as standard deviation error bars. Here’s how:

  1. Click the Chart Elements button> >Error Bars >More Options….
  2. On the Format Error Bars pane, select Custom and click the Specify Value button.
  3. In the Custom Error Bars dialog box, delete the contents of the Positive Error Value box, put the mouse pointer in the box (or click the Collapse Dialog icon next to it), and select a range in your worksheet (B7:D7 in our case).
  4. Do the same for Negative Error Value box. If you do not want to display negative error bars, type 0.
  5. Click OK.

Important note! Be sure to delete the entire contents of the entry boxes before selecting a range. Otherwise, the range will be added to the existing array like shown below, and you will end up with an error message:

It is quite difficult to spot this error because the boxes are narrow, and you cannot see all the contents.

If all done correctly, you will get individual error bars, proportional to the standard deviation values that you’ve calculated:

How to add horizontal error bars in Excel

For most chart types, only vertical error bars are available. Horizontal error bars can be added to bar charts, XY scatter plots, and bubble charts.

For bar charts (please do not confuse with column charts), horizontal error bars are the default and only available type. The screenshot below shows an example of a bar chart with error bars in Excel:

In bubble and scatter graphs, error bars are inserted for both x values (horizontal) and y values (vertical).

If you’d like to only insert horizontal error bars, simply remove vertical error bars from your chart. Here’s how:

  1. Add error bars to your chart as usual.
  2. Right-click any vertical error bar and choose Delete from the pop-up menu.

This will remove vertical error bars from all data points. You can now open the Format Error Bars pane (for this, double-click on any of the remaining error bars) and customize the horizontal error bars to your liking.

How to make error bars for a specific data series

Sometimes, adding error bars to all data series in a chart could make it look cluttered and messy. For example, in a combo chart, it often makes sense to put error bars to only one series. This can be done with the following steps:

  1. In your chart, select the data series to which you want to add error bars.
  2. Click the Chart Elements button.
  3. Click the arrow next to Error Bars and pick the desired type. Done!

The screenshot below shows how to do errors bars for the data series represented by a line:

As the result, the standard error bars are inserted only for the Estimated data series that we selected:

How to modify error bars in Excel

To change the type or appearance of the existing error bars, perform these steps:

  1. Open the Format Error Bars pane by doing one of the following:
    • Click the Chart Elements button >Error Bars >More Options…
    • Right-click error bars and select Format Error Bars from the context menu.
    • Double-click the error bars in your chart.
  2. To change type, direction and end style of the error bars, switch to the Options tab (the last one).
  3. To change the color, transparency, width, cap, join and arrow type, go to the Fill & Line tab (the first one).

How to delete error bars in Excel

To remove all error bars from your graph, click anywhere within the chart, then click the Chart Elements button and clear the Error Bars check box. The shortest instruction ever 🙂

To delete error bars for a specific data series, click on that data series to select it, then click the Chart Elements button and uncheck the Error Bars box.

If a data series has both vertical and horizontal error bars and you wish to delete the «extras», right-click the superfluous bars, and choose Delete from the context menu.

That’s how you do error bars in Excel. I thank you for reading and hope to see you on our blog next week!

Источник

Оцените статью
toolgir.ru
Adblock
detector