How to Do Data Scaling in Excel (3 Easy Methods)

 

We’ll use the dataset below, which includes student names and corresponding grades. We will scale the data in the Marks column.

sample dataset


Method 1 – Combine MIN and MAX Functions to Scale Data via Min-Max Normalization

Steps:

  • To scale the Marks column data, use the following formula.

=(C5-MIN(C:C))/(MAX(C:C)-MIN(C:C))

  • Press Enter.
  • You will get the following scaling data.

use MIN and Max functions to Do Data Scaling in Excel

  • Drag the Fill Handle icon down to fill the other cells with the formula.
  • You will get the scaling data in the entire Marks column.

Data Scaling in Excel

Note:

  • The MIN function does not count blank cells, so it will not return a blank cell if your chosen range of cells includes one. Instead, it will take the least significant value from all other non-empty cells.
  • You can input up to 255 arguments into the MIN function.

How Does the Formula Work?

  • C5-MIN(C:C): Once the minimum value of the C column has been determined and subtracted from cell C5, the formula C5-MIN(C:C) will return the value of 33.
  • (MAX(C:C)-MIN(C:C): This formula will yield the result of 62 after calculating the maximum value of the C column and deducting the minimum value from the maximum value.
  • (C5-MIN(C:C))/(MAX(C:C)-MIN(C:C)): The whole formula will return the scale value of 0.532.

Method 2 – Perform Data Scaling Through the Paste Special Feature by Dividing with 100

Steps:

  • Copy the data from the Marks column and paste it into the Scaling Data column.

use paste special feature to Do Data Scaling in Excel

  • Enter 100 in any cell outside the dataset and copy it.
  • Select the new column made by pasting Marks.
  • Right-click on the cell and choose Paste Special from the menu that appears.

click on paste special

  • The Paste Special window will appear.
  • Select Divide from the Operation option.
  • Click on OK.

  • You will receive the Marks column scaling data listed below.

Do Data Scaling in Excel

Note that this returns different values than traditional data scaling via min-max normalization.

Read More: How to Scale Data from 1 to 10 in Excel


Method – Apply AVERAGE and STDEV Functions

We will scale the data values so that their mean is 0 and their standard deviation is 1. We use the same dataset.

apply average and stdev functions to Do Data Scaling in Excel

Steps:

  • 15 is the mean value, and C16 is the standard deviation value.
  • To calculate the mean value, use the following formula.

=AVERAGE(C5:C13)

  • Press Enter.
  • You will get the following average value.

calculate mean value

  • To calculate the standard deviation value, use the following formula.

=STDEV(C5:C13)

  • Press Enter.
  • You will get the following standard deviation value.

  • To calculate the scale data value, select the first cell and use the following formula.

=(C5-$C$15)/$C$16

  • Press Enter.

  • Dag the Fill Handle icon down to fill the other cells with the formula.

perform Data Scaling in Excel


Download the Practice Workbook


<< Go Back to Scaling Formula in ExcelExcel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo