Written and maintained by CASRAI Editorial Board
Last updated
Excel groups its statistical worksheet functions into one reference list, but the list is alphabetical and says little about which function answers which research question. This guide organises the functions by task, shows the one choice that most often changes a result (sample or population), and explains how Excel treats text, logical values and blanks when you pass them in.
What Excel statistics functions are used for in research
Excel’s statistical functions cover descriptive summaries, relationships between two variables, tests and intervals, and simple linear fitting. Microsoft’s statistical functions reference lists them alphabetically. The map below regroups the ones researchers reach for first by the question they answer, summarising Microsoft’s one-line descriptions.
| Task | Functions | What they do (summarised from Microsoft) |
|---|---|---|
| Describe the centre | AVERAGE, MEDIAN, MODE.SNGL | Returns the average of its arguments; the median of the given numbers; the most common value in a data set |
| Describe the spread | STDEV.S, STDEV.P, VAR.S | Estimates standard deviation based on a sample; calculates standard deviation based on the entire population; estimates variance based on a sample |
| Describe position | PERCENTILE.INC, QUARTILE.INC, RANK.EQ | Returns the k-th percentile of values in a range; the quartile of a data set; the rank of a number in a list of numbers |
| Describe shape | SKEW, KURT | Returns the skewness of a distribution; the kurtosis of a data set |
| Count or average by condition | COUNTIFS, AVERAGEIFS | Counts the cells within a range that meet multiple criteria; averages all cells that meet multiple criteria |
| Relate two variables | CORREL | Returns the correlation coefficient between two data sets |
| Test and estimate | T.TEST, NORM.DIST, CONFIDENCE.T | Returns the probability associated with a Student’s t-test; the normal cumulative distribution; the confidence interval for a population mean, using a Student’s t distribution |
| Fit and predict | LINEST, FORECAST.LINEAR | Returns the parameters of a linear trend; returns a future value based on existing values |
For the underlying ideas, see the dictionary entries on descriptive versus inferential statistics, the p-value and the confidence interval. Single-function walkthroughs already on CASRAI cover the mode in Excel, the median in Excel, percentiles and quartiles, normality testing in Excel and scatter plots in Excel.
Sample or population: STDEV.S versus STDEV.P
Use STDEV.S when your data are a sample drawn from a larger population, and STDEV.P when your data are the entire population. Microsoft documents that STDEV.S uses the “n-1” method and assumes its arguments are a sample; if the data represent the entire population, it directs you to STDEV.P. VAR.S is the matching sample-variance function.
A worked example you can reproduce: for the five values 4, 8, 6, 5 and 3 the mean is 5.2 and the squared deviations from the mean sum to 14.8. Dividing by n-1 = 4 gives a sample variance of 3.7 and a sample standard deviation of about 1.9235 (this is what STDEV.S returns). Dividing by n = 5 gives a population variance of 2.96 and a standard deviation of about 1.7205 (this is what STDEV.P returns). Same data, two defensible answers; the choice must match what the data represent.
Excel 2010 names and older compatibility names
Microsoft tags STDEV.S, STDEV.P, VAR.S, MODE.SNGL, NORM.DIST, PERCENTILE.INC, QUARTILE.INC, RANK.EQ, T.TEST and CONFIDENCE.T as Excel 2010 functions. FORECAST was replaced by FORECAST.LINEAR in Excel 2016 and remains available for compatibility. When a collaborator’s older workbook uses a name without the dotted suffix, it is a compatibility function; check the reference before assuming the two behave identically.
Install or access: the Analysis ToolPak
The functions above are built in and need no installation. For menu-driven analysis, Microsoft provides the Analysis ToolPak add-in, loaded from its official instructions:
- Windows: choose File > Options > Add-Ins. In the Manage box select Excel Add-ins and choose Go. Tick Analysis ToolPak and choose OK. If it is not listed, choose Browse; answer Yes if prompted to install.
- macOS: choose Tools > Excel Add-ins, tick Analysis ToolPak, choose OK, then quit and restart Excel.
Microsoft states the result: the Data Analysis command is then available on the Data tab.
Step-by-step: a sample standard deviation with STDEV.S
- Enter your numeric values in one column, for example A2:A6 (4, 8, 6, 5, 3).
- Click an empty cell, such as C2.
- Type
=STDEV.S(A2:A6)and press Enter. The syntax is STDEV.S(number1,[number2],…); number1 is required and up to 254 arguments are accepted. - For a whole population, type
=STDEV.P(A2:A6)instead. - For the sample variance, type
=VAR.S(A2:A6).
Output and how to read it
With the example data, STDEV.S returns 1.923538 (displayed to the cell’s number format), VAR.S returns 3.7 and STDEV.P returns 1.720465. The standard deviation is in the same units as your data; the variance is in squared units. A standard deviation is only comparable across studies if both used the same sample or population convention, so report which function you used.
Troubleshooting
Why does my result ignore some cells?
Per Microsoft, when the argument is an array or reference, empty cells, logical values, text and error values in it are ignored. Numbers stored as text in a range are therefore skipped silently, which changes n and the result. Convert them to true numbers first.
Why does typing values directly behave differently?
Microsoft documents that logical values and text representations of numbers typed directly into the argument list are counted. Error values, or text that cannot be translated into numbers, cause errors. STDEVA is the variant that includes logical values and text in references.
Why do I get an error when I type values directly?
Microsoft documents that arguments that are error values, or text that cannot be translated into numbers, cause errors when typed into the argument list. Inside a range, such cells are ignored instead, so check how your data are being passed.
Alternatives outside Excel
Where you need scripted, repeatable analysis, the same sample-versus-population choice appears in other tools. The table records what each tool’s own documentation says.
| Tool | Relevant behaviour (from its documentation) |
|---|---|
| R, sd() | Computes the standard deviation of the values; like var, it uses denominator n – 1, which matches STDEV.S. |
| SciPy, scipy.stats.describe | Has a ddof argument (delta degrees of freedom, only for variance) with a default of 1, so its variance is a sample variance by default. |
CASRAI also has guides to SPSS, SciPy’s stats module and descriptive statistics in SPSS, and a note on reading a correlation matrix for CORREL output across several variables.
FAQ
Which Excel function gives the standard deviation of a sample?
STDEV.S. It uses the n-1 method and assumes the arguments are a sample. Use STDEV.P if your data are the entire population.
What replaced FORECAST?
FORECAST.LINEAR, introduced in Excel 2016. FORECAST remains available for compatibility.
Where is Data Analysis in Excel?
On the Data tab, after you load the Analysis ToolPak add-in through File > Options > Add-Ins on Windows or Tools > Excel Add-ins on macOS.
Does Excel count text inside a range when calculating standard deviation?
STDEV.S ignores text, logical values and empty cells inside a reference; STDEVA includes logical values and text.
Sources
- Microsoft Support: Statistical functions (reference), checked 2026-10-29.
- Microsoft Support: STDEV.S function, checked 2026-10-29.
- Microsoft Support: Load the Analysis ToolPak in Excel, checked 2026-10-29.
- R documentation: sd, checked 2026-10-29.
- SciPy documentation: scipy.stats.describe, checked 2026-10-29.
