Error bars in Charts in Excel

Add to Favorites
Author:

How to add, manage, and remove error bars in charts in Excel.

Error bars allow you to show the potential variance of a data point in a chart.  Maybe the values on the chart are an estimate and are within a 5% margin of error, this technique allows you to show that.

Sections:

Add Error Bars to Charts in Excel

Change the Error Bars Size/Calculation

Change the Appearance of Error bars

Remove Error Bars from a Chart

Notes

Add Error Bars to Charts in Excel

  1. Click the desired chart.  This causes three tabs to appear: Design, Layout, and Format.
  2. Click the Layout tab:
  3. Click Error Bars

    You can select one of the default options that are listed and get a basic Error Bar setup on your chart or you can click More Error Bars Options... to fine tune the setup.  If you select a default option, you are done and can go to the last step (Step 7), otherwise continue to Step 4.
  4. Click the More Error Bars Options... and you will see this screen:

    In this window you can change the display of the error bars but also the size of them.
  5. Look to the Error Amount section:

    Here you can choose how the bars should be calculated, using a Fixed value, Percentage, Standard deviation, Standard error, or Custom.  The first 3 are the ones that you will almost always use.
    Fixed value: is a simple number amount that will denote the size of the error bar.
    Percentage: is a calculation of the size of the error bar based on your current data set.
    Standard deviation: is a mathematical calculation used in statistics; if you don't know what this means, you probably don't need to use it.
  6. I will select percentage and set it to 7%.
  7. Hit the Close button to accept the changes and now you will see the Error Bars on your chart. (click the chart to deselect the error bars and see them better)

These error bars show where the plotted points may actually be.  Error bars allow you to show the potential error range of a number.  In this case, sales for the year, perhaps all of the costs associated with the business have not been factored into these numbers yet; maybe this is a preliminary gross profit number and so showing the user that there is a potential variance could be useful.

Change the Error Bars Size/Calculation

This is the same process as the steps above so I will just write it here; if you need a visual representation, follow the steps above.

  1. Select the chart and go to the Layout tab.
  2. Click Error Bars.
  3. Select an option from that drop-down menu, which means you are finished, or click More Error Bars Options... and then edit the options from there and click close when you are finished.

Basically, follow the same steps as above and you can manage and edit everything related to Error Bars in Excel.

Change the Appearance of Error Bars

  1. Click the chart.
  2. Go to the Layout tab that appears once you have clicked the chart and then click Error Bars and then More Error Bars Options...

  3. In the window that opens you have many options to change the appearance of the Error Bars.

    Look to the Display section that shows first and you can change if the error bars go up or down or both and if they have a "cap" or not.
    On the left side of the window you also have options to change the color of the line, style, shape, size, and effects.
  4. Play around with these options to get the desired look and, when you are finished, hit the Close button to see the result.

Remove Error Bars from a Chart

  1. Select the chart.
  2. Go to the Layout tab.
  3. Click the Error Bars button and then click the first option None.

Notes

Error bars can be helpful but they can also add clutter to a chart and are often difficult to read unless you are using a large chart.  The benefit of them is that they easily convey the notion that your data is not 100% accurate but, may instead, include a level of variance.  Weigh the factor of clutter with the desire to convey variance and act accordingly.

Download the sample file attached to this tutorial to see the above examples in Excel.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
How to Create and Manage a Chart in Excel
Tutorial: In this tutorial I am going to introduce you to creating and managing charts in Excel. Bef...
Error Values in Excel - Full Explanation
Tutorial: Here, I'll teach you what the errors in Excel mean.  There are many errors that you can...
Filter Data in Excel to Show Rows/Data That Meet Multiple Criteria for One Field - AutoFilter
Macro: This free Excel macro filters data in Excel based on multiple criteria for one field in th...
Filter Data in Excel - AutoFilter
Macro: This free Excel macro filters data in Excel using the autofilter feature in an Excel macro...
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
Macro: This free Excel macro illustrates all of the possible parameters and arguments that yo...
Add, Manage, & Remove Trendlines in Charts in Excel
Tutorial: How to add, manage, and remove trendlines in Excel Trendlines allow you to show trends i...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course