Round Formulas in Excel & Google Sheets
Download the example workbook
This tutorial will demonstrate how to use all of Excel and Google Sheets’ rounding functions to round numbers.
Excel’s round functions allow you to round numbers to specified number of digits.
The ROUND Function rounds numbers to a specified number of digits. When rounding to 0, a number rounds to the nearest whole number. Anything below .5 rounds down and anything equal to or above .5 rounds up.
As you can see, the ROUND Function takes two inputs:
- The number to round
- How many spots to round to the left (negative numbers) or right (positive numbers) of the decimal
The ROUNDUP Function works the same as the ROUND Function except it always rounds up to the specified number of digits:
The ROUNDDOWN Function works the same as the ROUND and ROUNDUP Functions except it always rounds down to the specified number of digits:
The TRUNC Function “truncates” a number after a certain number of digits.
The INT Function returns the integer value of a number. It works exactly the same as TRUNC for positive numbers. But will give a different result for negative numbers.
Rounding Functions – Round to Multiples
The above functions will round to a specified number of digits. To round to a specified multiple use the MROUND, FLOOR, and CEILING Functions.
The MROUND Function works similarly to the ROUND Function. However, instead of defining the number of digits to round to, you define the multiple to which to round. Here are some examples:
The FLOOR Function works just like the MROUND Function, except the FLOOR Function always rounds down.
The CEILING Function works just like the MROUND and FLOOR Functions, except it will always round up.
Round Without Formulas
In Excel, you can adjust how numbers are displayed with Number Formatting. Using Number Formatting you can round how the number is displayed.
With MROUND, you can round time like this:
You can also round percentages with the ROUND Function, just keep in mind that percentages are stored as decimal values, so you must set num_digits accordingly:
Round to Nearest Dollar
By setting num_digits to 0, you can round prices to the nearest dollar:
Round to Nearest Cent
Or to the nearest cent with num_digits = 2.
Round Number to n Significant Figures
With some additional calculations, you can use the ROUND Function to round a number to n significant digits:
Round a Sum – Round off Formula
You can place other calculations or functions within the ROUND Function. For example, you can round off a decmal:
Round Two Decimal Places
This example will round to two decimal places:
Round to Thousands
This example will round to the thousands place with a negative num_digits:
Round Up to Nearest 100
This example will round up to the nearest 100.
Round to Nearest 5
The MROUND, FLOOR, and CEILING Functions can round to the nearest 5:
Calculate Decimal Part of Number
The TRUNC Function can be used to calculate the decimal part of a number.
This works by first using TRUNC to calculate the integer portion of the number:
Round Formulas in Google Sheets
All of the above examples work exactly the same in Google Sheets as in Excel.