Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

Round to nearest 1000

Financial reports can sometimes be very messy to look at. Whereas consumers want to understand the big picture, sometimes the numbers are so detailed, they are difficult to comprehend.

Maybe the numbers have too many decimal numbers, or perhaps the numbers are so big that rounding them down to the nearest thousand may look cleaner. In this article you learn how to make excel round a number to the closest thousand. So how do you do it?

Syntax of the ROUND function

When you need to round a number you can use the ROUND function. The function takes two arguments, being the number to round and the amount of digits to round the number to.

= ROUND (number, num_digits)

In below picture cell C6 contains the formula:

=ROUND( B6, -3)

Round number to Thousands

The formula rounds the value of cell B6 (547,982.00) to 548,000.00. So how does that work? The ROUND function takes the cell reference with a number as first argument. The second argument then instructs the amount of digits to which you want to round the number.

A positive number will round a number to the specified amount of decimals and a negative number will round a number to the left of the decimal separator.

In this example, using -3 instructs the ROUND function to move the decimal three positions to the left. Or in other words, to the nearest multiple of 1000.

Transforming numbers

To give you a better idea of the effect different numbers have I added below table. It show the effect of changing the ‘num_digits’ argument from ‘3’ to ‘-3’. It is as if the comma takes a step back with each decrease of the ‘num_digits’ argument.

Excel ROUND function

And that’s how you can ROUND your numbers to the desired output. Please note that if your intention is to simply present your numbers in thousands, ROUND is not the best approach. Instead use the custom number format.

In the next post we will take a look at one of Excel’s most powerful functions, SUMPRODUCT.

Share on:

Latest from my blog

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.