How to Find a Z Value in Excel: Formulas and Examples

To calculate a z-score for a value in Excel, subtract the dataset mean from the value and divide by the standard deviation; Excel can also do this with STANDARDIZE. For example, if your observations are in B2:B6 and you want the z-score for B5, use =STANDARDIZE(B5,AVERAGE($B$2:$B$6),STDEV.S($B$2:$B$6)). A z-score tells you how many standard deviations a value is above or below the mean. If you instead need a z-value for a probability, use NORM.S.INV. The steps below explain both meanings so you can choose the right calculation.

These instructions are for Microsoft Excel for Microsoft 365 on Windows. You will need a worksheet containing the value or probability you want to analyze and, for a z-score, a suitable set of numeric observations. Excel for Mac has the same formulas; ribbon and keyboard details are noted where they differ.

What You’ll Need

A Microsoft Excel workbook with your data, or a known probability if you are looking for a critical z-value.
A clear definition of the data set: decide whether the numbers are a sample or the entire population. This choice affects the standard deviation and therefore the z-scores.
For a probability-based z-value, a probability between 0 and 1, such as 0.95. Be clear whether it represents a left-tail cumulative probability or a central confidence level.

Step-by-Step

Identify which z-value you need.
A z-score converts an observed value into standard-deviation units. It is useful for comparing a measurement with its group, such as comparing a test result with class scores. A critical z-value goes the other way: it starts with a probability under a normal distribution and returns the corresponding standard-score cutoff. If you have a raw measurement and a data set, calculate a z-score. If you have a probability or percentile, find the inverse normal value.
Arrange and check the data for a z-score.
Put the observations in one column, with a single number in each cell and no heading included in the calculation range. For this worked example, enter 68, 72, 75, 80, and 85 in B2:B6. The value to assess, 80, is in B5. Check for blanks, text stored as numbers, and accidental values such as a total row. A z-score is only meaningful relative to the group you intend to analyze.

For the example values in B2:B6, use a sample standard deviation because these five observations are being treated as a sample from a larger group. In a spare cell, calculate the mean with =AVERAGE(B2:B6). The result is 76. In another cell, calculate the sample standard deviation with =STDEV.S(B2:B6). The result is approximately 6.6708. These summary values let you check the result and make the calculation easier to understand.
Calculate the z-score with STANDARDIZE.
With the observations in B2:B6 and the value to assess in B5, enter =STANDARDIZE(B5,AVERAGE($B$2:$B$6),STDEV.S($B$2:$B$6)) in an empty cell. Excel returns approximately 0.5996. This means 80 is about 0.60 sample standard deviations above the group mean of 76. A positive result is above the mean; a negative result is below it; a result close to zero is near the mean.

STANDARDIZE takes the value, mean, and standard deviation in that order. The dollar signs lock the data range, so you can copy the formula down for other values without the reference shifting. If you want to assess each observation, put the formula beside the first row and fill it down. Keep the data range fixed while allowing the reference to the value in the current row to change.
Calculate the same score with the arithmetic formula if preferred.
With the value in B5 and the full sample in B2:B6, enter =(B5-AVERAGE($B$2:$B$6))/STDEV.S($B$2:$B$6). The result is approximately 0.5996, matching STANDARDIZE. This version makes the calculation visible: subtract the mean from the value, then divide by the standard deviation. Use one approach or the other; there is no need to calculate both in your final worksheet.

If the mean and standard deviation are already in separate cells, you can use those references instead. For example, if the value is in A2, the mean is in B2, and the standard deviation is in C2, enter =STANDARDIZE(A2,B2,C2). Confirm that all three cells contain numbers and that the standard deviation is greater than zero.
Choose the correct kind of standard deviation.
Use STDEV.S when your observations are a sample used to estimate a broader population. Use STDEV.P only when the values represent the entire population you want to describe. In the B2:B6 example, replacing STDEV.S with STDEV.P gives a different z-score because the population calculation divides by the number of observations rather than one less than that number. For these five values, the population standard deviation is approximately 5.9666 and the z-score for 80 is approximately 0.6704. Neither answer is universally correct; the right one depends on what the data represent.
Find a z-value from a probability when that is what you need.
NORM.S.INV returns the standard normal z-value whose cumulative area to the left equals the probability you provide. With 0.95 in A2, enter =NORM.S.INV(A2) in another cell. The result is approximately 1.6449, meaning about 95% of the standard normal distribution lies at or below that z-value. The probability must be greater than 0 and less than 1; 0 and 1 do not produce finite z-values.

For a central 95% interval, the upper cutoff is not the 0.95 left-tail value. A central interval leaves 2.5% in each tail, so its upper cutoff corresponds to 0.975. With 0.95 in A2 as the central confidence level, enter =NORM.S.INV(1-(1-A2)/2). The result is approximately 1.9600. The matching lower cutoff is approximately -1.9600. For a nonstandard normal distribution, where the mean and standard deviation are known, use NORM.INV instead. With probability in A2, mean in B2, and standard deviation in C2, enter =NORM.INV(A2,B2,C2) to get the value on the original measurement scale.
Label and format your result.
Give the result a clear heading, such as Z-score, Standard-normal cutoff, or 95% upper cutoff. A z-score is dimensionless, so it does not retain the units of the original data. Keep enough decimal places for your analysis; displaying two or three decimal places is often readable, but do not round the formula result before using it in later calculations. In Excel for Windows, use Home > Number to choose a display format. On Mac, the same number-format controls are available on the Home tab.

