Unusual use of Excel's Custom Number Formatting with Conditions & Symbols (Part 2)
HTML-код
- Опубликовано: 14 июл 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Enhance your Excel skills with this in-depth tutorial on advanced custom formatting techniques. Ideal for Excel users seeking to create visually impactful and informative spreadsheets without relying on conditional formatting.
⬇️ Download the workbook here: pages.xelplus.com/custom-form...
🌟 Key Learning Points:
- Understanding Custom Formatting: Learn the basics of custom formatting, including accessing it through the right-click menu and Format Cells option.
- Symbol-Based Deviation Indicators: Discover how to use custom formatting to display symbols (like arrows) to indicate data deviations, complete with color control.
- Font Tricks for Symbols: Explore using different fonts like Wingdings or Webdings to represent data symbols in Excel.
- Conditional Custom Formatting: Understand how to add conditions to custom formatting, allowing you to highlight significant data deviations.
- UniChar Formula for Symbols: Learn about the UniChar formula for accessing a wide range of symbols compatible with Excel's default font.
- Threshold-Based Formatting: See how to create custom formatting rules that only highlight data deviations beyond a certain threshold.
🚀 Practical Applications:
- Visually represent data changes or deviations with symbols and colors in Excel reports.
- Apply custom formatting to enhance data readability and interpretation without conditional formatting.
- Create dynamic Excel tables that automatically adjust formatting based on predefined rules.
Method #3: Use any symbol of your choice to show deviations and conditionally format these using custom formatting
Method #4: Use custom formatting to only show higher or lower deviations, i.e. use thresholds in custom formatting. The general custom formatting syntax that most are familiar with, is to format positive, negative, zero and text values. But you can also use custom formatting to format higher, lower and medium boundaries. In this video you'll see how that works.
For part 1 watch: • Four SMART Ways to use...
For part 3 watch: • Highlight Deviations w... - you'll find out how you can account for 4 conditional thresholds.
For symbols with conditional formatting watch: • Excel Conditional Form...
★ My Online Excel Courses ► courses.xelplus.com/
➡️ 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-part2-file
Such an amazing and useful feature, I've learnt so much from your videos. The way you explain and everything is awesome. You are the best Leila.
Thanks again for such an great effort.
Very useful. Thank you so much Leida for your time, effort and great energy!
Not just commenting on this video, but all I've seen so far. My understanding of excel has shot up a huge amount. I've learnt so much. Thank you.
Much appreciated Leila. Thank you very much for new ideas
Thank you so much. I've always wondered how Custom Formatting is done. Wow!
Thank you for bringing this functionality to my attention. You explained it very well and I learned something I had never thought of before. Excellent Job!
My pleasure. I'm glad the tutorial is useful for you.
I just used this custom formatting to my reports to my clients and to my manager. They were all impressed! Thank you!
That's great Jun!
Ooopppsss Custom Formatting
This is amazing! Thank you for sharing Leila. Must say, the way you explain is very easy to understand! You make learning interesting. Thank you.
My pleasure. Glad you find the tutorial helpful.
Liked the video even before I watched it! That's the kind of confidence your contents have won!
Really a Good Guidance
Your videos are often surprising and illuminating. I have recently discovered your channel and keep stumbling onto features I never knew existed.
What's more, your teaching style is clear and efficient. You're a natural. Thank you for taking the time to share all this. You deserve a million+ subscribers.
Wow, thank you!
This is just one of those videos from Leila that have taught me how to use Excel in unusual but extremely useful ways.
Ways that will make your colleagues and managers ask "HOW. DID YOU. DO THAT????". This is such an obscure, yet neat way of formatting your Excel reports
It's the video that made me decide to join the channel, because I hope that Leila will keep these tips coming.
Thanks for this gem, Leila!
Glad it was helpful! Custom formatting is such an underrated feature. Now go show them managers! 😁
Glad you found it helpful!
@@LeilaGharani
Yup - I used it to create a timeline to communicate to a colleague when and how I contacted customers via our CRM...
- "positive values" = "I sent the customer an email" (formatted as "@"),
- "negative values" = "I sent the customer an SMS/MMS/WhatsApp" (formatted as UTC character 📱) indicating a text message,
- "0" = "I sent the customer SnailMail / a paper letter" (formatted as UTC character "✉")
- "text" = currently unused - spare functionality for the future
Having information about the "read" status of an email, I was able to combine custom formatting (UTC symbol for the communication channel) with conditional formatting for the message (colors red = "unread" / green = "read", using "+1", "+2", etc.). Combining this with PowerQuery (linking to data in Salesforce), I can update a timeline with up-to-date communication information in a very compact, pictogram-based overview just by pressing the "Refresh All" button.
Very, very cool! Thanks again!
Thanks for taking us to an unexplored area in excel with so much utilities available in custom formatting...would be amply useful when implementing a user-defined colours or symbols
So true. It's an underrated functionality that can be really helpful.
Thank you 👍🏼! Very unique learning.
Great lesson, here, Leila! I can see a lot of cases where I'll be replacing conditional formatting with custom formatting, i.e. in tables. Thanks, again, for all of the helpful content on your channel.
You're very welcome Scott. Custom formatting can be a powerful alternative.
Great lesson, here, Leila! just arrive for this lesson after 6 years from share this video on RUclips but here am I arrived and enjoy with this custom formatting lesson.
Thanks, again, for all of the helpful content on your channel
Wonderful! Glad you found it helpful.
i barely knew anything about this and now with part 1 and 2 videos i'm way more knowledgable!
Can you help me on this? I didn't get it :(
I used custom formatting for the first time the other day. It works so seamlessly! Thank you!
That's great! You're very welcome Josiah.
Leila, you continue to reveal the deep mysteries of Excel! And custom formatting is a dark alley that I have feared to enter - until now, that is. 🤣
Also, I viewed another video of yours this evening to give me just enough information about conditional formatting to avoid having to format words based on values in other cells. I had to add a little guess work to what I saw, but was happily successful. I look forward to work tomorrow!
Thank you. LG = Life's Good.
Glad the videos are helpful 😊
Amazing Video . Thanks for sharing . Will start practicing it at work in place of conditional formatting
You have made me into an excel ninja at work!
Thank you and God bless you.
That's awesome! You're the one who's done the work though - hats off to you!
First of all, congrats on your channel. Great work!
One very useful feature which comes handy whenever you need to copy more than one symbol, number or even part of text, is the combination of the windows key + c to copy each one and then to use the windows key + v to choose from the clipboard which one(s) to paste in the cell.
One of the best teachers I come across
Thank you so much - especially for the unichar piece of information ...
Thank you. These two videos were very useful.
😍
Amazing! I didn't know about adding conditions in there. 🔥🔥🔥🔥
Thanks Oz. It's a nice feature. I'd read about it once somewhere but I completely forgot about it until I came to conditionally format the symbols...
Excellent tool for reporting financial indexes. Thanks Leila.
You're very welcome Flavio.
Wow, I use conditional format before. Now I learn custom format. Thank you, Excel Queen👸!
You're very welcome. I'm glad you find the tutorials useful :)
That was awesome! Thank you! Please keep such videos coming!👍👍
Glad you like it. Will do my best : )
Just what I needed for a spreadsheet that is really slow due to the masses of conditional formatting! It will be interesting to see how performance improves when I replace it all with custom formatting!
conditional formatting rocks! i love all your videos Leila
You're very welcome.
Thanks for the tips ! I'd forgotten the [color#] conditions, & didn't know how to do the thresholds. The speed difference between custom formatting and conditional was enlightening.
An alternative to the copying the symbols is (using numeric keypad) ALT+30 for the positive variance and ALT+31 for the negative variance. This can be quicker method
Nice!
I didn't know that.
Thank you and have a nice day.
I was looking to improve my excel skills and the stetics at the same time. Im glad i found your chanel. greetings from Dominican Republic
I'm glad to hear that Luis! Thanks for your comment :)
Smart solution! Thank you for the tip!
You're welcome Marcus.
I never knew we cud even do that in custom formatting. That's awesome. Thank you so much.
You're very welcome. I also didn't know for the longest time :)
Very Useful and interesting tutorial. Once again thanks
This is fantastic!!!
Thanks Leila, excellent learnt to express in a different way.
you're welcome Srinivas.
Very useful feature which I was not knowing early. Thanks a lot.
Eres la mejor!!
I love this. I wish you could label these with names regular uses could read.
Well planned & beautiful presentation on Custom Formatting
Thank you! Cheers!
EXCELLENT INFORMATION!
Leila, great indeed .congratulations
Thank you very much Leila
Amazing. Thank you. And your voice is very soothing. Thank you.
So nice of you
Custom formatting was something i used to avoid as i had been thinking hard to crack....but not any more after watching this video. Thanks a lot for sharing this and also i do watch all of your other videos which are so informative. Keep up the good work!
Great to hear!
Super excellent video. Thank you very very much. I consider you as my guide for Excel.
I'm honored. Glad you like the video.
Bem legal, obrigado.
Simply explaining complicated formula, awesome. Thanks for such useful video.
Most welcome, Vijay!
Amazing.. never knew one could put condition in custom formatting.
Great and interesting lesson, I was just interested in coloring the third (middle) part of condition with yellow for exp.
This is amazing!!!
There are some limitations with certain symbols, and also there are certain complications when applying conditional formatting or conditional formulas on the symbols of custom formatted cells (if they are not just used as visuals but as part of formulas that create dependencies and have precedents). This is a fantastic video. I've designed an ancient calendar with heliolunar concordance to modern calendars including all solar and lunar eclipses in addition to ancient cycles, which don't limit themselves to the excel 1900s date limit, and I had been using on my own some of these techniques you go over in a rather more polished manner than my trial and error discovery, but I did so mostly with conditional formatting. I think the possible advantage of custom formatting is that I might be able to reference the resulting custom-formatted cell. Great presentation. Thank you so much for putting this out there
Wow, that sounds complicated. Glad the video was helpful.
Great Video Leila, really clever, as always. I'll add the link to this video (along with other 1st one) to the reference sheet in the custom format file. I think these will be a great addition to the file.
Thank you Michael. That sounds great.
Masterclass ... as usuall :) a lot of Excel knowledge takan from your channel. Thank you for that.
One tip: to copy formula (or value) from the first cell of selected cells, it can be used simple Ctrl+D shortcut instead of F2 and Ctrl+Enter combination
Awesome ...... wow ..... reach mind and poor mind every body understand ......... your presentation is unique........
Thank you so much 😀
Thank you very much. You are just an Angel.
You're very welcome Aldric! Thank you for the kind words.
Excellent....Thanks
Thank you so much
loved it ,I always learn something useful from ur video,thank u
Very happy to hear that Vikas :) You're welcome.
hello, c'est magnifique, bravo
just applied the the custom formating concept with a smiley face and it worked........got u well!!!
That's great. Happy face is always good :)
You are my hero. That is all.
Leila - Thank You for some very valuable information. Have enjoyed all your very well thought out and very professional videos. Will continue to watch as always, good tips and I have shared your information to my other students and employees 👍👌😎
Thank you so much for your support and spreading the word David. I really appreciate that. I am also very happy to hear you find the content useful for your work!
Thanks Leila, another great video! As a heads-up, the Unichar function only works in Excel 2013 and newer versions so older versions still have to rely on finding the desired symbols within the various font sets.
You're very welcome Robert. Yes - you're right. I forgot about that actually. Thanks for posting.
Yeah that really helpful, Thanks for Making this kind of Video.Keep Making Like this. Thank You Very Much Leila..
My pleasure. Glad it's helpful.
Simply Awesome. Thanks for sharing.
Excel is such a powerful tool, but I guess we use only 5% of what’s available.
great video
Mind Blowing video I like your all video
Thank you. Glad you like them.
Great technique for Dashboard
Mam you are such an amazing teacher of excel...i have searched so many videos on excel by different people...but your style way of teaching plus way of presentaing information is best of all...Mam i just want to know have you upload videos on excel financial modelling or VBA too???
Thank you Ubaid. Appreciate the kind words. Not yet on VBA, but I am in the process of creating a VBA course and will be uploading VBA related videos soon. Anything special you're looking for?
Actually u have solved all my excel issues man...eveyday i watch 2 or 3 videos of yours beleive me mam my boss is just flatterns on me he is so impressed now with me...desperately waiting for your vba videos mam....thank you for such a good instructor...
You are really an Excel guru!!! Admire your skill, gonna to watch all your videos, hope my skill as good as yours one day, :-)
Wow, thank you for the kind words Carol. It's great you are determined to learn. We have that in common :)
congratulations you teach very well
Att, Gledson Programador Excel VBA
Thank you for the kind words.
very good
Very nice leila Thanks you
Glad you like it :)
Thanks
I like all your videos
Thank you :)
Thanks...
Great!!
Great teacher,,,, Where were you when I was at school !
that's Great 👍 thankyou Leila Mam
You are welcome 😊
superb plz make a video more if condition Indian projects base plz
You are best
That was great
Glad you like it.
🙏Thank You🙏
Amazing :)
Glad you like it Zaigham :)
Awsome
Very very impressive. Is it possible to also change the interior color of the cell using custom formatting ?
i am glad to join with you happy to see you
Great to have you here.
The video is super, can you please explain how to do if i need show greater than or lesser than between two numbers using same custom formatting, it would be great if you teach this also. Thanks
thank u
You're welcome tally guru.
Thanks a lot Leila. This is indeed helpful. But I was wondering how did you ensure that the decimals would go up to only 2 places? I am getting the entire range of decimals!
nice very nice
Hello! I'm appreciating your videos! It's cool! I'm trying to format using true and false in the square braquets but I can't. Do you know if it is possible?
👍👍👍 and the German formular for unichar is unizeichen.
Hi, thanks for this - as always - very interesting and well explained course. I have one additional question; Once i have setup my custom formats, is there a way to have them available in all workbooks i open? Currently they only appear in the workbooks where i have actually setup the custom formatting. I see i can save them into the template or save them as a macro, but is there an easy way to have them also for any workbook i open on my pc?
When formatting the percentage and the arrow in the same column, is there a way to keep the number black while the arrow stays green/red?
Thanks for making this video, custom formatting has saved me a lot of time at work! Much appreciated!
Really interesting thanks for sharing. i have one question, can we update a excel file data from master excel file without accessing or opening the master file