Computing a Z-Score in Excel

A z-score tells you how many standard deviations a single data point sits away from the average of your dataset. In Excel, you calculate it using the STANDARDIZE function, which takes three inputs: the value you are measuring, the mean (average) of your dataset, and the standard deviation. The formula is =STANDARDIZE(value, mean, standard_deviation).

Excel does the subtraction and division for you. If your data point is exactly at the mean, the z-score is 0. If it is one standard deviation above the mean, the z-score is 1. If it is one standard deviation below, the z-score is −1. Negative z-scores mean the value falls below the average; positive ones mean it falls above.

Key Takeaways

  • The STANDARDIZE function in Excel computes z-scores by subtracting the mean from your value and dividing by the standard deviation.
  • You need three pieces of information: the individual data point, the mean of the entire dataset (use AVERAGE), and the standard deviation (use STDEV.S for a sample or STDEV.P for a population).
  • A z-score of 0 means the value equals the mean; a z-score of 2 means it is two standard deviations above the mean.
  • You can explore the STANDARDIZE formula to an entire column at once by entering it once and dragging the fill handle down to copy it to other rows.

Setting Up Your Data and Calculating Mean and Standard Deviation

Start by organizing your raw data in a single column. For example, put your numbers in cells A2 through A50. In a separate cell, calculate the mean using =AVERAGE(A2:A50). In another cell, calculate the standard deviation using =STDEV.S(A2:A50) if your data is a sample, or =STDEV.P(A2:A50) if it represents an entire population. Most datasets you work with are samples, so STDEV.S is the more common choice.

Write these formulas in cells where you can see them clearly—for instance, put the mean in cell C2 and the standard deviation in cell C3. You will reference these cells in your STANDARDIZE formula, so keeping them visible and labeled makes your spreadsheet easier to follow and easier to fix if something goes wrong.

Using STANDARDIZE to Calculate Individual Z-Scores

In the cell next to your first data point (for example, cell B2), type the formula =STANDARDIZE(A2,$C$2,$C$3). Here, A2 is the individual value, $C$2 is the mean, and $C$3 is the standard deviation. The dollar signs ($) lock those cell references so they do not change when you copy the formula down.

Press Enter. Excel calculates the z-score for that single data point. If your value is 85, your mean is 75, and your standard deviation is 10, the z-score will be 1 (because 85 is one standard deviation above 75).

Copying the Formula to All Your Data Points

Click on the cell containing your STANDARDIZE formula (B2 in this example). Move your cursor to the small square at the bottom right corner of the cell—this is the fill handle. Click and drag it down to the last row of your data. Excel copies the formula to every cell you drag across, automatically adjusting the row reference (A2 becomes A3, A4, and so on) while keeping the mean and standard deviation references locked in place.

Alternatively, select the cell with the formula, copy it (Ctrl+C), then select the range where you want to paste it and paste (Ctrl+V). Both methods produce the same result: a z-score for every data point in your dataset.

Interpreting Your Z-Score Results

Once you have calculated z-scores for all your values, you can use them to spot outliers or compare values across different scales. A z-score between −2 and 2 is considered normal for most datasets. A z-score greater than 3 or less than −3 often signals an outlier—a value that is unusually far from the rest of your data.

Z-scores are also useful when you need to compare two datasets that use different units or scales. For example, if one dataset measures height in inches and another measures weight in pounds, converting both to z-scores puts them on the same footing so you can compare them directly.

Common Mistakes and How to Avoid Them

The most frequent error is forgetting the dollar signs in the cell references. Without them, when you copy the formula down, the references to your mean and standard deviation shift, and your calculations become wrong. Always use $C$2 and $C$3 (or whatever cells hold your mean and standard deviation) so those references stay fixed.

Another mistake is using the wrong standard deviation function. STDEV.S calculates the sample standard deviation and is appropriate for most real-world data. STDEV.P calculates the population standard deviation and is used only when your data represents every single member of a group, not a sample from a larger group. If you are unsure, STDEV.S is the safer choice.

A third pitfall is calculating the mean and standard deviation from a different range than your data. If your data runs from A2 to A50 but you accidentally calculate the mean from A2 to A40, your z-scores will be wrong. Double-check that your AVERAGE and STDEV ranges match your actual data range.

Frequently Asked Questions

What is the difference between STANDARDIZE and manually typing the formula?

STANDARDIZE does the same calculation as typing =(A2-$C$2)/$C$3 manually. Both produce identical results. STANDARDIZE is slightly shorter to type and reads more clearly, but the manual formula gives you more control if you need to modify the calculation.

Can I use z-scores to find percentiles in Excel?

Yes. Once you have a z-score, you can use the NORM.S.DIST function to find the percentile. For example, =NORM.S.DIST(1.5,TRUE) tells you what percentage of a normal distribution falls below a z-score of 1.5. The TRUE argument tells Excel to return the cumulative percentage rather than the probability density.

What does a negative z-score mean?

A negative z-score means the value is below the mean. For example, a z-score of −1.5 means the value is 1.5 standard deviations below the average. The sign straightforward indicates direction: negative is below, positive is above.

Do I need to sort my data before calculating z-scores?

No. Z-scores do not depend on the order of your data. You can calculate them whether your data is sorted, random, or in any other arrangement. The STANDARDIZE function only cares about the individual value, the mean, and the standard deviation—not the sequence.