ROUND in Excel: how to use it
With ROUND you choose how many decimal places remain. Formatting only changes what you see, ROUND changes the value itself.
Try the first exercises for freeIn the lesson ROUND, ROUNDUP and ROUNDDOWN
What does ROUND do?
ROUND rounds a number to the number of decimal places you choose: with 2 you get 12.35, with 0 a whole number. From 5 upwards it goes up, below that it goes down, as you learnt at school.
Important: formatting only changes what you see, while ROUND changes the number itself. If you keep calculating with a rounded amount, such as the tax on an invoice, round it with the function. ROUNDUP and ROUNDDOWN always round in one direction.
When do you use ROUND?
- A tax amount to two decimal places: =ROUND(A2*20%,2).
- An average without a long string of digits: =ROUND(AVERAGE(B2:B30),1).
- The number of tables for your guests, always rounded up: =ROUNDUP(A2/8,0).
- An amount to the nearest hundred for a budget: =ROUND(A2,-2).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
To the nearest number: from 5 upwards it goes up, below that it goes down.
Filled in: =ROUND(A1,2)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
ROUND | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | ROUND |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
number | The number or calculation you're rounding. | A1 |
num_digits | How many decimal places are left. With 0 you get a whole number, with -1 tens and with -2 hundreds. | 2 |
, | A comma separates the arguments from each other. | , |
ROUND step by step
C1 contains =ROUND(A1,2). Rounded to two decimal places. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click C1 and type ROUND with a bracket, followed by the cell with the number, A1, and a comma.
=ROUND(A1, - Type how many decimal places should be left: 2.
=ROUND(A1,2 - Close the bracket and press Enter: 12.3456 becomes 12.35, because the third decimal place is a 5.
=ROUND(A1,2)
More examples
Always up
C2 contains =ROUNDUP(A2/B2,0). 27 divided by 8 is 3.375. With ROUND you'd get 3 tables, and then three guests would have no seat.
To the nearest hundred
C1 contains =ROUND(A2,-2). With -2, Excel rounds to hundreds: 18462 becomes 18500.
Always down
C1 contains =ROUNDDOWN(A2,1). Rounded down to one decimal place: 7.85 becomes 7.8. ROUND would give 7.9.
Variations
ROUNDUP always rounds up and ROUNDDOWN always rounds down. CEILING and FLOOR round to a multiple, such as 0.05.
Where it often goes wrong
- An invoice can be a penny out if you only change the formatting.
- With a negative number of digits you round to tens or hundreds.
Practise this in the lesson ROUND, ROUNDUP and ROUNDDOWN. The first exercises are free.
Frequently asked questions
How do I round to the nearest 0.05?
With =ROUND(A1/0.05,0)*0.05, or =CEILING(A1,0.05) to always round up.
Read next
CEILING and FLOORMOD and INTLARGE and SMALLMEDIAN and MODESEQUENCE and RANDBETWEEN