If you don’t want unnecessary decimal places in cells because they cause ###### symbols to appear, or you don’t need accuracy down to the microscopic level, change the cell format to get the number of decimal places you want.

Or if you want to round to the nearest major unit, such as thousands, hundreds, tens, or ones, use a function in a formula.

By using a button:

Select the cells that you want to format.

On the Home tab, click Increase Decimal or Decrease Decimal to show more or fewer digits after the decimal point.

By applying a built-in number format:

On the Home tab, in the Number group, click the arrow next to the list of number formats, and then click More Number Formats.

In the Category list, depending on the type of data you have, click Currency, Accounting, Percentage, or Scientific.

In the Decimal places box, enter the number of decimal places that you want to display.

By using a function in a formula:

Round a number to the number of digits you want by using the ROUND function. This function has only two arguments (arguments are pieces of data the formula needs to run).

The first argument is the number you want to round, which can be a cell reference or a number.

The second argument is the number of digits you want to round the number to.

Suppose that cell A1 contains 823.7825. To round the number to the nearest:

Type =ROUND(A1,-3) which equals 1,00 0

823.7825 is closer to 1,000 than to 0 (0 is a multiple of 1,000 )

Use a negative number here because you want the rounding to happen to the left of the decimal point. The same thing applies to the next two formulas that round to hundreds and tens.

Type =ROUND(A1,-2) which equals 800

800 is closer to 823.7825 than to 900. We think you get the idea by now.

Type =ROUND(A1,-1) which equals 820

Type =ROUND(A1,0) which equals 824

Use a zero to round the number to the nearest single digit.

Type =ROUND(A1,1) which equals 823.8

Use a positive number here to round the number to the number of decimal points you specify. The same thing applies to the next two formulas that round to hundredths and thousandths.

Type =ROUND(A1,2) which equals 823.78

Type = ROUND(A1,3) which equals 823.783

Round a number up by using the ROUNDUP function. It works just the same as ROUND, except that it always rounds a number up. For example, if you want to round 3.2 up to zero decimal places:

=ROUNDUP(3.2,0) which equals 4

Round a number down by using the ROUNDDOWN function. It works just the same as ROUND, except that it always rounds a number down. For example, if you want to round down 3.14159 to three decimal places:

=ROUNDDOWN(3.14159,3) which equals 3.141

Tip: To get more examples, and to play around with sample data in an Excel Online workbook, see the ROUND, ROUNDUP, and ROUNDDOWN articles.

You can set a default decimal point for numbers in Excel Options.

Click Options (Excel 2010 to Excel 2016), or the Microsoft Office Button > Excel Options (Excel 2007).

In the Advanced category, under Editing options, select the Automatically insert a decimal point check box.

In the Places box, enter a positive number for digits to the right of the decimal point or a negative number for digits to the left of the decimal point.

Note: For example, if you enter 3 in the Places box and then type 2834 in a cell, the value will be 2.834. If you enter -3 in the Places box and then type 283 in a cell, the value will be 283000.

The Fixed decimal indicator appears in the status bar.

On the worksheet, click a cell, and then type the number that you want.

Note: The data that you typed before you selected the Fixed decimal check box is not affected.

To temporarily override the fixed decimal option, type a decimal point when you type the number.

To remove decimal points from numbers that you already entered with fixed decimals, do the following:

Click Options (Excel 2010 to Excel 2016), or the Microsoft Office Button > Excel Options (Excel 2007).

In the Advanced category, under Editing options, clear the Automatically insert a decimal point check box.

In an empty cell, type a number such as 10, 100, or 1,000, depending on the number of decimal places that you want to remove.

For example, type 100 in the cell if the numbers contain two decimal places and you want to convert them to whole numbers.

On the Home tab, in the Clipboard group, click Copy or press CTRL+C.

On the worksheet, select the cells that contain the numbers with decimal places that you want to change.

On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Paste Special.

In the Paste Special dialog box, under Operation, click Multiply.

## Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

## Learn how to round down in Excel

- Tweet
- Share

