When we are creating a report with Excel we often want to show values in thousands. So by that I mean we might have data that are sourced as dollars and cents and instead fo dividing everything by 1000, we just want the report to display as either thousands or even millions of dollars. In TM1 we can format a measure to display values in precisely this way and we can have those pull through to a Planning Analytics for Excel Exploration report. Or we can create a new measure that divides the dollars and cents measure by either 1000 or a million and use those. But what if we are using a Custom or Dynamic report and don’t want to create a new measure or change the formatting in TM1?
Formatting Thousands in Excel
Well, the answer where we want to display values in a report only in Excel in thousands is to format the values using plain old excel number formatting. To foo this just select the cells you want to format to show 1000’s and in the Excel ribbon, select the number formatting dropdown and then “More Number Formats…”.
Custom Number Display
Then in the dialog box, select Custom and type the formatting you want.
The likely formats you might want include (just copy the bits inside the inverted commas!):
- “,##0.0,” to display thousands with a single decimal place
- “,##0,” to display thousands with no decimal place
- “,##0.0,,” to display millions with a single decimal place (it has one more comma)
- “,##0,,” to display millions with no decimal place (this has one more comma, too)
That’s it. Pretty easy huh!
If you want more information on how to do this, please head over to Professor Excel where they have a great post on it.
