Written and maintained by CASRAI Editorial Board
Last updated
To calculate the standard error of the mean (SEM) in Excel, divide the sample standard deviation by the square root of the number of values: =STDEV.S(B2:B7)/SQRT(COUNT(B2:B7)). Using the same range in both halves is what keeps the result correct, because STDEV.S and COUNT ignore the same kinds of cells in a range (blanks, text and logical values), so the standard deviation and n always describe the same data.
This guide gives the formula, a worked example you can check by hand, the exact Microsoft menu path for mean ± SEM error bars, and the places the calculation goes wrong. SEM is a statistic about the precision of a mean; if you need to describe how spread out the individual values are, report the standard deviation instead, and always say which one a figure shows (see figure legend examples and the figure legend entry).
What SEM is used for in research
The SEM is the sample standard deviation divided by √n. It estimates how far a sample mean is likely to sit from the true population mean, and it shrinks as n grows. The standard deviation does not shrink with n; it describes the spread of the observations themselves. For that reason a mean ± SEM bar is narrower than a mean ± SD bar from the same data (for n above 1), and a reader cannot tell which one you used unless you state it. If you want an interval rather than a single error width, see confidence interval.
Install or access
No add-in is needed. STDEV.S, COUNT, SQRT and AVERAGE are built-in Excel functions. Microsoft’s Analysis ToolPak is an optional add-in that adds a Data Analysis command on the Data tab; on Windows you load it through File > Options > Add-Ins, set Manage to Excel Add-ins, select Go, tick Analysis ToolPak and select OK. Its Descriptive Statistics tool produces a report of univariate statistics, but the worksheet formula below is all you need for SEM and is easier to audit.
Step-by-step: calculate the mean, SD, n and SEM
Put your replicate values in one column. The example below uses six values, 12, 15, 11, 14, 13 and 16, in cells B2:B7.
- Mean. In an empty cell type
=AVERAGE(B2:B7). This returns 13.5. The documented Excel function for the mean is AVERAGE; there is no function named MEAN. - Sample standard deviation. Type
=STDEV.S(B2:B7). This returns 1.870829 (rounded to six places). Microsoft documentsSTDEV.Sas the function that treats its arguments as a sample of the population and uses the n-1 method;STDEV.Pis for a whole population and gives a smaller value on the same data. - Number of values. Type
=COUNT(B2:B7). This returns 6.COUNTcounts numbers; empty cells, text, logical values and errors inside a range are not counted. - SEM. Combine them in one cell:
=STDEV.S(B2:B7)/SQRT(COUNT(B2:B7)). This returns 0.763763, which is 1.870829 divided by √6 (2.449490).
Report this example as mean ± SEM = 13.5 ± 0.76 (n = 6). If you keep the SD, n and SEM in separate labelled cells, a reviewer can see each piece. For more on STDEV.S versus STDEV.P and other Excel functions, see Excel statistics functions; for the same quantity in code see standard deviation in R.
How to add SEM error bars in Excel
To chart group means with SEM bars, first compute one SEM per group as above, in cells next to the means. Then, following Microsoft’s instructions for Excel on Windows:
- Create a column, bar, line or scatter chart of the means. Microsoft lists error bars as available on 2-D area, bar, column, line, xy (scatter) and bubble charts.
- Select the data series, then on the Chart Design tab choose Add Chart Element > More Error Bars Options.
- In the Format Error Bars pane, on the Error Bar Options tab, under Error Amount choose Custom, then Specify Value.
- In Positive Error Value and Negative Error Value, select the range of cells that hold your SEM values (or type comma-separated values), then select OK.
Why Custom rather than the built-in option. Microsoft’s error-bar page offers predefined choices of Standard Error, Percentage and Standard Deviation under the Chart Elements button. A chart of means plots only one value per group, so a predefined option cannot see the replicates behind each mean. Pointing the Custom range at cells that contain =STDEV.S(range)/SQRT(COUNT(range)) makes the bar exactly the SEM you calculated. To remove error bars, clear the Error Bars check box under Chart Elements. The same decision, in another package, is covered in bar charts with error bars in SPSS.
Output and how to read it
With the worked data, the mean is 13.5, the SD is 1.87 and the SEM is 0.76. Read the SEM as the precision of the mean (here, a mean based on six values), not as the spread of the six values. Doubling n from 6 to 12 with the same SD would reduce the SEM by a factor of √2, while the SD would stay near 1.87. Two means with overlapping SEM bars are not, by that fact alone, proof of no difference; use a test or an interval for that conclusion.
Troubleshooting
The SEM looks too large or too small
Check that you used STDEV.S, not STDEV.P. Microsoft documents STDEV.P as the population version; on a sample it understates the SD and therefore the SEM.
Zeros or text in the range
Per Microsoft, AVERAGE ignores text, logical values and empty cells in a range but includes zeros. A 0 typed as a placeholder for a missing measurement is therefore treated as a real value and pulls the results. Leave missing cells blank. Numbers stored as text in a referenced range are not counted by COUNT, so convert them first.
Error values in the range
Microsoft documents that error values in a range cause AVERAGE to return an error, while STDEV.S and COUNT skip them. Clear the errors at the source so the mean, SD and n are all computed on the same cells.
#NUM! from SQRT
SQRT returns #NUM! for a negative number. With COUNT as the argument it cannot be negative, so a #NUM! here means the argument was changed to a calculated value that went below zero.
Alternatives: Google Sheets
Google Sheets has the same building blocks, so the SEM formula carries over with the sample-SD function named STDEV (keep text out of the range, since STDEV errors on text): =STDEV(B2:B7)/SQRT(COUNT(B2:B7)).
| Step | Excel | Google Sheets |
|---|---|---|
| Sample standard deviation | STDEV.S(number1,[number2],...), n-1 method |
STDEV(value1, [value2, ...]), “based on a sample”; fewer than two numeric values gives #DIV/0! |
| Count of numbers | COUNT |
COUNT, “the number of numeric values in a dataset” |
| Text in a referenced range | Ignored by STDEV.S |
Returns an error if a value argument includes text; STDEVA treats text as 0 |
FAQ
How do I calculate standard deviation in Excel?
Use =STDEV.S(range) for a sample, which is the usual case for experimental data, or =STDEV.P(range) if your cells are the entire population. Microsoft’s own example, breaking-strength values in A2:A11, returns 27.46391572 with STDEV.S.
How do I calculate SEM on Google Sheets?
Use =STDEV(range)/SQRT(COUNT(range)). STDEV is Sheets’ sample standard deviation function, as the table above shows.
How do I insert SEM in Excel?
Calculate it in a cell with =STDEV.S(range)/SQRT(COUNT(range)). To show it on a chart, use Add Chart Element > More Error Bars Options > Custom > Specify Value as described above and point both error value boxes at the SEM cells.
How do I calculate the mean in Excel?
Type =AVERAGE(range), for example =AVERAGE(B2:B7).
Is MEAN() a function in Excel?
No. The documented Excel function for the arithmetic mean is AVERAGE. Use AVERAGEA if logical values and text should be included, or AVERAGEIF and AVERAGEIFS to average only cells that meet criteria.
What is the mean formula?
The mean is the sum of the values divided by how many there are: (12 + 15 + 11 + 14 + 13 + 16) / 6 = 13.5. In Excel, AVERAGE does this for you.
Sources
Microsoft and Google documentation, checked 2026-10-29.