OTA Photos / Flickr

The ROUNDDOWN function is used to reduce a value by a specific number of decimal places or digits. It keeps the rounding digit the same, alters the value of the data in the cell, and rounds towards zero.

The information in this article applies to Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, and Excel Online.

## The ROUNDDOWN Function’s Syntax and Arguments

The syntax for the ROUNDDOWN function is

*= ROUNDDOWN (Number, Num_digits)*

The arguments for the function are:

**Number** — (required) the value to be rounded. This argument can contain the actual data for rounding, or it can be a cell reference to the location of the data in the worksheet.

**Num_digits** — (required) the number of digits to which the *Number* argument will be rounded.

- If the
*Num_digits*argument is set to 0, the function rounds the value down to the nearest integer. - If the
*Num_digits*argument is set to 1, the function leaves only one digit to the right of the decimal point and rounds it down to the next number. - If the
*Num_digits*argument is negative, all decimal places are removed, and the function rounds that number of digits to the left of the decimal point towards zero. For example, if the value of the*Num_digits*argument is set to -2, the function removes all digits to the right of the decimal point, rounding the first and second digits to the left of the decimal point down to the nearest 100.

The image above displays examples and gives explanations for several results returned by Excel’s ROUNDDOWN function for data in column A of the worksheet. The results (shown in column C) depend upon the value of the *Num_digits* argument.

The instructions below detail the steps taken to reduce the number in cell A2 in the image above to two decimal places using the ROUNDDOWN function. Because the function always rounds down, the rounding digit does not change.

## Enter the ROUNDDOWN Function

Options for entering the function and its arguments include:

- Typing the complete function:
*=ROUNDDOWN(A2,2)*into cell C3 in the worksheet; - Selecting the function and arguments using the function’s dialog box.

Using the dialog box simplifies entering the function’s arguments. With this method, it is not necessary to enter commas between each of the function’s arguments*.*

The steps below show how to enter the ROUNDDOWN function using the dialog box in Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Mac.

- Select cell
**C3**to make it the active cell. The results of the ROUNDDOWN function will be displayed here. - Select
**Formulas**. - Choose
**Math & Trig**to open the function drop-down list. - Select
**ROUNDDOWN**in the list to bring up the function’s dialog box. - In the dialog box, select the
**Number**line. - Select cell
**A2**in the worksheet to enter that cell reference into the dialog box as the location of the number to be rounded. - Select the
**Num_digits**line. - Enter “
**2**” to reduce the number in**A2**from 5 to 2 decimal places. - Select
**OK**to close the dialog box and return to the worksheet. - The answer 567.96 appears in cell C3.

When you select cell C2, the complete function =ROUNDDOWN(A2,2) appears in the formula bar above the worksheet.

**Excel Online** does not have a Formulas tab. To use the ROUNDDOWN function in Excel Online, use the Formula bar.

- Select cell
**C3**to make it the active cell. The results of the ROUNDDOWN function will be displayed here. - Select the
**Insert Function**button next to the Formula bar. - Select
**Math & Trig**from the Pick a Category drop-down list. - Select
**ROUNDDOWN**in the Pick a Function list. - Select
**OK**. - Select cell
**A2**to select it for the Number argument. - Enter “
**2**” to reduce the number in**A2**from 5 to 2 decimal places. - Press
**Enter**.

When you select cell C2, the complete function =ROUNDDOWN(A2,2) appears in the formula bar above the worksheet.

Whenever we are working with decimal values in Excel, we need to format them appropriately. One of the best ways to format decimal numbers is using the **ROUND** function. In this tutorial, we will learn how to the particular decimal value that we choose.

* *

*Figure 1. Final result*

**Syntax of the ROUND formula**

**=ROUND(number, num_digits)**

The parameters of the ROUND function are:

**number**– a decimal number which we want to round**num_digits**– a number of decimal places to which we want to round a number

**Setting up Our Data for the ROUND Function**

Our table consists of 4 columns: “Explanation” (column B), “Decimal Value” (column C), “Number of Digits” (column D) and “Result” (Column E). In column “Result” we want to get a rounded number, based on “Decimal Value” and “Number of Digits” columns.

