# How to standardize data in Excel

You can watch a video tutorial here.

Standardizing data is the process of rescaling the values in a dataset to a common scale so that it can be compared to other datasets or to understand the variance in the values. This is commonly used in machine learning to compare datasets that have different scales or units of measurement. In Excel, this can be done using the following functions:

1. AVERAGE(): this returns the mean of a set of values

a. Syntax: AVERAGE(range)

i. range: the range of values

2. STDEV.P(): this returns the standard deviation of a set of numbers

a. Syntax: STDEV.P(range)

i. range: the range of values

3. STANDARDIZE(): this returns a standardized value based on the mean and standard deviation of the dataset

a. Syntax: STANDARDIZE(number, mean, std)

i. number: the number to be standardized

ii. mean: the mean of the dataset

iii. std: the standard deviation of the dataset

### Step 1 – Calculate the mean of each set of data

– Select the destination cell

– Type the formula using cell references:

=AVERAGE(range of Biology (out of 100))

– Press **Enter**

– Select the destination cell

– Type the formula using cell references:

=AVERAGE(range of Maths (out of 85))

– Press **Enter**

### Step 2 – Calculate the standard deviation of each set of data

– Select the destination cell

– Type the formula using cell references:

=STDEV.P(range of Biology (out of 100))

– Press **Enter**

– Select the destination cell

– Type the formula using cell references:

=STDEV.P (range of Maths (out of 85))

– Press **Enter**

### Step 3 – Create the formulas for standardization

– Select the destination cell

– Type the formula using cell references:

=STANDARDIZE(Biology (out of 100), $Mean$, $Std deviation$)

– Press **Enter**

– Select the destination cell

– Type the formula using cell references:

=STANDARDIZE( Maths (out of 85), $Mean$, $Std deviation$)

– Press **Enter**

– In both formulas, make the ‘Mean’ and ‘Std deviation” constant by selecting the cell references and pressing** F4 **to add the dollar signs ($)

### Step 4 – Copy the formulas

– Select the cells with the formulas

– Using the fill handle, drag the formula to the remaining cells

OR

a. Select the cell with the formula and press **Ctrl+C** or choose **Copy** from the context menu (right-click)

b. Select the rest of the cells in the column and press **Ctrl+V** or choose **Paste** from the context menu (right-click)

### Step 5 – Check the result

– Go to **Home > Number**

– Click on the **Decrease Decimal **button

– Each dataset has been scaled to standardized values

– Each student’s performance in each subject can now be compared