10 Excel formatting tricks *ONLY* experts know

[@MRaedAlLulu]

Yes, this is how experts do.. Thank you, Chandoo!

[@TheROMaNProject]

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...

[@chandoo_]

Glad it was helpful!

[@wilsonthundil584]

From last 7 years I am your big follower ,Really amazing your talent

[@chandoo_]

Thanks Wilson. Wow, 7 years. 😍

[@jimmylui1358]

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

[@excelworx8712]

thanks for the time marker. very much needed

[@surajbhardwaj428]

Wonderfully explained.... Calculating hours is best...👌👌

[@Everyonelovesyou]

I came across all these problems..there is so simple way to fix ...Thanks Chandoo...I hv been your viewer since 1decade now

[@Everyonelovesyou]

Thanks Cloudyhills

[@chandoo_]

Wow.. Thanks for being with me and learning all these years :)

[@gwen-m8r]

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.

[@CrispusAttucks2315]

Man thank you!!! That merge and center option helped immensely!!!

[@logannasty3240]

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.

[@CrispusAttucks2315]

@@logannasty3240 lol. I understand you there!!

[@wrongturnlima007]

chandoo is a genius

[@Acheiropoietos]

I have been using Excel for 20 years. These tips will help me for another 20. ❤️ btw, I knew alt+enter 😎

[@chandoo_]

Great to hear!

[@ri_ma_ri]

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

[@chandoo_]

Nice one!

[@PaulasPicks]

This was awesome! I knew a couple already, but the rest were a-ha moments! Thank you!

[@chandoo_]

You're so welcome!

[@jeanjeannie6728]

Thank you! Your instructions are so clear and very useful - a big help : D

[@chandoo_]

Glad to hear that!

[@dhunpagla3871]

Merge across is new added in my knowledge book ...Thanks Mr. C 😎🙏

[@chandoo_]

Excellent!

[@leonidaskyrgiakos6881]

Real gem! Thanks for sharing!

[@chandoo_]

Glad you enjoyed it!

[@davidjordan3703]

I actually learned a trick or two - much to my amazement . Thank you

[@daborean]

Trick #5 is awesome!! Thanks

[@chandoo_]

You are welcome David... :)

[@BalaBharathkv]

I am glad that i know 6 of them already

[@chandoo_]

Woohoo...

[@ahmedwedibada7179]

Very useful and Informative ; clearly presented ; thank you very much!!!

[@NabilDerouiche]

Very good, thanks. For 10th point to format a very long numbers you can put ' before the number and all will be OK.

[@colinrothwell2303]

Thanks Chandoo. I've learnt so much from you over the years. Much appreciated.

[@swapna_learner]

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?

[@chandoo_]

You're very welcome! All of these should work in most versions of Excel.

[@zdslmn]

I learned a lot, thank you.

[@oliviasimon6130]

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

[@larryblack5804]

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.

[@SKVHappyHome]

Wonderful Chandoo 👍. Subscribed.

[@chandoo_]

Welcome Senthil...

[@mdmoniruzzaman4431]

Thanks for your many videos

[@chandoo_]

Glad you like them!

[@MrUvikram]

Thanks a lot Hero

[@ashleyspencer3664]

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.

[@chandoo_]

That is a great one. I use it all the time. You can see that + few other awesome charting tricks in this video kzbin.info/www/bejne/pIacmKNmr85-p80

[@tojyjv748]

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?

[@chandoo_]

Hi Tojy.. Thanks for the love. I use Camtasia for recording screen and LogiTech webcam for face.

[@lfjv]

Your videos are so helpful that youtube should give us an option of 100 likes per one account.

[@chandoo_]

Aww... 😍 That is so sweet of you Keshava.

[@AndreFouche]

Excellent - I have learnt some good new tricks! Thanks

[@chandoo_]

Great to hear!

[@24uyube]

These tricks are easy but explained nicely by Chandoo. The problem is knowing when to use them.

[@chandoo_]