* Figure 2. Data that we will use in the ROUND example*

**Round Decimal Values Using the ROUND Function**

We want to round the value from cell C3 by using the number of digits from cell D3l. The result will be stored in E3.

The parameter **number** is the cell C3 (4.238645), while the parameter **num_digits** is the cell D3 (0).

To apply the SUMIFS function, we need to follow these steps:

- Select cell E3 and click on it
- Insert the formula: =ROUND(C3,D3)
- Press enter
- Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

*Figure 3. Using the ROUND function to round decimal values*

As a result, we get value 4 in the cell E3. Putting 0 as num_digits means that we want to round number to the nearest whole number, which is 4. We must know that the function rounds number 1-4 down and 5-9 up. Because of that, 4.238645 rounding to 1 decimal is 4.2, while rounding to 2 decimals is 4.24

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

- Excel Tips
- Excel Functions
- Excel Formulas
- Word Tips
- Outlook Tips

## How to round then sum values by one formula in Excel?

If you have some data as below screenshot shown, and you want to round the data and then sum the data up. In general, you may round the data one by one by using ROUND function, and then use SUM function to add the rounding data up. But now this tutorial is talking about one formula to round then sum in Excel.

#### **Round then sum values with one formula**

To round then sum values with one step, you just need an array formula.

Select a cell you want to put the final result out, and type this formula =SUM(ROUND(B2:B5,0)) into it, then press **Shift + Ctrl + Enter** keys to get the result. See screenshot:

**Note:**

In the above formula, B2: B5 is the data range you want to round and sum up. You can change it as you need.

- Excel Tips
- Excel Functions
- Excel Formulas
- Word Tips
- Outlook Tips

## How to remove digits after decimal in Excel?

Sometimes you need to remove all digits after decimal to make numbers to whole numbers. You may think the Decrease Decimal feature can deal with it. Actually, the Decrease Decimal feature just changes a number’s display value, for example, show 2.5 as 3, but the actual value still is 2.5 showing in the formula bar. Therefore, how to remove digits after decimal in Excel? There are several methods for you.

**Remove digits after decimal with INT function or TRUNC function****Remove digits after decimal without formula by Round utility**

Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.

Supposing we have a purchase table as below screen shot shown, and now we need to remove digits after decimal for values in Amount Column. Excel functions can help you to remove digits after decimal easily. You can do as follows:

* INT function* : In a blank cell besides the value you will remove digits after decimal, enter the formula =INT(E2) , and the drag the Fill Handle to the range you need.

**Notes:**

(1) In the formula =INT(E2) , E2 is the cell that you will remove digits after decimal.

(2) INT function can also remain certain places of digits after decimal. For example, to remove all digits except the first one after decimal, you can apply the formula =INT(E2*10)/10.

* TRUNC function* : Besides the value you will remove digits after decimal, enter the formula =TRUNC(E2,0) into a blank cell, and then drag the Fill Handle to the range you need.

**Notes:**

(1) In the formula =TRUNC(E2,0) , E2 is the cell you will remove digits after decimal, 0 means removing all digits after decimal;

(2) To remove all digits but first one after decimal, you can apply the formula =TRUNC(E2,1) ;

**Tip**: Remove formulas but keep formula results: *(1)*select all formula cells; *(2)* copy these formula cells with pressing the **Ctrl** + **C** keys at the same time; *(3)* right click the first cell of destination range, and select **Values** below **Paste Options** in the context menu.

By the way, If you have Kutools for Excel installed, you can apply its **To Actual** feature to quickly convert all formulas to their formula results with only one click. See screenshots:

#### Batch add decimal point to existing numbers in a column/list without formula

As you know, decimal point can be automatically added with the specified decimal places when you typing numbers by Excel options > Advanced > **Automatically insert a decimal point**. However, this option will affect the whole worksheets. What’s about only automatically inserting decimal points to existing numbers in a specified range in Excel? Try **Kutools for Excel**’s **Operation** utility! **Full Feature Free Trial 30-day!**

