Excel: how to round numbers to the nearest 5 or 10?

Round up, round down or round off numbers in Excel with these formulas! Many examples are shared so you can learn easily the Excel ROUND functions. Discover how to round decimal numbers now!

Do you want to round up or round down numbers in your Excel sheet? Maybe you need to round numbers to the nearest multiple of 10 or 100? Is the ROUND function in Excel rounding up or down?

When presenting numbers to your manager or editing an invoice for a customer, it’s sometimes better to round numbers up or down. Instead of using the decimal places box in MS Excel for each cell, you can use a rounding function!

Learn how to round numbers to a specific decimal place and how to round numbers up or down with the formulas below. Of course, we will teach you how to round numbers to the nearest 5, 10, 100 or 1,000. Ready to learn?

Use the MROUND function in Excel to round a number to a specific multiple. The function will round up or down the number to the closest multiple you entered in its formula.

Syntax of the MROUND function: =MROUND(number,multiple).


* number – the value to round.
* multiple – the multiple to which you want to round the number argument.

MROUND function – Rounding a number to the nearest 5

Use this formula for the MROUND function: =MROUND(number,5).

MROUND function – Rounding a number to the nearest 0.5

Use this formula in the MROUND function: =MROUND(number,0.5).

MROUND function – Rounding a number to the nearest 100

Use this formula for the MROUND function: =MROUND(number,100).

You can also round a number to the nearest multiple of 10, 100, 1,000… by using the ROUND function in your Excel worksheet. Discover how, below!

Use the ROUND function in Excel to round a number to a certain number of decimals or to the nearest multiple of 10, 100, 1,000, 10,000, etc.

Syntax of the ROUND function: =ROUND(number,num_digits).


* number – the value to round.
* num_digits – the number of digits to which you want to round number.

Positive numbers in the num_digits argument correspond to the number of digits you want in your number. 1=1 digit like 43.1; 2=2 digits like 43.12, etc.
Use a negative number as num_digits and your number will be round to the nearest multiple of 10, 100, 1,000 and so on. -1=multiple of 10, -2=multiple of 100, –3=multiple of 1,000, etc.
Enter 0 as the num_digits argument to round to the nearest number without decimals.

ROUND function – Rounding a number to the nearest multiple of 100

In this example, we want to round numbers to the nearest multiple of 100. Numbers will be round up or down, depending on the nearest multiple of 100.
The formula we use is =ROUND(number,-2).

ROUND function – Rounding a number to 2 digits

Use this formula for the ROUND function if you only want 2 decimals in your result: =ROUND(number,2).

ROUND function – Rounding off a number (no decimal)

In this example, we want numbers to be rounded to the nearest number without decimals. The formula we are using is =ROUND(number,0).

If you wish to round up a number, use the ROUNDUP function in your Excel sheet. More information about this function below.

In Microsoft Excel, use the ROUNDUP function to round up a number.

Syntax of the ROUNDUP function: =ROUNDUP(number,num_digits).

* number – the value to round up.
* num_digits – the number of digits to which you want to round number up.

ROUNDUP function – Rounding up a number to 2 digits

Use this formula for the ROUNDUP function if you want to show only 2 decimals: =ROUNDUP(number,2).
In this example, you can compare the results for the ROUND, ROUNDUP and ROUNDDOWN functions with 2 digits.

ROUNDUP function – Rounding up a number without digits

Use this formula for the ROUNDUP function if you don’t want to show any decimal: =ROUNDUP(number,0).
In this example, you can compare the results for the ROUND, ROUNDUP and ROUNDDOWN functions without digits.

On the contrary, if you want to round numbers down in Excel, use the ROUNDDOWN function!

In Microsoft Excel, use the ROUNDDOWN function to round down a number.

Syntax of the ROUNDDOWN function: =ROUNDDOWN(number,num_digits).

* number – the value to round down.
* num_digits – the number of digits to which you want to round number down.

ROUNDDOWN function – Rounding down a number to 2 digits

Use this formula for the ROUNDDOWN function if you wish to show only 2 decimals: =ROUNDDOWN(number,2).
In this example, you can compare the results for the ROUND, ROUNDUP and ROUNDDOWN functions with 2 digits.

ROUNDDOWN function – Rounding up a number without digits

In Excel, use this formula for the ROUNDDOWN function if you don’t want to show any decimal: =ROUNDDOWN(number,0).
In this example, you can compare the results for the ROUND, ROUNDUP and ROUNDDOWN functions without digits.

Now you know all the ROUND functions in Excel and can use them with ease!

Rounding up or down or to the nearest multiple of 100 in Excel is now a skill of yours, well done. To master Microsoft Excel, join our Certifying Course now!

Receive a Free Spreadsheet Cheat Sheet if you sign up today.

Advertising
Analytics

Categories

AllAdvertisingFormatsPlatformsKPI'sAd News

What is O&O Advertising?

Discover one more important jargon term used in Programmatic Advertising: O&O advertising. Get familiar with O&O formats and advantages.

Read More

Best DSPs for Programmatic Advertising

Ted Jordan shares his list of best DSPs for Programmatic Advertising so you don’t have to lose time searching the internet to find the right Demand-Side Platform for your campaigns.

Read More
What is Programmatic Advertising?

What is Programmatic Advertising?

Understand what programmatic advertising is and how it works to increase your ROI and become a programmatic specialist.

Read More
Programmatic Advertising: What is a SSP?

Supply-Side Platforms (SSPs) Explained

SSPs basics in advertising: learn what Supply-Side Platforms are used for and who use them. Get familiar with more programmatic advertising technical terms.

Read More
Programmatic Advertising: What is a DSP?

What are DSPs?

A DSP is a great tool to use but what does DSP stand for in marketing? What are some Demand-Side Platforms examples? Today, we answer your questions!

Read More

Advertising News – 09 June 2024

This week, we'll explore Google's latest algorithm update and it's massive impact on independent websites, the newly passed media Freedom Act in the EU aimed at protecting journalists, and a...

Read More

Categories

AllFunctionsToolsShortcut Keys
Split Semicolon Separated Value in Excel

How to split semicolon separated values in Excel

Split semicolon separated values in Excel into columns and rows, or convert columns to semicolon separated values, like an Excel Master!

Read More

How to change shapes in Excel column charts

Learn how to easily change the columns shape in your Excel charts so your colleagues and customers will remember your impacting graphs.

Read More

How to use the LEFT, RIGHT and MID functions

Learn how to extract text in Excel with the LEFT, RIGHT and MID functions. Plenty of examples to learn from and functions are explained with simple words. If you start...

Read More

Excel IF statement: how to use it for 3 conditions

Learn how to use the Excel IF statement with multiple conditions and combine it with OR and/or AND functions!

Read More
Using Text to Column to Extract texts on Excel

Excel: How to split text into columns

If you need to extract data in Excel, you may want to use the Text to Columns tool. With only 3 steps, it's really easy to split text into columns.

Read More
Navigating with Tab, Enter, and Shift Key on Excel.

How to move between cells when arrow keys are not working (Excel)

Learn how to move between Excel cells without using your mouse or arrow keys. Discover more shortcuts and tricks below!

Read More