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!
@@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!
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.
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
Great lesson, here, Leila! just arrive for this lesson after 6 years from share this video on UA-cam but here am I arrived and enjoy with this custom formatting lesson. Thanks, again, for all of the helpful content on your channel
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.
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.
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
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!
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...
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
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!
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!
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
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.
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.
BTW, you can use two positive thresholds such as 50 and 100. The extra formula isn't necessary. This may be a new feature since the video came out but it works for me in Microsoft 365.
There is an ancient program in windows, called "charmap", but isn't included in the menu anymore. But it is still there. Select run, then type charmap. It will show the unicode values of the fonts too.
@@LeilaGharani Thank you for the great lesson. I actually knew custom formatting quite well, but I didn't have the idea of using it as icon set. I struggled with the iconsets in conditional formatting because of not having a exact choice of a set that I needed. But it didn't get into my mind that it could also be solved with custom formatting, and it is even easier that way. This video actually gave me a mindset that custom formatting is a good tool when you need 4 format choices of some result, without the need of nesting 4 IF formulas to achieve a similar result, or use conditional formatting (which is pain, as they sooner or latter got split after some insertions in the range).
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.
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!
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.
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!
Thanks for very interesting lection . I have a crazy question i can't find a solution . It is possible use custom formats based on the condition (value) of another cell ? Without any formula or conditional formatting . The custom system often is more useful than formulas , avoiding circular references etc ))) Thanks again 🌿
This is straight forward, do you have a similar "trick" for a profit and loss statement where for example if an expense goes down month to month the arrow should show a down arrow but colored green and vice versa? All this should be in one formula, I don't want to have to go and add a formula/formatting for Revenues and a different one for expenses. Thanks for your content
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
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
1. Can we use custom formatting in query table. 2. Can we use custom formatting with condition value cell in sheet other than that we are formatting. Thanks in advance for reply, regards
How can I do custom formatting to turn symbols "▲" or "▼" red or green based on results from a formula on that cell? I'm using the IF function to return those symbols as explained in another video.
How to apply custom formatting if I have thresholds like...it should be up green arrow if greater than 20, neutral arrow if the value is between and equal to 10 and 20, and down red arrow if value is less than 10?
Hey, Great video! I would like to ask you how can i write on the type the square root or 3 in the cube like a superscript. I would appreciate it. Thanks!!!
Great videos Leila, Very helpful but still couldn't find any solution for my problem. I have Column "B" which has already a Data Validation For Times those I wanted(1:00, 3:00, 4:30, 5:00, 6:15, 7:50, 10:20, 12:10, 14:20, 16:10, 18:10, 20:40, 23:00) User must choose between those times. How can I make the Column "B" accept only times between now and the next 3 Days. Not before and not after. very
![АЛАУДИНОВ у Скабеевой: эти их ВСУ нас НЕ ДОГОНЯТ 😁 [Пародия]](http://i.ytimg.com/vi/an0anqU4WGQ/mqdefault.jpg)
Grab the file I used in the video from here 👉 pages.xelplus.com/custom-formatting-part2-file
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!
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!
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.
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
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.
Great lesson, here, Leila! just arrive for this lesson after 6 years from share this video on UA-cam 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.
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 😊
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.
One of the best teachers I come across
Liked the video even before I watched it! That's the kind of confidence your contents have won!
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.
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!
Awesome ...... wow ..... reach mind and poor mind every body understand ......... your presentation is unique........
Thank you so much 😀
Great and interesting lesson, I was just interested in coloring the third (middle) part of condition with yellow for exp.
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...
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 :)
Really a Good Guidance
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
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.
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 :)
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!
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.
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.
Simply explaining complicated formula, awesome. Thanks for such useful video.
Most welcome, Vijay!
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 :)
Well planned & beautiful presentation on Custom Formatting
Thank you! Cheers!
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.
Excellent tool for reporting financial indexes. Thanks Leila.
You're very welcome Flavio.
Very useful feature which I was not knowing early. Thanks a lot.
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.
Amazing.. never knew one could put condition in custom formatting.
Amazing. Thank you. And your voice is very soothing. Thank you.
So nice of you
Thank you. These two videos were very useful.
😍
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 :(
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!
Leila, great indeed .congratulations
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
I love this. I wish you could label these with names regular uses could read.
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.
conditional formatting rocks! i love all your videos Leila
You're very welcome.
You are my hero. That is all.
Eres la mejor!!
BTW, you can use two positive thresholds such as 50 and 100. The extra formula isn't necessary. This may be a new feature since the video came out but it works for me in Microsoft 365.
Super excellent video. Thank you very very much. I consider you as my guide for Excel.
I'm honored. Glad you like the video.
Great technique for Dashboard
There is an ancient program in windows, called "charmap", but isn't included in the menu anymore. But it is still there. Select run, then type charmap. It will show the unicode values of the fonts too.
I didn't know that. Thanks for sharing, Gábor!
@@LeilaGharani Thank you for the great lesson. I actually knew custom formatting quite well, but I didn't have the idea of using it as icon set. I struggled with the iconsets in conditional formatting because of not having a exact choice of a set that I needed. But it didn't get into my mind that it could also be solved with custom formatting, and it is even easier that way.
This video actually gave me a mindset that custom formatting is a good tool when you need 4 format choices of some result, without the need of nesting 4 IF formulas to achieve a similar result, or use conditional formatting (which is pain, as they sooner or latter got split after some insertions in the range).
Simply Awesome. Thanks for sharing.
Excel is such a powerful tool, but I guess we use only 5% of what’s available.
Thank you very much Leila
Thank you so much. I've always wondered how Custom Formatting is done. Wow!
Thanks Leila, excellent learnt to express in a different way.
you're welcome Srinivas.
This is amazing!!!
Very useful. Thank you so much Leida for your time, effort and great energy!
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.
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!
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.
Thank you 👍🏼! Very unique learning.
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!
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 :)
Much appreciated Leila. Thank you very much for new ideas
that's Great 👍 thankyou Leila Mam
You are welcome 😊
I like all your videos
Thank you :)
Excellent....Thanks
Great info. But please tell me there's a way of coloring only the symbol and not the number if custom formatting a cell to show symbol & numbers
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...
Thank you so much - especially for the unichar piece of information ...
At 12:22 - can we show the numbers as % instead of decimals? Looks like its not possible
Thank you very much. You are just an Angel.
You're very welcome Aldric! Thank you for the kind words.
Try the "Windows key" +"V" to paste, this will allow you to pin things that you use a lot to the clip board so it is always available
Windows + V is great! If you're interested in more Windows tips you may want to check this out: ua-cam.com/video/do06MPWf9E4/v-deo.html
Thanks for very interesting lection . I have a crazy question i can't find a solution . It is possible use custom formats based on the condition (value) of another cell ? Without any formula or conditional formatting . The custom system often is more useful than formulas , avoiding circular references etc ))) Thanks again 🌿
This is straight forward, do you have a similar "trick" for a profit and loss statement where for example if an expense goes down month to month the arrow should show a down arrow but colored green and vice versa? All this should be in one formula, I don't want to have to go and add a formula/formatting for Revenues and a different one for expenses. Thanks for your content
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
great video
That was awesome! Thank you! Please keep such videos coming!👍👍
Glad you like it. Will do my best : )
EXCELLENT INFORMATION!
loved it ,I always learn something useful from ur video,thank u
Very happy to hear that Vikas :) You're welcome.
Smart solution! Thank you for the tip!
You're welcome Marcus.
Thank you so much
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
congratulations you teach very well
Att, Gledson Programador Excel VBA
Thank you for the kind words.
Very very impressive. Is it possible to also change the interior color of the cell using custom formatting ?
1. Can we use custom formatting in query table.
2. Can we use custom formatting with condition value cell in sheet other than that we are formatting.
Thanks in advance for reply, regards
Great teacher,,,, Where were you when I was at school !
To copy a formula down it's easier to do CTRL+D. That is fill down.
True - but Control + D copies the formatting with...
mam great teaching. I can not copy the symbol in custom formatting window, please help
superb plz make a video more if condition Indian projects base plz
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.
👍👍👍 and the German formular for unichar is unizeichen.
Thanks
very good
How can I do custom formatting to turn symbols "▲" or "▼" red or green based on results from a formula on that cell? I'm using the IF function to return those symbols as explained in another video.
Hi Leila, Thanks for sharing. May i know why i can't paste the symbol using CTR + V under Format Custom?
Mind Blowing video I like your all video
Thank you. Glad you like them.
How to apply custom formatting if I have thresholds like...it should be up green arrow if greater than 20, neutral arrow if the value is between and equal to 10 and 20, and down red arrow if value is less than 10?
This is amaaaaaaaaazing.
I just want to know how to set two thresholds for positive values. For example:
> 5% Red
> 0
it's literally in the video
Hey, Great video!
I would like to ask you how can i write on the type the square root or 3 in the cube like a superscript.
I would appreciate it. Thanks!!!
Can I use the custom format to have the arrow one color, but the number another color?
Great videos Leila, Very helpful but still couldn't find any solution for my problem. I have Column "B" which has already a Data Validation For Times those I wanted(1:00, 3:00, 4:30, 5:00, 6:15, 7:50, 10:20, 12:10, 14:20, 16:10, 18:10, 20:40, 23:00) User must choose between those times. How can I make the Column "B" accept only times between now and the next 3 Days. Not before and not after.
very