Updated date:

How to Use the STANDARDIZE Function in Excel

Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.

The STANDARDIZE Function

The STANDARDIZE function used in Microsoft Excel is a great short cut to finding Z-scores in data sets. The function requires the variable X that represents a data point, as well as the mean and standard deviation of a data set.

The STANDARDIZE function used in Microsoft Excel is a great short cut to finding Z-scores in data sets. The function requires the variable X that represents a data point, as well as the mean and standard deviation of a data set.

Here we go through everything that you need to know to better understand the STANDARDIZE function starting with understanding what a standardized value is and how we can use it.

What is the Standardized Value of a Distribution?

When one standardizes (normalizes) a value from a distribution the intention is to adjust a value of a probability distribution into alignment. When test scores are standardized, the intention would be to align the distribution of scores of students in the class to a normal distribution.

Standardized scores are useful because they allow one to find the probability of a score occurring and the ability to compare two scores from different normal distributions.

The Purpose of the STANDARDIZE Function

The standardize function is used to return a normalized value from a distribution as stated earlier. The normalized value that is returned is also called a Z-score. The Z-score is essentially the number of standard deviations from the population mean for a data point.

The formula in the illustration below shows exactly how the STANDARDIZE function calculates a z-score. The mean is subtracted from the number to be standardized. Then, this value is divided by the standard deviation of the data set.

Z-Score Equation

Z-scores are a popular standardized score used in area of statistics. This method can be used when the mean and standard deviation of a population are known.

Z-scores are a popular standardized score used in area of statistics. This method can be used when the mean and standard deviation of a population are known.

STANDARDIZE Function Syntax

The STANDARDIZE function needs to be inputted like a formula in Excel. To manually add this formula to a cell, the following steps should be taken:

1. A cell need to be clicked

2. "=STANDARDIZE(" needs to be typed into the cell.

3. An x value must be added followed by a comma.

4. The mean is added followed by a comma.

5. The standard standard deviation is added followed by a comma.

6. Finally, a close parenthesis is used at the end and the function can be entered.

The syntax is shown below followed by details on each arguments that makes up the STANDARDIZE function.

=STANDARDIZE(x, mean, standard_dev)

X - This is required and also the value that is to be normalized.

Mean - The mean is required and better described as the arithmetic mean of the data set.

Standard_dev - Required. The standard deviation of the data set.

Inserting the STANDARDIZE Function

The STANDARDIZE function can also be inserted into an Excel spreadsheet. This method is meant for those who are unfamiliar with the function syntax. The first steps to inserting start below.

To use this method the cell where the standardized result must display is selected. Next, the formulas tab is selected and the "More Functions" button on the Excel ribbon needs to be selected. The statistical selection from the list is then chosen followed by the STANDARDIZE selection from the drop down menu.

Steps to Insert the Standardize Function

To insert the STANDARDIZE function from the formula tab you must first navigate to: Formulas→More Functions→Statistical→STANDARDIZE

To insert the STANDARDIZE function from the formula tab you must first navigate to: Formulas→More Functions→Statistical→STANDARDIZE

After the the functions arguments window appears, values or references for each argument can be added to those fields.

The arrows on the side of the field can be used as an aid to selecting a cell reference. After the reference is selected or values are entered the OK button can be clicked.

The Functional Arguments

Here in the functional arguments window arguments for the STANDARDIZE function can easily be added. Instructions are given depending on which field the cursor is in so you can understand exactly what type of data needs to be added.

Here in the functional arguments window arguments for the STANDARDIZE function can easily be added. Instructions are given depending on which field the cursor is in so you can understand exactly what type of data needs to be added.

STANDARDIZE Function Syntax Examples

Below are some examples with explanations of types of syntax that can be used in two examples.

=STANDARDIZE(A2,A3,A4)

Used with cell references - Here the standardized form can be found with cell references. Each reference represents data calculated from the same distribution where A2 represents x or the value being standardized, A3 represents the mean, and A4 represents the standard deviation.

=STANDARDIZE(15,14.2,1.7)

Used with numbers - Here the standardized form can be found directly with numbers added to the standardize formula. The first number 15 is the x, 14.2 is the mean, and 1.7 would be the standard deviation.

References

Microsoft. (n.d.). STANDARDIZE function. Retrieved January 5, 2020, from https://support.office.com/en-us/article/standardize-function-81d66554-2d54-40ec-ba83-6437108ee775.

Wikipedia. (2019, December 5). Normalization (statistics). Retrieved January 24, 2020, from https://en.wikipedia.org/wiki/Normalization_(statistics)

Related Articles

How to Use the COUNT Function in Excel

How to Use the AVERAGE Function in Excel

How to Use the ABS Function in Excel

How to Remove Errors in Excel With the IFERROR Function

How to Use the VLOOKUP Function in Excel

Converting Measurement Units: Using the CONVERT Function

To learn more about using functions in Excel I recommend the following book. I have been using the Excel Bible for years to improve my understanding of all aspects of this Microsoft product.

The Excel 2019 Bible

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2020 Joshua Crowder