Excel Formatting - Turn Rounded Number to Non Rounding (Without Formulas)

[Martynt74]

I've got a file which pulls in data from an external data source. The external data source is formatted to round to Thousands. This is setup by the Workbook and works fine for all the other worksheets. However i've created a new sheet showing a smaller section and rounding to thousands doesn't really work.

I'd like to display these as the base numbers, however all the formatting methods i know always go up and not the other way. Because they're pulling values directly into the worksheet i can't modify using a formula without having a complete duplication with one sheet holding my values and then another sheet (or multiple columns in the same sheet) referencing the source data * 1000.

Anyone know of a way to format cells downwards rather than upwards?

 

[wesimmo]

Try Custom format

#,###",000"

Careful though because anybody doing calcs on it later will need to know that the value isn't what is displayed.

 

[Martynt74]

Try Custom format

#,###",000"

Careful though because anybody doing calcs on it later will need to know that the value isn't what is displayed.

Thanks, that kind of works, but it just rounds everything to whole numbers (And for any zeroes, it pulls in the ,000)

I was hoping to keep the rounding. So $18.49356 would turn into $18,493.56 if that makes sense? Whereas your solution pulls in $18,000

 

[wesimmo]

Ah, not rounded, just pulled in divided by 1000.

That's trickier...

I don't know why you cant just pull the data into a Table and have a column that does the *1000 and use that?

If you use a table linked to your data source then there must be an out of the ordinary reason why using a Table Column wont work.

 

[Martynt74]

It's an Excel Add in where you add various data elements into an organised report from a third party add-in called Adaptive Planning. The settings are set to round everything to thousands which works for various other worksheets, but due to the values i'm now looking at it doesn't quite work.

Was mainly trying to avoid the data table and then use a formula as it adds complexity vs using some formatting. It also throws off some functionality as you can click into each cell and see where the data is being pulling from due to the add-in and drill down into it etc, which isn't possible using the *1000 method.

I think i'll just remove the workbook rounding and then use formatting to turn the other sheets into #,###, formatting instead to drop those down into thousands.