Two powerful ways to remember anything are, 1. Practice 2. Link the idea with something you already know

[@senthu177]

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

[@chandoo_]

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.

[@nancynocdvel6620]

Theses are great tips, thank you. 👍

[@chandoo_]

Glad you like them!

[@jayadevpalai85]

These are really helpful 👏... Thanks for sharing. Cheers

[@chandoo_]

Glad you like them!

[@ravikumarayapilla8750]

Thank you sir.excellent sir

[@chandoo_]

Most welcome

[@silvana11221122]

@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

[@Mhmd_Owais]

Extremely useful

[@chandoo_]

Glad to hear that

[@chrisgibbs8204]

Really useful! Thanks Chandoo

[@cesaboy12]

very helpful thank you

[@chandoo_]

You're welcome!

[@stephenbarrett3067]

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.

[@andrevanroy3099]

Or use = --A1+10

[@jiajiaphotography]

I learned quite a few new tricks. Thanks 😊

[@chandoo_]

My pleasure 😊

[@ronnen-12]

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_]

Good point!

[@dmpatel6920]

simply awesome

[@chandoo_]

Thanks a lot 😊

[@ericlei2207]

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?

[@bobsmbln9099]

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 🙏😊😊

[@rameshks8449]

Very Useful Thanks !!!!!!!!!!!1

[@chandoo_]

You're welcome!

[@sureshpaul1055]

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.

[@uday3171]

Thank you.. good info ..

[@chandoo_]

Glad it was helpful!

[@gsmitchell]

Wow, that was great, thanks!

[@chandoo_]

Glad you liked it!

[@irwankurniawan1737]

Thank you!

[@chandoo_]

You're welcome!

[@jamesrawlings8493]

For very long numbers, I've had good results setting format as number with number of digits after decimal to zero.

[@chandoo_]

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_*

[@medinahelano]

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

[@ShivBoyAbhishek]

Thank you so much for being a great teacher....please add the practice file.

[@chandoo_]

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.

[@Oggiez65]

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

[@sanjeevkhakre2990]

Excellent 👌

[@chandoo_]

Thank you! Cheers!

[@ahmedmokhtar190]

Thank you so much great skills

[@TaxMentors]

Chandoo baba ki jai ho ,,,🙏🙏🙏🙏🙏

[@alwayspositive999]

Very nice vid thanks

[@querrythis]

Great tips and useful tips. Thanks

[@chandoo_]

Glad it was helpful!

[@gopigopichand4379]

As usual you are awesome.. Thank for sharing..

[@kevinslattery5748]

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.

[@chandoo_]

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.

[@felixbaumer3879]

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.

[@chandoo_]

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.

[@shanthidevig2395]

Thank you sir...

[@balajinarasimhan533]

Good stuff man. Keep it coming.

[@chandoo_]

Appreciate it!

[@richaagrawal376]

Plz explain the semicolon syntax in the seventh trick(decimal) as in why two semicolons and what they stand for. Thnaks

[@chandoo_]

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/

[@richaagrawal376]

@@chandoo_ thankuuu for replying !!

[@Alhusam]

Thanks Chandoo. This is really good content that deserves better sound quality. Good luck.

[@chandoo_]

I will try my best

[@Alhusam]

@@chandoo_No worries man. Actually, I think this a common issue one KZbin. I noticed that some other KZbinrs were informed about the same problem in their videos just recently. I doubt that it is a Google Chrome related problem. @KZbin should do an investigation on this. Thanks.

[@AliciaMarkoe]

Nice, thank you 🦋🌼🌷

[@chandoo_]

Welcome 😊

[@FRANKWHITE1996]

Thanks for sharing

[@chandoo_]

😊😍

[@rubelrana8401]

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.

[@moulik2341]

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. 🙂🙏

[@chandoo_]

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.

[@CurlingBirds]

Chandoo, you are awesome. Thank you for all you share.

[@wayneedmondson1065]

Hi Chandoo.. great! Thanks for sharing these tips. Thumbs up!!

[@chandoo_]

