Musiha Mahfuza Mukta
When you have a dataset that contains numbers with more than 4 digits, then you may need to scale them for lowering those digits. So, if you are looking for how to lower digits of numbers by scaling in Excel then you have come to the right place. Today, I will demonstrate how to do scaling numbers in Excel.
Furthermore, for conducting the session, I will use Microsoft 365 version.
Download Practice Workbook
You can download the practice workbook from here:
7 Effective Methods for Scaling Different Numbers in Excel
Here, I will describe seven suitable and easy methods to do scaling numbers in Excel. In addition, for your better understanding, I’m going to use a sample dataset. Which contains two columns. These columns represent some collected data of a company about the unit number of sales for the 1st seven months. The dataset is given below.
1. Combine Excel MAX & MIN Functions to Scale Numbers
Here, you can use a combination of MAX and MIN functions to do the scaling of numbers in Excel. Along with this, you may change the Number format to compare the numbers with the easy and one-digit scale. Now, let’s follow the steps.
Steps:
- First, you must select a new cell D5 where you want to keep the scaled number.
- Secondly, you should use the formula given below in the D5 cell.
=(C5 - MIN(C:C)) / (MAX(C:C) - MIN(C:C))
- Thirdly, press ENTER to get the result.
🔎 Formula Breakdown:
- Here, in this formula, the MIN function will return the minimum or lowest value from the given array.
- So, MIN(C:C)—> turns 600.
- Then, the MAX function will return the highest or maximum value from the mentioned array.
- Thus, MAX(C:C)—> gives 1900.
- Therefore, (MAX(C:C) – MIN(C:C))—> returns 1300.
- Lastly, (1000-600)/1300 gives the final result.
At this time, I will change the Number format for lowering the total decimals. Moreover, if you notice then you can see that the scaled numbers are in General format.
- Firstly, select the D5 cell.
- Secondly, from the Home tab >> go to Number group >> then click 8 times on the Decrease Decimal icon.
As a result, you can see that the scaled number is now in Number format.
- Then, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.
Lastly, you will get all the scaled numbers.
Read More: How to Scale Data from 1 to 10 in Excel (3 Quick Methods)
2. Use of Format Cells Feature for Scaling Numbers in Excel
Here, I will do scaling the numbers using the Format Cells feature. So, follow the steps below.
Steps:
- Firstly, you have to select the data range. Here, I have selected C5:C11.
- Secondly, you need to press the CTRL+1 keys to open the Format Cells dialog box directly.
Also, you can use the Context Menu Bar or the Custom Ribbon to use the Format Cells feature. In the case of using the Context Menu Bar, select the data range >> Right-Click on the data >> choose the Format Cells option.
On the other hand, in the case of using Custom Ribbon, select the data range >> from the Home tab >> go to the Format feature >> choose the Format Cells command.
Lastly, a dialog box named Format Cells will appear.
- Now, from that dialog box, you have to make sure that you are on the Number menu.
- Then, go to the Custom option.
- After that, in the Type box >> write down the following format.
 #,###,.##
