How to normalize data in Google Sheets and Excel

While working with data in Google Sheets and Excel, it is crucial to know how to normalize data. Normalizing data is to make the data fit in a particular range. The Z-score is the most common way of normalizing data in Google Sheets and Excel. This article will discuss ways of normalizing data in Excel and Google Sheets.

To normalize data in Google Sheets

Three crucial steps need to be followed while normalizing data in Google Sheets:

Find the Mean of the data

Here are the steps to follow:

1. Visit the Google account and log in using your email detail (That is, https://www.google.com/account).

2. From the Google Apps, click on the Sheets icon and select the existing Sheet.

3. On the data you want to normalize, locate an empty cell and name it Mean.

4. Click on the cell next to the one named Mean, and type this formulae =Average (


5. Select all the cell that contains your data. For example, =AVERAGE (A2:A7)

6. Finally, hit the Enter button.

Find the standard deviation

Steps:

1. Click on the cell below the mean cell, and name it Standard deviation.

2. Click on the cell next to the one named Standard deviation, and type this formulae =STDEV (

3. Select the entire cells that contain your data. For example, =STDEV (A2:A7)


4. Finally, hit the Enter button.

Normalize the data

This is the last step of normalizing your data. By now, the mean and standard deviation of your dataset, go ahead and normalize the dataset.

Steps:

1. Select a column and name it Normalized data.


2. On the first cell in the Normalized data column, type this formulae = STANDARDIZE (

3. Then, select the first cell that contains your data. For example, = STANDARDIZE (A2,


4. Next, select the Mean of your dataset. For example, = STANDARDIZE (A2, mean,

5. Then, select the standard deviation of your dataset. For example, = STANDARDIZE (A2, mean, std deviation)


6. Finally, hit the Enter button.

7. Use the dragging tool to apply formulae to all the cells that have the data.

To normalize data in Excel

Just like in Google Sheets, Excel involves three steps of normalizing data. They include:

Calculating mean

Steps:

1. Open the Excel application.

2. On the data you want to normalize, locate an empty cell and name it Mean.


3. Type this formulae =Average (. For example, =AVERAGE (A2:A7)

4. Finally, hit the Enter button.

Calculating the standard deviation

Steps:

1. Click on the cell below the mean cell, and name it Standard deviation.

2. Click on the cell next to the one named Standard deviation, and type this formulae =STDEV (range of values). For example, =STDEV (A2:A7)


3. Finally, hit the Enter button.

Normalizing the data

Steps:

1. Select a column and name it Normalized data.


2. On the first cell in the Normalized data column, type this formula = STANDARDIZE (A2, mean, std deviation).


3. Finally, hit the Enter button.

4. Use the dragging tool to apply formulae to all the cells that have the data.

Leave a Comment