10 Excel formatting tricks ONLY experts know

Подписаться 636 тыс.

Просмотров 325 тыс.

50% 1

Here is my top 10 list of formatting tricks in Excel that only pro users would know.
1. Merging without merging - centre across selection
2. Merge multiple cells with "Merge across"
3. No decimal points for large numbers with Custom cell formatting
4. Showing numbers in Thousands or millions with Custom cell formatting
5. New line in a cell with ALT+Enter
6. Copy widths alone with paste special
7. Skip zero in chart labels with custom cell formatting
8. Align & distribute charts with alignment tools
9. Show total hours with [h]:mm custom code
10. Text format for very long numbers
For more tips on Excel formatting, check out these pages:
chandoo.org/wp/excel-formatti...
Watch next 😀:
=============
10 Data Clean-up tricks with Excel (250k+ views) - • Data cleaning in Excel...
10 Awesome things you can do with Excel (220k+ views) - • 10 awesome things you ...
5 Skills you need to have to be a GREAT data analyst (150k+ views) - • 5 key skills you need ...
My most popular videos (playlist) - • Most Popular Excel & P...
Data Analysis Skills (playlist) - • Beginner to Pro FREE E...
#ExcelTips #MustWatch

Наука

Опубликовано:

7 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 245

@MRaedAlLulu 4 года назад

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

@TheROMaNProject 3 года назад

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_ 3 года назад

Glad it was helpful!

@jimmylui1358 4 года назад

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 3 года назад

thanks for the time marker. very much needed

@jeanjeannie6728 3 года назад

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

@chandoo_ 3 года назад

Glad to hear that!

@davidjordan3703 3 года назад

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

@CurlingBirds 4 года назад

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

@dhunpagla3871 4 года назад

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

@chandoo_ 4 года назад

Excellent!

@gwendylewis 3 года назад

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.

@wrongturnlima007 Год назад

chandoo is a genius

@PaulasPicks 3 года назад

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

@chandoo_ 3 года назад

You're so welcome!

@ahmedwedibada7179 3 года назад

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

@MrUvikram 3 года назад

Thanks a lot Hero

@seanmen4141 4 года назад

Thanks for sharing the tricks.

@Acheiropoietos 3 года назад

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

@chandoo_ 3 года назад

Great to hear!

@chrisgibbs8204 3 года назад

Really useful! Thanks Chandoo

@wilsonthundil584 2 года назад

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

@chandoo_ 2 года назад

Thanks Wilson. Wow, 7 years. 😍

@zdslmn Год назад

I learned a lot, thank you.

@wayneedmondson1065 4 года назад

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

@chandoo_ 4 года назад

Thanks for liking

@surajbhardwaj428 2 года назад

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

@NabilDerouiche 3 года назад

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

@leonidaskyrgiakos6881 2 года назад

Real gem! Thanks for sharing!

@chandoo_ 2 года назад

Glad you enjoyed it!

@1Shreshtha 4 года назад

I am your big fan. Thanks

@josephbarredo3 3 года назад

NEW HERE IN EXCEL GREAT VIDEO

@colinrothwell2303 4 года назад

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

@Everyonelovesyou 4 года назад

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

@Everyonelovesyou 4 года назад

Thanks Cloudyhills

@chandoo_ 4 года назад

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

@ahmedmokhtar190 3 года назад

Thank you so much great skills

@daborean 3 года назад

Trick #5 is awesome!! Thanks

@chandoo_ 3 года назад

You are welcome David... :)

@ri_ma_ri 3 года назад

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_ 3 года назад

Nice one!

@shanthidevig2395 Год назад

Thank you sir...

@alwayspositive999 3 года назад

Very nice vid thanks

@shiv1823 4 года назад

Nice Guruji 🙏🏻

@CrispusAttucks2315 3 года назад

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

@logannasty3240 3 года назад

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 3 года назад

@@logannasty3240 lol. I understand you there!!

@AndreFouche 3 года назад

Excellent - I have learnt some good new tricks! Thanks

@chandoo_ 3 года назад

Great to hear!

@gsmitchell 3 года назад

Wow, that was great, thanks!

@chandoo_ 3 года назад

Glad you liked it!

@nandakumarsanthosh 4 года назад

very nice!

@Oggiez65 3 года назад

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

@biomedbob59 4 года назад

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

@jhingur7169 3 года назад

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

@querrythis 3 года назад

Great tips and useful tips. Thanks

@chandoo_ 3 года назад

Glad it was helpful!

@saamekini1638 3 года назад

Thanks Chandoo

@ashleyspencer3664 3 года назад

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_ 3 года назад

That is a great one. I use it all the time. You can see that + few other awesome charting tricks in this video ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-nU9gp0ziKqk.html

@jiajiaphotography 3 года назад

I learned quite a few new tricks. Thanks 😊

@chandoo_ 3 года назад

My pleasure 😊

@mdmoniruzzaman4431 3 года назад

Thanks for your many videos

@chandoo_ 3 года назад

Glad you like them!

@suresh9242 3 года назад