All functions don’t modify original values in Excel, but sometimes you may just need to change original values. To remove digits after decimal from original values directly, you can apply the **Round** utility of **Kutools for Excel**.

**Kutools for Excel **– Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! **Free Trial Now!**

Kutools for Excel – Combines more than 300 Advanced Functions and Tools for Microsoft Excel

**1**. Select the cells that you will remove digits after decimal, and then click the **Kutools** > **Round**.

**2**. In the opening Round without Formula dialog box, type 0 in the **Decimal place** box, check the **Rounding down** option, and click the **Ok** button.

**Kutools for Excel **– Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! **Get It Now**

## Rounding Numbers & Removing Decimals in Excel

Excel includes quite a few functions that round or remove decimals from numbers. The video below demonstrates the difference between all these functions. Download the featured file here.

**VIDEO TRAINING – LEARN AT YOUR OWN PACE**

**DEEP DIVE** **INTO** **LOOKUP FUNCTIONS** – **XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP, MATCH, INDEX, IFERROR, ISNA, IFNA, LEFT, RIGHT, MID, FIND, SEARCH, LEN, SUBSTITUTE, REPLACE, TRIM, CLEAN** & **INDIRECT**. **Click here to enrol **

**THE MS ACCESS FOR BEGINNERS’ COURSE – Click here to enrol **

**FREE STUFF **

**70+ MUST KNOW EXCEL SHORTCUT KEYS:**

**Download the pdf**

**UNDERSTAND & FIX EXCEL ERRORS: Download the pdf**

Here’s our grand tour…

## INT Function

Rounds a number **down** to the nearest integer

## TRUNC Function

Truncates a number by removing the decimal portion. Has a **Num_digits** argument that allows you to specify precision – so for example 1 would leave one decimal point, -1 would zero the number to the left of the decimal place. =TRUNC(A1, 1)

## ODD Function

Rounds away from zero to the next odd integer. So for example 2.1 become 3 and -2.1 becomes -3.

=ODD(A1)

## EVEN Function

Rounds away from zero to the next even integer. So for example 3.1 becomes 4 and -3.1 becomes -4.

## ROUND Function

Values up to 0.49999999 are rounded towards zero; greater than are rounded away from zero to the next integer.

## ROUNDUP Function

Rounds away from zero to the next integer. Includes a **Num_digits** argument which allows you to specify the number of digits you want to round – a negative Num_digits value would round the figure to the left of the decimal place.

## ROUNDDOWN Function

Rounds towards zero to the next integer. Includes a **Num_digits** argument which allows you to specify the number of digits you want to round – a negative Num_digits value would round the figure to the left of the decimal place.

**VIDEO TRAINING – LEARN AT YOUR OWN PACE**

**DEEP DIVE** **INTO** **LOOKUP FUNCTIONS** – **XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP, MATCH, INDEX, IFERROR, ISNA, IFNA, LEFT, RIGHT, MID, FIND, SEARCH, LEN, SUBSTITUTE, REPLACE, TRIM, CLEAN** & **INDIRECT**. **Click here to enrol **

**THE MS ACCESS FOR BEGINNERS’ COURSE – Click here to enrol **

**FREE STUFF **

**70+ MUST KNOW EXCEL SHORTCUT KEYS:**

**Download the pdf**

**UNDERSTAND & FIX EXCEL ERRORS: Download the pdf**

## MROUND Function

Rounds a number to the multiple you specify. In the example below the retail prices need to be rounded to the nearest 25 pence. The formula used is =MROUND(C2, 0.25)

## CEILING/CELING.MATH Function

Rounds a number up to the nearest multiple of significance. In the example below I need to order in multiples specified in column C. Column B tells me how much I actually need, the CEILING function tells me how many I need to order.

**VIDEO TRAINING – LEARN AT YOUR OWN PACE**

Click Options (Excel 2010 to Excel 2016), or the Microsoft Office Button > Excel Options (Excel 2007).

