10. Add error bars to a data series

skjermbilde-2016-11-30-12-31-04It is often useful or required to show the variability of your data when making a chart. This variability can easily be translated by error bars added along your plot line or bars. The bars may represent standard deviation, standard error of the mean, confidence intervals… you choose.

Let’s take a practical example: temperatures were measured at 4 different locations in Bergen. The mean temperature was then calculated (using the function =AVERAGE(...), see here)  as well as the standard error of the mean (shortened SEM or SE, using the formula =STDEV(...)/SQRT(COUNT(...)), see here).

Let’s create the line chart corresponding to the average temperature for each date. Once the chart displayed, click on the  + sign at the top right of the chart area and tick Error bars (see picture under). Adjust a few extra options by clicking on the arrow next to Error bars if necessary. Another option is to click on the data series (line) in the plot area and find the icon Add chart element in the Design section of the Chart Tools. Select Error bars and  More Error Bars Options... to reveal the menu Format Error Bars to the right (see under).



skjermbilde-2016-11-30-13-13-49In the dialog box called Format Error Bars, choose 1) whether you want error bars above the data point, below the data point, or both, and 2) whether the bars will have a cap or not. Then, in Error Amount, choose which value has to be displayed. Since we calculated the SEM in the worksheet, choose Custom and click on Specify Value. The box Custom Error Bars shows up. For both positive and negative error values, select the range of cells in the worksheet that correspond to your data. Note that these values will be automatically paired with the corresponding data points for the given dates. Click OK and Close to validate and obtain the following chart.


skjermbilde-2016-11-30-13-15-10Assuming that you are working with a bar chart, the procedure explained above still applies and give the following result.

Note that the appearance of the error bars is also configurable. In the dialog box Format Error Bars, the section Line (accessible via the bucket icon) allows you to change colors and thickness, among other parameters.

  Fant du det du lette etter? Did you find this helpful?
[Average: 0]