# How to standardize data in Excel

Standardizing data transforms values in a dataset so they have a mean of zero and a standard deviation of one. This process, also known as normalizing or z-scoring, makes the data consistent and suitable for statistical analysis.

Toggle

## Using the STANDARDIZE Function

Excel’s built-in STANDARDIZE function simplifies this process. It requires three arguments:

• The value to standardize.
• The mean of the dataset.
• The standard deviation of the dataset.

The function returns the z-score for each value.

### Example: Standardizing Test Scores

Suppose you have a dataset of test scores in column A, with a mean of 75 and a standard deviation of 10. To standardize these scores, use the following formula in column B: =STANDARDIZE(A2, 75, 10)

This formula calculates the z-score for the test score in cell A2. Copy this formula down column B to get z-scores for all test scores in column A.

### Understanding Z-Scores

Z-scores indicate how far each test score is from the mean, measured in standard deviations:

• A z-score of 1.5 means the test score is 1.5 standard deviations above the mean.
• A z-score of -0.5 means the test score is 0.5 standard deviations below the mean.

### Benefits of Standardizing Data

Standardizing data can:

• Reduce the impact of outliers.
• Address scale differences and measurement errors.
• Facilitate comparisons across different groups or populations.
• Enable statistical tests that assume normality, such as t-tests or ANOVA.

## Steps to Standardize Data in Excel

Use AVERAGE and STDEV.P functions to calculate the mean and standard deviation.

=AVERAGE(A2:A100) // for mean
=STDEV.P(A2:A100) // for standard deviation

Assuming the mean is in cell B1 and standard deviation is in cell B2, enter the formula: =STANDARDIZE(A2, \$B\$1, \$B\$2)

Drag the fill handle to copy the formula down column B.

### Example Implementation

Suppose your data is in the range A2:A100. Here’s how you can standardize it:

Calculate Mean and Standard Deviation:

Mean in B1: =AVERAGE(A2:A100)

Standard Deviation in B2: =STDEV.P(A2:A100)

Standardize Data: In cell B2: =STANDARDIZE(A2, \$B\$1, \$B\$2)

Copy this formula down to B100.

By following these steps, you can standardize your data in Excel, making it more suitable for various types of statistical analyses.