**DEEP DIVE** **INTO** **LOOKUP FUNCTIONS** – **XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP, MATCH, INDEX, IFERROR, ISNA, IFNA, LEFT, RIGHT, MID, FIND, SEARCH, LEN, SUBSTITUTE, REPLACE, TRIM, CLEAN** & **INDIRECT**. **Click here to enrol **

**THE MS ACCESS FOR BEGINNERS’ COURSE – Click here to enrol **

**FREE STUFF **

**70+ MUST KNOW EXCEL SHORTCUT KEYS:**

**Download the pdf**

**UNDERSTAND & FIX EXCEL ERRORS: Download the pdf**

### CEILING or CEILING.MATH?

The difference between CEILING and CEILING.MATH is the way it rounds negative numbers. CEILING rounds positive and negative numbers away from zero, whereas CEILING.MATH rounds positive numbers away from zero and negative number towards zero. CEILING.MATH is only available in Excel 2013, and is renamed from CEILING.PRECISE in Excel 2010.

## FLOOR/FLOOR.MATH Function

Rounds a number down to the nearest multiple of significance. In the example below I need return items but in the specified multiples in column C. Column F tells me how many I want to return, the FLOOR function tells me how many I can return.

Setting the rounding precision forces Excel to always round numeric values to a specified number of digits following the decimal, saving you from having to use the ROUND function on each formula. Here’s how it works.

Note that setting Excel’s rounding precision can save time, but be certain you want to round all values before you enable this feature. When Excel is set to round numbers, it deletes the extra data following the precision point you set, permanently affecting the accuracy of the numbers in your workbook. This means, for example, that if a value like “12.7851698” is displayed with two digits after the decimal point, then Excel will permanently delete the “51698” and leave only the “12.76” remaining.

This feature also works on a per-workbook basis, meaning you can enable it for some workbooks and not others. You must enable it for an entire workbook, though; you can’t enable it only for specific sheets within a workbook.

## Where to Find the “Set Precision As Displayed” Option

Navigate to the “File” menu.

Select the “Options” menu.

In the Excel Options window that appears, click the “Advanced” category on the left.

On the right, scroll all the way to the bottom. You’ll find the “Set Precision As Displayed” option in the “When Calculating This Workbook” section. You can select different currently-opened workbooks using the drop-down.

When you select the check box, Excel displays a warning letting you know that the data in the workbook will permanently lose accuracy. Click the “OK” button.

Now click the “OK” button to exit the Excel Options window and return to the “File” menu.

You can set how many digits are kept by altering how numbers are displayed in the “Home” menu’s “Number” group.

## Remove decimals without rounding

- Tweet
- Share

The TRUNC function is one of Excel’s group of rounding functions even though it may or may not round the identified number.

As its name suggests, it can be used to truncate or shorten the target number to a set number of decimal places without rounding the remaining digits or whole number.

## Truncate Values to a Set Number of Decimal Places

The function only rounds numbers when the *Num_digits* argument is a negative value – rows seven to nine above.

In these instances, the function removes all decimal values and, depending on the value of *Num_digits*, rounds the number down to that many digits.

For example, when *Num_digits* is:

- Equal to -1, the number is rounded down to the nearest value of 10. Shown in row seven.
- Equal to -10, the number is rounded down to the nearest value of 100. Shown in row eight.
- Equal to -100, the number is rounded down to the nearest value of 1,000. Shown in row nine.

## The TRUNC Function’s Syntax and Arguments

A function’s syntax refers to the layout of the function and includes the function’s name, brackets, and arguments.

The syntax for the TRUNC function is:

= TRUNC (Number, Num_digits)

Number – the value to be truncated. This argument can contain:

- The actual data to be truncated. Shown in rows two and three above.
- The results of a formula. Shown in row three above, where TRUNC is used to shorten the value returned by the PI function.
- A cell reference to the location of the data in the worksheet. Shown in rows four to nine above.
- The reference can also point to a cell containing the value returned by a formula – the value in cell A4 is generated by the PI function.

Num_digits (Optional): The number of decimal places to be left by the function.

