With a value in A2, its mean in B2, and its standard deviation in C2, calculate its z score in Excel with =STANDARDIZE(A2,B2,C2). For a list of observations, you can instead calculate the mean and standard deviation from the data range within the formula; the right standard deviation function depends on whether your data is a sample or the entire population.
The Excel formula for a z score
Excel’s STANDARDIZE function calculates a standardized value from three inputs: the observation, the mean, and the standard deviation. Its syntax is STANDARDIZE(x,mean,standard_dev). The result is the observation’s z score: how many standard deviations it lies above or below the mean.
For a worksheet where the observation is in A2, the mean is in B2, and the standard deviation is in C2, enter =STANDARDIZE(A2,B2,C2) in another cell. The formula does not calculate the mean or standard deviation for you; those values must already be available in B2 and C2.
You can also calculate the same result directly by subtracting the mean and dividing by the standard deviation. If the observations are in A2:A11 and the value to score is in A2, enter =(A2-AVERAGE($A$2:$A$11))/STDEV.S($A$2:$A$11) in B2. This version calculates a sample z score. The dollar signs keep the data range fixed if you copy the formula down the column.
What a z score tells you
A z score expresses a value relative to a group’s mean and standard deviation, rather than in the original measurement units. A score of zero means the value equals the mean. A positive score is above the mean; a negative score is below it. A score of 2 means the value is two standard deviations above the mean, not that it is two units above it.
Standardizing can help compare values measured on different scales. For example, a test score of 80 and a delivery time of 80 minutes cannot be compared directly. Their z scores show how unusual each value is relative to its own group. That comparison is useful only if the reference groups and the meaning of the measurements are appropriate for the question.
A z score does not by itself tell you the probability of an outcome or prove that a value is an error. Probability interpretations often rely on an additional assumption that the data follow a normal distribution. The calculation itself is simply a description of distance from the mean in standard deviation units.
Choose sample or population standard deviation
The standard deviation choice affects the z score. Use STDEV.S when your observations are a sample used to represent a broader population. It estimates population variability using the sample and divides the squared deviations by one fewer than the number of observations. Use STDEV.P when your values include every member of the population you intend to describe; it divides by the number of observations.
For example, a handful of surveyed customers is normally a sample of a larger customer base. A complete list of all employees in a small department may be the whole population if the question concerns only that department. The choice is about what the data represent, not about which function gives a preferred result. STDEV.S often produces a slightly larger standard deviation than STDEV.P for the same nonconstant data, so its z scores tend to be closer to zero.
If the values are in A2:A11 and you want the sample standard deviation, enter =STDEV.S(A2:A11). If those same cells contain the complete population, calculate the population standard deviation with =STDEV.P(A2:A11). These functions ignore blank cells and text in referenced cells, but numeric values stored as text may not be treated as observations. Check that the selected range contains the data you intend to analyze.
Worked example with a small data set
Suppose A2:A6 contains the values 10, 12, 14, 16, and 18. Their mean is 14. The sum of their squared deviations from the mean is 40. Because these five values are being treated as a sample, the sample standard deviation is the square root of 40 divided by 4, or approximately 3.1623.
With the sample values in A2:A6 and the target observation 18 in A6, enter =(A6-AVERAGE($A$2:$A$6))/STDEV.S($A$2:$A$6) in B6. The result is approximately 1.2649. In other words, 18 is about 1.26 sample standard deviations above the sample mean of 14.
If the five listed values are the entire population of interest instead, the population standard deviation is the square root of 40 divided by 5, approximately 2.8284. With the population values in A2:A6 and the observation in A6, use =(A6-AVERAGE($A$2:$A$6))/STDEV.P($A$2:$A$6). The result is approximately 1.4142. The value and mean did not change; the different standard deviation explains the different z score.
Calculate z scores for every row
To standardize a whole column, put one observation per row and use a fixed range for the reference data. For example, if the observations are in A2:A11, enter the sample formula in B2, then fill it down through B11. Each row’s value changes, while the dollar signs keep the mean and standard deviation based on the same complete data set.
With values in A2:A11 and the first observation in A2, the formula for B2 is =(A2-AVERAGE($A$2:$A$11))/STDEV.S($A$2:$A$11). After filling down, the row reference changes to A3, A4, and so on, but the reference range remains A2:A11. Include all intended observations in that fixed range; otherwise, the scores may be based on an incomplete group.
For a larger workbook, it can be clearer to calculate the mean and standard deviation once in separate cells, then refer to those cells with STANDARDIZE. This makes the assumptions visible and makes it easier to check the inputs. The formula works in Microsoft 365 for Windows, Excel for Mac, and Excel for the web. If your regional settings use a different formula argument separator, Excel may require that separator when you type a formula.
Interpret the result carefully
The sign and size of the score are the first things to read. A score near zero indicates a value near the mean relative to the group’s spread. A score of -1.5 indicates a value one and a half standard deviations below the mean. The score is not a percentage, and a score of 1 does not mean the observation is one percent above average.
In normally distributed data, z scores can be connected to probabilities or percentiles using the normal distribution. But many real data sets are skewed, have several clusters, or contain extreme observations. In those cases, a z score still describes distance from the mean, but familiar normal-curve probability interpretations may not apply. Check a chart or other summary of the distribution before drawing conclusions.
Also remember that the mean and standard deviation are sensitive to extreme values. A very large observation can pull the mean toward itself and increase the standard deviation, changing the scores of other observations. For skewed data or data with substantial outliers, consider whether a median-based comparison or another method better fits the question.
Common formula problems and checks
- Division by zero: If all observations are identical, their standard deviation is zero, so a z score cannot be calculated by division. Check whether the data vary and whether the range is correct.
- Wrong data range: A heading, omitted row, or unrelated value can change the mean and standard deviation. Confirm the range includes exactly the observations that define the reference group.
- Inconsistent groups: Comparing one value against a mean from one population and a standard deviation from another produces a number, but not a meaningful z score. Use matching reference data.
- Unexpected negative values: A negative result is not an error by itself. It means the observation is below the mean.
- Too many displayed decimals: The underlying calculation can be more precise than the cell display. Format the result to a suitable number of decimal places rather than changing the calculation just to make it look tidy.
If you want a friendly message when the standard deviation is zero, you can wrap the calculation in IFERROR. With the observation in A2 and the sample data in A2:A11, enter =IFERROR((A2-AVERAGE($A$2:$A$11))/STDEV.S($A$2:$A$11),"Check data"). This catches calculation errors, including division by zero, but it can also hide other issues. Use it as a display safeguard, not a substitute for checking the underlying data.
STANDARDIZE versus a manual calculation
STANDARDIZE is concise when the mean and standard deviation already sit in separate cells, and its function name makes the intent recognizable. A manual calculation is handy when you want Excel to calculate the statistics from a range in the same formula or when you need to make the sample-versus-population choice explicit with STDEV.S or STDEV.P.
Both approaches use the same underlying calculation: subtract the mean, then divide by the standard deviation. They should agree when they use the same observation, mean, and standard deviation. STANDARDIZE does not decide whether your data are a sample or a population; it uses whatever standard deviation you supply. A clear worksheet should label how the mean and standard deviation were obtained.
Frequently Asked Questions
What is the simplest z score formula in Excel?
With the value in A2, the mean in B2, and the standard deviation in C2, use =STANDARDIZE(A2,B2,C2). Make sure the standard deviation is greater than zero and matches the group used to calculate the mean.
Should I use STDEV.S or STDEV.P for a z score?
Use STDEV.S when your data are a sample representing a larger group. Use STDEV.P when the data contain the entire population you want to describe. The right choice depends on the purpose and scope of the data.
Can a z score be negative?
Yes. A negative z score means the observation is below the mean. Its absolute size shows how many standard deviations away it is, using the standard deviation supplied to the calculation.
Why does Excel return an error for my z score?
A common cause is a standard deviation of zero, which occurs when all included values are identical. Also check for an empty or incorrect range and confirm that the standard deviation input is numeric and greater than zero.
Does a z score tell me the percentile?
Not by itself. A z score gives a distance from the mean in standard deviation units. Converting it to a percentile using a normal curve assumes a suitable normal distribution; that assumption may not fit your data.
Related
- Excel Timesheet Format: Build a Reliable Weekly Sheet
- Excel Timesheet for Multiple Employees: Setup and Tips
- Excel Invoice Bill Format: Build a Useful Template
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel XLOOKUP Examples for Common Spreadsheet TasksMicrosoft Excel’s XLOOKUP finds a value in one range and returns the corresponding value from another. For example,…
- Excel Rank Formula Without Duplicate Ranks: ExamplesTo give tied values different, consecutive ranks in Excel, enter =RANK.EQ(B2,$B$2:$B$10,0)+COUNTIF($B$2:B2,B2)-1 beside the first value and fill down.…
- Count Unique Records in Excel: Formulas and PivotTablesTo count distinct values in a column in Microsoft Excel for Microsoft 365, put this formula in an…
- XLOOKUP Excel Column Index: What Replaces It?XLOOKUP does not use a column index number. Instead, you give it the range or array to search…
- Excel RIGHT Function Examples: Extract Text from the EndUse Excel’s RIGHT function to return characters from the end of a text value. For example, if A2…
- Count Each Unique Value in Excel with a FormulaTo count how often each value appears, put your source values in A2:A11, then enter =SORT(UNIQUE(FILTER(A2:A11,A2:A11<>""))) in D2…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.