Sometimes you don’t require numbers to the nth level of detail, especially if you want to avoid Excel’s ###### symbol or having to resize your columns. Rounding data with formula or formatting allows you to do this.

Rounding with number formatting

The quickest way to round numbers for presentational purposes while keeping the microscopic detail of the value in the cell is number formatting.

In your Excel ribbon under the Home tab and Number section you will find the two buttons below that allow you to toggle the decimal place of the cells you have highlighted.

Rounding with formulas

In other cases, rounding numbers using formulas can be great to use as an input for other reporting where the requirement may be whole numbers or two decimal places such as accounting or tax reporting systems. In these cases we can use the round formula below.

Syntax

• =ROUND(number,num_digits)

Explanation

number: the value or cell reference which you wish to round
num_digits: the number of digits you that you wish to round to

Rounding to a specific decimal place or nearest major unit using formulas

Rounding up with formulas

• =ROUNDUP(number,num_digits)

Rounding down with formulas

• =ROUNDDOWN(number,num_digits)

Summary

In conclusion, the method that you use to round numbers to a required digit depends on the requirements of your spreadsheet and what the output will be used for. In most cases using the number formatting with the Excel ribbon would suffice, especially if you don’t want to lose the detail behind the numbers. In some cases the output might be required for a journal upload to an accounting system, in which case the numbers may have to be rounded to two decimal places and therefore using formula to round values would be preferred.