- If
*Num_digits*is omitted a default value of zero is used for this argument and all decimal places are removed and a non-rounded integer value is returned by the function. Shown in row five above.

If you don’t want unnecessary decimal places in cells because they cause ###### symbols to appear, or you don’t need accuracy down to the microscopic level, change the cell format to get the number of decimal places you want.

Or if you want to round to the nearest major unit, such as thousands, hundreds, tens, or ones, use a function in a formula.

By using a button:

Select the cells that you want to format.

On the Home tab, click Increase Decimal or Decrease Decimal to show more or fewer digits after the decimal point.

By applying a built-in number format:

On the Home tab, in the Number group, click the arrow next to the list of number formats, and then click More Number Formats.

In the Category list, depending on the type of data you have, click Currency, Accounting, Percentage, or Scientific.

In the Decimal places box, enter the number of decimal places that you want to display.

By using a function in a formula:

Round a number to the number of digits you want by using the ROUND function. This function has only two arguments (arguments are pieces of data the formula needs to run).

The first argument is the number you want to round, which can be a cell reference or a number.

The second argument is the number of digits you want to round the number to.

Suppose that cell A1 contains 823.7825. To round the number to the nearest:

Type =ROUND(A1,-3) which equals 1,00 0

823.7825 is closer to 1,000 than to 0 (0 is a multiple of 1,000 )

Use a negative number here because you want the rounding to happen to the left of the decimal point. The same thing applies to the next two formulas that round to hundreds and tens.

Type =ROUND(A1,-2) which equals 800

800 is closer to 823.7825 than to 900. We think you get the idea by now.

Type =ROUND(A1,-1) which equals 820

Type =ROUND(A1,0) which equals 824

Use a zero to round the number to the nearest single digit.

Type =ROUND(A1,1) which equals 823.8

Use a positive number here to round the number to the number of decimal points you specify. The same thing applies to the next two formulas that round to hundredths and thousandths.

Type =ROUND(A1,2) which equals 823.78

Type = ROUND(A1,3) which equals 823.783

Round a number up by using the ROUNDUP function. It works just the same as ROUND, except that it always rounds a number up. For example, if you want to round 3.2 up to zero decimal places:

=ROUNDUP(3.2,0) which equals 4

Round a number down by using the ROUNDDOWN function. It works just the same as ROUND, except that it always rounds a number down. For example, if you want to round down 3.14159 to three decimal places:

=ROUNDDOWN(3.14159,3) which equals 3.141

Tip: To get more examples, and to play around with sample data in an Excel Online workbook, see the ROUND, ROUNDUP, and ROUNDDOWN articles.

You can set a default decimal point for numbers in Excel Options.

Click Options (Excel 2010 to Excel 2016), or the Microsoft Office Button > Excel Options (Excel 2007).

In the Advanced category, under Editing options, select the Automatically insert a decimal point check box.

In the Places box, enter a positive number for digits to the right of the decimal point or a negative number for digits to the left of the decimal point.

Note: For example, if you enter 3 in the Places box and then type 2834 in a cell, the value will be 2.834. If you enter -3 in the Places box and then type 283 in a cell, the value will be 283000.

The Fixed decimal indicator appears in the status bar.

On the worksheet, click a cell, and then type the number that you want.

Note: The data that you typed before you selected the Fixed decimal check box is not affected.

To temporarily override the fixed decimal option, type a decimal point when you type the number.

To remove decimal points from numbers that you already entered with fixed decimals, do the following:

In the Advanced category, under Editing options, clear the Automatically insert a decimal point check box.

In an empty cell, type a number such as 10, 100, or 1,000, depending on the number of decimal places that you want to remove.

For example, type 100 in the cell if the numbers contain two decimal places and you want to convert them to whole numbers.

On the Home tab, in the Clipboard group, click Copy or press CTRL+C.

On the worksheet, select the cells that contain the numbers with decimal places that you want to change.

On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Paste Special.

In the Paste Special dialog box, under Operation, click Multiply.

## Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.