Error bars in Excel make it easier to navigate data and information, primarily if you deal with lots of numbers and percentages.
Keeping track of it all can be difficult, so implementing error bars in Excel and Google Sheets is an excellent way to make your life easier.
This quick how-to guide will cover adding error bars in Microsoft Excel first and then Google Sheets.
How to Make a Graph in Excel
Before you can add error bars to your Excel spreadsheet, you first need to have a graph, and a graph is only creatable if there is already some information on your spreadsheet.
Below is a simple example to illustrate a 2D clustered column.
The months, sales, and expenses need to be in the spreadsheet before creating a graph. If there is no information in the spreadsheet, there will be no data when you make it.
How to Add Error Bars in Excel
Now that you have a graph with information in place, you can go ahead and add error bars by first clicking on any part of your graph. When you click on the chart, three small icons will appear on the right side of the graph, and you want to select the first icon, which will be a + symbol.
Selecting the + will bring up the “chart elements” menu that contains a list of options, and from here, you can click on the box beside “error bars” to add error bars to your graph. Additionally, you can click on the right-facing arrow to the right of the “error bars” option to bring up four extra options.
You should find the four extra options listed as follows.
Standard Error
The standard error option will take the mean of all values in the graph and show you its standard error.
Percentage
The percentage option will add error bars of 5% value to your graph, and 5% is the percentage that Excel always defaults to. So, if you wish to change the percentage, you will need to do so manually in the “More Options” section.
Standard Deviation
The Excel standard deviation option will display how much the data deviates as a whole. To find standard deviation in excel manually, you will need to use the command =STDEV.
Referring back to the graph image above, to find the standard deviation of the three expenses numbers, in the B5 cell, you type =STDEV(C2:C4) and press enter. Doing this will take the data in C2, C3, and C4 and provide the standard deviation, which is $1,000.
Here is another example image:
Note: There are other variations of the =STDEV command that you can use to calculate different data types. You can see these if you only type =STD into a cell.
More Options
Selecting “More options” will bring up additional options for customizing your error bars, but this is unnecessary if you want to add standard error bars.
Other Ways of Adding Error Bars in Excel
Here are the ways to add different types of error bars to an excel graph.
Adding Standard Error Bars in Excel
To add standard error bars in excel, click on the “Standard Error” option from the four extra options, and this will add the standard error bars to your graph.
How to Add Individual Error Bars in Excel
For adding individual error bars in excel, you’re going to click on your graph, click on the + for chart elements, and select “More Options” from the list of four additional error bars options. A new dialogue will pop up next, and from here, you must choose the series you want to add error bars to and click ok.
You will see a new “Format Error Bars” section appear on the right side of the spreadsheet, and here, you will want to go to the bottom of the section until you see the “Custom” option.
Click on that option and then select the “Specify Value” option beside it.
A small dialogue box should appear in the middle of the spreadsheet next, and here you will have to delete the values in both the positive and negative error value sections. After deleting the values, click on the positive error value box and click on a cell or a range of cells in the worksheet.
Repeat the same process for the negative error value box before clicking ok. Individual error bars will then show in the graph.
How to Add Vertical Error Bars in Excel/How to Add Horizontal Error Bars In Excel
Vertical error bars are the default orientation of error bars in most chart types, so adding them to a graph is quickly done with the above methods. Since horizontal bars only work with specific chart types, it is more difficult to access them, but you can still add them to a graph if the graph uses a compatible chart type, as seen below.
Adding horizontal error bars works the same as adding vertical errors bars, so ensure you have a compatible chart and follow the above steps.
How to Add Error Bars in Google Sheets
Adding error bars in Google Sheets works similarly to adding error bars in Excel so that you can refer to much of the information already covered above for additional guidance.
That said, navigating your way to the error bars option is different in Google Sheets compared to Excel.
You will still need to have information and a graph to add error bars.
If data exists in your spreadsheet, click the “Insert” option in the toolbar at the top of the spreadsheet. You will then see a “Chart” option in the dropdown menu that appears.
Note: Be sure to first highlight all the cells you want to include in your graph because your chart will otherwise say “No data” when you create it.
Once you create your graph with your desired data, right-click on the chart and click the “Series” option. Doing this will bring up an additional set of options where you can choose one of the following.
- Apply to all series
- Column 1
- Column 2
Select Apply to all series to select all columns in the graph, and you will then see the chart editor appear on the right side of the spreadsheet. From here, scroll down until you see an empty box with the words “Error bars” beside it.
Click the box, and you will automatically add error bars to all columns in the chart.
How to Add Vertical Error Bars in Google Sheets/How to Add Horizontal Error Bars In Google Sheets
Like with Excel, vertical error bars are the default for Google Sheets, so adding horizontal error bars requires a specific chart type. However, bar charts are generally the most uncomplicated charts to use, making turning a column chart into a bar chart advisable and easy to do.
After creating your chart, right-click on it and select the “Chart style” option. Doing this will bring up the customize menu on the right of the spreadsheet, and you will want to then click the “Setup” option beside “Customize.”
At the top of the “Setup” page will be the chart type. By default, it will say a column chart, and clicking on it will bring up a complete list of other chart types for you to choose.
(image)
Select one of the bar charts from the list, and from there, you can follow the above steps for adding error bars, which will be horizontal by default this time instead of vertical.
Conclusion
Knowing the ins and outs of adding error bars to Microsoft Excel and Google Sheets will take you far, as they make working with data more manageable.
You can do much more with error bars and different chart types, so make sure to continue exploring their utility to maximize your effectiveness!
If you’re looking to do more reading on Microsoft Excel and Google Sheets features, check out these guides on How to Create a Dropdown In Google Sheets & Excel and How to Password Protect an Excel File.
