Peter Eb on Custom Number formats in Excel (and this works in Reporting Services too!)

This saves so much hassle when dealing with $K or $M, or even $B values.

HOWTO: Show numbers as 1,000s in Excel

Posted Thursday, April 17, 2008 6:00 PM by petereb

This time there's nothing really specific about PPS. It's more of an Excel post actually... 

I've seen this a few times. Sometimes the data form authors want to show is big, and they want to show 1 instead of 1000 or 1 instead of 1000000. There are a bunch of complicated games you an play with formulas and rounding to get close to this behavior. But in this case you shouldn't settle for close. This is bizarrely not explicitly shown in the Excel UI, but this is well supported by the custom number format functionality. You just use a custom number format:

in thousands: #,

in millions: #,,

Not sure off hand what to do in other regional settings. You may still have to use a comma "," or you might need to use the decimal separator "." or whatever it is called in your culture... In my test the number format works well:

Although I agree formatting a matrix properly is a little tricky until you get the hang of it, unfortunately this is a short post and I'll save larger formatting discussions for another post. For data entry into cells with this formatting, you still type the entire number. If you want to avoid that part too then I suggest doing that conversion in the fact table to begin with...

Peter Eb.