How to Reduce Decimals in Excel (10 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

To reduce decimal points in Excel:

  1. Select the cell where you want to decrease decimal points.
  2. 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.

Overview of reducing decimal points pwemanently

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:

  1. Select a range of cells.
  2. Go to Home tab > Number group >  Decrease Decimal button.
    Decrease decimal to reduce decimals in Excel
  3. Click on the button one or multiple times.
    This will reduce the decimals in your selected data range.Decrease decimal to reduce decimals in Excel

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:

  1. Select a range of cells.
  2. Press Ctrl+1 to open the Format Cells dialog box.
    Using Format Cells window to decrease decimals in Excel
  3. 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.

    Insert Decimal places

This will reduce the decimals to 2 places in your selected cells.

Reduce format cells to decrease decimals in Excel

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:

  1. Select the cell where you want to decrease decimal points.
  2. 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.
    Using ROUND function
  3. Press the Enter key to see the reduced decimal value.
    Using ROUND function
  4. Use the Fill Handle to copy the formula in the remaining cells.
    Drag and drop after ROUND function

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.
ROUND function from Math

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:

  1. Select the cell where you want to decrease decimal points.
  2. Inset the formula: =ROUNDUP(D5,2)
    Here, D5 represents decimal value and 2 represents desired decimal places. Replace these arguments with your required values.
  3. Press Enter.
    This will reduce the decimal places.Using ROUNDUP function
  4. Drag the Fill Handle icon down to copy the formula in the remaining cells.
    Using ROUNDUP and autofill

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:

  1. Select the cell where you want to decrease decimal points.
  2. 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.
  3. Press Enter.
    Now you will see the reduced decimal value in the selected cell.
    Result of ROUNDDOWN function
  • You can copy the formula in the remaining cells using the Fill Handle tool.
    Reducing decimals using ROUNDOWN in Excel

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:

  1. Select the cell where you want to decrease decimal points.
  2. 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.
  3. Press the Enter key to see the reduced decimal value.
    Using MROUND function to reduce decimals in Excel
  4. Finally, drag down the Fill Handle icon to copy the formula in the remaining cells.
    MROUND function for reducing decimals

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:

  1. Select a cell.
  2. Inset the formula: =TRUNC(D5,2)
    Here, D5 is the cell with decimal points and 2 is the decimal points that will remain.
  3. Press Enter.
    This will reduce the decimal value.
    Reducing decimals using TRUNC function in Excel
  4. Finally, drag down the Fill Handle icon to fill the remaining cells.
    Reducing decimal using TRUNC function

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:

  1. Select the cell where you want to decrease decimal points.
  2. 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).
  3. Press Enter.
    You will see the reduced decimal value in the selected cell.
    Reducing Decimals using INT function
  4. Drag the Fill Handle icon down to fill the remaining cells.
    Reducing Decimals using INT function

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:

  1. Select the cell where you want to decrease decimal points.
  2. 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.
  3. Press Enter to get the reduced decimal value.
    Reducing Decimal using FLOOR and IF
  4. Drag the Fill Handle icon down to copy the formula in the remaining cells.
    Reducing Decimal using FLOOR and IF

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:

  1. Select the cell where you want to decrease decimal points.
  2. 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.
    Reducing Database using Ceiling Function
  3. Hit the Enter key.
    Now, you will see the reduced decimal value.
    Reducing decimals using Ceiling Function
  4. You can use the Fill Handle to copy the formula in the remaining cells.
    Reducing Database using Ceiling Function

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.
Remove Decimal Permanentlt

To permanently remove decimals in Excel, you have to enable the Set precision as displayed option from Excel Advanced Options. Here’s how:

  1. Go to File.
    Remove decimal permanently
  2. Select Options.
    Goto option
    Then, the Excel Options window will open.
  3. 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.Set precision as displayed to reduce decimals in ExcelA message box will appear to ensure your last change.
    • Press OK.Warning box
  4. Afterward, in the Excel Options window, click OK.
    Press okThis will take you to the worksheet.
  5. 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. Finally permanently removedThus, 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

<< Go Back to Decimals in Excel | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo