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!

[@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

[@wrongturnlima007]

chandoo is a genius

[@MrUvikram]

Thanks a lot Hero

[@jeanjeannie6728]

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

[@chandoo_]

Glad to hear that!

[@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!!

[@wilsonthundil584]

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

[@chandoo_]

Thanks Wilson. Wow, 7 years. 😍

[@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

[@BalaBharathkv]

I am glad that i know 6 of them already

[@chandoo_]

Woohoo...

[@dhunpagla3871]

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

[@chandoo_]

Excellent!

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

[@zdslmn]

I learned a lot, thank you.

[@PaulasPicks]

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

[@chandoo_]

You're so welcome!

[@daborean]

Trick #5 is awesome!! Thanks

[@chandoo_]

You are welcome David... :)

[@mdmoniruzzaman4431]

Thanks for your many videos

[@chandoo_]

Glad you like them!

[@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 :)

[@colinrothwell2303]

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

[@surajbhardwaj428]

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

[@ulambaulamba]

Extremely useful

[@chandoo_]

Glad to hear that

[@NabilDerouiche]

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

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

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

[@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!

[@rameshks8449]

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

[@chandoo_]

You're welcome!

[@SKVHappyHome]

Wonderful Chandoo 👍. Subscribed.

[@chandoo_]

Welcome Senthil...

[@alwayspositive999]

Very nice vid thanks

[@dmpatel6920]

simply awesome

[@chandoo_]

Thanks a lot 😊

[@ravikumarayapilla8750]

Thank you sir.excellent sir

[@chandoo_]

Most welcome

[@cesaboy12]

very helpful thank you

[@chandoo_]

You're welcome!

[@irwankurniawan1737]

Thank you!

[@chandoo_]

You're welcome!

[@ahmedwedibada7179]

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

[@shanthidevig2395]

Thank you sir...

[@FRANKWHITE1996]

Thanks for sharing

[@chandoo_]

😊😍

[@uday3171]

Thank you.. good info ..

[@chandoo_]

Glad it was helpful!

[@chrisgibbs8204]

Really useful! Thanks Chandoo

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

[@gsmitchell]

Wow, that was great, thanks!

[@chandoo_]

Glad you liked it!

[@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

[@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

[@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

[@bijaytanty8434]

Thanks Sir

[@shiv1823]

Nice Guruji 🙏🏻

[@saamekini1638]

Thanks Chandoo

[@ahmedmokhtar190]

Thank you so much great skills

[@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!

[@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

[@josephbarredo3]

NEW HERE IN EXCEL GREAT VIDEO

[@nandakumarsanthosh]

very nice!

[@sanjeevkhakre2990]

Excellent 👌

[@chandoo_]

Thank you! Cheers!

[@ocoolwow]

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

[@nancynocdvel6620]

Theses are great tips, thank you. 👍

[@chandoo_]

Glad you like them!

[@gopigopichand4379]

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

[@jiajiaphotography]

I learned quite a few new tricks. Thanks 😊

[@chandoo_]

My pleasure 😊

[@preejancm]

Useful

[@chandoo_]

Glad to hear that

[@CurlingBirds]

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

[@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

[@jayadevpalai85]

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

[@chandoo_]

Glad you like them!

[@TaxMentors]

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

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

[@excelemployeeleavetracker1274]

Very well done.

[@chandoo_]

Glad you liked it!

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

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

[@1Shreshtha]

I am your big fan. Thanks

[@querrythis]

Great tips and useful tips. Thanks

[@chandoo_]

Glad it was helpful!

[@rimshamurtaza7075]

Chandoo can you share this workbook for practice plz

[@balajinarasimhan533]

Good stuff man. Keep it coming.

[@chandoo_]

Appreciate it!

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

[@seanmen4141]

Thanks for sharing the tricks.

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

[@illmtoloko]

On the first one I was "wow" kkkkkkkkkkk

[@jaketribb43]

didnt work for me though

[@Reduce_Scan]

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

[@suresh9242]

Thank you, this will help me a lot.

[@chandoo_]

Glad to hear that!

[@andreag7530]

In glad to find out I must be classed as an excel expert lol 😃

[@chandoo_]

Yes you are!

[@parvezahmed3559]

Love you, great thing to learn

[@chandoo_]

Happy to help!

[@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?

[@wayneedmondson1065]

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

[@chandoo_]

Thanks for liking

[@diraviams9717]

It is nice

[@chandoo_]

Thank you...

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

[@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

[@AliciaMarkoe]

Nice, thank you 🦋🌼🌷

[@chandoo_]

Welcome 😊

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

[@bidhan0001]

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

[@raitup00]

Very useful video!

[@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 !!

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

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

[@umanggupta4769]

Good

[@chandoo_]

Thanks

[@mdmoniruzzaman4431]

Please show how to quickly track data entry skip

[@chandoo_]

What is data entry *skip?*

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

[@amoblng8464]

@chandoo How to reset from Million Format to General Number Format ?

[@pushkalupadhyay]

Chandu sir mujhe trend analysis learn Krna hai

[@JFerrazChannelTV]

The best one was the one you weren’t planning to show, the ;;;

[@chandoo_]

It is well hidden ;)

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

[@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

[@yesyoucanexcel441]

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

[@vijaysarathy3833]

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

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

[@jonathanm9436]

Some cool features I was unaware of - only knew two. Thanks - I'll be putting them to good use. Can I suggest that you use a better microphone - this one sounds like you're talking through a pillow 😂.