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.
@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, 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.
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?
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?
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.
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.
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.
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 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
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.
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 🙏😊😊
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.
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.
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
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/
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
Hi Chandoo, thanx for the video. However, I fight with Excel for many years: if I import a csv file with decimal points into a system with default decimal commas, Excel changes numbers as 3.5 into a date 5th March. Is there a possibility in Excel to prevent this automation? Only Power Query is able to process this correctly.
@@chandoo_No worries man. Actually, I think this a common issue one UA-cam. I noticed that some other UA-camrs were informed about the same problem in their videos just recently. I doubt that it is a Google Chrome related problem. @UA-cam should do an investigation on this. Thanks.
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.
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.
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
From last 7 years I am your big follower ,Really amazing your talent
Thanks Wilson. Wow, 7 years. 😍
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!!
Wonderfully explained.... Calculating hours is best...👌👌
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.
I learned a lot, thank you.
This was awesome! I knew a couple already, but the rest were a-ha moments! Thank you!
You're so welcome!
Real gem! Thanks for sharing!
Glad you enjoyed it!
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 :)
chandoo is a genius
I have been using Excel for 20 years. These tips will help me for another 20. ❤️
btw, I knew alt+enter 😎
Great to hear!
Thanks a lot Hero
Trick #5 is awesome!! Thanks
You are welcome David... :)
Thank you! Your instructions are so clear and very useful - a big help : D
Glad to hear that!
I actually learned a trick or two - much to my amazement . Thank you
Wonderful Chandoo 👍. Subscribed.
Welcome Senthil...
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!
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!
I am glad that i know 6 of them already
Woohoo...
Thanks for your many videos
Glad you like them!
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.
Very useful and Informative ; clearly presented ; thank you very much!!!
@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
Excellent - I have learnt some good new tricks! Thanks
Great to hear!
Extremely useful
Glad to hear that
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.
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?
Chandow please I want to change Test_E to Test E by format cell
Theses are great tips, thank you. 👍
Glad you like them!
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.
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 ua-cam.com/video/nU9gp0ziKqk/v-deo.html
Really useful! Thanks Chandoo
Very Useful Thanks !!!!!!!!!!!1
You're welcome!
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
Wow, that was great, thanks!
Glad you liked it!
Sir excellent information,can we convert angular degree in degree, minutes and seconds I.e .5.2 deg in 5:12:00
simply awesome
Thanks a lot 😊
very helpful 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.
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.
Chandoo can you share this workbook for practice plz
Very nice vid thanks
These are really helpful 👏... Thanks for sharing. Cheers
Glad you like them!
Thanks Chandoo. I've learnt so much from you over the years. Much appreciated.
I learned quite a few new tricks. Thanks 😊
My pleasure 😊
Thank you!
You're welcome!
Thank you.. good info ..
Glad it was helpful!
Thank you so much great skills
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.
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.
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
Thank you sir.excellent sir
Most welcome
Thanks for sharing
😊😍
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 sir...
As usual you are awesome..
Thank for sharing..
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
Very well done.
Glad you liked it!
Excellent 👌
Thank you! Cheers!
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!
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 🙏😊😊
Good stuff man. Keep it coming.
Appreciate it!
very nice!
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.
Great tips and useful tips. Thanks
Glad it was helpful!
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
Chandoo baba ki jai ho ,,,🙏🙏🙏🙏🙏
Thank you, this will help me a lot.
Glad to hear that!
NEW HERE IN EXCEL GREAT VIDEO
Chandoo, you are awesome. Thank you for all you share.
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.
@chandoo
How to reset from Million Format to General Number Format ?
Love you, great thing to learn
Happy to help!
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.
Thanks Sir
excellent excel teaching and very useful knowledge sharing ,thank you,chandoo
Hi Chandoo.. great! Thanks for sharing these tips. Thumbs up!!
Thanks for liking
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 !!
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.
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
Hi Chandoo, thanx for the video. However, I fight with Excel for many years: if I import a csv file with decimal points into a system with default decimal commas, Excel changes numbers as 3.5 into a date 5th March. Is there a possibility in Excel to prevent this automation? Only Power Query is able to process this correctly.
Hmm, I would use Power Query too as Excel import / copy paste is a bit finicky.
Hi chandu i need to training on power query and power bi how to reach you
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 UA-cam. I noticed that some other UA-camrs were informed about the same problem in their videos just recently. I doubt that it is a Google Chrome related problem. @UA-cam should do an investigation on this. Thanks.
Useful
Glad to hear that
Nice Guruji 🙏🏻
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.
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.
Love u Chandu....
Thank you 😊
Thanks for sharing the tricks.
Did not expect it but I could have used this a month ago, it would have made my job so much easier.
Nice, thank you 🦋🌼🌷
Welcome 😊
Very useful video!
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
I am your big fan. Thanks
Can you improve on sound quality? Thanks !
Chandu sir mujhe trend analysis learn Krna hai