How to Use STANDARDIZE Function in Excel - Practical Guide

Learn to use the STANDARDIZE function to convert data into standardized scores (z-scores) by centering and scaling values. This tutorial shows how to normalize datasets for statistical analysis, comparison across different scales, and identifying outliers in Excel.

Why This Matters

Standardization is essential for comparing variables with different units and scales, enabling fair statistical analysis and machine learning preparation.

Prerequisites

•Basic understanding of Excel formulas and cell references
•Familiarity with statistical concepts (mean and standard deviation)
•Access to Excel 2007 or later versions

Step-by-Step Instructions

Open your data in Excel

Launch Excel and open a spreadsheet containing numerical data you want to standardize; ensure data is organized in a column.

Create a header for standardized values

Click on a column next to your data and add a header (e.g., 'Standardized') where results will appear.

Enter the STANDARDIZE formula

In the first cell below your header, type: =STANDARDIZE(value, mean, standard_dev) replacing 'value' with your data cell, 'mean' with AVERAGE function, and 'standard_dev' with STDEV function.

Apply formula to all rows

Select the cell with your formula, copy it (Ctrl+C), then select the range where you want results and paste (Ctrl+V) to apply to entire dataset.

Verify results

Check that standardized values center around 0 with most values between -3 and +3; values far from 0 indicate outliers.

Alternative Methods

Manual calculation with basic formulas

Calculate z-scores manually using =(value-AVERAGE(range))/STDEV(range) without the STANDARDIZE function for more transparency in your spreadsheet.

Use Data Analysis ToolPak

Access Data > Data Analysis > Descriptive Statistics to standardize an entire dataset automatically without writing individual formulas.

Tips & Tricks

✓Use absolute references ($) for mean and standard deviation ranges so they don't change when copying formulas down.
✓Standardized values typically range from -3 to +3; investigate any values outside this range as potential outliers.
✓The STANDARDIZE function returns a decimal; format cells as Number with 2-3 decimal places for clarity.

Pro Tips

★Combine STANDARDIZE with conditional formatting to highlight outliers exceeding ±2 standard deviations automatically.
★Use standardized data in scatter plots and correlations for fair visual comparisons between differently-scaled variables.
★Create a data validation rule requiring standardization before statistical analysis to ensure dataset consistency.

Troubleshooting

Formula returns #DIV/0! error

This occurs when standard deviation equals zero (all values are identical). Review your data to ensure variation exists; if intentional, use conditional logic to handle this case.

Standardized values seem incorrect

Verify that mean and standard deviation formulas reference the correct range using absolute references ($A$1:$A$100). Check that AVERAGE and STDEV functions match your data range.

Formula doesn't copy down to all cells

Select the cell with the formula and drag the fill handle (small square at bottom-right corner) down to the last row, or copy-paste to the desired range.

Related Excel Formulas

AVERAGE STDEV STDEV.S STDEV.P VAR NORM.S.DIST Z.TEST

Frequently Asked Questions

What is a z-score and why should I standardize data?

A z-score measures how many standard deviations a value is from the mean, enabling fair comparison of data on different scales. Standardization is crucial for statistical analysis, machine learning, and identifying outliers.

Can I use STANDARDIZE with non-numeric data?

No, STANDARDIZE only works with numerical values. Non-numeric data must be converted to numbers first, or alternative analysis methods should be used.

What's the difference between STDEV.S and STDEV.P for STANDARDIZE?

STDEV.S calculates sample standard deviation (use for samples), while STDEV.P calculates population standard deviation (use for entire populations). Choose based on whether your data represents a sample or complete population.

Can negative standardized values indicate errors?

No, negative standardized values are normal and simply indicate values below the mean. A properly standardized dataset has both positive and negative values centered around zero.