Link to home
Start Free TrialLog in
Avatar of Calyx Teren
Calyx TerenFlag for United States of America

asked on

Excel custom format for numbers shown as ($K)

I need an Excel custom format to apply K, M, B endings when the original numbers are shown as ($K). For example, $4 is really $4000, and $87.6K is really $87,600,000 or $87.6M. This is common way of improving readability in corporate financial documents where the numbers are all large.

I have an Excel custom format that shows real numbers with their K and M shortened versions:

I tried making a second version, but it didn't work. It looks like Excel knows what M and K are and won't let me apply them when the source numbers don't agree with the normal definition.
Avatar of byundt
Flag of United States of America image

If the raw data are text that look like numbers, you must convert them to real numbers before using them in calculations. You can do so with a formula like:

Open in new window

You may then use either your original Custom format, or
If you want to show B for billions, you could use a Custom number format [>1000000000]$#.##0,,,\B;[>1000000]$#.0,,\M;$#.0,K
and a formula to convert text to numbers like:

Open in new window

Avatar of Arana (G.P.)
Arana (G.P.)

I dont get how  $87.6K is really $87,600,000 or $87.6M, is it 87.6k or 87.6M ?
Avatar of Calyx Teren


[Answer to Arana's question] In corporate planning documents, where the numbers are often in thousands, millions, and billions, it's very common for amounts to be shown as $K, meaning that you should add three zeroes onto the end of every number. Fictitious example:

Revenue per region in 2019 ($K):
- EMEA - $190
- APAC - $65
- AMR-N - $478
- LATAM - $32

These tables are often copied from Excel, and the benefit of doing it this way instead of with Ks and Ms is that the numbers remain numbers and can still be added, multiplied, etc. The formula I listed below takes care of that, but it's not common knowledge.
Avatar of Calyx Teren
Calyx Teren
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I understand about corporate numbers and the need to keep smaller numbers, its just I was confused about your statement, as I read it i was under the impression that same amount could mean 3 different quantities, which was not the case : if  $4 is really $4000 and 86,600,000 was in fact 87.6M why would you want to add 3 zeroes in one case but not in the other, I had to reread it more times to actually understand what you where trying to say.