Rounding via formats

Custom formats

By Jeff Robson
When you’re doing your budgets or annual reports, you often need to switch easily between dollars and cents, whole dollars, and thousands.

Switching between dollars and cents, and whole dollars is easy: just use Excel’s built in formats (either the “$” or “,” buttons) and increase or decrease the number of decimal places shown using the “Increase decimal” or “Decrease decimal” buttons.  All pretty logical so far.

But what about showing your numbers rounded to the nearest thousand?

It’s a real pain in the neck to use the ROUND function because you have to modify every formula!  Ick!

A much easier way is to round all the numbers using a custom format:

1. Select the cells to be formatted.

2. Go to Format > Cells > Number > Custom

3. Enter #,###,;-#,###,;”-“ in the Type field, or #,###, ;(#,###),;” -”  if you prefer brackets around your negative numbers.

Voila!  All your numbers have been rounded to the nearest thousand, yet you haven’t changed the underlying data at all!