For dashboards and summaries, big numbers read better rounded to the nearest thousand or million. ROUND with a negative number of digits rounds to the left of the decimal point.
The example
1,234,567 → 1,235,000 (nearest thousand).
| A | B | |
|---|---|---|
| 1 | Value | Nearest 1,000 |
| 2 | 1,234,567 | 1,235,000 |
The formula
The formula:
How it works
How it works:
ROUND(value, -3)rounds to the nearest thousand;-6to the nearest million.- A negative digits argument rounds to the left of the decimal point — the mirror image of rounding decimals.
- Pair with a custom number format to display in thousands without changing the value:
#,##0,"K". - Use
ROUNDUP/ROUNDDOWNwith negative digits to force direction (e.g. budget ceilings).
Round vs format: ROUND(A2,-3) changes the stored value to a multiple of 1,000. A number format like #,##0, (trailing comma) only displays the value in thousands while keeping full precision. Use ROUND when totals must match the rounded figures; use the format when you just want a cleaner look.
Try it: interactive demo
Value and digits.
Variations
Nearest million
-6 digits:
Round up to thousand
Always up:
Display in K
Format, not value:
Pitfalls & errors
Negative digits round left. -3 means thousands; positive digits round decimals instead.
Rounded totals may not add up. Sum of rounded parts can differ from the rounded total by a thousand or two.
Format ≠ ROUND. A trailing-comma format only changes the display, not the stored number.
Practice workbook
Frequently asked questions
How do I round to the nearest thousand in Excel?
How do I show numbers in thousands without changing them?
Why don't my rounded numbers sum correctly?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class