Avatar of Calyx Teren
Calyx Teren
Flag 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:
[>1000000]$#.0,,"M";[>999]$#,"K";$#,##0

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.
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Arana (G.P.)

8/22/2022 - Mon
byundt

$#,##0.0,K
$#,##0.0,,\M
byundt

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:
=IF(RIGHT(B2,1)="M",1000000,IF(RIGHT(B2,1)="K",1000,1))*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"$",""),"M",""),"K",""),",","")

Open in new window


You may then use either your original Custom format, or
[>1000000]$#.0,,\M;[>999]$#.0,K;$#,##0
byundt

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:
=IF(RIGHT(B2,1)="B",1000000000,IF(RIGHT(B2,1)="M",1000000,IF(RIGHT(B2,1)="K",1000,1)))*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"$",""),"B",""),"M",""),"K",""),",","")

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Arana (G.P.)

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

ASKER
[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.
ASKER CERTIFIED SOLUTION
Calyx Teren

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Arana (G.P.)

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.