If you are looking for special tricks to know how to scale data in Excel, you’ve come to the right place. There are numerous ways to do scaling data in Excel. This article will discuss the details of 3 methods to do this task in Excel. Let’s follow the complete guide to learn all of this.
How to Do Data Scaling in Excel: 3 Easy Methods
In the following section, we will use three effective and tricky methods to scale data in Excel. This section provides extensive details on these methods. For our demonstration, we’ll use the dataset below, which includes student names and corresponding grades. We will now scale the data in the Marks column according to our preferences.
You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.
1. Combine MIN and MAX Functions to Scale Data in Excel
Here, we are going to demonstrate how to scale in Excel using MIN and MAX functions. In Microsoft Excel, the MIN function is generally used to extract the lowest or smallest value from a range of cells containing numerical data only. As part of Excel STATISTICAL functions, the MAX function returns the largest value among the arguments given. Let’s walk through the following steps to do the task.
📌 Steps:
- First, to scale the Marks column data write down the following formula.
=(C5-MIN(C:C))/(MAX(C:C)-MIN(C:C))
- Next, press Enter.
- Therefore, you will get the following scaling data.
- Next, drag the Fill Handle icon to fill the other cells with the formula.
- Consequently, you will get the following scaling data of the Marks column.
💡 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)): Finally the whole formula will return the scale value of 0.532.
Read More: How to Do Multidimensional Scaling in Excel
2. Perform Data Scaling Through Paste Special Feature
In this method, we will demonstrate how to perform data scaling through the paste special feature. In order to demonstrate our point, we will use the same dataset in this instance. Follow the following process to complete the task.
📌 Steps:
- First, copy the data from the Marks column and paste it into the Scaling Data column.
- After that, enter 100 in any cell, copy it, choose the cell you would like to scale, and then right-click on the cells and choose Paste Special from the menu that appears.
- Therefore, Paste Special window will appear.
- Next, select Divide from the Operation option.
- Then, click on OK.
- As a result, you will receive the Marks column scaling data listed below.
Read More: How to Scale Data from 1 to 10 in Excel
3. Apply AVERAGE & STDEV Functions
In this method, we will apply AVERAGE and STDEV functions to scale data. In Excel, the AVERAGE function falls under the statistical functions category and provides the average value of an input argument. Using the STDEV function in Excel, the standard deviation of any data collection can be calculated. In this case, we will scale a collection of data values so that their mean is 0 and their standard deviation is 1. We use the same dataset in this case to illustrate our point. To accomplish the task, use the steps below.
📌 Steps:
- First, to calculate the mean value, write down the following formula.
=AVERAGE(C5:C13)
- Next, press Enter.
- Therefore, you will get the following average value.
- Next, to calculate the standard deviation value, write down the following formula.
=STDEV(C5:C13)
Here, C15 is the mean value, and C16 is the standard deviation value.
- Then, press Enter.
- Therefore, you will get the following standard deviation value.
- Next, to calculate scale data value, select the cell and write down the following formula.
=(C5-$C$15)/$C$16
- Then, press Enter.
- Therefore, you will get the following scale data value.
- Next, drag the Fill Handle icon to fill the other cells with the formula.
- As a result, the scaling information for the Marks column will be as follows.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.
Conclusion
That’s the end of today’s session. I strongly believe that from now you may be able to perform scaling data in Excel. If you have any queries or recommendations, please share them in the comments section below. Keep learning new methods and keep growing!
<< Go Back to Scaling Formula in Excel | Excel for Statistics | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
