Four SMART Ways to use Custom Formatting instead of Conditional Formatting in Excel - Part 1
Вставка
- Опубліковано 15 лип 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Learn why custom formatting can be a superior choice over conditional formatting, especially when dealing with large datasets.
⬇️ Download the workbook here: pages.xelplus.com/custom-form...
Custom formatting is really easy to use once you understand the rule behind it. In this video, I show you a simple way of showing variances using symbols - up/down arrows or any symbol of your choice - with Excel's custom formatting.
🔍 What's Inside:
▪️ Understanding Custom vs. Conditional Formatting: We begin by defining these two key Excel features and their applications.
▪️ Speed and Efficiency: Discover how custom formatting can be up to three times faster than conditional formatting with a practical test on a 40,000-line dataset.
▪️ Step-by-Step Guide: We walk you through four versatile ways to use custom formatting instead of conditional formatting, focusing on the first two methods in this video.
▪️ Sneak Peek: Stay tuned for the next video where we'll reveal how to use custom symbols for deviations and add thresholds to your deviations using custom formatting.
▪️ Simple Logic Explained: Learn the basic rules behind custom formatting, including how to format positive, negative, and zero values, as well as text.
00:00 Why Use Excel Custom Formatting over Conditional Formatting
04:02 Basics of Excel Custom Formatting
08:05 Up/Down Arrows for Deviations with Excel Custom Formatting
12:32 Up/Down Arrows and Percentage Deviations with Excel Custom Formatting
For Part 2 watch: • Unusual use of Excel's...
We normally use custom formatting to show numbers with thousand separator, as percentage or even to show green for positive and red for negative values. Basically to format the numbers in our reports. But we hardly ever use custom formatting to show deviations with symbols. Or to disguise our numbers to look like symbols instead. The trick is super simple.
Why would you want to use custom instead of conditional formatting?
1. Your files will process faster.
2. It's super easy to setup!
This video belongs to a 2 part series. This is part 1 of 2. In the next video, I will show you how you can add thresholds to your deviations using custom formatting.
VBA code from Excel MVP Charles Williams to time your formulas is included here: bit.ly/2q1fpSK
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/custom-formatting-file
Hi Leila for some reason I am not able to download this file. I got the email but when I click on download I am unable to get the file. I tried using two different emails. Can you help please? Thank you
I have been teaching Excel for many years but when I saw your videos I really impressed your teaching style. The way you instruct is really awesome.
Thank you Yasir for the kind words.
Wow Lelia,
I always used to wonder the logic of the custom formatting. With this tutorial you have made it so easy to understand. You are very correct, this custom formatting is awesome, less time consuming and less complicated compared to conditional formatting. Time and again to say, I am a great fan of the way you explain complex excel concepts... GREAT JOB DEAR...
Thank you so much Sachin. Agree - custom formatting is indeed a very powerful feature. In the next video, we'll take a step further and look at how we can get custom formatting to handle thresholds, like conditional formatting can :)
Sure looking forward to it...
"Commnent id"
THANKS !!!, me encantó, LOVE IT!
A short cut for getting the up triangle and down triangle is, for UP - ALT+30 (on numeric keypad), for down ALT+31 (on numeric keypad). This is quicker and easier to remember than inserting symbols
Thanks so much for this - my Symbols dialog was playing up so could only access normal arrows 🎉
Remember she was teaching syntax, so in this example and ease of understanding without it becoming to overwhelmingly or confusing it was better to use insert symbols. However after the meaning concept was achieve using keyboards would be ideal 🎉
thanks
Wow. I've been curious about custom formatting for a while, AND I've recently been banging my way through conditional formatting. You've literally just saved me hours of work, and opened up a whole new function of Excel that I didn't know existed!
Glad I could help!
This blew my mind. I've been using a combination of conditional formatting and the TEXT function and concatenating strings and all this time I could have simply used custom format!! OMG. Thank you so much. You're awesome.
You're very welcome Luciano! Glad you find this useful.
I can’t believe I’m sitting here at home on my own time watching a video about Excel that makes me excited to get to work and see what I can do with Custom Formatting - thank you for a clear explanation, I had never really investigated the power of this tool in the past and can’t wait to see what I can do with it!
Same here 🙌
THANK YOU for being the person to finally De-Mystify the Custom Format options in Excel. For years I have been a little intimidated by how this actually works, but YOU explained it perfectly and in a very useful & time efficient manner. Great job!
Glad it was helpful! Thank you for the nice feedback.
Thank you Leila for not only training us in a lucid and a fun manner but also keep trying to update us with the latest tricks and Techniques.I am in real continuously being benifited to be the part of your course @ Udemy.
Thank you Munendra for your support! I'm very glad to hear you find the content useful and easy to follow.
Thank You Leila. You are researching every aspect of MS Excel. Hats off to you.
You are a genius. Words cannot express my gratitude. Much appreciated.
This is so a pleasure to learn from you: first you find topic that is real painful, and with a nice voice, nice pace, and great pedagogy you walk us through.
Thank you very much for this gift.
This is typically something we need in a hurry and don’t know how and why ;-)
Thanks, like a lot of others I was sort of aware that custom formatting existed but hadn't played round with it. For some time I've been wanting the '-' to show up for zero values when using the currency symbol with negatives in red, to hide zero value to a certain degree. Now I have the answer!!
Thank you! I needed to add "+" and "-" in front of some numbers and this was a BIG help!
Explaining the basic rules of custom formatting along with the color coding is simply outstanding!
Glad you like it!
I just started a new job as a financial manager and your video are a tremendous help. There are lots of teachers out there but your the best hands down. Thank you!
Wow, thank you. Good luck with the new job!
I have used excel for 25yrs and you are showing me simple things like the use of the * in custom formatting that I was never aware of.
Bravo!
I'm glad you find them useful :)
The way you speak and clearly say each point and logic......is brilliant.
=If(Now()>0,"Leila is best","Earth has stopped rotating")
😘
Genuinely interesting video. I had never realised you could use custom formatting in such a way.
You also delivered the information in such a clear and precise manner that it was simple to follow and understand.
I haven’t tried this out yet but I will be interested to see if the language specific elements differentiate between American English and English English, i.e. Color or Colour.
Thank you so much Leila. Your tutorial helped me create my Stock Portfolio and Tracker on Excel. Trust me those triangles look super cool on spreadsheet.
It is simple but very useful in any important presentation. From those small and simple it is helping lectures to influence audiences. Thanks a lot!
Thank you for sharing this. It really means a lot in my office work.
You have explained so well Leila! Thank you so much! My excel skill is quite basic and I will continue to watch all the videos that you made and learned them all! Looking forward to seeing more videos from you! You are such a great teacher.
It's great you are dedicated to learning new things. I'm glad to have you here.
Thank you! I've been using Excel since first version and use format cell but used conditional formatting because I never took the time to learn custom format. This just saved performance of a large file I'm working on with lots of formatting.
Great to hear Shawn!
Finally a great explanation about custom formatting! Many thanks for posting helpful tutorials!!
You have the best didactic of all excel teachers !! Tks for sharing these excellent tricks, Leila !
ohhhh thank you Maique & you're very welcome.
this is a fantastic video, I was searching for a proper custom formatting teaching from years... thanks a ton.. :)
Glad you find this useful! You're very welcome :)
Leila Gharani many thanks
Very well explained with a clear voice. Thank you.
Nicely presented Leila, thank you! I am now able to replace Conditional formatting with Custom formatting, so now my monster workbook looks and performs so much better!
Very happy to hear that Bob :)
Haha love that initial comment about getting scared of the formatting and pasting stuff from Google. I had no idea custom formatting was so logical till I saw this video. Thanks heaps!
Thank you Leila for these grand tutorials. You are a treasure.
I have been using Excel for years and somehow never came across this channel, it's simply amazing.
i'm loving this channel every single day !!!!! exactly what I was looking for and the execution from Leila is right on target. Thank you
Awesome! Thank you!
Fantastic delivery of material. I found your tutorials very helpful. Thank you!
wow -- very nice. i had viewed ~8 vids, avoiding this one with a 'pretty face. turns out, u provided the best content, rhythm in explaining the content, and u explain both, basic and complex processes within the lesson. then i explored the library of great content and viewed your story -- triple wow. great job Leila -- thank you.
Thank you Peter for your kind comment. I'm glad you decided to click!
this helped me out immensely. thank you!
After many many years of working with Excel I finally understand the idea of custom formatting. Thank you!!!
Great to hear Henric!
Simply you are AWESOME, I love Excel and watched many videos but this is something really cool.
Thank you for an excellent tutorial, it was great and easy to follow.
THANKS SO MUCH LEILA. I LOVE YOUR VIDEOS. ITS SO EDUCATING
I'VE BEEN SUBSCRIBED TO UR CHANNEL FOR OVER A YEAR NOW
You have the perfect way of teaching training. Love the small bites of information.
I did know how to custom format positive and negative values from an infographic tutorial of yours. But this is beyond my imagination. Thank you
Thanks Leila. you are a champ. making a great difference in people's working life.
Number 2 fit my needs perfectly. Thank you, Leila!
Hello Leila; Thanks very much. I'm enjoying tutorials you provide through your channel. Thanks very much.
Leila, I was looking for this IF formula and your style just rocks ... solved my headache as simple as it sounds ))) 🔥
My respected virtual teacher! The logic of custom formatting always remained a mystery for me, but this video revealed this secret now. Thank you so much. May you live long on youtube to share knowledge for spreadsheet lovers.
You're very welcome!
Thank you! I have not used Conditional Formatting this way... very refreshing way to use it.
You're welcome. Glad you find it useful.
Thank you Leila. Simple, yet practical tid bits.
you are one of the greatest teachers.
wow!~ so simple yet powerful formatting. Really learnt from you. Thanks.
And I must tell you that your voice is so sweet and clear, so comfortable to listen to.
Thank you again👍
You're very welcome. Glad you find the tutorial helpful.
Super Video... LG...great explanation. I have seen "n" number of excel videos in UA-cam, among all you are the best teacher for my me.
Amazing information demonstrated with clarity. Thanks so much.
Dear Miss Gharani,
your lessons are truly EXCEL-lent. Thank you so much for sharing your knowledge with such clarity and generosity.
Thank you Alberto for your comment! I'm very glad you find the content useful. I also learn a lot from the Excel online community here so everyone wins : )
Love your videos Leila. Thank you. Keep it up!!!
Thank you so much for your time and effort
I really appreciate your input on all of your videos. I would like you to make a suggestion, regarding the topic, how could you determine the color of the text or the font (number or code or RGB) of a text or font with custom or conditional formatting? Thanks in advance
brilliant, just brilliant use of custom formatting....
Absolutely brilliant, Thank you
love the feature and your explaining style
Thumbs up from NL,EU very helpful. clear and calm voice. keep on the good work
🤯🤯🤯 Woooow! I've always wondered what those codes meant. And this is super cool! Super easy to understand. Thanks thanks!
It's only last week I've found your channel. And I've been binging since! I've also recommended it to my colleagues 🥰
I must say it is great to learn something new about excel everyday - thanks to you this is really possible!
I'm happy to hear that Srecko!
Thanks for your tutorials! I always learn something new with you. God bless you
Thanks for watching and for your support Dom :)
I don't know if I am falling in love with Excel or those big brown eyes 😍. Thanks for the video.
Watching more custom videos during COVID! thanks for the refresher video!
Thanks for your videos!!
Beautifully done. The revelation of Excel beauty. Thank you Leila Gharani.
Glad you like it. You're very welcome Peter.
Thanks Leila, VERY USEFUL VIDEO
This was brilliant and just what I needed for work. Thank you so very much! You are the best Leila!
You're so welcome!
Hey,
I have been working with excel for 14+ years. What I came to know today, I have never thought of ...
Great tricks and thanks for the tips. ..
I also come across new stuff in Excel all the time... the fun doesn't end :)
Valuable tip. Thanks for your sharing your knowledge and expertise
Hi Leila - this is super helpful! ...really appreciate your dedication to helping Excel users. R
Awesome trick, really was wondering what those hash in custom formatting...cool stuff.
So interesting and very clearly explained; thank you.
I had taught Excel for years, and knew about the power of custom formatting; but I had only taught it as useful for adding text to numbers, such as "sf" for square foot or "/mth" for per month. I was honestly not thinking I would get a much of a lesson with this video, BUT. I feel like I've been driving my Ferrari custom formatting without every getting out of first gear....I've just enjoyed a thrilling joy-ride with someone who knows how to take that Ferrari and run it thru the gears. Thanks!
Glad you found something useful, Dan!
Hi Leila,
Your videos and explanations are very nice. Thank you!
Keep going! A.
Leila, long time user of excel , considered expert by my coworkers…. You tips are excellent. See my question about time
Thanks Leila, this was very helpful
wow...never knew that custom formatting could be this effective . and thanks for some good tips .cheers
Excellent video!
this is absolute class, thank you
Very cool! Always there is smth you do not know. Thank you very very much)
Excellent, this is very useful. Thanks
Thanks for the wonderful video.
YOU ARE AMAZING!!! You save my work! Tks a lot!
Glad to hear that!
i did it and its amazing. thanks you so much .
This is a great and very useful for your everyday work,
I have been looking for these trick with colors. Thank you so much Leila.
Glad you finally found it :)
Thanks for your sharing. The content is very useful.
At 11:08 you say "Conditional formatting" when you meant to say "Custom formatting "... excellent video
Awesome video ... Thank you for making and sharing it ☺
Awesome tutorial as usual....
Very very useful function! thank you.
Stunning! Awesome !! Thank you Leila.
You're welcome NC.
Impressive how easy you explain it!
I wanted to thank you and mention for other viewers that I immediately discovered this also works well with emoji. Win+. (period) brings up the emoji keyboard. Because sometimes a facepalm is the correct symbol.
I've been using Excel over 20 years used custom formatting a lot, but never had a clue you could do so much with it.
There's always more to learn :)
Thank you Leila. This is amazing!