To reduce decimal points in Excel:
- Select the cell where you want to decrease decimal points.
- Go to Home tab > Number group > Decrease Decimal.
Now, you will see that decimal points have been decreased.
Reducing decimal points means decreasing the precision or level of detail in numerical values, resulting in a rounded representation of the original data. Reducing decimals in Excel makes numbers look cleaner and easier to read in spreadsheets, especially when working with currency or percentage data.
In this Excel tutorial, you will learn how to reduce decimals in Excel.
Consider a dataset where values contain 5 decimal decimal places. To make the data cleaner, I have reduced decimal places to 2 places.
10 Ways to Reduce Decimals in Excel
You can reduce decimals using the Decrease Decimal button, Format Cells dialog box, and Excel functions.
Here are 10 methods to reduce decimals in Excel:
Using Decrease Decimal Button
Applying the Decreasing Decimal button is the quickest way to reduce decimals from selected cells. Every time you press the button, a decimal point will be reduced.
To reduce decimal points by using the Decrease Decimal button, apply the following steps:
- Select a range of cells.
- Go to Home tab > Number group > Decrease Decimal button.
- Click on the button one or multiple times.
This will reduce the decimals in your selected data range.
Note: Reducing the number of decimal places using the Decrease Decimal button does not change the underlying values in the cells; it only changes the way the numbers are displayed. The actual precision of the numbers remains unchanged.
Using Format Cells Dialog Box
Format Cells in Excel is a versatile tool for customizing cell appearance, including number formats, fonts, and alignment. Using the Format Cells dialog box, you can reduce the decimal points.
To reduce decimal points using the dialog box, follow the steps below:
- Select a range of cells.
- Press Ctrl+1 to open the Format Cells dialog box.
- In the Format Cells dialog box:
- Choose Accounting/Currency/Percentage/Scientific from Category based on your data type.
- Put the desired number in the Decimal places box.
Here, I have entered 2. - Click OK.
This will reduce the decimals to 2 places in your selected cells.
Applying ROUND Function
The ROUND function in Excel rounds a number to a specified number of decimal places. If the digit right after the rounding position is 5 or higher, the digit at the rounding position is increased by 1. Otherwise, the digit at the rounding position remains unchanged.
To reduce the decimal points using the ROUND function, follow the steps below:
- Select the cell where you want to decrease decimal points.
- Insert the following formula:
=ROUND(D5,3)
Here, D5 represents the cell with decimal value and 3 indicates the number of desired decimal places. Replace these arguments with your required values.
- Press the Enter key to see the reduced decimal value.
- Use the Fill Handle to copy the formula in the remaining cells.
Note: You can also find the ROUND function from Formulas > Math & Trig > ROUND. When the Function Argument window opens, enter the cell reference or number for which you want to reduce the decimal and the number of decimal places you want in the Number and Num_digits boxes respectively, and press OK.
Using ROUNDUP Function
The ROUNDUP function in Excel rounds up a number to a specified decimal place. For example, =ROUNDUP(15.678, 1) results in 15.7.
To reduce the decimal points using the ROUNDUP function, follow the steps below:
- Select the cell where you want to decrease decimal points.
- Inset the formula:
=ROUNDUP(D5,2)
Here, D5 represents decimal value and 2 represents desired decimal places. Replace these arguments with your required values. - Press Enter.
This will reduce the decimal places. - Drag the Fill Handle icon down to copy the formula in the remaining cells.
Thus, you can reduce decimals from a number in Excel using the ROUNDUP function.
Applying ROUNDDOWN Function
The ROUNDDOWN function, on the other hand, always rounds down a number to a specific number of digits.
To reduce the decimals applying the function, you can follow the steps below:
- Select the cell where you want to decrease decimal points.
- Inset the formula:
=ROUNDDOWN(D5,1)
Here, D5 represents the cell with decimal value and 1 indicates the number of desired decimal places. Change these arguments with your required values.
- Press Enter.
Now you will see the reduced decimal value in the selected cell.
- You can copy the formula in the remaining cells using the Fill Handle tool.
Using MROUND Function
The MROUND function in Excel rounds a number to the nearest multiple specified by the user. For instance, =MROUND(23.456, 5) rounds to the nearest multiple of 5, yielding 25.
You can use the MROUND function to reduce decimals by rounding them to a specified multiple. Here’s how:
- Select the cell where you want to decrease decimal points.
- Inset the formula:
=MROUND(D5*10,1)/10
Here, D5 is the decimal data, and 1 sets the rounding interval, like rounding to the nearest whole number when divided by 10. You can replace these values with your desired multiple. - Press the Enter key to see the reduced decimal value.
- Finally, drag down the Fill Handle icon to copy the formula in the remaining cells.
Thus you can reduce decimals from a number in Excel using the MROUND function.
Using TRUNC Function
The TRUNC function in Excel keeps a specific number of decimal places by removing the rest of the decimals.
Here’s how you can use the TRUNC function to reduce decimal points in Excel:
- Select a cell.
- Inset the formula:
=TRUNC(D5,2)
Here, D5 is the cell with decimal points and 2 is the decimal points that will remain. - Press Enter.
This will reduce the decimal value.
- Finally, drag down the Fill Handle icon to fill the remaining cells.
Applying INT function
The INT function in Excel rounds down a number to its nearest integer. However, you can still use it to reduce decimals and return a specific number of decimal places.
If you want to return n decimal places after reducing decimals, then, you have to multiply the argument in the INT function with 10n and divide the result from the INT function with 10n. For example, if you want to round a cell and return only 2 decimal places, your multiplier is 102 i.e. 100.
Here are the steps to reduce decimal places using the INT function:
- Select the cell where you want to decrease decimal points.
- Inset the formula:
=INT(D5*1000)/1000
Here, D5 is the decimal data, and 1000 represents the multiplier for rounding to 3 decimal places (as 103 = 1000). - Press Enter.
You will see the reduced decimal value in the selected cell.
- Drag the Fill Handle icon down to fill the remaining cells.
Note: The INT function makes a negative number more negative by rounding it down. For example, if you use =INT(-33.456), the INT function will return -34.
Combining IF and FLOOR Functions
The FLOOR function in Excel rounds a number down to the nearest specified multiple. For example, =FLOOR(42.789, 5) rounds down to the nearest multiple of 5, resulting in 40. This function is often used in mathematical calculations to round down any number.
To reduce decimals with a combination of IF and FLOOR functions, follow the steps below:
- Select the cell where you want to decrease decimal points.
- Inset the formula:
=FLOOR(D5, IF(D5>0, 0.001, -0.01))
Here, D5 is the decimal value, and the formula rounds it down to the nearest smaller multiple of 0.001 if positive, or to the nearest smaller multiple of -0.01 if negative. Change the cell reference and multipliers according to your required values.
- Press Enter to get the reduced decimal value.
- Drag the Fill Handle icon down to copy the formula in the remaining cells.
Use CEILING Function
The CEILING function rounds up a number to the nearest integers. Like the FLOOR function, this function is also used in mathematical fields to round up a number.
To reduce the decimals with the CEILING function in Excel, you can follow the steps below:
- Select the cell where you want to decrease decimal points.
- Inset the formula:
=CEILING(D5,0.01)
Here, D5 is the decimal number you want to round up, and 0.01 is how much you want to round it up by.
- Hit the Enter key.
Now, you will see the reduced decimal value.
- You can use the Fill Handle to copy the formula in the remaining cells.
How to Remove Decimals Permanently in Excel
If you reduce decimals using the Decrease Decimals or Format Cells feature in Excel, only the displayed value will change and the actual precision of the numbers remains unchanged.
To permanently remove decimals in Excel, you have to enable the Set precision as displayed option from Excel Advanced Options. Here’s how:
- Go to File.
- Select Options.
Then, the Excel Options window will open. - In the Excel Options window:
- Select the Advanced from the left-side pane.
- Enable the Set precision as displayed option under When calculating this workbook.A message box will appear to ensure your last change.
- Press OK.
- Afterward, in the Excel Options window, click OK.
This will take you to the worksheet. - Now, if you select the range with decimals and click the Decrease Decimals button or use the Format Cells dialog box to reduce decimals, you will notice that the cell value and formula bar value are equal. Thus, you can remove decimals permanently in Excel.
Download Practice Workbook
Conclusions
This Excel tutorial presented a set of methods to reduce decimals in Excel, catering to different preferences and precision needs. Whether using quick options like the Decrease Decimal button or more specific functions such as ROUND, ROUNDUP, and others, users can customize their approach. If you have any queries or suggestions about this article, leave a comment in the comment box.
Frequently Asked Questions
How to round a decimal to an integer in Excel?
In Excel, you can round a decimal number to the nearest integer using the ROUND function. If your decimal value is in cell A1, you can apply the formula =ROUND(A1, 0) in the desired cell to round it to the nearest whole number. Alternatively, you can use ROUNDUP(A1, 0) to always round up or ROUNDDOWN(A1, 0) to always round down to the nearest integer. Adjust the cell reference as needed for your specific data, and the result will display the decimal number rounded to the nearest whole integer.
Are there any limitations to reducing decimals in Excel?
While reducing decimals improves readability, it’s essential to be mindful of potential loss of precision. Depending on your data and calculations, rounding errors may accumulate, impacting the accuracy of your results.
How to reduce decimals in Excel using a shortcut key?
To quickly reduce decimals in Excel using a keyboard shortcut, you can press Alt+H to access the Home tab, followed by 9 to activate the Decrease Decimal button. Alternatively, you can use Alt+F9 to toggle the display of formulas, and then press Alt+H+9 to reduce decimals. This keyboard shortcut is effective for instantly decreasing the number of decimal places in the selected cell or range without navigating through the ribbon.
Related Articles
- How to Change Decimal Separator in Excel
- How to Change 1000 Separator to 100 Separator in Excel
- How to Reduce Decimals in Excel
- How to Add Decimals in Excel
- How to Remove Decimals in Excel
- How to Convert Decimal to Whole Numbers in Excel
- How to Convert Decimal to Fraction in Excel
- Excel Decimal Places Problem
- How to Limit Decimal Places in Excel
- How to Remove Decimals in Excel Formula Bar
<< Go Back to Decimals in Excel | Number Format | Learn Excel