Discover more practical Excel tricks like the one in this video by joining our full "Excel Essentials" course here 👉 www.xelplus.com/course/learn-excel-essentials/
@kuuuyajim
3 жыл бұрын
I like the custom format best because you can work on them without having to separate the text. I would just need to make a note if it's on a shared report. Thank you, Leila! 😉
@danilo352
3 жыл бұрын
I always make formulas that start with something like =IF (A2=“”,””,actual formula goes here). I do that so that my spreadsheet is clean without a bunch of zeros. I think you just changed EVERYTHING for me with the tip on Custom number formatting. I never realized you can separate positives, negatives, zero, and text with the semi-colon. Thank you!
@emile8147
3 жыл бұрын
Custom numbers is a good tool to clean zeros on some specific range If you want to clean zeros into the whole worksheet, File-Options, Advanced, uncheck "Show a zero in cells that have zero value"
@danilo352
3 жыл бұрын
@@emile8147 great tip!!!
@SuperWolfkin
3 жыл бұрын
check our her previous video on custom formatting that goes a bit deeper kzitem.info/news/bejne/1X2PnWSpnaKSf5g
@emile8147
3 жыл бұрын
@@SuperWolfkin And because conditional formatting give you access to custom formatting (Format-Number-Custom in the Conditional formatting dialog box), you can mix both
@obiomavirginia8159
2 жыл бұрын
@@emile8147 I tried this but I didn’t notice anything on my worksheet all the numbers in the cells that have zeros where still there. Or am I doing it wrong ?
@aformagie
3 жыл бұрын
Thanks Leila, really increased my excel tips and tricks in the last few months since subscribing, and was joining fields yesterday and was looking into this. Great timing. Cheers
@bruce2357
3 жыл бұрын
Even when you know what's coming you tend to learn something. I knew you were going to use custom formatting because I've been doing that for years but I only knew half of what you showed. Not a big flash fill user, I don't need it but the other two methods I've been using for a while. Thanks.
@LeilaGharani
3 жыл бұрын
Glad you found something new!
@coryburris8211
3 жыл бұрын
I have been using custom number format “;;;” for years as a way to effectively hide a cell. I modified a stock timekeeping template that had both a numeric date field and a text version of the date range, hiding the MONTH and DATE functions that I used to build up the text from the numeric date. My version looked exactly like the template since those functions were “hidden” but I only had to enter the numeric date instead of also having to type text. Later, I modified it further to extract the numeric date from the file name itself, so then the only time I typed a date was when I saved the weekly file.
@RiyadHussain
2 жыл бұрын
A gifted teacher.
@johnmccormick2883
3 жыл бұрын
Always pick up more useful Excel knowledge watching your videos… I’ve been using custom formatting for years and your tip on the “;”’s is great!
@pegmolter3542
3 жыл бұрын
Loved the custom formatting option. Two lessons in one. Thanks!
@LeilaGharani
3 жыл бұрын
Glad you liked it!
@hansklein3649
3 жыл бұрын
Thank you so much, Ms. Gharani! I never realized till now how much dynamical techniques does excel allow. It's trully inexhaustible!
@lizmcneill1641
2 ай бұрын
Option 3 was a lifesaver tks
@AP-nb6og
2 жыл бұрын
Thank you this is the clearest explanation I've found so far!!!
@johnbishop6045
3 жыл бұрын
Custom formatting - exactly what I needed. I've been using simple custom formatting for years but didn't know about its full capabilities until I saw your excellent tutorial. Thanks Leila!
@yakuzatigers
3 жыл бұрын
Thanks Leila. The custom number format is an awesome tool.
@peterbebbington3435
3 жыл бұрын
Great video. I used it today. Knowing how the custom formatting can be used properly really helps
@LeilaGharani
3 жыл бұрын
Great to hear, Peter!
@mramsch
3 жыл бұрын
Leila, great video again, well done! What I appreciate most is that you put solutions into the whole context of the Excel universe, which helps people to gain a structured top-down view and use the right tool deliberately. And I love your clear directly to-the-point language combined with extremely well chosen examples. I'm teaching Excel courses myself from time to time, and your style has become my ideal. :)
@LeilaGharani
3 жыл бұрын
Thank you for the kind words!
@Dithermaster
3 жыл бұрын
I've only watched a few of your videos so far, but I expect I'll be watching more. They are concise, easy to follow, and on useful topics. Thank you!
@LeilaGharani
3 жыл бұрын
Glad you're enjoying the videos!
@36sachin
3 жыл бұрын
Your follower from India thanks a lot to teaching new things
@LeilaGharani
3 жыл бұрын
My pleasure!
@Acheiropoietos
3 жыл бұрын
I have been using the custom formatting trick for a long time, but never realised the 4 conditions! Thank you for showing the way!
@LeilaGharani
3 жыл бұрын
Happy to help!
@dougmphilly
2 жыл бұрын
custom number formatting is awesome and is such great way of saving time.
@boskostulic4360
Жыл бұрын
Thanks a bunch, Leila!
@user-tt4cj9rv3l
8 ай бұрын
Greetings from Thailand, I am using the 3rd options. The result is great, thank you so much.
@bojanasmiljanic7452
3 жыл бұрын
Wow, thank you Leila! You are making my reports and charts looking more and more professional with each new tip. 🍀
@ordinarygurl2401
2 жыл бұрын
Finally got what I was looking for thank you so much ❤️
@TerryMundy
2 жыл бұрын
Thank Leila! So far this is the most valuable coarse for me. I'm able to share this information with other co-workers and they want me to pass on a big "Thank you!".😃
@davidferrick
3 жыл бұрын
Whole numbers are easy, using percents, dates and sometimes decimals is a game changer and forces you to use the TEXT() function (eg. ="ID: "&Text(B2,"#0.0") PS, love your 3rd option to maintain integrity of value for calculations.
@haroldsmith45302
3 жыл бұрын
Thank you, Ms. Gharani. Even though I have been using Excel for decades, you just taught me some useful new techniques. Subscribed.
@bluffmasterhara
5 ай бұрын
You are amazing! I like the last option same as yourself
@bakarpk
3 жыл бұрын
I use formula “concatenate" for multiple text addining from different cells/sheets
@vassiliscapsis9932
3 жыл бұрын
yes this also what I use.
@axelknutt5065
3 жыл бұрын
Me too, but only because I like saying ‘concatenate’
@CalebPieps
3 жыл бұрын
Using =CONCAT( ) is a better version on the older concatenate formula.
@florentdargassies1367
3 жыл бұрын
@@CalebPieps Is it better than & ?
@bakarpk
3 жыл бұрын
@@CalebPieps Its available only new versions...
@vamp9750
3 жыл бұрын
nice the last method is new to me and I could use it in my work thanks
@johnphilippatos
3 жыл бұрын
Great tutorial Leila. What I usually do in such a case, is keep a column (hidden or not) with the actual values and use the second method (the one with the "&") to show the end result on another column. This way I don't mess up with my formatting, and if I need to calculate, I can always refer to the cells on the column with the actuall values, or even use the LEFT, RIGHT or MID formulas on the result column to isolate the value, even if this can prove to be dangerous if you are not able to check it easily, as you may lose digits in some cells. There is also the CONCATENATE formula, which can be use to combine the values of different cells (or the same cell multiple times), but I don't know whether it has any difference than the "&". Thanks again. All the best.
@alexkweya3060
3 жыл бұрын
I'm curious though, if the data is growing, do you have to keep applying the formula to newer rows of data, as opposed to if the custom formatting had been applied to a column. This is also my way of asking: Is there a way to automatically apply a formula to an entire column such that when I'm entering new rows of data I don't have to drag the fill handle/use the Fill function? I may be late to the party but this is something I really need to figure out, @Leila Gharani.
@Barbie.7884
3 жыл бұрын
Again, another video that solves a problem I’m currently working on. Thank you! I never would have thought about custom format as an option.
@LeilaGharani
3 жыл бұрын
Glad to help!
@saneud15
3 жыл бұрын
Great! I never learned so much from anywhere. I used Excel a lot when I was working, now I just wished I knew all these tricks before. Thank you very much Leila.
@LeilaGharani
3 жыл бұрын
Glad it was helpful!
@hambanathan5054
2 жыл бұрын
Thank you Leila this has increased my understanding
@muhammadsameer674
Жыл бұрын
You deserve millions of subscribers and billions of views. You are best teacher of excel on the internet alive.
@alexandersmith2465
Жыл бұрын
I like the way you explain things, your voice, speaking speed, content and overall presentation is engaging and educating. Have picked up few very useful excel tips from your videos. Keep up the great work. cheers
@LeilaGharani
Жыл бұрын
Thank you so much 🙂
@sachinrv1
3 жыл бұрын
Excellent. What we, with older Excel version do to look like "FLASH FILL" is, we select the range C2:C6, and write the formula you showed, in cell C2, and then press Ctrl+Enter, and BOOM.. it does it to all cells in the range. In fact I use this trick to look pro. Thanks Leila for your video !
@LeilaGharani
3 жыл бұрын
Great tip! We shall call it SACHIN FILL 😊
@ruslanbaibozov9409
3 жыл бұрын
Thanks Leila! The last is great one and new for me!
@christoslefkimiotis9889
3 жыл бұрын
Custom formatting seems to be the optimal solution, the custom formatting is a powerful tool and a black box for many. I have seen even formulas inside the dialogue box of a custom formatting .....like if is > 1 add an "s" if not add nothing.... using [1]... I was not able to decode it. It would be good if you could do a video on custom formatting (if you haven't done already) Thx
@josephcoon5809
3 жыл бұрын
Search field: “conditions in custom formatting” The first two hits were: Part 1: kzitem.info/news/bejne/1X2PnWSpnaKSf5g Part 2: kzitem.info/news/bejne/x5eGuYyEpIdijGU I have done a lot of research, so I kind of have a knack for building optimal search phrases for different platforms. Google has some simple controls for curating your search results, but all platforms follow some pretty basic algorithms at a fundamental level. I don’t mind doing the research for others, but I cannot imagine always waiting on an answer from someone in a comment section so I try to urge them to get proficient using the search feature. Cheers 🍻
@LeilaGharani
3 жыл бұрын
Yes - you can use thresholds here too. I explain that here kzitem.info/news/bejne/x5eGuYyEpIdijGU - but a better and more flexible method would be to use something like this: kzitem.info/news/bejne/0oGm3nmiZpF6hG0 for general custom formatting, check out: kzitem.info/news/bejne/l26Ky6iVcXR_dW0 Hope these help.
@christoslefkimiotis9889
3 жыл бұрын
@@LeilaGharani thank you very much, for your reply and the videos, I will give it a go. For a reason that I can't explain I can never remember or find an easy way to access all this information regarding custom formatting..... I will watch all of your videos and take notes with pen and paper.......Thank you very much
@Ayubgn
2 жыл бұрын
I have been looking for 3:34 forever. Kudos!
@davegoodo3603
3 жыл бұрын
Thanks Leila, you've made Custom Formatting your own. Such a useful video.
@LeilaGharani
3 жыл бұрын
Glad it was helpful!
@Scott93312
3 жыл бұрын
LOL, I'm not sure why I watched this video as I was 100% confident I knew all you were going to talk about, yet that last part about custom formatting the text value was new to me... ... So thanks again... ... For the 100th time. LOL
@LeilaGharani
3 жыл бұрын
Glad you found something new, Scott!
@prof.code-dude2750
3 жыл бұрын
Terrific Leila😱! Really like the 3rd method as it is easier to apply, easily changeable and allows us to work with numbers still. The formatting can also be changed back to normal which is a BONUS😎! Thanks for the tutorial 👍 Waiting for your #Shorts on the weekend 🤔 wonder what the topic would be 🤔
@ramanjips5162
3 жыл бұрын
Hi Mam , I need to write different text in front of value in every cell. In this case what formula works?
@josephcoon5809
3 жыл бұрын
@@ramanjips5162 I cannot post the links, but search KZitem for “conditions in custom formats” There are two videos by Leila on the topic.
@ramanjips5162
3 жыл бұрын
@@josephcoon5809 thank you Joseph.
@MrValdesbg
3 жыл бұрын
After I watched your video for custom formatting a while ago the last tip was logical and I am already using it. I was almost sure that this will be one of the 3 tips and the other will be via formula.
@mrk8212
3 жыл бұрын
Thank you! another great vid
@stavrosxakis7360
2 жыл бұрын
You are just amazing. When I see a title that refers to something I (thought) I already know, at the end of the video I always get surprised!
@sibijacob1795
Жыл бұрын
New Perspective and very Informative
@simoiyahector-morales3781
3 жыл бұрын
Absolutely love your tutorials
@rosafecuay6592
2 жыл бұрын
Best saver of.life ever!
@hirenkagathara7216
3 жыл бұрын
I learnt lot of things in Excel Keep going keep sharing your knowledge Love ❤️ from IND
@vijayarjunwadkar
3 жыл бұрын
Thank you Leila! The custom formatting is really good one! Keep up your great teaching! 😊👍
@LeilaGharani
3 жыл бұрын
Thanks Vijay! Will try my best 😊
@nickbell6311
2 жыл бұрын
Thanks again for making wonderful video
@LeilaGharani
2 жыл бұрын
My pleasure 😊
@36sachin
3 жыл бұрын
Thanks for teaching us a new formulas and lot of new things in excel BBA and more due to this we are able to grow our skill
@rkmishraaecs
3 жыл бұрын
Hope soon we will get another such great tips from you
@monsurhelal9996
3 жыл бұрын
Many Many Thanks.
@garynagle3093
3 жыл бұрын
Great video. I always used formulas. Love the custom formatting
@LeilaGharani
3 жыл бұрын
👍
@tahirqureshitgq7892
2 жыл бұрын
Excellent, thanks.
@lpanebr
3 жыл бұрын
I knew there could be something that I didn't know yet! Ever since watching your dedicated video on custom number formatting I've used it constantly. ❤️ But using the @ sign to format text I don't know yet and it is something I want to do from time to time. THANKS!
@satyendrakumar-wk4np
Жыл бұрын
Worked for me, thanks
@masuoodahmad
3 жыл бұрын
Really very useful video, thanks
@360viajero7
2 жыл бұрын
The custom has a great potential. Thanks!
@salimashraf
3 жыл бұрын
I was using the third option partly , but other two options were also interesting and the using the custom option with different four types is also very useful. thanks for sharing this video.
@merchillio
3 жыл бұрын
Wow! I never used custom formating for text. Given that I’m often using data that can be both numerical and alphanumerical, I’ll explore it. I think you just saved me a lot of time
@LeilaGharani
3 жыл бұрын
Great! Very glad to hear that :)
@ShajithManhalangadan
3 жыл бұрын
My favourite class teacher #Leila 😄😄, and today i got a best lesson (the idea of seperation with ";") which i am gonna use it a lot for sure.
@Franceskineos
3 жыл бұрын
Great ! Last method is usefull to add automatically unit of measure to the values in the cells.
@fredrickrajaprakashan3639
2 жыл бұрын
You are the BEST !!!
@chh8860
3 жыл бұрын
I don't think, I have an immediate application for this technique. But sometimes the 'Aha!!!' light comes on later. It will certainly provide me with another 'arrow in my quiver'. As usual, "Thursday's With Leila" never disappoints. This channel continues to be a 'gem'.
@LeilaGharani
3 жыл бұрын
Thank you! I hope you enjoyed your coffee 😊
@chh8860
3 жыл бұрын
@@LeilaGharani ☕ ... ahhhhh
@stephenstange4194
3 жыл бұрын
Custom formatting for sure. I never realized you could separate +;-;0;text... Thanks for the great video!
@palomarodriguez3308
3 жыл бұрын
That last tip was especially in useful 🙏
@XRelabs
3 жыл бұрын
i wish my students watch your channel very informative and helpful thank you.
@wayneedmondson1065
3 жыл бұрын
Hi Leila. Great tips! Thanks for sharing :)) Thumbs up!!
@LeilaGharani
3 жыл бұрын
Thanks for watching, Wayne!
@velonino2691
3 жыл бұрын
Custom format is awesome
@tifouraamina1761
3 жыл бұрын
Thanks Leila 🤗
@slickeno4117
Жыл бұрын
You are amazing thank you!
@cgswm92
2 жыл бұрын
Thank you so much !!!!
@pvv_live2226
3 жыл бұрын
Using cell formatting is a cool solution, Leila!
@iam_nick
2 жыл бұрын
As per usual, you are awesome
@master_jk6509
3 жыл бұрын
Thanks a lot. I am jayalath, I'm from Sri Lanka. I follow your KZitem Channel and I get more knowledge from your channel Thanks
@LeilaGharani
3 жыл бұрын
Thanks and welcome!
@sahnounf2883
3 жыл бұрын
I learn a lot from your videos much appreciated ❤️👍
@m.mahesh2155
3 жыл бұрын
I love this ❣️❣️❣️ video...tqqqqqq
@olipeeeeee
3 жыл бұрын
as always a good Video. 🙏 I prefer custom formating.
@timhogan6710
3 жыл бұрын
I like the formatting way best.
@glennfelpel9785
3 жыл бұрын
This deserves about 10 thumbs up Thank you
@LeilaGharani
3 жыл бұрын
My pleasure 😊
@CarlosRodriguez-hb3vq
3 жыл бұрын
Brilliant!
@nicholasmanyunga7911
3 жыл бұрын
I like the custom format best.
@edilpoulina1870
3 жыл бұрын
GR! I used to use the Custom format to add a valuta that didn't exist in Excel before, to numbers, e.g. ANG (Antillean Guilder) and COP (Colombian Peso).
@userjjjuuigjtuigghfhg
3 жыл бұрын
Very Nice ... thank you
@taizoondean689
3 жыл бұрын
Thanks Ma'am 🙏
@abaasanji4979
3 жыл бұрын
Thanks Leila I always use the concatenate function, but rarely used flash fill since it is not Dynamic. I just added 2 more methods to my skills. You're the best.
@josephcoon5809
3 жыл бұрын
😂 Concatenate has been a mainstay for many of my more complicated formatting needs, but now I’m excited to see how the & method works. Do you use CHAR(10) for adding line feeds in your CONCATENATE functions? If you turn on Wrap Text, it will activate the line feed wherever you place the CHAR(10).
@mdmajharishere
3 жыл бұрын
*Happy Teachers Days.* *Ma'am.*
@kuldeep.3012
3 жыл бұрын
As always, I just appraise you. Thanks my dear Teacher
@munnasengar35
Жыл бұрын
Thank you very much mam this is very helo full for me today.
@sanupvaidya
3 жыл бұрын
I use custom formatting many times. But whatI learned new is ; separator for different types, that's kinda cool.
@sriramamurty1298
3 жыл бұрын
Great....thanks a lot
@EXCELwaleBhaiyyaa
3 жыл бұрын
3rd Method is Super Awesome.. 1st and 2nd was also good.. Beautifully explained.. @Leila Gharani
@LeilaGharani
3 жыл бұрын
Thanks a lot 😊
@mowburnt
3 жыл бұрын
Using "Indirect" and "Concatenate" together is one of my favourites in formulaes :-)
@LeilaGharani
3 жыл бұрын
👍
@chamindabasnayake4844
3 жыл бұрын
Thanks Leila, third method is very cool. CONCATENATE formula could be another option.
Пікірлер: 296