Thank you, this will help me a lot.

@chandoo_ 3 года назад

Glad to hear that!

@nancynocdvel6620 3 года назад

Theses are great tips, thank you. 👍

@chandoo_ 3 года назад

Glad you like them!

@jayadevpalai85 3 года назад

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

@chandoo_ 3 года назад

Glad you like them!

@sureshpaul1055 3 года назад

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.

@hazemali382 3 года назад

Great ♥

@balajinarasimhan533 3 года назад

Good stuff man. Keep it coming.

@chandoo_ 3 года назад

Appreciate it!

@SKVHappyHome 3 года назад

Wonderful Chandoo 👍. Subscribed.

@chandoo_ 3 года назад

Welcome Senthil...

@parvezahmed3559 3 года назад

Love you, great thing to learn

@chandoo_ 3 года назад

Happy to help!

@sunnydwivedi3094 4 года назад

Nice!!

@uday3171 3 года назад

Thank you.. good info ..

@chandoo_ 3 года назад

Glad it was helpful!

@ulambaulamba 3 года назад

Extremely useful

@chandoo_ 3 года назад

Glad to hear that

@ravikumarayapilla8750 3 года назад

Thank you sir.excellent sir

@chandoo_ 3 года назад

Most welcome

@bijaytanty8434 Год назад

Thanks Sir

@cesaboy12 3 года назад

very helpful thank you

@chandoo_ 3 года назад

You're welcome!

@AliciaMarkoe 3 года назад

Nice, thank you 🦋🌼🌷

@chandoo_ 3 года назад

Welcome 😊

@BalaBharathkv 3 года назад

I am glad that i know 6 of them already

@chandoo_ 3 года назад

Woohoo...

@oliviasimon6130 3 года назад

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 3 года назад

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.

@shezadtahir6179 4 года назад

thank you bro, see more

@rameshks8449 3 года назад

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

@chandoo_ 3 года назад

You're welcome!

@irwankurniawan1737 3 года назад

Thank you!

@chandoo_ 3 года назад

You're welcome!

@FRANKWHITE1996 3 года назад

Thanks for sharing

@chandoo_ 3 года назад

😊😍

@dmpatel6920 3 года назад

simply awesome

@chandoo_ 3 года назад

Thanks a lot 😊

@excelemployeeleavetracker1274 3 года назад

Very well done.

@chandoo_ 3 года назад

Glad you liked it!

@sanjeevkhakre2990 4 года назад

Excellent 👌

@chandoo_ 4 года назад

Thank you! Cheers!

@FaysalEasyExcel 4 года назад

thankyou sir, it's another awesome technique. love it.

@jamesrawlings8493 3 года назад

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

@chandoo_ 3 года назад

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 2 года назад

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

@tojyjv748 3 года назад

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_ 3 года назад

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

@mmincredible1451 4 года назад

Love u Chandu....

@chandoo_ 4 года назад

Thank you 😊

@bobsmbln9099 3 года назад

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

@ronnen-12 3 года назад

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_ 3 года назад

Good point!

@ShivBoyAbhishek 3 года назад

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

@chandoo_ 3 года назад

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.

@ericlei2207 2 года назад

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?

@Alhusam 3 года назад

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

@chandoo_ 3 года назад

I will try my best

@Alhusam 3 года назад

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

@preejancm 3 года назад

Useful

@chandoo_ 3 года назад

Glad to hear that

@lfjv 2 года назад

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

@chandoo_ 2 года назад

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

@silvana11221122 2 года назад

@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

@yesyoucanexcel441 2 года назад

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

@stephenbarrett3067 4 года назад

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 3 года назад

Or use = --A1+10

@24uyube 4 года назад

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

@chandoo_ 4 года назад

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

@rubelrana8401 3 года назад

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.

@senthu177 3 года назад

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_ 3 года назад

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.

@TaxMentors 4 года назад

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

@thesandy1413 4 года назад

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

@chandoo_ 4 года назад

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

@thesandy1413 4 года назад

@@chandoo_ thank you for reply and help

@swapna_learner 2 года назад

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_ 2 года назад

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

@rimshamurtaza7075 Год назад

Chandoo can you share this workbook for practice plz

@vijaysarathy3833 4 года назад

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

@diraviams9717 3 года назад

It is nice

@chandoo_ 3 года назад

Thank you...

@jonathanm9436 3 года назад

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

@andreag7530 3 года назад

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

@chandoo_ 3 года назад

Yes you are!

@kevinslattery5748 3 года назад

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_ 3 года назад

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.

@pushkalupadhyay 2 года назад

Chandu sir mujhe trend analysis learn Krna hai

@ocoolwow 3 года назад

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

@umanggupta4769 3 года назад

Good

@chandoo_ 3 года назад

Thanks

@gopigopichand4379 4 года назад

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

@Reduce_Scan Год назад

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

@amoblng8464 2 года назад

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

@richaagrawal376 2 года назад

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

@chandoo_ 2 года назад

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 2 года назад

@@chandoo_ thankuuu for replying !!