How to round numbers in Excel using formatting and functions

Excel Navigator
Sep 7
4 min read

There are different ways that you can round numbers in Excel, either formatting the numbers so that the number of decimal places you see changes or using functions which change the number of decimal places the number has.

Depending on what you need this can have an impact on how the numbers are treated for calculations, or if you are using your spreadsheet as an upload to another system, which is often the case for accountants who upload journal templates.

Download File

If you would like to follow along with the examples download the file below.

Understanding how Excel formats decimal numbers

If you enter a large decimal number in Excel there may be a difference between what you see in the cell and what you see in the formula bar.

This is because to save space Excel may reduce the number of visible numbers after the decimal place so that the number can be displayed in a cell.

There is also the option to manually change the number of decimal places you see in a cell while leaving the original larger number intact.

A quick way to change the number of decimal places visible is to select the cell or cells you want to change and use the increase or decrease decimal buttons on the Home tab under the Number section.

Doing this will change the format of your number so that the decimal places visible in the cell increase or decrease, but they will not change the underlying value of the number.

As well as using the increase or decrease decimal buttons you can also format the number value to change the number of decimals that are displayed in a cell.

To do this you can select the cell or cells that you want to change and press Ctrl + 1 to open the Format Cells window.

This will allow you to choose the number of decimals that will display as well as see a sample of what the number will look like when formatted.

Like when using the increase/decrease buttons though the underlying value will not change, even though the number in the cell is formatted to display with two decimal places.

This can cause a number of issues if you have lots of cells formatted to display a fixed number of decimal places, but the underlying value has more.

If you are trying to use your file as an upload to a system that can only accept numbers up to two decimal places, like an accounting system, it may look like your numbers are correct but when you attempt to upload you will get an error as the system will attempt to import the underlying number rather than the formatted number.

This can also impact any calculations you make using the formatted cell.

When multiplying the decimal number that is formatted to have two decimal places by six the output shows more than two decimal places.

If you want the actual number to reflect the displayed number of decimal places then you can use one of Excel’s rounding functions.

Using the ROUND function

The ROUND function is used to round a number to a specific number of digits. Unlike just formatting with Increase/Decrease Decimal or using the Format Cells window, this function changes the underlying value which will affects calculations.

The syntax is:

=ROUND(number, num_digits)

number - The number or cell reference you want to round.

num_digits - How many digits you want to round the number to.

If you want to change the value in a cell you could use the ROUND function to convert the number and then copy and paste the output of the ROUND function to leave you with the correct underlying value.

You can also use ROUND directly in formulas so that any output will give you the correct decimals.

The ROUND function will always round the numbers up or down based on whether the last number is less than five or greater than or equal to 5.

If you want to control the direction of the rounding then you can use the ROUNDUP or ROUNDDOWN functions.

Using the ROUNDUP and ROUNDDOWN functions

The syntax for ROUNDUP and ROUNDDOWN are the same:

=ROUNDUP(number, num_digits)

=ROUNDDOWN(number, num_digits)

number - The number or cell reference you want to round.

num_digits - How many digits you want to round the number to.

Unlike the ROUND function these functions do not take less than or greater than 5 into account.

Instead they will always round in one direction regardless of the last decimal number.

Like with the ROUND function if you want to have only the underlying value with the correct number of decimal places showing then you can copy the cell with the function in it (Ctrl + C) and then paste it as a value (Ctrl + Shift + V).

Conclusion

There are two main ways to change the number of decimals in Excel:

Formatting – Changes the way the number displays in a cell but does not change the number of decimals in the underlying value

Functions – Changes both the way the number is displayed and the number of decimals in the underlying value

If you need the actual number to change so that it can be used for forward calculations or uploading to another system then it is best to use the functions.

However if you do not want the overall value of a number to change but just want it to appear with less decimals then formatting is the better option.