Format an Excel Report to show Thousands

When we are creating a report with Excel we often want to show values that are sourced as dollars and cents 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 formats 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 format a report only in Excel is to format the values using plain old excel number formatting. To format the cells in Excel, 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…”.

Formatting a Planning Analytics Excel report to use thousands or millions.

Custom Number Format

Then in the Number Formatting dialog box, select Custom and type the formatting you want.

Displaying thousands or millions in a PAX Dynamic or Custom report.

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!

You might also like

Also in the series