How to Use Z-Scores to Analyze Data in Excel
To calculate a Z score in Excel, subtract the mean from a value and divide by the standard deviation: =(A2-AVERAGE($A$2:$A$101))/STDEV.S($A$2:$A$101), or use the built-in =STANDARDIZE(A2, AVERAGE($A$2:$A$101), STDEV.S($A$2:$A$101)). The result tells you how many standard deviations the value sits above (positive) or below (negative) the average. Values beyond about +2 or -2 are unusual and worth a closer look, which makes Z scores a quick way to flag outlier returns, prices, or ratios.
Try it in your own spreadsheet
Get the free MarketXLS sample workbook (.xlsx).
No account needed. MarketXLS live data requires a paid plan. Preview workbook
Explore more videos
What are Z Scores?
A Z score is a measure of standard deviation from the mean of a set of data. Z scores are commonly used in statistics to measure the amount of deviation from the mean for any given value. The Z score formula is:
z = (x – μ) / σ
where
x – is a single value from the data set
μ – is the mean of the data set
σ – is the standard deviation of the data set
Data Standardization in Excel
Data standardization converts values measured on different scales into Z scores, so they share a mean of 0 and a standard deviation of 1. Standardizing does not make data normally distributed; it only rescales it. Excel's STANDARDIZE function does this in one step.
Calculating Z Scores in Excel
Excel calculates Z scores with the same formula shown above: enter =STANDARDIZE(x, mean, standard_dev) next to each value and copy it down. The Z scores can then be used for further analysis, such as plotting a bell curve for the data, creating a Z score table or interpreting the data using a Z score interpretation method.
Excel Descriptive Statistics and Statistical Analysis
In addition to calculating Z scores, Excel provides many tools for descriptive statistics and statistical analysis. These include functions for calculating quantiles, median, mode, variance, standard deviation and other measures of data spread.
How MarketXLS Can Help
MarketXLS is an Excel add-in that pulls stock prices, historical data, and fundamentals into cells, for example =Last("AAPL"), =PERatio("AAPL"), or =QM_GetHistory("AAPL") for a price history. Once the data is in the sheet, Excel's STANDARDIZE, AVERAGE, and STDEV.S turn it into Z scores, for example to find stocks whose P/E is far from their peer group's average. Quotes are 15-minute delayed on the Standard plan and real-time on the Advanced and Business plans.
Related financial-analysis guides
Altman Z-Score bankruptcy-risk dashboard guide
Value-At-Risk Var Using Excel (With Marketxls)