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]
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.
I have an Excel custom format that shows real numbers with their K and M shortened versions:
[>1000000]$#.0,,"M";[>999]
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.
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:
You may then use either your original Custom format, or
[>1000000]$#.0,,\M;[>999]$
=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]$
If you want to show B for billions, you could use a Custom number format [>1000000000]$#.##0,,,\B;[
and a formula to convert text to numbers like:
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",""),",","")
[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.
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.
Hi everyone, thank you for your work on my question. I kept researching and eventually found a site that provided enough information that I was able to figure it out for myself just now.
The correct formula for representing a number like 437 as $437K and 18,225 as $18M is:
[>999]$##0.0,"M";[>1]$##0"
Original format, for numbers that are written out fully, was [>1000000]$#.0,,"M";[>999]
The site is https://exceljet.net/custom-number-formats and is well worth reading.
I figured out the millions first. The K was trickier and kept displaying $0K for 437, until the info from this site told me that the comma stands for thousands in custom formats. When I eliminated the comma from both the millions clause, which had two commas in the original, and the K, it worked perfectly.
The correct formula for representing a number like 437 as $437K and 18,225 as $18M is:
[>999]$##0.0,"M";[>1]$##0"
Original format, for numbers that are written out fully, was [>1000000]$#.0,,"M";[>999]
The site is https://exceljet.net/custom-number-formats and is well worth reading.
I figured out the millions first. The K was trickier and kept displaying $0K for 437, until the info from this site told me that the comma stands for thousands in custom formats. When I eliminated the comma from both the millions clause, which had two commas in the original, and the K, it worked perfectly.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial