How to ROUND a Number to Nearest 1000

Excel - How to Round A number To Nearest 1000 - Featured ImageMany times people work with numbers that are too detailled to present. They may have too many decimal numbers, or perhaps the numbers are so big that rounding them down to thousands may look cleaner. A colleague asked me recently how to round numbers to the nearest thousand. So how do you do it?

Formula

The formula arguments are:

= 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.

Below table shows 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.

Note: If your intention is to simply present your numbers in thousands, ROUND is not the best approach. Instead use the custom number format #.##0. K.

Please leave a comment below with questions or suggestions.

Rick de Groot

Rick is the founder and editor of Excel Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills. Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

Leave a Reply

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

Post comment