Thanks for liking

[@josephbarredo3]

NEW HERE IN EXCEL GREAT VIDEO

[@vijaysarathy3833]

excellent excel teaching and very useful knowledge sharing ,thank you,chandoo

[@excelemployeeleavetracker1274]

Very well done.

[@chandoo_]

Glad you liked it!

[@shiv1823]

Nice Guruji 🙏🏻

[@suresh9242]

Thank you, this will help me a lot.

[@chandoo_]

Glad to hear that!

[@rimshamurtaza7075]

Chandoo can you share this workbook for practice plz

[@parvezahmed3559]

Love you, great thing to learn

[@chandoo_]

Happy to help!

[@ocoolwow]

Did not expect it but I could have used this a month ago, it would have made my job so much easier.

[@naveedulhaq9542]

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

[@chandoo_]

Thanks Naveed for these shortcuts.

[@govindavelankarvelankar1140]

Naveed, I always use this

[@bijaytanty8434]

Thanks Sir

[@yesyoucanexcel441]

Sir excellent information,can we convert angular degree in degree, minutes and seconds I.e .5.2 deg in 5:12:00

[@raitup00]

Very useful video!

[@saamekini1638]

Thanks Chandoo

[@1Shreshtha]

I am your big fan. Thanks

[@bartoni79]

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

[@chandoo_]

The link is only for source "formatting". Data is always linked.

[@seanmen4141]

Thanks for sharing the tricks.

[@biomedbob59]

Great stuff. I wish I knew some of this 10 years ago when I could enhance my salary!

[@jhingur7169]

a lot of them were not available in old versions of Excel.

[@thesandy1413]

this is really awesome.Thank you. can you show the formatting for Lacs as well. As we prefer lacs than millions

[@chandoo_]

Hi Sandip.. Thank you. Please refer to this page for instructions on Lacs formatting. chandoo.org/wp/indian-currency-format-excel/

[@thesandy1413]

@@chandoo_ thank you for reply and help

[@nandakumarsanthosh]

very nice!

[@govindkumardasamantarao562]

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.

[@chandoo_]

The easy option is to just calculate the ls in a different cell and use it for labels etc.

[@preejancm]

Useful

[@chandoo_]

Glad to hear that

[@lettynavazquez5764]

Your classes are great! i love it. Is there a way to filter between times? Say for instance, I have a courier team who drives medical supplies back and forth all day. So we have 1st shift, 2nd shift, 3rd shift, overnight and weekend shift. I have 6 months worth of data and i was trying to see overnight shift only. So i wanted to filter it to where i only see routes picking up items from 10:30pm to 7:00am. Is this possible? do you have a tutorial on how to do that if it is possible? Please advise. :)

[@chandoo_]

Thanks Lettyna... Interesting question. I haven't found an easy way yet, but you can do this. Add an extra column to extract the time portion. Use =TIME(timestamp). Now apply filters and filter "greater than" with 10:30 PM OR "less than" 7:30 AM.

[@lettynavazquez5764]

@@chandoo_ thank you very much. This helped me alot. My boss will be impressed. :)

[@lettynavazquez5764]

@@chandoo_ I am having an issue. So it worked the first time. Now when i Filter for a different shift I am getting the shift asked for plus 3rd shift. Also the shift that i asked for also had 3rd shift that didn't show up the first time. Can you make a video on how to do this. It worked just once and now i can't get it to filter again.

[@venkatiyer7459]

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

[@goxokogoxoki6734]

Great video, Chandoo. However, ISO code for million is M, not m :)

[@chandoo_]

Thank you. Yes, you are right.

[@Reduce_Scan]

Chandow please I want to change Test_E to Test E by format cell

[@bidhan0001]

Text format for very long numbers with arithmetic application should be appreciated

[@mdmoniruzzaman4431]

Please show how to quickly track data entry skip

[@chandoo_]

What is data entry *skip?*

[@mmincredible1451]

Love u Chandu....

[@chandoo_]

Thank you 😊