I have considered myself to be a “power user” of Excel, having used it since first introduced to the market. Yet I wasn’t aware of any of these very useful “hacks”. Thank you for teaching these to us, and for showing so many real life examples! Kudos...
1. Merging without merging - centre across selection 0:28 2. Merge multiple cells with "Merge across" 2:14 3. No decimal points for large numbers with Custom cell formatting 3:11 4. Showing numbers in Thousands or millions with Custom cell formatting 5:10 5. New line in a cell with ALT+Enter 7:08 6. Copy widths alone with paste special 8:03 7. Skip zero in chart labels with custom cell formatting 9:08 8. Align & distribute charts with alignment tools 11:47 9. Show total hours with [h]:mm custom code 13:48 10. Text format for very long numbers 15:05
I am a former "expert" now retired. It's fun to see functionality presented this way. Good work. And thanks. This does leave me wondering if there is a way to display multiple cell formats, similar to the way that cell formulas can be displayed.
Great tricks. Just adding on, Trick1: "Center across Selection" is same as mergecell. Even after "Center across selection" You will not find values in the corresponding cells, but one
No, it is not the same. With "Center across Selection", the text is positioned across the centre of the selected cells, but . . . . . the cells are not merged !! Each of the selected cells will remain separately selectable after doing this operation. After you do "Merge Cells", you will be unable to select the individual cells separately, you will only be able to select the single big cell which resulted from the merge.
With the last tip, if you want to use the value of the number entered as text, you can use eg =VALUE(A1)+10 This retruns the value of the text entry, as best as Excel can determine.
One I like that not many people know is when you have built your perfect chart you can then paste special formats to your other charts for a consistent look.
That is a great one. I use it all the time. You can see that + few other awesome charting tricks in this video ruclips.net/video/nU9gp0ziKqk/видео.html
Nice tips thanks. For the 10th trick, I think it would be better to use the format "number" than the format "text". In that case we can do arithmetic calculations as well.
@chandoo hello hope you and family are well. sorry to ask an additional question - How do you find a monetary value in an unstructured tex paragraph? Please
Hi James.. good tip. Excel would still truncate the number if you enter 16 or more digits. Try entering the number 1234567812345678 and Excel will change it to 123456781234567 *_0_*
A trick to work with numbers as text is to sum 0 when you need to reference the cell, for example using a vlookup. E.g. VLOOKUP(A1+0,...) assuming A1 is a cell with a number as a text
Thanks for the nice tips. In point 7, can we hide the formula in sheet one which are linked with sheet two, in such a way that if we put data in the second sheet, the sheet one pick the data but without data in second sheet will look complete blank.
Thanks for sharing! For the decimal part, I wonder whether or not it is possible to format a cell not showing decimals it will round down? (so if it is 1.99 it will become 1.9 for 1 decimal place and 1 for no decimals) Because I try to implement a VBA to switch between 2 decimal places and 0 decimal place but only rounded down, do you think that can be done without using an assistant column?
Very informative. I especially loved the first one about merging cells. I also liked the presentation. One request. Will you please tell which software did you use to record your screen and to edit it?
What does the “link to source” checkbox do in the chart formatting button? I assume it means if the source data changes, the chart updates? But isn’t that what you’d always want? Around 10:30
Wow.. that is a basic but i dont know all that those tricks! 😱😱 After many years... I realized how stupid I'am 😂😂😂😂. Now I've learned thanks for this videos 🙏😊😊
Thank you so much for this short video on trick about format. Most of them were known but the number formatting is such a useful feature that i never bothered to explore. Does the abor applicable only for Office 365 or earlier versions as well?
Hi, Good details shared, thanks, Pls suggest, I need add 0 in front of 8 digit product codes if put " only its taking and not fining in in master sheet
Hi Senthil. You are welcome. You can first select the cells where data will be typed and set the format to TEXT (from format cells). Then you can type 0 and Excel will keep it.
Excellent hacks bro.. Thanks for sharing. I have a question. Can we show Thousands and Millions in the same column???? Like A1 has 22000 -> 22 K A2 has 43000000 -> 43 M Appreciate your help. 🙂🙏
Yes we can. You can use up to 2 conditions in custom cell format codes to show thousands and millions or millions and billions. For example, this code will get you what you need. [>=1000000]#,##0,,"m";[>=1000]#,##0,"k";#,##0 All the best.
Hi Richa... Excel values have a formatting code broken to 4 parts. Each part tells Excel how to format the value for one scenario. It goes like this by default: positive value format ; negative value format ; zero format ; any thing else format The parts are delimited by semicolon. For example, to show () instead of - sign in front a number and to show - instead of 0, we can use this code. 0;(0);"-";@ For more on this, read this page - chandoo.org/wp/a-technique-to-quickly-develop-custom-number-formats/
Thank you, there are some nice tricks. I like to ad something to the long numbers. Keep them as a standard number, but reduce the decimal to zero, then they still work as numbers but are not reduced to scientific format.
Great tip! See my notes on other comments. This method works fine until you cross 15 digits. Then Excel will ignore the last digits and turns them into 0s.
ChanduJi, is there auto format to show 346762 to 3.47 L here L means Lakhs. Basically Rs. 3,46,762 as Rs. 3.47 lakhs after decimal aedjustment. Often there is a requirement for showing this in graphs.
Thank you. I am not sure about the technical reason, but I believe [ ]s are used for conditions and to convert a value to whatever format you want without the limitation. So for example, [h] will show total hours in a value (even if more than 24), [mm] will show number of minutes and [ss] will give you total seconds.
Thanks Sir .... I wish to have the number for Lakhs and Crore [Indian Numbering System] in number format ... can you please provide the trick...thanking you in anticipation
I do not understand why "Center across selection" is better than merge with the comment you said that choosing the cell to the left has the value but the one next to it doesn't - that is true in both cases (that being said, I did not know that "Center across selection" was around and I will use it - I hate merged cells for filter reasons, etc.)
@@chandoo_No worries man. Actually, I think this a common issue one RUclips. I noticed that some other RUclipsrs were informed about the same problem in their videos just recently. I doubt that it is a Google Chrome related problem. @RUclips should do an investigation on this. Thanks.
Thank you Sir 🙏 But "Merge Across"(cells with data) is not working in my system. After selecting the cells with data, if I click "Merge Across", it shows "Merging cells only keeps the upper-left value and discards other values." Why?
at point 10: you can use double negation (-- i.e. minus minus) in front of cell reference to convert implicitly from text to number. IMPORTANT NOTE: Excel work with only 15 semnificatie digits
Yes, this is how experts do..
Thank you, Chandoo!
I have considered myself to be a “power user” of Excel, having used it since first introduced to the market. Yet I wasn’t aware of any of these very useful “hacks”. Thank you for teaching these to us, and for showing so many real life examples! Kudos...
Glad it was helpful!
1. Merging without merging - centre across selection 0:28
2. Merge multiple cells with "Merge across" 2:14
3. No decimal points for large numbers with Custom cell formatting 3:11
4. Showing numbers in Thousands or millions with Custom cell formatting 5:10
5. New line in a cell with ALT+Enter 7:08
6. Copy widths alone with paste special 8:03
7. Skip zero in chart labels with custom cell formatting 9:08
8. Align & distribute charts with alignment tools 11:47
9. Show total hours with [h]:mm custom code 13:48
10. Text format for very long numbers 15:05
thanks for the time marker. very much needed
I am a former "expert" now retired. It's fun to see functionality presented this way. Good work. And thanks. This does leave me wondering if there is a way to display multiple cell formats, similar to the way that cell formulas can be displayed.
Great tricks. Just adding on, Trick1: "Center across Selection" is same as mergecell. Even after "Center across selection" You will not find values in the corresponding cells, but one
No, it is not the same.
With "Center across Selection", the text is positioned across the centre of the selected cells, but . . . . . the cells are not merged !! Each of the selected cells will remain separately selectable after doing this operation.
After you do "Merge Cells", you will be unable to select the individual cells separately, you will only be able to select the single big cell which resulted from the merge.
With the last tip, if you want to use the value of the number entered as text, you can use eg =VALUE(A1)+10
This retruns the value of the text entry, as best as Excel can determine.
Or use = --A1+10
Very good, thanks. For 10th point to format a very long numbers you can put ' before the number and all will be OK.
Merge across is new added in my knowledge book ...Thanks Mr. C 😎🙏
Excellent!
From last 7 years I am your big follower ,Really amazing your talent
Thanks Wilson. Wow, 7 years. 😍
Thank you! Your instructions are so clear and very useful - a big help : D
Glad to hear that!
Man thank you!!! That merge and center option helped immensely!!!
I agree. And I wish that the makers of the forms I get from our departments also knew it. I hate to have to work with merged cells.
@@logannasty3240 lol. I understand you there!!
chandoo is a genius
Thanks a lot Hero
I have been using Excel for 20 years. These tips will help me for another 20. ❤️
btw, I knew alt+enter 😎
Great to hear!
I actually learned a trick or two - much to my amazement . Thank you
For the merge you can also merge and format the first one how you like then use the format painter tool and select all the other cells
One I like that not many people know is when you have built your perfect chart you can then paste special formats to your other charts for a consistent look.
That is a great one. I use it all the time. You can see that + few other awesome charting tricks in this video ruclips.net/video/nU9gp0ziKqk/видео.html
I came across all these problems..there is so simple way to fix ...Thanks Chandoo...I hv been your viewer since 1decade now
Thanks Cloudyhills
Wow.. Thanks for being with me and learning all these years :)
Nice tips thanks. For the 10th trick, I think it would be better to use the format "number" than the format "text". In that case we can do arithmetic calculations as well.
Good point!
I am glad that i know 6 of them already
Woohoo...
@chandoo hello hope you and family are well. sorry to ask an additional question - How do you find a monetary value in an unstructured tex paragraph? Please
This was awesome! I knew a couple already, but the rest were a-ha moments! Thank you!
You're so welcome!
Wonderfully explained.... Calculating hours is best...👌👌
For very long numbers, I've had good results setting format as number with number of digits after decimal to zero.
Hi James.. good tip. Excel would still truncate the number if you enter 16 or more digits. Try entering the number 1234567812345678 and Excel will change it to 123456781234567 *_0_*
A trick to work with numbers as text is to sum 0 when you need to reference the cell, for example using a vlookup. E.g. VLOOKUP(A1+0,...) assuming A1 is a cell with a number as a text
For No. 7, I just changed the font color to white and it does the same trick. Great vid thought, I learned something new :D
Nice one!
Real gem! Thanks for sharing!
Glad you enjoyed it!
Thanks for the nice tips. In point 7, can we hide the formula in sheet one which are linked with sheet two, in such a way that if we put data in the second sheet, the sheet one pick the data but without data in second sheet will look complete blank.
Trick #5 is awesome!! Thanks
You are welcome David... :)
Thanks for sharing!
For the decimal part, I wonder whether or not it is possible to format a cell not showing decimals it will round down? (so if it is 1.99 it will become 1.9 for 1 decimal place and 1 for no decimals)
Because I try to implement a VBA to switch between 2 decimal places and 0 decimal place but only rounded down, do you think that can be done without using an assistant column?
I learned a lot, thank you.
Very informative. I especially loved the first one about merging cells. I also liked the presentation. One request. Will you please tell which software did you use to record your screen and to edit it?
Hi Tojy.. Thanks for the love. I use Camtasia for recording screen and LogiTech webcam for face.
Thanks for your many videos
Glad you like them!
Thanks Chandoo. I've learnt so much from you over the years. Much appreciated.
Chandow please I want to change Test_E to Test E by format cell
Extremely useful
Glad to hear that
These tricks are easy but explained nicely by Chandoo. The problem is knowing when to use them.
Two powerful ways to remember anything are,
1. Practice
2. Link the idea with something you already know
What does the “link to source” checkbox do in the chart formatting button? I assume it means if the source data changes, the chart updates? But isn’t that what you’d always want? Around 10:30
The link is only for source "formatting". Data is always linked.
Thank you sir.excellent sir
Most welcome
Wow.. that is a basic but i dont know all that those tricks! 😱😱 After many years... I realized how stupid I'am 😂😂😂😂. Now I've learned thanks for this videos 🙏😊😊
simply awesome
Thanks a lot 😊
very helpful thank you
You're welcome!
Thank you!
You're welcome!
Your videos are so helpful that youtube should give us an option of 100 likes per one account.
Aww... 😍 That is so sweet of you Keshava.
For Widths, use Alt, O, C, A for Columns & Alt, O, R, A for Rows.
Alternatively, you can use Alt, H, O, I as well
Thanks Naveed for these shortcuts.
Naveed, I always use this
Very Useful Thanks !!!!!!!!!!!1
You're welcome!
Thank you so much for this short video on trick about format. Most of them were known but the number formatting is such a useful feature that i never bothered to explore. Does the abor applicable only for Office 365 or earlier versions as well?
You're very welcome! All of these should work in most versions of Excel.
Chandoo can you share this workbook for practice plz
Hi, Good details shared, thanks, Pls suggest, I need add 0 in front of 8 digit product codes if put " only its taking and not fining in in master sheet
Hi Senthil. You are welcome.
You can first select the cells where data will be typed and set the format to TEXT (from format cells). Then you can type 0 and Excel will keep it.
Thank you sir...
Wonderful Chandoo 👍. Subscribed.
Welcome Senthil...
Thank you.. good info ..
Glad it was helpful!
Very useful and Informative ; clearly presented ; thank you very much!!!
Excellent hacks bro.. Thanks for sharing.
I have a question. Can we show Thousands and Millions in the same column????
Like A1 has 22000 -> 22 K
A2 has 43000000 -> 43 M
Appreciate your help. 🙂🙏
Yes we can. You can use up to 2 conditions in custom cell format codes to show thousands and millions or millions and billions. For example, this code will get you what you need.
[>=1000000]#,##0,,"m";[>=1000]#,##0,"k";#,##0
All the best.
Thank you so much for being a great teacher....please add the practice file.
Hi Shiv... I don't have a practice file for this as these are formatting tips. You can use any of your own files and learn these.
Plz explain the semicolon syntax in the seventh trick(decimal) as in why two semicolons and what they stand for. Thnaks
Hi Richa... Excel values have a formatting code broken to 4 parts. Each part tells Excel how to format the value for one scenario.
It goes like this by default:
positive value format ; negative value format ; zero format ; any thing else format
The parts are delimited by semicolon.
For example, to show () instead of - sign in front a number and to show - instead of 0, we can use this code.
0;(0);"-";@
For more on this, read this page - chandoo.org/wp/a-technique-to-quickly-develop-custom-number-formats/
@@chandoo_ thankuuu for replying !!
Thanks for sharing
😊😍
Sir excellent information,can we convert angular degree in degree, minutes and seconds I.e .5.2 deg in 5:12:00
@chandoo
How to reset from Million Format to General Number Format ?
Hi Chandu, i really like to you for enriching my Excel knowledge, can you please help me with a tutorial on Drag and drop scheduler ghant chart.
Thank you, there are some nice tricks. I like to ad something to the long numbers. Keep them as a standard number, but reduce the decimal to zero, then they still work as numbers but are not reduced to scientific format.
Great tip! See my notes on other comments. This method works fine until you cross 15 digits. Then Excel will ignore the last digits and turns them into 0s.
Thanks Sir
Nice Guruji 🙏🏻
Very nice vid thanks
Wow, that was great, thanks!
Glad you liked it!
Thank you so much great skills
Chandoo baba ki jai ho ,,,🙏🙏🙏🙏🙏
Really useful! Thanks Chandoo
ChanduJi, is there auto format to show 346762 to 3.47 L here L means Lakhs. Basically Rs. 3,46,762 as Rs. 3.47 lakhs after decimal aedjustment. Often there is a requirement for showing this in graphs.
The easy option is to just calculate the ls in a different cell and use it for labels etc.
Great video✔️
📍Maybe an expansion on cell formatting re use of [ ] and ;
Eg the 9th example in this video, used [h], but not so clear a reason.
Thank you. I am not sure about the technical reason, but I believe [ ]s are used for conditions and to convert a value to whatever format you want without the limitation. So for example, [h] will show total hours in a value (even if more than 24), [mm] will show number of minutes and [ss] will give you total seconds.
Excellent - I have learnt some good new tricks! Thanks
Great to hear!
Excellent 👌
Thank you! Cheers!
Did not expect it but I could have used this a month ago, it would have made my job so much easier.
NEW HERE IN EXCEL GREAT VIDEO
Thanks Sir .... I wish to have the number for Lakhs and Crore [Indian Numbering System] in number format ... can you please provide the trick...thanking you in anticipation
Very well done.
Glad you liked it!
I learned quite a few new tricks. Thanks 😊
My pleasure 😊
These are really helpful 👏... Thanks for sharing. Cheers
Glad you like them!
Great stuff. I wish I knew some of this 10 years ago when I could enhance my salary!
a lot of them were not available in old versions of Excel.
Chandoo, you are awesome. Thank you for all you share.
Please show how to quickly track data entry skip
What is data entry *skip?*
Theses are great tips, thank you. 👍
Glad you like them!
very nice!
Can you improve on sound quality? Thanks !
Chandu sir mujhe trend analysis learn Krna hai
Great tips and useful tips. Thanks
Glad it was helpful!
Useful
Glad to hear that
Thank you, this will help me a lot.
Glad to hear that!
As usual you are awesome..
Thank for sharing..
I do not understand why "Center across selection" is better than merge with the comment you said that choosing the cell to the left has the value but the one next to it doesn't - that is true in both cases (that being said, I did not know that "Center across selection" was around and I will use it - I hate merged cells for filter reasons, etc.)
You are right. The merged cells (while good for display reasons) can wreak havoc with your filters, pivots and even VBA.
Thanks Chandoo
Thanks for sharing the tricks.
Text format for very long numbers with arithmetic application should be appreciated
I am your big fan. Thanks
Thanks Chandoo. This is really good content that deserves better sound quality. Good luck.
I will try my best
@@chandoo_No worries man. Actually, I think this a common issue one RUclips. I noticed that some other RUclipsrs were informed about the same problem in their videos just recently. I doubt that it is a Google Chrome related problem. @RUclips should do an investigation on this. Thanks.
this is really awesome.Thank you. can you show the formatting for Lacs as well. As we prefer lacs than millions
Hi Sandip.. Thank you. Please refer to this page for instructions on Lacs formatting. chandoo.org/wp/indian-currency-format-excel/
@@chandoo_ thank you for reply and help
Thank you Sir 🙏 But "Merge Across"(cells with data) is not working in my system. After selecting the cells with data, if I click "Merge Across", it shows "Merging cells only keeps the upper-left value and discards other values." Why?
You are welcome.
Merge across only works when you have data in one cell per row of selection. Adjust your selection and try again.
at point 10: you can use double negation (-- i.e. minus minus) in front of cell reference to convert implicitly from text to number. IMPORTANT NOTE: Excel work with only 15 semnificatie digits
Good idea...
YES
In glad to find out I must be classed as an excel expert lol 😃
Yes you are!
Hi chandu i need to training on power query and power bi how to reach you