Display numbers rounded to the nearest thousand

Question

Display numbers rounded to the nearest thousand

Anonymous

How do I format a number displaying to thousands? This is not a graph but a regular worksheet number.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

9 additional answers

Answer 1

Ashish Mathur 101.8K Volunteer Moderator

Hi,

The Custom format should be 0.0,

Hope this helps.

0 comments

Answer 2

... How to add commas to the format (i.e., 1122334 becomes 1,122,000)?

If I could wade in here, you need to force the comma's display since you are forcing the zeroes in the ones, tens and hundreds (truncating the value)

#,###,,\0\0\0

... bu that might produce unexpected results for numbers less than 1,000 so you can conditionally apply it like this,

[>999]#,##0,,\0\0\0;General

Answer 3

Anonymous

Hi,

So 1001 would round to 1000, 1 rounds to what? You can try this custom format

#,\0\0\0

Thanks Mike! I never would have guessed that solution.

Next question: How to add commas to the format (i.e., 1122334 becomes 1,122,000)?

Thanks again,

Greg

0 comments

Answer 4

Anonymous

If you want to just display to the nearest thousand, but retain the original value, use a custom number format "0," (without the quotes). For example, if A1 contains the value 123,456, the cell will display 123.

0 comments

Answer 5

Anonymous

Hi,

So 1001 would round to 1000, 1 rounds to what? You can try this custom format

#,\0\0\0

0 comments

Share via

Display numbers rounded to the nearest thousand

9 additional answers