To calculate a z-score in Excel, subtract the mean from the value and divide by the standard deviation. If A2:A6 contains the data and B2 contains the value to score, use =STANDARDIZE(B2,AVERAGE($A$2:$A$6),STDEV.S($A$2:$A$6)); for the example data 10, 12, 14, 16, 18 and a value of 18, the result is about 1.2649. A z-score is different from a critical z value or a z-test p-value, so first identify which one you need.
What a z value means
A z-score tells you how far a value is from a mean, measured in standard deviations. A score of 0 is exactly at the mean; a score of 1 is one standard deviation above it; and a score of -2 is two standard deviations below it. The sign shows the direction, while the absolute size shows the distance.
For example, if a test has a mean of 70 and a standard deviation of 10, a score of 80 has a z-score of 1. It is one standard deviation above the average. A z-score does not by itself say whether a result is unusually high or low; that judgment depends on the distribution and the question being asked.
People also use “z value” to mean a cutoff on the standard normal curve, such as the z value that marks the upper 2.5% of a distribution. That is a critical value, not the standardized score of an individual observation. Excel can calculate both, but the formulas are different.
Calculate a z-score with an Excel formula
The general calculation is (value − mean) ÷ standard deviation. Excel’s STANDARDIZE function performs that calculation when you provide the value, mean, and standard deviation. With observations in A2:A6 and the value to score in B2, the formula is =STANDARDIZE(B2,AVERAGE($A$2:$A$6),STDEV.S($A$2:$A$6)). The example data have a mean of 14 and a sample standard deviation of about 3.1623, so for B2 = 18 the result is (18 − 14) ÷ 3.1623, or about 1.2649.
You can also write the calculation directly. With the same layout—observations in A2:A6 and the value in B2—the equivalent formula is =(B2-AVERAGE($A$2:$A$6))/STDEV.S($A$2:$A$6). The direct version makes each part of the calculation visible; STANDARDIZE can be easier to recognize when reviewing a worksheet. Both return the same result when they use the same mean and standard deviation.
To score several values, place the values to score in B2:B20, enter the formula in C2, and fill it down. Keep the data range fixed with dollar signs, as in $A$2:$A$6, so it does not shift as the formula is copied. The reference to B2 should remain relative so it changes to B3, B4, and so on.
Choose the right standard deviation
The choice between sample and population standard deviation changes the z-score. Use STDEV.S when your observations are a sample used to estimate a broader population. Use STDEV.P when the data in the range represent the entire population you intend to describe. Do not choose one merely because it produces a preferred result; base the choice on how the data were collected and what they represent.
For the five values 10, 12, 14, 16, and 18, the mean is 14. Their population standard deviation is about 2.8284, while their sample standard deviation is about 3.1623. Therefore, 18 has a population-based z-score of about 1.4142, but a sample-based z-score of about 1.2649. The difference arises because the sample standard deviation uses a denominator adjusted for estimating variability from a sample.
If your data are a complete set—for example, every item in a particular batch—and you are describing that batch, STDEV.P may fit. If the measurements are a subset intended to represent a larger group, STDEV.S is usually the relevant choice. Explain which definition you used when sharing results so another reader can reproduce them.
Find a critical z value in Excel
A critical z value is a percentile of the standard normal distribution. In Excel for Microsoft 365 on Windows, use NORM.S.INV to convert a cumulative probability into a z value. For a 95% two-sided confidence level, the central area is 0.95 and 0.025 remains in each tail, so the positive cutoff is the 97.5th percentile.
If cell B1 contains the significance level 0.05, the formula for the positive two-sided cutoff is =NORM.S.INV(1-B1/2). It returns approximately 1.959964. The corresponding lower cutoff is -1.959964; the interval between them contains 95% of the standard normal distribution. For a one-sided test with the same 0.05 significance level, the upper cutoff is =NORM.S.INV(1-B1), which returns approximately 1.644854. These formulas assume B1 holds alpha as a decimal, not the number 5.
For a confidence interval or hypothesis test, decide whether the question is one-sided or two-sided before selecting a cutoff. A two-sided 5% test allocates 2.5% to each tail, whereas a one-sided 5% test places all 5% in one tail. Using the wrong tail setup changes the cutoff and can change the conclusion.
Convert a z-score to a probability
After calculating a z-score, you may want the area under the standard normal curve associated with it. NORM.S.DIST calculates that probability. Its second argument should be TRUE for the cumulative probability to the left of the z-score; using FALSE returns the height of the probability density curve at that point, not the cumulative area.
With a z-score in C2, the formula =NORM.S.DIST(C2,TRUE) returns the proportion of the standard normal distribution at or below that score. For C2 = 1, the result is about 0.8413, meaning roughly 84.13% of the distribution lies below 1. This interpretation depends on using a standard normal model; it does not mean that 84.13% of any arbitrary data set must fall below its calculated z-score.
For a right-tail probability, subtract the cumulative probability from 1. With the z-score in C2, =1-NORM.S.DIST(C2,TRUE) gives the area above it. For a two-sided probability beyond either tail, use =2*(1-NORM.S.DIST(ABS(C2),TRUE)). With C2 = 1, that result is about 0.3173. These are normal-curve probabilities; they are not automatically the right p-value for every study or test design.
Z-score versus Z.TEST
Excel’s Z.TEST function is easy to confuse with a formula for a z-score because of its name. It returns a one-tailed probability for a z-test; it does not return the standardized score. A score tells you how many standard deviations a value is from a mean. A p-value is a probability calculated under a specified test model, and it answers a different question.
For example, if A2:A11 contains sample observations and B1 contains a hypothesized mean, =Z.TEST(A2:A11,B1) returns a one-tailed p-value. If you want to provide a known population standard deviation, supply it as the optional third argument, as in =Z.TEST(A2:A11,B1,5). When that argument is omitted, Excel estimates the standard deviation from the sample. Check the function’s assumptions and the direction of the test before interpreting its result; do not label its output a z-score.
For a two-sided test, a one-tailed result cannot simply be treated as the two-sided p-value in every situation. The usual conversion doubles the relevant smaller tail probability when the test is symmetric, but the test setup and direction matter. If you need a z statistic itself, calculate it from the observed value or sample mean, its hypothesized value, and the appropriate standard error instead of relying on Z.TEST.
Check the data and interpret the result
A formula can calculate a number correctly while the number answers the wrong question. Before interpreting a z-score, check the observations and the data definition. Missing values, text stored as numbers, accidental headers in a calculation range, or a mixed set of measurement units can affect the mean and standard deviation. Confirm that the value being scored uses the same units and measurement process as the reference data.
- Check the range: Make sure the mean and standard deviation include the intended observations and exclude labels or unrelated values.
- Check the spread: A standard deviation of zero means all included observations are identical; dividing by zero cannot produce a meaningful z-score.
- Check outliers: Extreme observations can pull the mean and inflate the standard deviation, changing the scores of other values.
- Check the model: A z-score can be computed for data that are not normally distributed, but normal-curve probabilities and cutoffs may then be inappropriate.
- Check the interpretation: A z-score of 2 is two standard deviations above the selected mean, not necessarily a 2% probability or a guarantee that a value is rare.
For a large, roughly bell-shaped distribution, the standard normal curve is often a useful reference. For strongly skewed data or a small sample, a normal-based probability can be misleading. A percentile, a plot, or a method suited to the data may be more informative. Standardizing is a rescaling operation; it does not make the original data normally distributed.
Common Excel issues and useful checks
If a formula displays as text instead of calculating, check whether the cell is formatted as Text or whether an apostrophe appears before the equals sign. Change the cell format to General, then edit and re-enter the formula. If Excel shows an error, check that the standard-deviation range contains numeric observations and that it has enough values for the selected calculation.
Microsoft 365 for Windows and Excel for Mac use the same formulas described here. Excel for the web also supports these standard statistical functions, although the surrounding interface can differ. Excel installations configured for some regions use semicolons rather than commas as formula argument separators; the formulas here use the comma separators requested for standard English-language syntax.
One useful check is to verify the center of the data. With observations in A2:A6 and the same range used to calculate the mean and standard deviation, the mean itself has a z-score of 0. Another check is to calculate a score manually for one row and compare it with Excel’s result. In the example, (18 − 14) ÷ 3.1623 is about 1.2649, matching the formula. These checks can catch a shifted range or a population-versus-sample mismatch.
Frequently Asked Questions
What is the simplest formula for a z-score in Excel?
Use (value − mean) ÷ standard deviation, or Excel’s STANDARDIZE function. For a value in B2 and data in A2:A6, use =STANDARDIZE(B2,AVERAGE($A$2:$A$6),STDEV.S($A$2:$A$6)) if the range is a sample. Choose STDEV.P instead when it represents the full population of interest.
How do I find the z value for a 95% confidence level?
For a two-sided 95% confidence level, the positive standard normal cutoff is about 1.96 and the negative cutoff is about -1.96. If B1 contains alpha, 0.05, use =NORM.S.INV(1-B1/2) for the positive cutoff. A one-sided 95% bound uses about 1.645 instead.
Does Z.TEST give me the z-score?
No. Z.TEST returns a one-tailed p-value for a z-test. Calculate a z statistic separately using the relevant observed value, hypothesized mean, and standard error, and make sure the test assumptions are appropriate.
Why is my z-score negative?
A negative z-score means the value is below the mean used in the calculation. For example, a value of 12 relative to a mean of 14 has a negative numerator. The negative sign indicates direction; it does not mean the calculation failed.
Should I use STDEV.S or STDEV.P?
Use STDEV.S when your data are a sample used to estimate a larger population’s variability. Use STDEV.P when the range is the full population you intend to describe. The right choice follows the data’s role, not the size of the z-score you want.
Related
- Excel Slicer Formatting: Styles, Size, Layout, and Use
- Excel Slicers: What They Do and How to Use Them
- Excel Slicers: How to Create and Use Them
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Custom Number Format for Thousands (k)To show Excel numbers in thousands with a “k” suffix without changing their stored values, select the cells,…
- Excel Power Query Logo: What It Looks Like and MeansIn Excel for Microsoft 365 on Windows, Power Query is identified mainly by the Get Data commands on…
- Multiply Hours by Money in Excel: A Practical GuideTo calculate pay from decimal hours, put the hours in B2 and the hourly rate in C2, then…
- Google Sheets Freeze Rows Greyed Out? Causes and FixesIf Freeze rows is greyed out in Google Sheets, first make sure you can edit the spreadsheet and…
- Where Did Undo and Redo Go in Excel?In Excel for Microsoft 365 on Windows, Undo and Redo are usually in the Quick Access Toolbar at…
- Sum in Excel From Different Sheets: A Step-by-Step GuideTo add the same cell from several sheets in Excel, enter a SUM formula such as =SUM(Jan!B2,Feb!B2,Mar!B2) in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.