Additionally, you can see the sample format immediately, in the Sample box.
- Finally, you have to press OK to get the changes.
As a result, you will see the following scaled numbers.
Read More: How to Create a Rating Scale in Excel (4 Easy Ways)
3. Apply Generic Formula to Scale Different Numbers
Also, you can use a generic formula to scale numbers in Excel. The steps are given below.
Steps:
- First, you must select a new cell D5 where you want to keep the scaled number.
- Secondly, you should use the formula given below in the D5 cell.
=0.01*C5-10
- Thirdly, press ENTER to get the result.
Here, you don’t need to change the formatting.
- Subsequently, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.
As a result, you will get all the scaled numbers.
Read More: Scaling Formula in Excel (6 Practical Examples)
4. Merge INT, CEILING, and MAX Functions to Resize Numbers
Here, you can merge some Excel functions for scaling numbers. In addition, those functions are INT, CEILING, and MAX functions. Let’s see the steps.
Steps:
- First, you must select a new cell D5 where you want to keep the scaled number.
- Secondly, you should use the formula given below in the D5 cell.
=INT(CEILING(C5,MAX($C$5:$C$11)/10)*10/MAX($C$5:$C$11))
- Thirdly, press ENTER to get the result.
🔎 Formula Breakdown:
- Here, in this formula, the MAX function will return the highest or maximum value from the mentioned array.
- Thus, MAX($C$5:$C$11)—> gives 1900.
- Then, the CEILING function rounds up a number to the nearest top integer.
- Therefore, CEILING(C5,MAX($C$5:$C$11)/10)—> returns 1140.
- Lastly, the INT function gives integer form by rounding down.
- So, INT(1140*10/1900)—> returns 6.
- Consequently, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.
Finally, you will get all the scaled numbers.
Read More: How to Do Data Scaling in Excel (3 Easy Methods)
5. Unite STANDARDIZE, AVERAGE, and STDEV.S Functions in Excel
Here, you can unite some other Excel functions for scaling numbers. In addition, those functions are STANDARDIZE, STDEV.S, and AVERAGE functions. So, let’s see the steps.
Steps:
- First, you must select a new cell D5 where you want to keep the scaled number.
- Secondly, you should use the formula given below in the D5 cell.
=STANDARDIZE(C5,AVERAGE($C$5:$C$11),STDEV.S($C$5:$C$11))
- Thirdly, press ENTER to get the result.
🔎 Formula Breakdown:
- Here, in this formula, the STDEV.S function will find out the standard deviation of a given sample. Actually, this function doesn’t count any logical value or text within that sample.
- So, STDEV.S($C$5:$C$11)—> returns 437.525509460387.
- Then, the AVERAGE function will calculate the average value of the mentioned array.
- Thus, AVERAGE($C$5:$C$11)—> gives 1314.28571428571.
- Finally, the STANDARDIZE function computes the normalized value with the help of the average value and standard deviation.
- After that, change the cell value format to Number from the General format. Also, you can use the Decrease Decimal icon for lowering the decimal points.
- Then, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.
And you will get all the scaled numbers.
6. Combine ROUND, MAX & MIN Functions to Scale Numbers
Here, you can use another combination of some Excel functions for scaling numbers like ROUND, MIN, and MAX functions. So, let’s see the steps.
Steps:
- First, you must select a new cell D5 where you want to keep the scaled number.
- Secondly, you should use the formula given below in the D5 cell.
=ROUND((C5-MIN($C$5:$C$11))/(MAX($C$5:$C$11)-MIN($C$5:$C$11))*10,0)
- Thirdly, press ENTER to get the result.
🔎 Formula Breakdown:
- Here, in this formula, the MIN function will return the minimum or lowest value from the given array.
- Thus, MIN($C$5:$C$11)—> turns 600.
- Then, the MAX function will return the highest or maximum value from the mentioned array.
- So, MAX($C$5:$C$11)—> gives 1900.
- After that, the ROUND function will round a number with certain digits.
- Lastly, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.
So, you will get all the scaled numbers.
7. Employing Paste Special Feature for Resizing Numbers in Excel
You can use the Paste Special feature to resize the numbers in Excel. But, to do so, at first you have to modify your dataset. So, follow the steps given below.
Steps:
- Firstly, write 1000 in the E5 cell. The highest value of my dataset is 1900 (4 digits). Here, you should use a particular number based on your dataset.
- Secondly, copy the E5 cell by pressing CTRL+C.
- Consequently, select the data up to which you want to scale.
- After that, right-click on the Mouse.
- Then, from the Context Menu Bar >> choose Paste Special option.
As a result, you will see a new dialog box named Paste Special.
- First, choose Values from the Paste option.
- Then, select Divide from the Operation option.
- Subsequently, press OK.
Finally, you will see the following scaled numbers.
Practice Section
Now, you can practice the explained method by yourself.
Conclusion
I hope you found this article helpful. Here, I have described 7 suitable methods for scaling numbers in Excel. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
