How to Normalize Data in Google Sheets

Normalizing data in Google Sheets rescales your numbers to a standard range, usually 0 to 1, so you can compare columns that have different units or magnitudes. This guide walks you through the two most common methods: min-max scaling (which forces values between 0 and 1) and Z-score standardization (which centers data around 0 with a standard deviation of 1). You’ll need a Google account and a spreadsheet with your data already in columns.

What You’ll Need

A Google Sheets spreadsheet with the data you want to normalize in a single column (or multiple columns, one at a time).
Basic familiarity with entering formulas in Google Sheets.
Access to the Google Sheets menu (File > New > Spreadsheet) if you’re starting from scratch.

Step-by-Step

Open your spreadsheet in Google Sheets. Make sure your data is in one column, with no blank cells in the middle. For example, put your values in column A, starting at A1.
Create a new column for the normalized values. Click on the header of the column to the right of your data (e.g., column B) and type a label like “Normalized” in B1. This keeps your original data intact.
Find the minimum and maximum of your data. In an empty cell (e.g., D1 and D2), enter the formulas:
```
=MIN(A:A)
```
and
```
=MAX(A:A)
```
This gives you the min and max that you’ll use in the normalization formula. If your data has many rows, you can also use these functions directly inside the normalization formula to avoid extra cells.
Enter the min-max normalization formula. In cell B2, type:
```
=(A2-MIN($A$2:$A$100))/(MAX($A$2:$A$100)-MIN($A$2:$A$100))
```
Replace $A$2:$A$100 with the actual range of your data (adjust the row numbers). The dollar signs lock the range so it doesn’t change when you copy the formula down. This formula subtracts the minimum from each value and divides by the range (max-min), giving a result between 0 and 1.
Copy the formula down the column. Click on cell B2, then hover over the small blue square in the bottom-right corner of the cell until your cursor becomes a plus sign. Click and drag down to fill the formula for all rows that have data in column A. Alternatively, double-click the blue square to auto-fill down to the last row of data.
Check your results. The smallest value in your data should now be 0, and the largest should be 1. All other values should be between 0 and 1. If you have negative values, they will become negative after normalization (since min-max scaling preserves the relative distances).
For Z-score standardization (optional), use this formula instead in B2:
```
=(A2-AVERAGE($A$2:$A$100))/STDEV($A$2:$A$100)
```
This subtracts the mean and divides by the standard deviation. The result will have a mean of 0 and a standard deviation of 1. This is useful when you want to handle outliers better, as it doesn’t force values into a fixed range.
Copy the Z-score formula down just like you did for min-max. Values will be both positive and negative, with most falling between -3 and 3.

Common Mistakes

Not locking the range with dollar signs. If you omit the $, copying the formula down will shift the min/max range, causing incorrect normalization for each row.
Including the header row in the range. If your data starts in row 1 with a header, using A:A will include text, and the MIN/MAX functions will ignore text but may cause errors if you have non-numeric data. Always specify the data range only (e.g., A2:A100).
Using the wrong standard deviation. Google Sheets has STDEV (sample) and STDEVP (population). If your data is a sample of a larger population, use STDEV. If it’s the entire population, use STDEVP. Using the wrong one changes the Z-scores slightly.
Forgetting to handle blank cells. If you have blank cells in your data range, the formula will treat them as 0, which can skew your min/max and normalization. Filter out blanks or use a different range.
Normalizing columns with different units together. If you have two columns with different units (e.g., height in cm and weight in kg), normalize each column separately, not combined, unless you intend to create a combined index.

Tips

Use a separate sheet for normalized data to keep your original data clean and avoid accidental overwrites.
Consider using the ARRAYFORMULA function to apply normalization to an entire column at once. For example, in B2, type:
```
=ARRAYFORMULA((A2:A100-MIN(A2:A100))/(MAX(A2:A100)-MIN(A2:A100)))
```
This fills the whole column without dragging, but you must ensure the range sizes match.
If you have outliers, min-max scaling will compress the rest of the data. Z-score is more robust to outliers because it uses mean and standard deviation, which are less affected by extreme values.
For large datasets, use the QUERY or FILTER functions to exclude blank rows before normalizing, or sort your data first to identify any anomalies.
Document your normalization method in a cell comment or a separate notes column, so you remember which method you used when you revisit the sheet.

Troubleshooting

#DIV/0! error: This means the denominator is zero. This happens when all values in your range are identical (max-min = 0). In that case, normalization is not meaningful; you can set the result to 0 or 1 manually, or use Z-score instead (which will also produce zeros).
#VALUE! error: This usually means your range includes non-numeric text. Check for any cells with text, errors, or formulas that produce text. Remove or correct them.
Results are not between 0 and 1: If you’re using min-max and get values outside [0,1], double-check that you locked the range with $ and that your min/max are correct. Also, ensure you didn’t accidentally use a different range for min and max.
Formula not copying correctly: If dragging the fill handle gives you the same value for all rows, your range might not be locked properly, or you might have set calculation to manual. Go to File > Settings > Calculation and set it to “On change and every minute” or “On change”.
Z-scores look too large or too small: This can happen if your data has extreme outliers. Consider using a robust standardization method, or check for data entry errors.

Frequently Asked Questions

Q: What is the difference between normalization and standardization?
Normalization (min-max) rescales data to a fixed range, usually 0 to 1. Standardization (Z-score) rescales data to have a mean of 0 and a standard deviation of 1, but the range is not fixed. Choose based on your use case: normalization for bounded data, standardization for data with outliers or when you need a normal-like distribution.

Q: Can I normalize data with negative values?
Yes, min-max normalization will produce negative values if your data has negatives, because the formula preserves the original distribution. For example, if your min is -10 and max is 10, a value of -5 becomes 0.25. This is fine if you want to preserve relative distances.

Q: How do I normalize multiple columns at once?
You can apply the same formula to each column separately, or use ARRAYFORMULA with a range that includes multiple columns, but you’ll need to adjust the formula to handle each column independently. The easiest way is to normalize each column one at a time.

Q: What if my data has missing values?
Blank cells are ignored by MIN, MAX, AVERAGE, and STDEV functions, but the normalization formula will treat a blank as 0, which is wrong. Either delete blank rows, or use a formula that checks for blanks, like =IF(ISBLANK(A2),"",(A2-MIN(...))/...).

Q: Does normalization change the shape of my data distribution?
Min-max scaling preserves the original distribution shape, just rescales the axis. Z-score standardization also preserves shape, but shifts and scales it. Neither method makes data normally distributed; they only change the scale.

Q: Can I undo normalization?
Yes, if you know the original min and max (or mean and standard deviation), you can reverse the formula. For min-max, the inverse is: original = normalized * (max – min) + min. Keep the min and max values somewhere safe if you need to reverse the process.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →