Only major thing I saw missing here is that you can add conditional formatting within the positions. For example, if you wanted that only numbers below -100 were highlighted red, you could write [0] when applying a date format, so that zeros don't automatically show up as 1/0/1900 in Excel. Also would have been good to mention that you could write Excel's color numbers, like [color30] instead of [red], that seems to be more helpful and with more options than words.
Custom Number Formatting in EXCEL had never ever been taught this effectively; it was never before made this simple even for the least tech-savvy audience. Again a masterpiece video from Leila Gharani to demystify Excel's best kept secrets. Merci beaucoup, Leila! Cheers to Vienna!
That is so nice! Thank YOU for your very kind comment. I'm glad the tutorial is easy to follow. The first time I came across custom formatting I was so confused with all the syntax.
I hope you read this. How can I separate thousands with a space (33 454) and separate decimals too. Like this (example: 343 776.787 96). It was a great video. Very useful
Dear Leila. Thanks for your useful videos. I newly start work with excel and I need you help . Think about number auto fill but I need auto fill like AA,AB,AC...till ZZ. So how can I do this. Thanks for everything. Mt.Abbasi
Thanks for this helpful post, I have 1 question please. Is there a way to make the number formats like twitter number of followers (example if 100,000 it will be 100K)? thanks
Thank you Leila, your explanations are so fantastic and easy to understand. However, in my opinion MS way of dealing with date is completely absurd. In an international environment, where the same Excel file goes from a German to a Norvegian to an India guy and finally goes back to a French woman who consolidates the results, Excel simply will not work. For instance DATAVALUE. I cannot understand that this function does not take a second (optional) parameter where you can tell the language format of the given date. How would it be with something like DATAVALUE(15.12.2022,"de-DE") or the like?
Occasionally, I use cell formatting for hiding a long string such as a file location down a long path of sub-folders. the cell will say "File Location" and anyone can read the actual text in the formula bar. the idea is to not take up too much space on the worksheet but still provide the user with needed information.
You can do that in your Windows regional settings. Go to region settings in windows where it says "change date, time or number formats". Click on additional settings, under "digit grouping" select the one you'd like from the drop down.
Thank you for the suggestion. I set digit grouping in the region and language settings. But in excel when i applying number format it looks the same..1,525,458.00 instead of 15,25,458.00.
THANK YOU !! Do the format is it always fixed when sending the file to anyone else ? Also, can #,##0 replaced by #,### ? (I wonder why the zero is necessary)
Thank you. I'm glad it makes some sense now. I remember the first time I opened the custom dialogue box and saw all that syntax, I clicked away immediately :)
Hi Leila.. thanks for this great video On CNF.. another EXCEL feature that is hidden in plain sight. Your use and creativity extends it even further. Thanks for all the tips and tricks to improve my work. Thumbs up!
Another great video Leila, thank you! I have another related question for custom formats for dates. In this scenario, I have a birthdate column. Some people don't like to put in the YEAR they were born. Is there a way to customize the format of a cell so that it accepts both the m/d AND m/d/yyyy formats? This would be so I can enter "1/1" it'll stick and if I put in "2/2/2002" it'll also stick and be an actual number? I'm assuming when I don't enter a year it'll assume the current year. The follow-up question would be how to sort these dates to ignore year and just sort by Month then Date.
Hello Lelia , how do I change this 1000s accounting sign to M ? Can you help ? 2,318 K all my numbers are using decimals to appear as k when in sub millions. Thank you.
Hi I am entering a 16 digit number with dash symbol every 4 number (1234-5678-9876-1234) but if i customize it using this format ####-####-####-#### or 0000-0000-0000-0000 the end digit always turn into zero. How should I do it? Thank you.
Dear Leila - I am looking for a custom formatting for Indian number display (we have commas after every hundred except for the first thousand - eg. "89,76,67,123.00" ). I have a format [>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0 which works for positive numbers, but reverts to millions for negative numbers. Could you help me please?
Hi Leila, can you help me figure out my excel problem, I ran into a question in my MO 211 exam to format an array column of numbers into 5 digits, for example if we use the custom number format we would type in "00000" so if the number 123 will become 00123. The thing here is in the exam instead of zeros they wanted the hash sign, or number sign so in our previous example instead of 00123 the exam wanted to show the result ##123. I hope you can help me. thank you in advance
I have a date column that I need to flag the cells that are empty, and I tried using these custom formats (m/d/yyyy;"Never"), (m/d/yyyy;;"Never"), (m/d/yyyy;;;"Never") and none of them work correctly. Is this possible with date columns?
I have account number with dot "." separater (delimiter) like 201.280.5114 Where first 3 digits are Branch code [201=Local office], Middle 3 digits are product code [280= salary account] and last part is 3 to 5 digits are account serial number . My querie is how can I get 13 digits account number padding with zero like 2012800005114. How can I add zero in the middle of this , I want to add zero in 3rd part only which is serial number. Branch code and product code are fixed 3 digits dut serial numbers varies 3 to 5 digits.
Leila, Thanks so much for the video. I've got a question: how can we apply this to text? For instance: I have a drop-down list from which multiple options can be selected. How can I use custom formatting so that the color of the text changes as I select different values from this drop-down list?
I need to be able for format policy numbers which are insurance policy numbers from an old system. They use hyphens. m-yy-# or m-yy-## or m-yy-### OR mm-yy-#, mm-yy-##, mm-yy-###. mm=month, yy=year, and #, ##, or ### is the number in that particular month. When I save a system generated report to excel, i get the correct format sometimes, but other times it converts the data to a date format. How can I 1) keep Excel from converting to date format or 2) convert back to the format I need if we can't keep excel from switching it in the 1st place?
Requesting to solve the problem. excel enter date in cell A 1,2,3,4 & quantity in cell B 1,2,3,4 regularly. I want to reflect the last entry to another cell like C1 for date & D1 for qty and updates when I enter A 5 & B5. Pl solve with formula.
@Sivanandh & @Chomik59 You can do that in your Windows regional settings. Go to region settings in windows where it says "change date, time or number formats". Click on additional settings, under "digit grouping" select the one you'd like from the drop down.
@@LeilaGharani Thanks for your response. Unfortunately that's how we are managing it now. But, 1.Changing it under regional settings would change across the system. I was wondering if there could be an option to store it locally for an excel book (anything using macro?) 2.I would like to have 1 column grouped for every 3 digits & another column as 3,2,2,..
how can I create a cuetom format that uses a comma for the 0.00 i.e. 0,00? and also has a space for the 1,000, i.e. 1 000? actual format is 1 200,00- for minus one thousand two hundred and no cents? This is the way the bank supplied statements! i think they want you to go in and pay for statements.
I often use custom number formatting to truncate large numbers to “k” or “M” for thousands or millions, i.e. 1243686 to 1.24 M. Makes large tables much easier to read. Although, I can never remember the number format code for this and always have to google it.
That's a good one too. I use it a lot for number formatting in charts too. I should probably make a follow up video for this one :) thanks for the suggestion. The format is #,##0,, "M" and one comma for the thousand. It depends on what your thousand separator is though - if it's a dot instead (like in Germany), you need to use .. for M and one . for thousand.
Yes, I use it for charts all the time too. It's quicker to grasp than scientific notation or god-forbid showing all the zeros (I've seen this a lot, sadly). I also found you can put a conditional in the number formatting, so if it's less than a million it will use the "k" formatting and more than a million the "M" formatting. This is often useful if all your data is under a million or all over a million and you can use just one custom number format formula.
I sometimes run to a problem where the number shown in cell is 234.00 but behind at the formula bar is 234,4545446 and i cant make it be 234.00 ( i will covert to csv after)
my son is exporting data from one database system to excel, then saving as csv, to be imported to a new database system. why was the csv saving the format/mask form and not the raw data underneath? he did find that if while in excel, he erased the formatting by changing it to "general" before saving as csv, that it would import to the new database... but that's one extra step that might not be necessary? ps, I have e-mailed him a link to your video... thanks for this explanation, in all my work for 20 yrs w/excel, I had never looked at this.
hiiiiie Mam.. i have a doubt.. i use excel to put number of Meters so my value sometimes comes in decimals n sometimes it doesnt.. so is there any forrmula where i can get such a value that if my value comes in like this 5555.50 it should stay like this but if my value is just 5555.. i want it like just 5555 I dont want 5555.00 like if value is in decimal it should stay but if its not there should be no decimal points.. plzz help me
hey please help me my data in a cell is 60 13/06/2020 13/06/2020 this a ledger of my account statement when I convert to excel it combined in a cell so I need to remove 60 and the first date please help me to do this to data contain more than 1000 raws so I cannot manually do this
CSV not allows 16 digits number and if convert as type text and save,but if opened again it formats agin to default.is any custom format ting available to retain text
Until yesterday I was reviewing some of your RUclips videos for custom formating to learn more and then today you released this video. You have my thanks..... In case I have a clarification request on custom formatting, how I can raise it. Keep your efforts...
Perfect timing Towfeek. You can post a comment here, but if it's something that requires a screenshot, best is to address it inside my courses or on the Microsoft Tech community for Excel here: techcommunity.microsoft.com/t5/Excel/ct-p/Excel_Cat
Thank you for highlighting this trick, which is even better in visualization, when you have to change the axis to suppress zero values or doing custom values like 1M instead of 1,000,000.
Hi! I don’t use it much because to me it had a kind of mystery associated with it. But now, after the series of videos from you, it’s much easier to understand how to use it properly. Thanks Leila for helping us!!
Hello Leila. Your videos are wonderfull. They are very simple and easy to follow. I have learned a lot from them. I need your help with something: is there a way to change color to some bars in a pivot graph? For example if a target is achieved or not.
Hello Leila, I work on a spreadsheet daily at work where I enter numbers codes, for instance F-37-G42-O-0000. This is the document number our system generates when we create an invoice. I type such numbers daily with those “-“. Is there a way I could use custom formatting so that I do not have to type those dashes every time? I just want to simply type the document numbers and excel types the dashes by itself when I hit enter. Thanks
thousand 10^3 and million means 10^6. formatting is easy to use one comma at end for thousands and two commas at end for million. However, in India many balance sheets with large numbers need to be viewed in a unit known as Crores, 10^7. Could you please explain how it could be done? I am asking how a 123,456,789 can be formatted to be viewed as 12.35 Crores?
But one doubt...how to convert applied custom format to permanent format....I mean...we copy the same in notepad and paste the same in excel...to make that format appear in the formula bar....any other way to do so ?
QUESTION: How do you make cells, default the decimal column to 2 digits exclusively and disregard the rounding off calculations. I try to compare two cells using the =IF() wizard, although both cells to be compared have the same value in each, for example 5.86, but the "wizard" is showing a comparison between a directly entered value of 5.86 to the calculated value, that resulted in 5.867, and the result of the comparison is always FALSE? Anyway, I went to the OPTIONS menu of EXCEL 2019 and turned ON the: Automatically insert a decimal point at 2 places from the Advanced section, now I get the results I want when comparing, but when entering whole numbers without using the decimal key, it gets treated as a decimal value, and a ZERO is inserted in the cell, if the cell(s) is formatted as a number with zero decimal places. Anyway, your video's have given great insights, did not realize the things that can be done with EXCEL.
How can i format cell’s comma style ie. I need to convert 1,000,000,000.00 to Tk. 1,00,00,00,000.00 also in negative value ie. Tk. (1,00,00,00,000.00). Please suggest without VBA & changing regional location. Thanks in Advance.
HI Leila. Another wonderful video. Is it possible to create a file of custom formats, to distribute to other users? Need to create 30 or so formats showing currency codes (GBP,USD,EUR etc)
Thank you very much. This tutorial was very helpful. I am tasked with preparing reports with large numbers and shortcuts and custom formatting is a game changer for me. It helps especially with showing the data in a chart. Customization makes for a much cleaner report.
Grab the file I used in the video from here 👉 pages.xelplus.com/custom-number-tips-file
I just need to say that, Leila you are my hero!! I keep surprising my bosses with my reports all thanks to your videos! Love you woman!🙌🏻🙌🏻🙌🏻
I'm so glad you are making good use of the videos Alina. Let's keep surprising them :)
100% agree. Leila is legend and said in a user friendly way
U simply my hard lessons at school
Positive, negative, zeros, text. Got it!
Isn't it technically
Positive; negative; zeros; text
This is helpful
Awesome I think no one exist like you. You & your videos are stupendous.
I'm pleased to watch your video.
Only major thing I saw missing here is that you can add conditional formatting within the positions. For example, if you wanted that only numbers below -100 were highlighted red, you could write [0] when applying a date format, so that zeros don't automatically show up as 1/0/1900 in Excel. Also would have been good to mention that you could write Excel's color numbers, like [color30] instead of [red], that seems to be more helpful and with more options than words.
ruclips.net/video/tGY70sdpaLc/видео.html
I like the video too, but I was looking for data validation too.
Wow instead of using the 0000 format i was typing '0000 directly in the cell. From now on I'll be using this method! Thanks! 😊
YES, YES! I'm glad you watched this :)
yeah.. same here. i did the same. never thought there's an easier way
How to do HTS code like format cells (0000.00.0000)? So I don't have to keep typing two decimal points (.) using my left hand. Please help me.
Custom Number Formatting in EXCEL had never ever been taught this effectively; it was never before made this simple even for the least tech-savvy audience. Again a masterpiece video from Leila Gharani to demystify Excel's best kept secrets.
Merci beaucoup, Leila! Cheers to Vienna!
That is so nice! Thank YOU for your very kind comment. I'm glad the tutorial is easy to follow. The first time I came across custom formatting I was so confused with all the syntax.
I hope you read this. How can I separate thousands with a space (33 454) and separate decimals too. Like this (example: 343 776.787 96). It was a great video. Very useful
Dear Leila. Thanks for your useful videos.
I newly start work with excel and I need you help . Think about number auto fill but I need auto fill like AA,AB,AC...till ZZ. So how can I do this.
Thanks for everything.
Mt.Abbasi
=CHAR(INT((ROWS($B$2:B2)-1)/26)+65)&CHAR(65+MOD(ROWS($B$2:B2)-1,26))
I think this can help you.
Thanks Leila. You are the best😊
Thanks for this helpful post, I have 1 question please. Is there a way to make the number formats like twitter number of followers (example if 100,000 it will be 100K)? thanks
How to Convert Numbers into Lakh & Crore - Excel Tricks & Tips #excel
ruclips.net/video/ICL15ALV9Gs/видео.html
Thank you Leila, your explanations are so fantastic and easy to understand.
However, in my opinion MS way of dealing with date is completely absurd. In an international environment, where the same Excel file goes from a German to a Norvegian to an India guy and finally goes back to a French woman who consolidates the results, Excel simply will not work. For instance DATAVALUE. I cannot understand that this function does not take a second (optional) parameter where you can tell the language format of the given date. How would it be with something like DATAVALUE(15.12.2022,"de-DE") or the like?
Occasionally, I use cell formatting for hiding a long string such as a file location down a long path of sub-folders. the cell will say "File Location" and anyone can read the actual text in the formula bar. the idea is to not take up too much space on the worksheet but still provide the user with needed information.
Is there any way to show numbers using custome formatting like 13,15,300. The comma separaters in between 5 and 3, and 3 and 1.
You can do that in your Windows regional settings. Go to region settings in windows where it says "change date, time or number formats". Click on additional settings, under "digit grouping" select the one you'd like from the drop down.
Thank you for the suggestion. I set digit grouping in the region and language settings. But in excel when i applying number format it looks the same..1,525,458.00 instead of 15,25,458.00.
So amazing. Sad I'm seeing this so late in life.
THANK YOU !!
Do the format is it always fixed when sending the file to anyone else ?
Also, can #,##0 replaced by #,### ? (I wonder why the zero is necessary)
Absolutely helpful vid. I've always wondered how custom formating worked. Thank you Leila.
Thank you. I'm glad it makes some sense now. I remember the first time I opened the custom dialogue box and saw all that syntax, I clicked away immediately :)
Hi Leila.. thanks for this great video On CNF.. another EXCEL feature that is hidden in plain sight. Your use and creativity extends it even further. Thanks for all the tips and tricks to improve my work. Thumbs up!
Thanks Wayne! I'm a big fan of custom formatting. I prefer it over conditional formatting because it's also faster.
Another great video Leila, thank you!
I have another related question for custom formats for dates. In this scenario, I have a birthdate column.
Some people don't like to put in the YEAR they were born.
Is there a way to customize the format of a cell so that it accepts both the m/d AND m/d/yyyy formats?
This would be so I can enter "1/1" it'll stick and if I put in "2/2/2002" it'll also stick and be an actual number?
I'm assuming when I don't enter a year it'll assume the current year.
The follow-up question would be how to sort these dates to ignore year and just sort by Month then Date.
Hello Lelia , how do I change this 1000s accounting sign to M ? Can you help ? 2,318 K
all my numbers are using decimals to appear as k when in sub millions. Thank you.
Hi I am entering a 16 digit number with dash symbol every 4 number (1234-5678-9876-1234) but if i customize it using this format ####-####-####-#### or 0000-0000-0000-0000 the end digit always turn into zero. How should I do it? Thank you.
Dear Leila - I am looking for a custom formatting for Indian number display (we have commas after every hundred except for the first thousand - eg. "89,76,67,123.00" ). I have a format [>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0 which works for positive numbers, but reverts to millions for negative numbers. Could you help me please?
Leila,
You are very good at teaching!
Always clear explanations and valuable tips.
Thank you very much!
Glad you like it Agostinho!
Hi Leila, can you help me figure out my excel problem, I ran into a question in my MO 211 exam to format an array column of numbers into 5 digits, for example if we use the custom number format we would type in "00000" so if the number 123 will become 00123. The thing here is in the exam instead of zeros they wanted the hash sign, or number sign so in our previous example instead of 00123 the exam wanted to show the result ##123. I hope you can help me. thank you in advance
I have a date column that I need to flag the cells that are empty, and I tried using these custom formats (m/d/yyyy;"Never"), (m/d/yyyy;;"Never"), (m/d/yyyy;;;"Never") and none of them work correctly. Is this possible with date columns?
I have account number with dot "." separater (delimiter) like 201.280.5114
Where first 3 digits are Branch code [201=Local office], Middle 3 digits are product code [280= salary account] and last part is 3 to 5 digits are account serial number . My querie is how can I get 13 digits account number padding with zero like 2012800005114. How can I add zero in the middle of this , I want to add zero in 3rd part only which is serial number. Branch code and product code are fixed 3 digits dut serial numbers varies 3 to 5 digits.
Leila, Thanks so much for the video. I've got a question: how can we apply this to text?
For instance: I have a drop-down list from which multiple options can be selected. How can I use custom formatting so that the color of the text changes as I select different values from this drop-down list?
You can use conditional formatting
I need to be able for format policy numbers which are insurance policy numbers from an old system. They use hyphens. m-yy-# or m-yy-## or m-yy-### OR mm-yy-#, mm-yy-##, mm-yy-###. mm=month, yy=year, and #, ##, or ### is the number in that particular month. When I save a system generated report to excel, i get the correct format sometimes, but other times it converts the data to a date format. How can I 1) keep Excel from converting to date format or 2) convert back to the format I need if we can't keep excel from switching it in the 1st place?
hi leila, how do you display large numbers on columns. For example i want to display 10000, 10001, 10002, etc to 10100 in Column A1 to A100
Requesting to solve the problem. excel enter date in cell A 1,2,3,4 & quantity in cell B 1,2,3,4 regularly. I want to reflect the last entry to another cell like C1 for date & D1 for qty and updates when I enter A 5 & B5. Pl solve with formula.
Is it possible to customize digit grouping using Custom Number formatting ?
For ex :
Cell 1 : 123,456,789
Cell 2 : 12,34,56,789
Good question.
@Sivanandh & @Chomik59 You can do that in your Windows regional settings. Go to region settings in windows where it says "change date, time or number formats". Click on additional settings, under "digit grouping" select the one you'd like from the drop down.
@@LeilaGharani
Thanks for your response. Unfortunately that's how we are managing it now. But,
1.Changing it under regional settings would change across the system. I was wondering if there could be an option to store it locally for an excel book (anything using macro?)
2.I would like to have 1 column grouped for every 3 digits & another column as 3,2,2,..
thanks mam
how can I create a cuetom format that uses a comma for the 0.00 i.e. 0,00? and also has a space for the 1,000, i.e. 1 000? actual format is 1 200,00- for minus one thousand two hundred and no cents? This is the way the bank supplied statements! i think they want you to go in and pay for statements.
طلبت منك ارفاق الترجمة العربية لانوا احببت اسلوبك ولم تردي علي
I often use custom number formatting to truncate large numbers to “k” or “M” for thousands or millions, i.e. 1243686 to 1.24 M. Makes large tables much easier to read. Although, I can never remember the number format code for this and always have to google it.
That's a good one too. I use it a lot for number formatting in charts too. I should probably make a follow up video for this one :) thanks for the suggestion. The format is #,##0,, "M" and one comma for the thousand. It depends on what your thousand separator is though - if it's a dot instead (like in Germany), you need to use .. for M and one . for thousand.
Yes, I use it for charts all the time too. It's quicker to grasp than scientific notation or god-forbid showing all the zeros (I've seen this a lot, sadly).
I also found you can put a conditional in the number formatting, so if it's less than a million it will use the "k" formatting and more than a million the "M" formatting. This is often useful if all your data is under a million or all over a million and you can use just one custom number format formula.
I sometimes run to a problem where the number shown in cell is 234.00 but behind at the formula bar is 234,4545446 and i cant make it be 234.00 ( i will covert to csv after)
Pls help to get below answer in custom format
Input number is 10,25,00,000 & out put should come 10.25
Formatting Codes don't apply in Format shape in my graph - Why is that? Been trying all different codes - but it won't recognize and shows up weirdly.
my son is exporting data from one database system to excel, then saving as csv, to be imported to a new database system. why was the csv saving the format/mask form and not the raw data underneath? he did find that if while in excel, he erased the formatting by changing it to "general" before saving as csv, that it would import to the new database... but that's one extra step that might not be necessary? ps, I have e-mailed him a link to your video... thanks for this explanation, in all my work for 20 yrs w/excel, I had never looked at this.
thank you so much for your nice presentation, my sister. How can I change from 10 20, 30, and 40 to 1, 2,3, and 4?
hi in my excell 2007 after typing 2500.....its showing 25, if i type 250000....then it will show 2500 ...last 2 zeros is dis appear what setting ...is
Why is there always a zero after a percentage? For example 73% it becomes 7300%. How to fix it?
hiiiiie Mam.. i have a doubt.. i use excel to put number of Meters so my value sometimes comes in decimals n sometimes it doesnt.. so is there any forrmula where i can get such a value that if my value comes in like this 5555.50 it should stay like this but if my value is just 5555.. i want it like just 5555 I dont want 5555.00 like if value is in decimal it should stay but if its not there should be no decimal points.. plzz help me
Mam in cell number is showing in 2 decimal but in fomula bar it is showing 5 decimal why is it happening i set it two decimal in custom formating
How to get only the decimal? For instance, if I get 12,34 to get only 34...
Hi Leila, I am a big fan of you from India, Thanks for your Videos.
hey please help me my data in a cell is 60 13/06/2020 13/06/2020 this a ledger of my account statement when I convert to excel it combined in a cell so I need to remove 60 and the first date please help me to do this to data contain more than 1000 raws so I cannot manually do this
How to define Million and Thousand Separator as well as Crores and Lakhs Separators in the same excel sheet without changing the regional settings?
please help to convert the number to crore.
eg. Input = 102500000 & output should be 10.25
I asked you to attach the Arabic translation because I liked your style and you did not answer me
Can anyone helpme with this Scenario - i want to add '$' & Million to the numbers at same time like - $30M can anyone help me with this.
Can you use color when custom formatting dates or just stick with conditional formatting?
CSV not allows 16 digits number and if convert as type text and save,but if opened again it formats agin to default.is any custom format ting available to retain text
thanx leila you are the queen
excellent idea...
Whaaaa. Mind blown. Wonderful videos as always.
I use ctrl +shift +7 to format in thousands
Mam can you tell how to add comma after every three digits in Excel as they use in US accounting system.
Great work, Leila. learn something new about the difference between 0 n # in customer formatting:)
Glad it's useful Sherry!
Until yesterday I was reviewing some of your RUclips videos for custom formating to learn more and then today you released this video. You have my thanks.....
In case I have a clarification request on custom formatting, how I can raise it.
Keep your efforts...
Perfect timing Towfeek. You can post a comment here, but if it's something that requires a screenshot, best is to address it inside my courses or on the Microsoft Tech community for Excel here: techcommunity.microsoft.com/t5/Excel/ct-p/Excel_Cat
Thank you for highlighting this trick, which is even better in visualization, when you have to change the axis to suppress zero values or doing custom values like 1M instead of 1,000,000.
I'm glad you like it!
what will be the number format to display 1,00,00,000 (one crore) as 1.00 or 10,00,00,000 (ten crore) as 10.00
Hi! I don’t use it much because to me it had a kind of mystery associated with it. But now, after the series of videos from you, it’s much easier to understand how to use it properly. Thanks Leila for helping us!!
You're very welcome Luis!
@@LeilaGharani can i have ur contct number
Thank you Leila great video Viele Liebe Grüsse
Danke Liebe Katerina :)
Very Nice & Useful
Thanks
Om Sai Ram
You are the best.
hi... please tell me how to do sum if numeric data with alphabets.. like if i have to add hours.. say 8 hrs and 2 hrs and so on and so forth
Hello Leila. Your videos are wonderfull. They are very simple and easy to follow. I have learned a lot from them.
I need your help with something: is there a way to change color to some bars in a pivot graph? For example if a target is achieved or not.
Another video opportunity: how all these behave when filter is used.
how to make the custom format permenently in excel, so even we want to use it on the other file, we don't need to make format again?
I m also using format cells same way and doing experiments But u have amazing knowledge about it.👏😊
Glad you find it useful.
Hi Leila @LeilaGharani. if ;;; hides everything and appears in formula bar. this is working fine with text and numbers. How to do for dates?
this video was supercalifragilisticexpialidocious
hope u be ok ! every time i insert number in the cell it automatically change into decimal how it can be solved?
Hello Leila,
I work on a spreadsheet daily at work where I enter numbers codes, for instance F-37-G42-O-0000. This is the document number our system generates when we create an invoice. I type such numbers daily with those “-“. Is there a way I could use custom formatting so that I do not have to type those dashes every time? I just want to simply type the document numbers and excel types the dashes by itself when I hit enter. Thanks
can i check is there a bug in the file. after saving the file all my cell content will change from general to date. how to prevent this ?
why does concatenate not carry a number custom format into the result? 01 results into 1
i want to change a cell to custom formatting and it only changes manual ones and not the exiting data?
please tell me how can i convert 170.8 million into a normal number.
Thank you for creating and sharing this useful tutorial. It's really very helpful :)
I'm glad the video is helpful!
I use to give thumb up at beginning of your videos .
It is really excellent channel .
That's very nice, thank you!
This video was top-notch! Thank you very much for doing this.
You're very welcome Jay! Glad you like it.
How can we define dufferent color to text using format cells while there is formula in cell.
Dear teacher lily your too quick in your explen pleas be slow
Thanks Leila, It's very interesting and functional
Glad you like it!
thank you for your video, is it possible to display n/a as 0% ?
thanks, how do I extract "-24,661.17" from "From operations last year: -24,661.17 Cr." pl help
Hi,
Please share Your Question on goodnessshallprevail@gmail.com
Thank You!
We can also use colors in custom number formatting. For example: [Color44]#,##0.00;[Color38]-#,##0.00;[Color46]0
dmcritchie.mvps.org/excel/colors.htm
First time I recognized diff Btwn 0 n #
Thnx
Took me a while to figure it out too :)
thousand 10^3 and million means 10^6. formatting is easy to use one comma at end for thousands and two commas at end for million. However, in India many balance sheets with large numbers need to be viewed in a unit known as Crores, 10^7. Could you please explain how it could be done? I am asking how a 123,456,789 can be formatted to be viewed as 12.35 Crores?
How to Convert Numbers into Lakh & Crore - Excel Tricks & Tips #excel
ruclips.net/video/ICL15ALV9Gs/видео.html
Big thank-you, Leila! Very useful tips
You're very welcome Yulin!
Mas um ótimo conteúdo Leila
Att, Gledson Programador Excel VBA
Custom formatting is an often overlooked feature. Glad you like the content.
Super likes ...it was not known to.me completely....I always wondered y three times ;;; made things invisible 😆
But one doubt...how to convert applied custom format to permanent format....I mean...we copy the same in notepad and paste the same in excel...to make that format appear in the formula bar....any other way to do so ?
I like your videos 😍, thanks
QUESTION: How do you make cells, default the decimal column to 2 digits exclusively and disregard the rounding off calculations. I try to compare two cells using the =IF() wizard, although both cells to be compared have the same value in each, for example 5.86, but the "wizard" is showing a comparison between a directly entered value of 5.86 to the calculated value, that resulted in 5.867, and the result of the comparison is always FALSE?
Anyway, I went to the OPTIONS menu of EXCEL 2019 and turned ON the: Automatically insert a decimal point at 2 places from the Advanced section, now I get the results I want when comparing, but when entering whole numbers without using the decimal key, it gets treated as a decimal value, and a ZERO is inserted in the cell, if the cell(s) is formatted as a number with zero decimal places.
Anyway, your video's have given great insights, did not realize the things that can be done with EXCEL.
How can i format cell’s comma style ie.
I need to convert 1,000,000,000.00 to Tk. 1,00,00,00,000.00 also in negative value ie. Tk. (1,00,00,00,000.00).
Please suggest without VBA & changing regional location.
Thanks in Advance.
HI Leila. Another wonderful video.
Is it possible to create a file of custom formats, to distribute to other users? Need to create 30 or so formats showing currency codes (GBP,USD,EUR etc)
How to format cell to make them negative with formula
Thank you very much. This tutorial was very helpful. I am tasked with preparing reports with large numbers and shortcuts and custom formatting is a game changer for me. It helps especially with showing the data in a chart. Customization makes for a much cleaner report.