  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 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",""),",","")
``````

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",""),",","")
``````
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

[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.