Common Mistakes

Mixing up a z-score and an inverse-normal cutoff. STANDARDIZE uses an observed value and a mean and standard deviation. NORM.S.INV starts with a probability and returns a z-value. Using the wrong direction answers a different question.
Using the wrong data range. Including a heading, a subtotal, or unrelated observations changes the mean and standard deviation. Check the range addresses before trusting the output.
Choosing STDEV.S or STDEV.P without considering the data. The two functions do not generally return the same value. Decide whether your numbers are a sample or the complete population, and use the matching function consistently.
Entering 95 instead of 0.95 as a probability. In Excel, a probability for NORM.S.INV must be a decimal between 0 and 1. A cell displayed as 95% is stored as 0.95 and is valid; the number 95 is not.
Interpreting 0.95 as a two-tailed 95% cutoff. NORM.S.INV(0.95) is a one-sided 95th percentile. A central 95% range uses 0.975 for its upper cutoff and 0.025 for its lower cutoff.
Dividing by zero or using a constant data set. If every observation is identical, the standard deviation is zero and a z-score cannot be calculated by dividing by it.

Tips

Keep your raw observations unchanged and put formulas in a separate column. This makes it easier to audit the calculation and reuse the source data.
Use absolute references for a fixed data range when filling a z-score formula down. The dollar signs in $B$2:$B$6 prevent that range from moving.
For a large data set, calculate the mean and standard deviation in labeled cells once, then refer to them in each row. This makes the sheet easier to review and update.
Use the value of a z-score as a relative measure, not as proof that an observation is important or unusual. Context, sample size, distribution shape, and the question being asked all matter.
If you are comparing a value with a normal model rather than simply standardizing a data set, check that a normal distribution is a reasonable assumption. A z-score can be calculated for many data sets, but probability interpretations rely on a distribution model.

Troubleshooting

The formula returns #DIV/0!. The standard deviation is zero, often because the selected observations are all identical or the referenced standard-deviation cell is blank or zero. Check that the range contains multiple valid numeric values and that it describes the group you intended. If the data are genuinely constant, a z-score is undefined rather than a value Excel can repair.

The formula returns #VALUE! or an unexpected result. Look for text values or numbers stored as text in the data range, and confirm that the value, mean, and standard deviation arguments are in the correct order. Check that the formula uses commas between arguments. Excel installations configured for a different regional format may display a semicolon as the argument separator; use the separator Excel accepts in your installation. The formula names remain STANDARDIZE, STDEV.S, and AVERAGE in Microsoft 365.

NORM.S.INV returns #NUM!. The probability is probably 0, 1, below 0, or above 1. Enter a probability strictly between 0 and 1, and check whether a percentage cell contains a valid percentage rather than a whole-number percentage such as 95.

Your answer differs from an online calculator or class example. Compare the mean, standard deviation method, and data range first. One calculation may use a population standard deviation while another uses a sample standard deviation. Also check whether the requested probability is one-tailed or two-tailed and whether it is a left-tail or right-tail probability. Small differences can also come from rounding displayed values; compare full-precision results before rounding.

You cannot find a function or the result looks like text. Excel for Microsoft 365 includes STANDARDIZE, NORM.S.INV, and NORM.INV. If a formula appears literally in the cell, change the cell format from Text to General, then re-enter the formula. On Windows, press F2 and then Enter to re-enter it; on Mac, use Control+U to edit the cell and press Return. If F2 behaves differently on a Mac keyboard, use the function-key setting or edit the cell directly.

Frequently Asked Questions

What is the easiest way to calculate a z-score in Excel?
Use STANDARDIZE with the value, the mean, and the standard deviation. For a value in B5 and a data set in B2:B6, the worked example above returns approximately 0.5996 when using the sample standard deviation.

What does a z-score of 2 mean?
It means the value is two standard deviations above the mean used in the calculation. A z-score of -2 is two standard deviations below the mean. The score alone does not tell you the probability unless you also make an appropriate distribution assumption.

How do I find the z-value for the 95th percentile?
Use NORM.S.INV with a probability of 0.95. It returns approximately 1.6449 because 95% of a standard normal distribution is at or below that point. This is different from the upper cutoff of a central 95% interval, which is approximately 1.9600.

Should I use STDEV.S or STDEV.P?
Use STDEV.S when your listed observations are a sample from a larger population. Use STDEV.P when they are the complete population of interest. Make the choice based on how the data were collected, not on which answer seems more familiar.

Can I calculate z-scores for every row?
Yes. Keep the complete data range fixed and let the value reference move as you fill the formula down. Each row is then compared with the same mean and standard deviation, making the results comparable.

Does a z-score have units?
No. The subtraction and division convert the original measurement into standard-deviation units, so the result is dimensionless. Keep the original value and its measurement unit in the worksheet if readers need to see what the score represents.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.