How to Round Numbers in Excel: ROUND, ROUNDUP, ROUNDDOWN and MROUND

shape
shape
shape
shape
shape
shape
shape
shape
How to Round Numbers in Excel: ROUND, ROUNDUP, ROUNDDOWN and MROUND - featured image

Excel can round numbers in two very different ways: by changing how a number is displayed, or by changing the number itself. The difference matters for prices, tax, payroll and any total that must add up exactly. This guide explains the six rounding functions you will use most, ROUND, ROUNDUP, ROUNDDOWN, MROUND, CEILING and FLOOR, along with INT and TRUNC, shows how to round to decimals, whole numbers, tens and hundreds, explains why a total can look wrong when you only format cells, and gives worked examples and fixes for the common mistakes.

Updated November 2026

In this guide

  1. Formatting vs rounding
  2. The ROUND function
  3. ROUNDUP and ROUNDDOWN
  4. MROUND, CEILING and FLOOR for multiples
  5. INT and TRUNC
  6. Which function to use
  7. Worked example: invoice with tax
  8. Rounding to significant figures
  9. Practice problems
  10. Rounding in dates, time and currency
  11. Quick recap
  12. Rounding a whole column and keeping the original
  13. Common mistakes

Formatting vs rounding

When you click the Decrease Decimal button, Excel changes only the display. The cell still stores the full value. If A2 holds 2.567 and you display it with one decimal, you see 2.6, but a formula that uses A2 still works with 2.567. This is why a column of values that appear to add up to 10.0 may total 10.1 when you sum them, since the hidden decimals add up. To change the stored value, use a rounding function. Choose display formatting for reports where the exact value must stay available, and use a function when you need the rounded value in later calculations, such as invoices.

The ROUND function

ROUND rounds to a number of digits that you choose. The syntax is =ROUND(number, num_digits). With a positive num_digits, it rounds to decimal places: =ROUND(2.567,2) returns 2.57. With zero, it rounds to a whole number: =ROUND(2.567,0) returns 3. With a negative number, it rounds to the left of the decimal point: =ROUND(1234.5678,-2) returns 1,200. ROUND follows the usual rule that 5 and above rounds away from zero, so 2.5 becomes 3 and minus 2.5 becomes minus 3.

ROUNDUP and ROUNDDOWN

ROUNDUP always rounds away from zero and ROUNDDOWN always rounds toward zero, regardless of the next digit. =ROUNDUP(2.341,1) returns 2.4, even though the next digit is only 4. =ROUNDDOWN(2.399,1) returns 2.3, even though the next digit is 9. Use ROUNDUP when you must never under-charge, such as the number of boxes needed to ship 41 items at 10 per box: =ROUNDUP(41/10,0) returns 5. Use ROUNDDOWN when you must never over-count, such as the number of full weeks in 50 days: =ROUNDDOWN(50/7,0) returns 7.

MROUND, CEILING and FLOOR for multiples

These functions round to a multiple. =MROUND(17,5) returns 15, because 17 is closer to 15 than to 20. =MROUND(18,5) returns 20. CEILING always goes up to the next multiple: =CEILING(17,5) returns 20. FLOOR always goes down: =FLOOR(17,5) returns 15. These are useful for pricing in steps, such as rounding a price to the nearest 0.05 with =MROUND(A2,0.05), or rounding time to the nearest quarter hour with =MROUND(A2,"0:15"). MROUND needs the number and the multiple to have the same sign, otherwise it returns an error.

INT and TRUNC

INT rounds down to the nearest integer, which for negative numbers means away from zero: =INT(-2.5) returns -3. TRUNC removes the decimal part without rounding: =TRUNC(-2.5) returns -2, and =TRUNC(2.567,2) returns 2.56. For positive numbers, INT and TRUNC give the same result. Choose TRUNC when you need to cut off digits, such as keeping two decimals of a rate without rounding up.

Which function to use

The table compares the main functions for the same input.

FormulaResultWhat it does
=ROUND(2.567,2)2.57Nearest, 2 decimals
=ROUNDUP(2.341,1)2.4Always up, 1 decimal
=ROUNDDOWN(2.399,1)2.3Always down, 1 decimal
=ROUND(1234.5678,-2)1200Nearest hundred
=MROUND(17,5)15Nearest multiple of 5
=CEILING(17,5)20Up to multiple of 5
=FLOOR(17,5)15Down to multiple of 5
=INT(-2.5)-3Down to integer
=TRUNC(-2.5)-2Cut decimals

Worked example: invoice with tax

