@@mantakemahlaremm It depends if you want to do negative millions, negative thousands, negative smaller numbers as well as positive millions, positive thousands and positive smaller numbers. If you do, it can't be done directly with this feature because there is a built-in limit of 3 definitions. So ... you would use custom formatting within conditional formatting instead. Here's how: a) Select the cell or cell range you are working with. b) On the *Home* ribbon, click *Conditional Formatting*, then *New Rule* , then *Format only cells that contain* . c) Create your condition, e.g. *Cell Value | Greater than | 1000000* or *Cell Value | Less than or equal to -1000000* etc. d) Click the *Format* button. e) In the Format Cells dialog, click the *Number* tab, then choose *Custom* and type a single custom format definition into the *Type* box. f) Click OK twice to close both dialogs. g) Repeat from step (b) for each different condition and format you need. Let me now how you go. Jason
@@JasonMorrell Thank you so much for your help. I managed to figure it out and ended up with 2 conditions with their reverse for (-ve) numbers: Condition 1: Cells that contain Cell Value Greater than 1000000 Format: [>=1000000000] R0 \b;[>=1000000] \R0 \m; R0 Condition 2: Cells that contain Cell Value Between than 1000 & 999999 Format: [>=1000] \R0 \k; R0 Add two more reverse conditions to handle (-ve) numbers. Note: The SA version uses spaces for commas. Once again thank you for your assistance. I’m a Happy subscriber
There is a baked in limit of 3 number definitions, so if you want to do negative combos as well as positive combos, I would be moving towards using Conditional Formatting with a formula. Here's how: a) Select the cell or cell range you are working with. b) On the Home ribbon, click *Conditional Formatting*, then *New Rule* , then *Format only cells that contain* . c) Create your condition, e.g. Cell Value | Greater than | 1000000 or Cell Value | Less than or equal to -1000000 etc. d) Click the *Format* button. e) In the Format Cells dialog, click the *Number* tab, then choose *Custom* and type a single custom format definition into the *Type* box. f) Click OK twice to close both dialogs. g) Repeat from step (b) for each different condition and format you need. Let me now how you go. Jason
Jason this is an excellent tip. I was aware of square brackets but I thought this was only for seating colour of the text e.g. negative numbers in red. What other things can you do with this functionality? Keep up the great work
Hi David. Thanks for your comment. Much appreciated. Custom formatting mostly comes down to the placeholders you use for the type of data you're using. For example for dates, try d, dd, ddd, dddd, m, mm, mmm, mmmm, yy and yyyy. For time, try h, hh, m, mm, s and ss. For durations, like 8 hours per day for 5 days, if you add up the hours the total will reset to zero when it passes 24 hours. To prevent this set a custom format of [h]:mm instead of hh:mm. For numbers, the # placeholder indicates an optional digit and the 0 placeholder indicates a mandatory digit. This gets you around leading zero problems like if you were to type 00001 into a cell it only displays 1 but with a custom format of 00000, the leading zeroes are retained. That's just a few. All the best. Jason
Thanks indeed for the video. How about the inverse process of converting from K and M into simple numbers? Any help with that would be much appreciated.
Hi Aksel. It depends on how the numbers are defined. If the "k" and "m" are added through formatting (as per the video), then you can simple switch to another number format. But if they are text values like 123.4k or 234.5m (maybe from exported data), you could use a simple formula like this to give you the actual number: =IF(RIGHT(A1, 1)="k", LEFT(A1, LEN(A1)-1) * 1000, IF(RIGHT(A1, 1)="m", LEFT(A1, LEN(A1)-1) * 1000000, A1)) Let me know how you go. Jason
Great idea! What about B for Billions, T for Trillions, Qa (or Q) for Quadrillions, Qi (or Qui) for Quintillions, Sx for Sextillions, Sp for Septillions, Oc (or O) for Octillions, No (or N) for Nonillions, Dc (or D or De) for Decillions, UD for Undecillions and DD for Duodecillions?
I'm not sure if this is a serious question but you can certainly code each of those up to a maximum of 3 combinations per custom format. That's a restriction imposed by Excel. So, for billions, millions and thousands it would look like [>=1000000000] \B\$ 0.000,,, ;[>=1000000] \M\$ 0.000,, ; K\$ 0.000 Hope that helps. All the best.
[Quick followup] Bubblun, here's a method that gets around the 3 format limit. You can combine *Custom Format* with *Conditional Formatting* . Here's how it works. a) Select the cell or cell range you are working with. b) On the *Home* ribbon, click *Conditional Formatting* , then *New Rule* , then *Format only cells that contain* . c) Create your condition, e.g. *Cell Value* | *Greater than* | *1000000* . d) Click the *Format* button. e) In the *Format Cells* dialog, click the *Number* tab, then choose *Custom* and type a single custom format definition into the *Type* box. f) Click OK twice to close both dialogs. g) Rinse and repeat from step (b) for each different condition and format you need . I hope this is useful. Jason
@@Reza_Audio I'm not sure if Google Sheets can do it as I believe it only offers a fraction of what Excel can do (and I don’t use it). Perhaps another reader can offer a more definitive answer ...
I don't know if I fully understand your question, but if you mean convert, for example, 1.2(k) into 1,200 inside a pivot table then you need to create a calculated field from within the Fields, Items & Sets option where you would take the field that contains the base value (e.g. 1.2) and multiply it by 1000.
Slow method: 1. Type 1 into cell A1. 2. Type 2 into cell A2. 3. Select A1:A2. 4. Drag the autofill handle down to row 1,000,000 (takes about 2-3 minutes to get down there). or Fast Method: 1. Type =ROW() into A1. 2. Copy cell A1. 3. In the name box, type A1000000. 4. Press Ctrl + Shift + Up arrow (this selects up to cell A1). 5. Press Shift + Down arrow. 6. Paste. 7. Select column A and copy. 8. Right-click on column A and choose the 'Values Only' icon.
@@JasonMorrell I just found that out minutes after posting the question and was unable to achieve what I wanted without conditional formatting using boolean logic similar to what you've shown. I love the end result of the one I am doing because I can use M and K. Great example of usage here: excelribbon.tips.net/T010227_Handling_Negative_Numbers_in_a_Complex_Custom_Format.html
Hi Super. I answered this same question for Aksel Qorduko below, but here is the answer again: It depends on how the numbers are defined. If the "k" and "m" are added through formatting (as per the video), then you can simply switch to another number format. But if they are text values like 123.4k or 234.5m (maybe from exported data), you could use a simple formula like this to give you the actual number: =IF(RIGHT(A1, 1)="k", LEFT(A1, LEN(A1)-1) * 1000, IF(RIGHT(A1, 1)="m", LEFT(A1, LEN(A1)-1) * 1000000, A1)) Let me know how you go. Jason
Hi Sujata. Thanks for your question regarding Lakhs and Crores (I must admit I had never heard of these and had to look them up!). Use this custom format: [>9999999]##\,##\,##\,##0.00;[>99999]##\,##\,##0.00;##,##0.00 For negative values you can adapt it slightly to: [
In india values are measured in Crores and lakhs instead of millions an billions...10 M = 1 CR...and 100K = 1 lakhs...can you please help similar custom formatting for this system please...
Hi Vivek, this question has already been answered (see Sujata Pandirkar's question below). Do this... Use this custom format: [>9999999]##\,##\,##\,##0.00;[>99999]##\,##\,##0.00;##,##0.00 For negative values you can adapt it slightly to: [
@@JasonMorrell Thank you Jason..however answer given by you caters requirement of appearance of numbers according to Indian coma system....what I really want is visual appearance should change when value exceeds lakhs and crores using custom formats....will try to explain through example...suppose I have entered 1,00,000 it should appear as 1 Lakh or 1 L...the way we do for Millions...and if value is in crores i.e. 2,50,00,000 then it should appear as 2.5 CR instead of entire value with Indian coma system....hope I have been able to convey my query to you....thanking you in advance...
Vivek, a small change to the formula shown in the video will achieve this. [>=10000000] ##0.0,, C\R;[>=100000] #,##0.0, L; #,##0.0 Here are some test results: 123 = 123.0 1234 = 1,234.0 12345 = 12,345.0 123456 = 123.5 L 1234567 = 1,234.6 L 12345678 = 12.3 CR 123456789 = 123.5 CR Over to you Vivek. If it's still not exactly what you are after, you now have enough info to be able to adapt it. Let me know how you go. Jason
@@JasonMorrell Sorry Jason...this is still not correct...last value should reflect as 12.3 CR and second last should appear as 1.23 CR 100 LAKHS makes it to 1 CR which is 10 Million.. Thank you so much for your time and attention....
Brilliant! Thanks for sharing: just what I needed!
You're welcome. Thanks Steve.
Clearest tutor so far. Thanks sir
Thank you. I appreciate that.
Perfect. Thank you!
You're welcome.
This is just what I'm looking for!! Thank you!
Fantastic! Thanks for commenting.
Just found this video and it's effing brilliant !
Cheers Michael
Excellent! Very useful.
Cheers Ziaur
This is awesome. Thanks for sharing!
No worries. Thanks Rajat.
Hello O2RIV,
Thanks a lot for the video.
Shouldn't we put *backslash for k* in the formula?
You can put one in if you want. Excel removes it.
OMG! I loved this video. Thank you for sharing. Just subscribed to your channel. I wish I had known you before : )
That's very encouraging. Thanks Gustavo.
Great tip. I am going to try for the negative numbers too.
Good to hear. Cheers Thamil.
@@JasonMorrell Please help with one that also deals with negative numbers ([>=1000000] 0 \m;[>=1000] 0 k; 0)
@@mantakemahlaremm It depends if you want to do negative millions, negative thousands, negative smaller numbers as well as positive millions, positive thousands and positive smaller numbers. If you do, it can't be done directly with this feature because there is a built-in limit of 3 definitions.
So ... you would use custom formatting within conditional formatting instead. Here's how:
a) Select the cell or cell range you are working with.
b) On the *Home* ribbon, click *Conditional Formatting*, then *New Rule* , then *Format only cells that contain* .
c) Create your condition, e.g. *Cell Value | Greater than | 1000000* or *Cell Value | Less than or equal to -1000000* etc.
d) Click the *Format* button.
e) In the Format Cells dialog, click the *Number* tab, then choose *Custom* and type a single custom format definition into the *Type* box.
f) Click OK twice to close both dialogs.
g) Repeat from step (b) for each different condition and format you need.
Let me now how you go.
Jason
@@JasonMorrell Thank you so much for your help. I managed to figure it out and ended up with 2 conditions with their reverse for (-ve) numbers:
Condition 1: Cells that contain Cell Value Greater than 1000000
Format: [>=1000000000] R0 \b;[>=1000000] \R0 \m; R0
Condition 2: Cells that contain Cell Value Between than 1000 & 999999
Format: [>=1000] \R0 \k; R0
Add two more reverse conditions to handle (-ve) numbers.
Note: The SA version uses spaces for commas.
Once again thank you for your assistance. I’m a Happy subscriber
Thanks Jason! This is dope. How do we apply the same to negative numbers?
There is a baked in limit of 3 number definitions, so if you want to do negative combos as well as positive combos, I would be moving towards using Conditional Formatting with a formula.
Here's how:
a) Select the cell or cell range you are working with.
b) On the Home ribbon, click *Conditional Formatting*, then *New Rule* , then *Format only cells that contain* .
c) Create your condition, e.g. Cell Value | Greater than | 1000000 or Cell Value | Less than or equal to -1000000 etc.
d) Click the *Format* button.
e) In the Format Cells dialog, click the *Number* tab, then choose *Custom* and type a single custom format definition into the *Type* box.
f) Click OK twice to close both dialogs.
g) Repeat from step (b) for each different condition and format you need.
Let me now how you go.
Jason
THANK YOU!
You're welcome!
Thank you very much
You're very welcome.
Thankyouuuu finally i got the auto way to do it
Cool.. Great to hear.
Thanks this works very nice.
You're welcome! Jason.
AMAZING! Thank you! one question tho...how can I, for example, introduce the $ symbol in front if I'm handling dollar sums?
Just type a $ at the start of the number definition, like [>=1000000] $0.0,, \m; ... etc.
@@JasonMorrell Awesome! Thank you so much for the quick reply!
Thanks man
No worries Rafael.
Jason this is an excellent tip. I was aware of square brackets but I thought this was only for seating colour of the text e.g. negative numbers in red. What other things can you do with this functionality?
Keep up the great work
Hi David. Thanks for your comment. Much appreciated. Custom formatting mostly comes down to the placeholders you use for the type of data you're using.
For example for dates, try d, dd, ddd, dddd, m, mm, mmm, mmmm, yy and yyyy.
For time, try h, hh, m, mm, s and ss. For durations, like 8 hours per day for 5 days, if you add up the hours the total will reset to zero when it passes 24 hours. To prevent this set a custom format of [h]:mm instead of hh:mm.
For numbers, the # placeholder indicates an optional digit and the 0 placeholder indicates a mandatory digit. This gets you around leading zero problems like if you were to type 00001 into a cell it only displays 1 but with a custom format of 00000, the leading zeroes are retained.
That's just a few. All the best.
Jason
HI!
Thanks for this video.
How do you do if you want to do the same, but adding the $ sign before the number?
Thank you!
Julio, just type a $ before the definition, like [>=1000000] $0.0,, \m; ... etc.
thank you very much, i find it easier to use 000,00 k for the thousands, it just looks neater that way to me
No worries. There's no ONE right way!
Impressive
No worries.
This was VERY helpful. Thank you! Can you show a custom format with B and M with dollar signs?
Hi Sarah. Try this:
[>=1000000000] \B\$ 0.000,,, ;[>=1000000] \M\$ 0.000,, ; K\$ 0.000,
Great! what if there are negative values
Sebastian, you have 2 options.
1. Create a negative check inside square brackets, e.g. [
Where did I get these formulas
Sorry, I'm not sure what you're asking.
Thanks indeed for the video. How about the inverse process of converting from K and M into simple numbers? Any help with that would be much appreciated.
Hi Aksel. It depends on how the numbers are defined.
If the "k" and "m" are added through formatting (as per the video), then you can simple switch to another number format.
But if they are text values like 123.4k or 234.5m (maybe from exported data), you could use a simple formula like this to give you the actual number:
=IF(RIGHT(A1, 1)="k", LEFT(A1, LEN(A1)-1) * 1000, IF(RIGHT(A1, 1)="m", LEFT(A1, LEN(A1)-1) * 1000000, A1))
Let me know how you go.
Jason
@@JasonMorrell Beautiful, worked like Magic
Great idea! What about B for Billions, T for Trillions, Qa (or Q) for Quadrillions, Qi (or Qui) for Quintillions, Sx for Sextillions, Sp for Septillions, Oc (or O) for Octillions, No (or N) for Nonillions, Dc (or D or De) for Decillions, UD for Undecillions and DD for Duodecillions?
I'm not sure if this is a serious question but you can certainly code each of those up to a maximum of 3 combinations per custom format. That's a restriction imposed by Excel. So, for billions, millions and thousands it would look like
[>=1000000000] \B\$ 0.000,,, ;[>=1000000] \M\$ 0.000,, ; K\$ 0.000
Hope that helps. All the best.
[Quick followup] Bubblun, here's a method that gets around the 3 format limit. You can combine *Custom Format* with *Conditional Formatting* . Here's how it works.
a) Select the cell or cell range you are working with.
b) On the *Home* ribbon, click *Conditional Formatting* , then *New Rule* , then *Format only cells that contain* .
c) Create your condition, e.g. *Cell Value* | *Greater than* | *1000000* .
d) Click the *Format* button.
e) In the *Format Cells* dialog, click the *Number* tab, then choose *Custom* and type a single custom format definition into the *Type* box.
f) Click OK twice to close both dialogs.
g) Rinse and repeat from step (b) for each different condition and format you need .
I hope this is useful.
Jason
@@JasonMorrell Yu know how to do this in google spreadsheets ? I need to use format in M, B and T but I stuck
@@Reza_Audio I'm not sure if Google Sheets can do it as I believe it only offers a fraction of what Excel can do (and I don’t use it). Perhaps another reader can offer a more definitive answer ...
Thank you sir. But what if number less than zero ex : (1000000) or (1000)
?
You can use < or
My data values are in K's but I want to convert them in numbers.. how do I sir ( especially in pivot table)
I don't know if I fully understand your question, but if you mean convert, for example, 1.2(k) into 1,200 inside a pivot table then you need to create a calculated field from within the Fields, Items & Sets option where you would take the field that contains the base value (e.g. 1.2) and multiply it by 1000.
can you make a video of how to count from1-1m easily?
Slow method:
1. Type 1 into cell A1.
2. Type 2 into cell A2.
3. Select A1:A2.
4. Drag the autofill handle down to row 1,000,000 (takes about 2-3 minutes to get down there).
or Fast Method:
1. Type =ROW() into A1.
2. Copy cell A1.
3. In the name box, type A1000000.
4. Press Ctrl + Shift + Up arrow (this selects up to cell A1).
5. Press Shift + Down arrow.
6. Paste.
7. Select column A and copy.
8. Right-click on column A and choose the 'Values Only' icon.
How about including negatives in that format?
David, you can create negative checks like [
@@JasonMorrell I just found that out minutes after posting the question and was unable to achieve what I wanted without conditional formatting using boolean logic similar to what you've shown. I love the end result of the one I am doing because I can use M and K. Great example of usage here: excelribbon.tips.net/T010227_Handling_Negative_Numbers_in_a_Complex_Custom_Format.html
I want to do the reverse how to do this
Hi Super. I answered this same question for Aksel Qorduko below, but here is the answer again:
It depends on how the numbers are defined.
If the "k" and "m" are added through formatting (as per the video), then you can simply switch to another number format.
But if they are text values like 123.4k or 234.5m (maybe from exported data), you could use a simple formula like this to give you the actual number:
=IF(RIGHT(A1, 1)="k", LEFT(A1, LEN(A1)-1) * 1000, IF(RIGHT(A1, 1)="m", LEFT(A1, LEN(A1)-1) * 1000000, A1))
Let me know how you go.
Jason
@@JasonMorrell Thank you so much sir for such a quick comment i have subscribed you channel 🙏🙏
How to display crore value in two digits? Pls help...
Hi Sujata. Thanks for your question regarding Lakhs and Crores (I must admit I had never heard of these and had to look them up!).
Use this custom format:
[>9999999]##\,##\,##\,##0.00;[>99999]##\,##\,##0.00;##,##0.00
For negative values you can adapt it slightly to:
[
In india values are measured in Crores and lakhs instead of millions an billions...10 M = 1 CR...and 100K = 1 lakhs...can you please help similar custom formatting for this system please...
Hi Vivek, this question has already been answered (see Sujata Pandirkar's question below). Do this...
Use this custom format:
[>9999999]##\,##\,##\,##0.00;[>99999]##\,##\,##0.00;##,##0.00
For negative values you can adapt it slightly to:
[
@@JasonMorrell Thank you Jason..however answer given by you caters requirement of appearance of numbers according to Indian coma system....what I really want is visual appearance should change when value exceeds lakhs and crores using custom formats....will try to explain through example...suppose I have entered 1,00,000 it should appear as 1 Lakh or 1 L...the way we do for Millions...and if value is in crores i.e. 2,50,00,000 then it should appear as 2.5 CR instead of entire value with Indian coma system....hope I have been able to convey my query to you....thanking you in advance...
Vivek, a small change to the formula shown in the video will achieve this.
[>=10000000] ##0.0,, C\R;[>=100000] #,##0.0, L; #,##0.0
Here are some test results:
123 = 123.0
1234 = 1,234.0
12345 = 12,345.0
123456 = 123.5 L
1234567 = 1,234.6 L
12345678 = 12.3 CR
123456789 = 123.5 CR
Over to you Vivek. If it's still not exactly what you are after, you now have enough info to be able to adapt it.
Let me know how you go.
Jason
@@JasonMorrell Sorry Jason...this is still not correct...last value should reflect as 12.3 CR and second last should appear as 1.23 CR
100 LAKHS makes it to 1 CR which is 10 Million..
Thank you so much for your time and attention....