All You Need To Know on How to Round a Number in Excel

Rounding off numbers results in values with fewer digits that approximates only the original or non-rounded number. Despite being less accurate, it is often preferred because it’s easier to report and communicate, and just plainly faster to work with. Imagine working with a worksheet with revenues, expenses, values, and balances right down to every decimal!

As a simple Basic Match refresher, use below reference on how to determine the number of digits:

Using Excel, you can round off a number to the nearest hundred, thousand, million, and beyond. There are many ways to round a number in Excel. In this article, we will discuss the many functions and features that will help you how to round numbers in Excel -whether how to round up or how to round down in Excel and even round towards and away from zero.

2 Quick Ways on How to Round Numbers in Excel Using Formatting Features

A manufacturing plant has the following total cost per unit for the 1st half of the year. The total cost accounts for direct material, labor, overhead, etc. costs associated with the finished product.

Displaying the values on the worksheet as is can be confusing yet alone working with it can make things complicated. To round a number in an Excel worksheet proves reasonable and beneficial not only to the analyst and accountants but also for the managers, executives, and other decision-makers to whom these will be presented. Below are steps on how to round numbers in Excel quickly using built-in features and functions.

Round a Number in Excel Using the Number Group

The Number Group is found in the Home Tab. Numbers in Excel can be formatted to show commas,  currency symbols, percentages, fractions, etc. This is also used to reflect decimal places and so round a number in Excel.

The total manufacturing cost per month (B3 to G3) is formatted as Accounting. You can decrease and increase the decimal places by:

  1. Using the buttons provided in the Number Format (A); Or
  2. Using the Format Cells dialogue box (B). Tip: Use the keyboard shortcut to open the Format Cells dialogue box, press CTRL +1.

Using the   button to decrease the number of decimal places to 2, (The  button is used to increase the number of decimal places), the table for the cost per unit now looks less confusing and easier to work with. If you click on the cell, notice that even after reducing the decimal places to two the original and non-rounded number is still reflected in the formula bar. If the values were formatted as Number or Currency, the same applies.

Using the Format Cells dialogue box, you can decrease the number of decimal places to 2 by using the down arrow button.

Round a Number in Excel Using Custom Format

Using the Format Cells dialogue box, Excel allows cells customized formats. You can select the existing formats or create a new customized format.

How to Round a Number in Excel Using ROUND Function

You may ask, how to round numbers in Excel without using formats?  You can round a number in Excel using the ROUND function in Excel.

The ROUND function is under the Math/Trig function and rounds a number to a specific number of digits. Numbers 1,2,3, and 4 are rounded down while 5,6,7,8, and 9 are rounded up.

The syntax or formula of the ROUND function:

Where:

Number

– Represents the number that has to be rounded (reference cells are allowed).

Num_digits

– Represents the number of digits to round the number to. You can reference the number itself or a reference cell.
– If the Num_digits is greater than zero it identifies the number of digits to the RIGHT of the decimal point- tenths, hundredths, thousandths, etc. (See cell B2 in the Excel table below)
– If the Num_digits is zero the number is rounded to its nearest integer or whole number.  (See cell B3)
– If the Num_digits is less than zero it identifies the number of digits to the LEFT of the decimal point-ones, tens, hundreds, etc.  (See cell B4)

All arguments are required.

Using the manufacturing cost per unit example and applying the ROUND function to round a number in Excel for the month of January, the results are:

Reference Table of Example for Using the Round Function in Excel

To learn more about the ROUND function with other examples.

How to Round Down in Excel with ROUNDDOWN

This section focuses on how to round down in Excel using another type of function called the ROUNDDOWN function.

The ROUNDDOWN function is also under the Math/Trig functions and it rounds a number in Excel towards zero. So numbers 1, 2, 3, 4, 5, 6, 7, 8 and 9 are always rounded down which makes the resulting values lower.

The Syntax or formula of the ROUNDDOWN function

Where:

Number

– Represents the number that has to be rounded (reference cells are allowed).

Num_digits

– Represents the number of digits to round the number to. You can reference the number itself or a reference cell.
– If the Num_digits is greater than zero it rounds a number in Excel DOWN towards the RIGHT of the decimal point- tenths, hundredths, thousandths, etc. (See cell B2 in the Excel table below)
– If the Num_digits is zero it rounds a number in Excel DOWN towards its nearest integer or whole number. (See cell B3)
– If the Num_digits is less than zero it rounds a number in Excel DOWN towards the LEFT of the decimal point- ones, tens, hundreds, etc. (See cell B4)

Applying the ROUNDDOWN function in Excel to the month of January Manufacturing Cost per unit:

Reference Table of Example for Using the Rounddown Function in Excel

Notice how the rounded numbers are lesser than the original non-rounded number. The further you round to the left of the decimal point the lesser it will be, the ROUNDDOWN function is useful for how to round numbers in Excel towards zero. You can use this function to achieve conservative figures and balances.  Learn more about the ROUNDDOWN function with other examples.

How to Round Up in Excel with ROUNDUP

This last section focuses on how to round up in Excel using another function called the ROUNDUP function.

The ROUNDUP function is the opposite of the ROUNDDOWN function as it rounds a number in Excel away from zero. The numbers 1, 2, 3, 4, 5, 6, 7, 8 and 9 are always rounded up so resulting values are higher. However, similar to the ROUND and ROUNDDOWN functions it’s under the Math/Trig functions.

The Syntax or formula of the ROUNDUP function:

Where:

Number

– Represents the number that has to be rounded (reference cells are allowed).

Num_digits

– Represents the number of digits to round the number to. You can reference the number itself or a reference cell.
– If the Num_digits is greater than zero it rounds a number in Excel UP towards the RIGHT of the decimal point- tenths, hundredths, thousandths, etc. (See cell B2 in the table below)
– If the Num_digits is zero it rounds a number in Excel UP towards its nearest integer or whole number. (See cell B3)
– If the Num_digits is less than zero it rounds a number in Excel UP towards the LEFT of the decimal point – ones, tens, hundreds, etc. (See cell B4)

Applying the ROUNDUP function to the month of January Manufacturing Cost per unit.

Reference Table of Example for Using the Roundup Function in Excel

Take note of how the rounded numbers are greater than the original non-round number especially when you round a number in Excel further to the left of the decimal point because this function is useful on how to round numbers in Excel further away from zero. To learn more about the ROUNDUP function with other examples.

Let’s Wrap Up the ROUND Functions for How To Round Numbers in Excel

To summarize the results of how to round numbers in Excel using ROUND, ROUNDDOWN, and ROUNDUP functions for the month of January:

Below is a summarized table showing the results of how to round numbers in Excel using ROUND, ROUNDDOWN, and ROUNDUP functions from January to June:

As you can see, to round numbers in Excel makes it easier and faster to work with, simple to report and communicate to end-users, and standardizes the numbers to significant and meaningful values only.

You’ve learned there are various ways on how to round numbers in Excel- you can use Excel’s built-in formatting features, the ROUND, ROUNDDOWN, and ROUNDUP functions. For reference, use this free table to help you identify and differentiate which ROUND functions to use.

Was this helpful?