An item costs 19.99 and the tax rate is 5 percent. The tax is 19.99 times 0.05, which is 0.9995. If you only format the cell to two decimals, it shows 1.00, but the stored value is 0.9995. With ten of these items, the displayed values would show 10.00 in tax, but the sum is 9.995 and the total might show a one-cent difference. To avoid mismatches, round the tax: =ROUND(B2*0.05,2) returns 1.00 and the invoice total matches the sum of the displayed lines. This is the standard approach for invoices, payroll and any report that must add up on paper. Our annual income calculator and the invoice guides on this site use the same idea.

Rounding to significant figures

Excel has no built-in function for significant figures, but you can use =ROUND(A2,2-INT(LOG10(ABS(A2)))) to round to 3 significant figures. For 12,345 the result is 12,300, and for 0.012345 it is 0.0123. Change the 2 to the number of figures minus one. This is helpful in science and engineering reports, where precision is described in significant figures rather than decimal places. Wrap it in an IF function to handle zero, since LOG10 of zero is an error: =IF(A2=0,0,ROUND(A2,2-INT(LOG10(ABS(A2))))).

Practice problems

Try these on your own sheet. First, round 7.846 to one decimal place: =ROUND(7.846,1) returns 7.8. Second, a job needs 23 pallets and each truck holds 4, so use =ROUNDUP(23/4,0) to get 6 trucks. Third, a price of 4.37 must end in 0 or 5 cents, so =MROUND(4.37,0.05) returns 4.35. Fourth, round 15,678 to the nearest thousand with =ROUND(15678,-3), which returns 16,000. Fifth, cut 9.999 to one decimal without rounding with =TRUNC(9.999,1), which returns 9.9. Check each answer by comparing the result with the original and thinking about which direction the number moved. Doing a few of these by hand builds the habit of choosing the right function instead of guessing.

For a bigger exercise, create a table of ten prices in column A, a tax rate in B1, and a tax column with =ROUND(A2*$B$1,2). Then add a total column and compare the sum of the displayed tax values to the sum of the rounded formulas. They now match exactly. Repeat with a formatted but unrounded column and note the difference. This is the exact situation that causes one-cent errors on invoices, and seeing it yourself makes the fix easy to remember.

Rounding in dates, time and currency

Rounding is not only for plain numbers. Time in Excel is stored as a fraction of a day, so rounding a timesheet to the nearest quarter hour uses =MROUND(A2,"0:15"), and rounding down to the full hour uses =FLOOR(A2,"1:00"). For payroll, many companies round clock-in times in favor of a fixed rule, so agree the rule with the client before building the formula. For currency, always round to the number of decimals the currency uses, usually two, and round each line before you total it so the invoice agrees with the printed lines. For currencies without decimals, such as the Japanese yen, use ROUND with zero digits. When you convert between currencies, round only the final converted amount, not the exchange rate, because rounding the rate itself introduces a visible error on large amounts. Finally, when you build a dashboard, use rounding functions for values that feed other formulas and number formats for labels and charts, so the data stays accurate while the display stays clean and easy to read.

Quick recap

Use ROUND for normal rounding, ROUNDUP and ROUNDDOWN to force a direction, MROUND, CEILING and FLOOR for multiples, and INT or TRUNC to cut decimals. Use formatting only for display. Round at the end of a calculation, keep the raw data, and test totals against the displayed values. A negative num_digits rounds to tens, hundreds and thousands. If a function returns an error, check that the input is a number and not text.

Rounding a whole column and keeping the original

To round many values, add a helper column next to the data and fill the formula down. Keep the original column for reference, and base later calculations on the rounded column if the rounded values are the official numbers. If you want to replace the original, copy the rounded results and use Paste Special, then Values, over the old data. Another way is to use Paste Special, Multiply, with a rounding formula, but it is less clear. When the data will be audited, keeping both columns with clear headings is the best practice, because anyone can see the rounding step.

Common mistakes

The most common mistake is formatting numbers instead of rounding them, which leads to totals that look off by a cent. Another is rounding at every step of a long calculation, which builds up errors, so round only at the end unless a rule requires it. A third is to use ROUND on text values, which gives a #VALUE! error, so convert text first. A fourth is to mix up the sign of num_digits, since a negative value rounds the left side. Finally, remember that Excel stores about 15 digits of precision, so very long decimals may show tiny differences such as 0.30000000000000004 in other tools, and ROUND to a sensible number of digits removes them.

Frequently asked questions

How do I round to 2 decimal places in Excel?

Use =ROUND(A2,2).

How do I always round up in Excel?

Use ROUNDUP, for example =ROUNDUP(A2,0).

How do I round to the nearest 5 or 10?

Use MROUND, for example =MROUND(A2,5).

What is the difference between formatting and ROUND?

Formatting changes only the display. ROUND changes the stored value.

How do I round to the nearest hundred?

Use =ROUND(A2,-2).

Related guides and free tools

Sources: IF function, Microsoft Support

Leave a Reply

Your email address will not be published. Required fields are marked *