Excel’s z-score function is STANDARDIZE, which calculates how many standard deviations a value is from a mean. If the value is in A2, the data is in A2:A6, and you want the population standard deviation, enter =STANDARDIZE(A2,AVERAGE($A$2:$A$6),STDEV.P($A$2:$A$6)) in another cell. The same calculation can be written as (value − mean) ÷ standard deviation.
What a z score tells you
A z score, also called a standard score, expresses a value’s position relative to a group in units of standard deviations. A score of 0 means the value equals the group’s mean. A positive score is above the mean, and a negative score is below it. For example, a z score of 1.5 means the value is 1.5 standard deviations above the mean; a score of −2 means it is two standard deviations below the mean.
This standardization makes measurements from different scales easier to compare. A test score of 80 points and a delivery time of 40 minutes cannot be compared directly as raw numbers. Their z scores can show which is farther from its own group average, provided each is standardized using an appropriate mean and standard deviation. A z score does not, by itself, say whether a value is good or bad; that depends on what the measurement represents.
STANDARDIZE syntax and arguments
The function’s syntax is STANDARDIZE(x, mean, standard_dev). The arguments are the value to convert, the mean for the comparison group, and that group’s standard deviation. Excel returns the standardized value as a number. The function does not calculate the mean or standard deviation from a range automatically: you supply those values or include functions such as AVERAGE and STDEV.P in the arguments.
For a small, defined data set, you can put the calculation directly in a cell. With the five observations in A2:A6, and with A6 as the value being standardized, enter =STANDARDIZE(A6,AVERAGE(A2:A6),STDEV.P(A2:A6)) in B6. The dollar signs used in the opening example are not essential for a single calculation; they are useful when a formula will be copied, as they keep the reference range fixed.
STANDARDIZE requires a standard deviation greater than zero. If the standard deviation is zero, the data have no variation, so a z score relative to that group cannot be calculated. A negative standard deviation is not meaningful for this purpose either. Excel reports an error when the supplied standard deviation is zero or negative.
Worked example with five values
Suppose A2:A6 contains 10, 12, 14, 16, and 18. The mean is 14. The population standard deviation is approximately 2.8284, so the z score for 18 is approximately 1.4142: the value is about 1.41 population standard deviations above the mean. The z score for 10 is approximately −1.4142. These values are symmetric because the data are evenly spaced around 14.
With 10, 12, 14, 16, and 18 in A2:A6 and the value 18 in A6, enter =STANDARDIZE(A6,AVERAGE($A$2:$A$6),STDEV.P($A$2:$A$6)) in B6. The result is approximately 1.4142. The arithmetic is (18 − 14) ÷ 2.8284, which is about 1.4142. If you format the result to two decimal places, Excel displays 1.41; changing the displayed decimals does not change the underlying result.
The direct arithmetic version is useful for understanding what the function does. With the same values in A2:A6 and the observation in A6, enter =(A6-AVERAGE($A$2:$A$6))/STDEV.P($A$2:$A$6) in B6. It returns the same result as STANDARDIZE, apart from possible differences in displayed rounding. The named function is often easier to read, while the arithmetic form makes the components explicit.
Calculate z scores for an entire column
To standardize every observation in a list, use a fixed range for the mean and standard deviation, then fill the formula down. With the full comparison data in A2:A6, enter =STANDARDIZE(A2,AVERAGE($A$2:$A$6),STDEV.P($A$2:$A$6)) in B2. Copy or fill B2 down through B6. Each row’s value reference changes, while the dollar signs keep the comparison range at A2:A6.
In Excel for Microsoft 365 on Windows, select B2 and drag the fill handle at its lower-right corner down to B6, or copy B2, select B3:B6, and paste. The Windows shortcut Ctrl+D fills down when the top cell and cells below it are selected. On Mac, use Command+D. If the list is likely to grow, converting the data to an Excel table with Insert > Table can make formulas easier to extend as rows are added; check that the formula’s statistical range includes all observations intended for comparison.
Decide what counts as the comparison group before filling the formula. If each score should be measured against the same complete set, use the entire fixed range. If a score is meant to be compared only with its own category, calculate the mean and standard deviation separately for each category. Mixing groups with different baselines can make a technically correct calculation misleading.
Choose population or sample standard deviation
The choice of standard deviation is a statistical decision, not a difference in the STANDARDIZE function itself. Use STDEV.P when the values in the range represent the entire population you want to describe. Use STDEV.S when the range is a sample intended to estimate variability in a larger population. STDEV.S applies a sample adjustment, so its result is generally a little larger than STDEV.P for the same nonconstant data set.
For the five values 10, 12, 14, 16, and 18, treating them as the full population gives a standard deviation of about 2.8284. Treating them as a sample gives about 3.1623. With the sample interpretation and 18 in A6, enter =STANDARDIZE(A6,AVERAGE($A$2:$A$6),STDEV.S($A$2:$A$6)) in B6. The result is about 1.2649, calculated as (18 − 14) ÷ 3.1623. That differs from the population-based score because the denominator differs.
Do not choose STDEV.S simply because a worksheet has a limited number of rows, or STDEV.P simply because it has many. Ask whether the listed values are the full group of interest or a sample used to say something about a larger group. If a report or course specifies a convention, follow it and state which standard deviation was used so readers can interpret the result.
Interpretation and limits
A z score is a measure of relative distance, not a probability and not a ranking by itself. A score of 2 does not mean there is a 2% chance of the value occurring. If the measurements follow a normal distribution, z scores can be connected to probabilities using the normal distribution. Without that distributional assumption, the score still describes distance from the mean in standard-deviation units, but normal-curve probability rules may not apply.
Likewise, common rules such as flagging scores beyond +2 or −2, or beyond +3 or −3, are screening conventions rather than universal definitions of an outlier. A value can be unusual without being erroneous, and a z score can be affected by skewed data, extreme values, small groups, or a mixture of different populations. Investigate flagged observations in context rather than deleting them automatically.
When calculating a z score for each item using the same data set, the scores describe the items relative to that set. If you add or remove observations, the mean and standard deviation can change, and so can every score. For comparisons over time, decide whether to recalculate the baseline for each period or keep a reference-period mean and standard deviation fixed; those choices answer different questions.
Common errors and troubleshooting
- Using the wrong range: Check that the mean and standard deviation are based on the same intended group. Including a header, unrelated rows, or a different category changes the calculation.
- Zero variation: If every value is identical, the standard deviation is zero and there is no meaningful z score. Check whether the selected range is correct rather than trying to conceal the error.
- Text instead of numbers: Imported values that look numeric may be stored as text. Excel’s statistical functions may not treat text in referenced cells as numeric observations. Convert or clean the data, then verify the range and results.
- References moving when copied: If the comparison range shifts as you fill down, add dollar signs to lock it, as in $A$2:$A$6. Leave the individual observation reference relative so it updates for each row.
- Unexpected negative results: A negative score is not an error when the value is below the mean. Check the observation and mean before changing the formula.
- Too much rounding: Keep the full calculated precision for downstream formulas and use cell formatting to control what is displayed. Rounding the z score early can affect later calculations.
STANDARDIZE versus related Excel functions
STDEV.P and STDEV.S calculate standard deviations; they do not return z scores on their own. AVERAGE calculates the mean. STANDARDIZE combines the value, mean, and standard deviation into one standardized result, so it is the direct function for the question of how far a value is from a mean in standard-deviation units.
Z.TEST is a different function. It returns a one-tailed probability associated with a z-test, given an array and a hypothesized value for the population mean; it is not a substitute for STANDARDIZE and does not simply return the z score of one cell. If your goal is to compare an individual observation with a group, use STANDARDIZE or the equivalent arithmetic. If your goal is a statistical hypothesis test, make sure you understand the test’s assumptions and what its probability represents before using Z.TEST.
Frequently Asked Questions
What is the Excel formula for a z score?
Use STANDARDIZE with the observation, the relevant mean, and the relevant standard deviation. The equivalent calculation is (observation − mean) ÷ standard deviation. Choose STDEV.P or STDEV.S according to whether the data are a population or a sample.
Does Excel have a ZSCORE function?
No built-in Excel function is named ZSCORE. STANDARDIZE is the built-in function for calculating a z score when you provide the value, mean, and standard deviation.
Is STANDARDIZE the same as Z.TEST?
No. STANDARDIZE returns a standardized value. Z.TEST returns a one-tailed probability for a z-test. They address different statistical questions.
Can a z score be negative?
Yes. A negative score means the observation is below the mean used in the calculation. Its size indicates how many standard deviations below the mean it is.
Should I use STDEV.S or STDEV.P for z scores?
Use STDEV.P for a complete population you want to describe and STDEV.S when your values are a sample of a larger population. The choice changes the standard deviation and therefore the resulting z scores.
What does a z score of 1 mean?
It means the value is one standard deviation above the mean used to calculate the score. It does not automatically correspond to a particular probability unless additional distribution assumptions are justified.
Related
- Split Cell Colour in Excel: Methods and Limitations
- Excel Solver: Setup, Optimization Models, and Fixes
- Solver in Excel: Where to Find It and How to Enable It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Approximate Match Lookup in Excel: Formulas and TipsFor a numeric approximate lookup in Excel, use an ascending threshold table and a formula that returns the…
- Excel IFS Statement Examples and How to Use the FunctionExcel’s IFS function checks conditions in order and returns the result paired with the first condition that is…
- Excel Sum Across Sheets: Formulas and 3D ReferencesTo sum the same cell across consecutive worksheets in Excel, use a 3D reference such as =SUM(Jan:Mar!B2). If…
- Excel IFS in Multiple Cells: Apply Logic Across RangesTo use IFS in multiple cells, write the formula for the first row, then copy or fill it…
- Concatenate in Excel with the Ampersand (&) OperatorTo concatenate text in Excel, enter an ampersand between the cell references you want to join. With a…
- Outlook Rules: Filter Your Incoming Mail AutomaticallyCreate an Outlook rule by choosing a condition, such as who sent a message or a word in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.