DISCOVER
×

How to Calculate a Z-Score in Excel 2007

A z-score, also called a "standard score" or a "z-value," is a statistical tool that tells the readers of your data how much a number deviates from a mean. To manually calculate a z-score, you'd have to subtract the mean from your data point and divide that result by the standard deviation. A much easier way to calculate z-scores is by using a function that is available to you in Microsoft Excel 2007.

Open Excel 2007 and a blank spreadsheet.

Enter all of your data in one column. Each separate piece of data should be in its own cell.

Click on an empty cell within the spreadsheet and then calculate the mean of your data by entering an average function in the formula bar and hitting "Enter." The mean will be displayed in the empty cell. If your data was listed in cells B3 to B52, your function would look like this: "Average (B3:B52)."

Click on another empty cell within your spreadsheet and then calculate the standard deviation of your data by entering a standard deviation function in the formula bar and hitting "Enter." If your data was listed in cells B3 to B52, your function would look like this: "STDEV (B3:B52)."

Click on an empty cell in the spreadsheet and then click on the "fx" button, located beside the formula bar. Enter the cell number that you would like to calculate a z-score for in the "X" box. Enter the mean you calculated in Step 3 in the "Mean" box. Enter the standard deviation you calculated in Step 4 in the "Standard_dev" box.

Click "OK." The z-score for the number you entered in the "X" box will be displayed in the empty cell you selected in Step 5.

Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author

Based in Halifax, Nova Scotia, Jordan Whitehouse has been writing on food and drink, small business, and community development since 2004. His work has appeared in a wide range of online and print publications across Canada, including Atlantic Business Magazine, The Grid and Halifax Magazine. Whitehouse studied English literature and psychology at Queen's University, and book and magazine publishing at Centennial College.