Amongst all the courses that I have followed on-line, yours are my favorites. Your presentations are very well made. Your voice and the pace of explanations are, for me, the most appropriate ones.
Thank you Leila. I appreciate how much thought goes into each of your videos. Very professional and easy to follow. I didn't know about the color choices, and I will start using them. One small comment about this video is that the 5% value overlaps the first two conditions, so one small tweak I did on the second part of the condition was N16
Another awesome tutorial Leila. Thank you!! I am currently about half-way through your Excel VBA & Macros course. I can't tell you how useful that course has been. It is so well organized and presented. The reference material you provide in that course has been invaluable for reviewing the information you so adeptly presented. What great value! I am most grateful.
I have all of the Leila's You Tube Videos. Now will be following up each technique slowly offline. Your excel file with video list was great to begin with. Shukran ukhty
Have seen all the custom formatting videos posted by you. Very smart use of the feature plus thoroughly useful and impressive video. Thanks for coaching through.
Hi Leila - great video again. Shows why Excel is the best program ever made: there are often multiple solutions to a problem. In this case and more in general I still tend to use conditional formatting instead of custom formatting. Ran into the limitation of the use of colors with custom formatting. Therefore applied the same logic via conditional formatting, but applying a custom color on the ‘•’ or up and down markers.
Fantastic explanation, I finally understand, your presentation on all 3 of this series made it possible to understand a tricky subject, thank you so much. John in Ireland.
HI Leila, Marvelous as always. When I was studying custom formatting I remember reading that it only take 3 thresholds. But, I see you found the workaround by using converting the number values to text - great workaround. I'll have to add this to the CF article in some way. Slap my forehead I didn't think! Suberb!
I really cant have the words that enough to thank you as they way it should be. Your videos are very helpful and you are able to deliver the superior information within easy way. As simple way to thank you with comment ( what can I say except AMAZING EFFORTS). Thanks as much as you deserved it !
Hey Leila, Nice trick and advance way of using custom formatting, very faster way we can do this than the conditional formatting..Thanks ..You rocks as always ...Keep sharing
Great series of videos. I've made notes and will be using some of these. Thanks. And I've created links so I can return to your videos and review them when I get confused.
Leila Gharani Is there any way to count unique with criteria (distinct count by formulas) We can use pivot for distinct count, If there is way to use formula it would be pleasure for me plz try to make distinct count with criteria video
I appreciate that. I'm glad you like the videos. I can make bits and pieces of Excel dashboards for RUclips, but a complete dashboard is too long for here....
Sure. Actually thank you for your question! It inspired me to experiment. Thanks for your support for the iron chef :) I'm looking forward to the ingredients.....
Hi Leila, The way you explain things is amazing. Thank you so much for sharing such a nice video. Could you please make a video on bullet chart? Like actual profit against budget/target profit... Kind Regards
Hi Arun - You're very welcome. Thank you for your suggestion. I have a full section on bullet charts in my data visualization course. The course is filmed on Excel 2010 though. I'll be creating a new visualization course on the latest version of Excel soon. I'll also make a note to add a lecture on bullet charts to RUclips.
You're welcome. I have a few videos on gantt charts here: ruclips.net/video/QZd3iXcueaI/видео.html and here: ruclips.net/video/KtR-CVxC5qA/видео.html - you'll find the link to the workbooks in the description of the videos.
Thank you for the insight on Custom Formatting. I'm close to using it, but I'm still not sure it'll work for me. There are several commonalities I see in your usage of Cust. Form. One is that you are making a separate (3rd) column from Plan v Actual columns. I'm using the Actual column to format comparison to Plan - to avoid making a separate column each month. Also, Cust. Form. seems confined to formatting +ve. -ve, and 0 whereas I think i need more options. Here's my problem. Conditional Formatting is plain not working for me and I can't explain why. I've taken 3 excel courses so i'm not exactly a beginner but i feel like it now. I'm using simple color (R,Y,G) conditional formatting, no arrows or special characters. And I have used it this way many times before. So i think the real problem is going to be "user error" - which is great - then it's fixable (hopefully ;-) I have a month by month column of actuals that are conditionally formatted against a single column of plan or budget column. I've been waiting to conditionally format each month after the actuals are posted. But i decided to "get smart" and copy and paste my (correct!) conditionally formatted columns to the blank future columns so when the numbers are posted the formatting would already be there - well that was a big mistake. (The numbers & formulas did copy correctly [go F4!]). All the conditional formatting is wrong now - well, let me say, the formatting looks right, but it just isn't working anymore. I am looking at my cond. formatting formula for Yellow and the cell meets the condition (like it does in all the other cells . . !), but it is showing Green or Red, ugggh. So, i have cleared all the Conditional Formatting from all future column cells and started over - and still no good. it's like the old formatting is still there or something. Very frustrating. that's why I'm considering Custom Formatting. Anyway, i may just have to go back to Conditionally formatting month by month because the copy & paste of conditional formatting only didn't work. I tried several different ways too. Anyway, if there is a way to copy Conditional Formatting (only) to other cells, please let me know. btw, i'd like to copy & paste across rows, down columns and across both at same time (the array). and I tried the Paste Special option of Conditional Formatting only - and still didn't work. But that was after several other tries . . .so i think i had conflicting & overlapping conditional formatting. wow, what a long comment/question, whew!
Great video, it is amazing. The only thing that I don´t understand is why for the first exercise, for the positive values you pick up D6 which is a negative value?
6:45 It may be easier to just create a simple equation that subtracts your threshold number from the processed value. This will result in positive numbers for values greater than the threshold, negative values for values less than the threshold, and zero of values equal to the threshold. Now all you need is a standard value format for ve+; ve-; 0; @
I'm excited to experiment with custom formatting . Thanks for this series Leila. What I'm wondering is if you can also control the cell fill color similar to how you changed the text color for thresholds.
Hello dear Leila, would you consider to level up the volume of this video please? Because I have the vol. on max on my PC now and it's very difficult to understand the things you are explaining ;) Thank you
Hi Leila, Thank you for your very informative videos. Please can I ask you, is there a function equivalent to the Goal Seek feature in Excel to calculate the goal seek value automatically when you change the variables involved?
Excellent videos! Is there a way to use custom format to put a date before today in red for example? I am trying not to use conditional formatting for this. Thanks!
Very helpful video... I have 1 question. In my sheet I am using code 0.?? to align values in a column. Is it possible to hide "." when I type a whole number? I want to show "5" instead of "5."
So very useful and well presented, as ever! I've applied some custom number formatting to a calculated field on a pivot table, to show % difference in sales vs previous year, but some cells show errors (#DIV/0!) and some show -100% whenever a product has zero sales the year before or zero sales so far this year. I'd like to hide these from displaying if possible, is that possible with custom number formatting? Or do I somehow have to build an iferror check into the pivot table calculated field?
These videos are so useful. I have subscribed to the channel. Thank you. The workbook download bitly link doesn't seem to be working at the xelplus page.
Glad to have you onboard, Kimberley. I just tested the download for the workbook from the website and they worked fine for me. Maybe try with a different internet browser?
Great video! Is it possible to use custom formatting to apply a color fill to the cells based on their values? Or to set threshold dependent fill colors like with conditional formatting? Thanks!
Hi, you will to do it manually. With a max 4 conditional colors given the constrain of positive, negative, zeros & text: +;-;0;@ Courtesy of Ms Leila in her workbook to the type of colors you would like to display for the conditions: Reference as per Microsoft Color codes for custom formatting: msdn.microsoft.com/en-us/library/cc296089(v=office.12).aspx For Unicode symbols, you can reference alanwood's Unichar formula with the symbol numbers found at this website. www.alanwood.net/demos/wingdings.html - for wingdings
Hi Leila, thks for the knowledge... I wonder whether this custom fornatting applicable for chart label? I would like to combine this trick with your udemy class material : add dynamic series labels inside excel charts
Glad you like it. Yes - it works for pivot table as well, but you can't use this to color the cell - you can use it to change the font color or use symbols but not the cell color.
Could you or anyone please help to solve such case : I want to display calculated difference in absolute number (110-100=10), but to apply standard icons (either custom or conditional formatting) in same cell based on difference percentage (>=10% - green,
Hi Leila, i tried to link a formatted cell to my chart title. It works but the color was fixed to black and not following the eg red color for negative no. Is there a way for this to work? Thanks and appreciate your help.
Did you ever figure this out? Im getting the same thing for one table, but my other table doesn’t have it. The difference between the two is one didn’t have a condition on the positive end. Only on the negative end
Can the custom format work without any function as my data as Cubes file and all functions are automatically and only i can see the result, but i need to make custom format for each %
Grab the file I used in the video from here 👉 pages.xelplus.com/formatting-thresholds-file
Amongst all the courses that I have followed on-line, yours are my favorites. Your presentations are very well made. Your voice and the pace of explanations are, for me, the most appropriate ones.
Thank you Leila. I appreciate how much thought goes into each of your videos. Very professional and easy to follow. I didn't know about the color choices, and I will start using them. One small comment about this video is that the 5% value overlaps the first two conditions, so one small tweak I did on the second part of the condition was N16
Reading comments made by your followers is as interesting as your videos. Great! keep it that way.
Thank you Sandeep. I love the comments, the suggestions & questions too. I really appreciate the community here! Thank you for your support.
Very useful.. This channel is for those who think they are already good in Excel.
Thank you Arvind for your support :)
Actually, thanks a lot as you take ur comments as an important, you are one of the best instructors at YT😊
Your every video has a new opportunity to learn. Thanks a lot Leila
Happy to hear that!
Another awesome tutorial Leila. Thank you!! I am currently about half-way through your Excel VBA & Macros course. I can't tell you how useful that course has been. It is so well organized and presented. The reference material you provide in that course has been invaluable for reviewing the information you so adeptly presented. What great value! I am most grateful.
I have all of the Leila's You Tube Videos. Now will be following up each technique slowly offline. Your excel file with video list was great to begin with. Shukran ukhty
Have seen all the custom formatting videos posted by you. Very smart use of the feature plus thoroughly useful and impressive video. Thanks for coaching through.
Hi Leila - great video again. Shows why Excel is the best program ever made: there are often multiple solutions to a problem. In this case and more in general I still tend to use conditional formatting instead of custom formatting. Ran into the limitation of the use of colors with custom formatting. Therefore applied the same logic via conditional formatting, but applying a custom color on the ‘•’ or up and down markers.
great tutorial. I think most of my excel tricks came from your channel. thank you
Fantastic explanation, I finally understand, your presentation on all 3 of this series made it possible to understand a tricky subject, thank you so much. John in Ireland.
HI Leila,
Marvelous as always. When I was studying custom formatting I remember reading that it only take 3 thresholds. But, I see you found the workaround by using converting the number values to text - great workaround. I'll have to add this to the CF article in some way. Slap my forehead I didn't think! Suberb!
Hi Michael - yes it hadn't occurred to me either, until I saw the questions :)
Interesting. That makes sense
I really cant have the words that enough to thank you as they way it should be. Your videos are very helpful and you are able to deliver the superior information within easy way. As simple way to thank you with comment ( what can I say except AMAZING EFFORTS). Thanks as much as you deserved it !
easiest way to learn advanced excel ...big thank you
very nice logic of assigning positive/negative number/ zero/ text to get formatting working
Thanks Vida:) yes, those 4 conditions can come in handy...
Hey Leila,
Nice trick and advance way of using custom formatting, very faster way we can do this than the conditional formatting..Thanks ..You rocks as always ...Keep sharing
Thank you Dashrath. Agree - I also prefer to custom formatting than conditional formatting...
I learn a lot with your videos...
Thank you!!
Hi Leila.. thanks for another excellent video and tutorial. I use your tips and techniques in my reports every day. Thumbs up!
Wise and creative solution, Leila. Thanks by share this with your audience. Good job and looking forward to your next lesson.
Thank you Waldir for watching. I'm glad you find it useful.
I enjoy your class very much Leila, I never thought such things exist!
Glad you liked it!
Hi Leila, it was really creative. Actully i knew custom formatting but i didn't have your creative mind to do this with using if.
Thanks alot.
You're very welcome. I actually only got creative because of the questions I got :)
I really enjoyed the custom format series. Thank you Leila.
It's a great video. Thank you for taking the time to answer our questions with a video.
Great series of videos. I've made notes and will be using some of these. Thanks. And I've created links so I can return to your videos and review them when I get confused.
Glad you find the videos useful Pete!
Brilliant Leila, thank you so much for all your explanations! 💫
Leila , really you're süper and useful person. Thanks. I noticed power of cell formatting thanks to you. May Allah pleased from you.
Excellent as always. Thanks for sharing!
You're very welcome Yulin.
I have no words for you LG💕 many many thanks to sharing amazing excel videos Lots of love from bottom of heart ❤️
Thank you Dev for your continuous support :)
Leila Gharani Is there any way to count unique with criteria (distinct count by formulas)
We can use pivot for distinct count, If there is way to use formula it would be pleasure for me plz try to make distinct count with criteria video
@@Dev_Bartwal Hello Dev,
Use countifs function to obtain total number of counts based on your single or multiple criteria.
@@raghuv7114 i want unique count
@@Dev_Bartwal Hi, Can you please elaborate or put picture ?
Thanks for sharing this Leila
Thanks a lot posting this video. It is really useful.
Glad it was helpful!
Now my vocabulary is finished for your teaching mam
So creative and to the point. Many thanks for that!
You're very welcome :)
The best Excel Chart Instructor on Udemy. Leila is skillionaire
Awesome leila the way you teach...
Tq very much..
Very useful😍, i try to apply this concept in my report. Tqvm for sharing
Hi Leila, you are the best in data visualization, please make some videos about Dashboards.
I appreciate that. I'm glad you like the videos. I can make bits and pieces of Excel dashboards for RUclips, but a complete dashboard is too long for here....
Leila Gharani Thank you so much.
Very nice Leila. The way you explain, its fantastic.....Could you please also make a video on bullet chart?
Awesome thanks for answering my question and I'm looking forward to you winning the iron chief excel 🤗🤗🤗🤗
Sure. Actually thank you for your question! It inspired me to experiment. Thanks for your support for the iron chef :) I'm looking forward to the ingredients.....
Awesome tip.. I will definitely use it in my reports
Thank you Ganesh! Glad you find the tip useful.
Thanks Leila
Hi Leila, again a great video. Thanks a lot!
You're very welcome :)
Thank you for another great video
You're welcome Regis. Thank you for your support.
Thanks for the custom video!
You're welcome Doug :) Thank you for watching.
Thank u so much Ms. Leila the video really useful
Thank you Ismail for watching. I'm glad you find it useful :)
As usual always creative, greetings
Thank you! I'm glad you like it.
Thank you for all
You're welcome Silvan :)
Bundle of Thanks Mam Very Very Informative Video U R Great.
You're very welcome Khalid. Thank you for watching.
Brilliant, thank you Leila :)
Thank you Malina :)
Thanks a ton.. fantastic video
You're very welcome Sushant. Thanks for your support.
Veryyyyy nice. Thank you so much for sharing. 💐👍
Sure :) thank you for watching.
I love this channel. I wish there was a similar channel for MS Access as well. Does anyone know?
Good Leila nice video I m try this condition for my report. And one thing u r looking beautiful 😊
Thank you Vijay :)
🤯🤯🙌🏻🙌🏻🙌🏻 Thankyou
i really love this......got u!!!
Glad you like it. Thanks for your support Samuel.
Great trick ❤️
Some Keyboard shortcuts which improves productivity :- ruclips.net/video/s5Zu6UoUodk/видео.html
Thanks
You're very welcome.
AWESOME!!!
Thanks leila :)
You're very welcome John :)
Hi Leila,
The way you explain things is amazing. Thank you so much for sharing such a nice video.
Could you please make a video on bullet chart? Like actual profit against budget/target profit...
Kind Regards
Hi Arun - You're very welcome. Thank you for your suggestion. I have a full section on bullet charts in my data visualization course. The course is filmed on Excel 2010 though. I'll be creating a new visualization course on the latest version of Excel soon. I'll also make a note to add a lecture on bullet charts to RUclips.
Thank you Leila. I have enrolled 4 of your online courses. Excited 😀😀😀
thanks.
Very nice! 1 small question, in part 2, you made 2 conditions to apply if it's =5%, doesn't this clash?
Lets do it :P
I used instead =IF(D2>10,1,IF(D2>5,-1,IF(D2>3,0,"T"))) and it works fine.
@@vandalo7494 Great, thanks.
Just set the threshold column equal to the Dev column minus the threshold value.
Now all you have to do is apply a standard ve+;ve-;0;@ format.
Thank you very much.
Useful. Clear explanation. Please share a link to your examples about Gantt charts in Excel
You're welcome. I have a few videos on gantt charts here: ruclips.net/video/QZd3iXcueaI/видео.html and here: ruclips.net/video/KtR-CVxC5qA/видео.html - you'll find the link to the workbooks in the description of the videos.
Thank you for the insight on Custom Formatting. I'm close to using it, but I'm still not sure it'll work for me. There are several commonalities I see in your usage of Cust. Form. One is that you are making a separate (3rd) column from Plan v Actual columns. I'm using the Actual column to format comparison to Plan - to avoid making a separate column each month. Also, Cust. Form. seems confined to formatting +ve. -ve, and 0 whereas I think i need more options. Here's my problem. Conditional Formatting is plain not working for me and I can't explain why. I've taken 3 excel courses so i'm not exactly a beginner but i feel like it now. I'm using simple color (R,Y,G) conditional formatting, no arrows or special characters. And I have used it this way many times before. So i think the real problem is going to be "user error" - which is great - then it's fixable (hopefully ;-) I have a month by month column of actuals that are conditionally formatted against a single column of plan or budget column. I've been waiting to conditionally format each month after the actuals are posted. But i decided to "get smart" and copy and paste my (correct!) conditionally formatted columns to the blank future columns so when the numbers are posted the formatting would already be there - well that was a big mistake. (The numbers & formulas did copy correctly [go F4!]). All the conditional formatting is wrong now - well, let me say, the formatting looks right, but it just isn't working anymore. I am looking at my cond. formatting formula for Yellow and the cell meets the condition (like it does in all the other cells . . !), but it is showing Green or Red, ugggh. So, i have cleared all the Conditional Formatting from all future column cells and started over - and still no good. it's like the old formatting is still there or something. Very frustrating. that's why I'm considering Custom Formatting. Anyway, i may just have to go back to Conditionally formatting month by month because the copy & paste of conditional formatting only didn't work. I tried several different ways too. Anyway, if there is a way to copy Conditional Formatting (only) to other cells, please let me know. btw, i'd like to copy & paste across rows, down columns and across both at same time (the array). and I tried the Paste Special option of Conditional Formatting only - and still didn't work. But that was after several other tries . . .so i think i had conflicting & overlapping conditional formatting. wow, what a long comment/question, whew!
As planilhas ficarão mais atrativas e com um aspecto profissional. Obrigado Leila.
Thank you Luciano :) Agree - they will look more attractive & organized.
Really nice
Loved it
Great video, it is amazing. The only thing that I don´t understand is why for the first exercise, for the positive values you pick up D6 which is a negative value?
6:45 It may be easier to just create a simple equation that subtracts your threshold number from the processed value. This will result in positive numbers for values greater than the threshold, negative values for values less than the threshold, and zero of values equal to the threshold.
Now all you need is a standard value format for ve+; ve-; 0; @
12:50
IF(N6
nice video Leila
Thank you Martijn. I'm glad you like it!
Thanks a lot.
You're very welcome.
Interesting. But first case could by simpler. =IF(ABS(D6) > E4; D6; 0)
I just want to say, thank you Leila, you are amazing. I have a question, How can I align the arrows and the numbers, so it doesn't show up messy?
Very useful
Thank you
You're very welcome. Thank you for watching.
I'm excited to experiment with custom formatting . Thanks for this series Leila.
What I'm wondering is if you can also control the cell fill color similar to how you changed the text color for thresholds.
Hi Mark - Yes you can but you need to use conditional formatting for that.
@@LeilaGharani that's what I'm doing now. Was just hoping custom formatting might work too 😆. Thanks for sharing your awesome skills
loveuuuuu mam
That's great ! İf we have more than four conditions, can we also create custom formatting ?
Hello dear Leila, would you consider to level up the volume of this video please? Because I have the vol. on max on my PC now and it's very difficult to understand the things you are explaining ;) Thank you
Hi Leila, Thank you for your very informative videos. Please can I ask you, is there a function equivalent to the Goal Seek feature in Excel to calculate the goal seek value automatically when you change the variables involved?
Hi Leila! Could you please explain this formatting formula?
[>10]General;[
How to create stock market ticker. If price increases cell colour should blink green once and blink red when price decreases
Excellent videos! Is there a way to use custom format to put a date before today in red for example? I am trying not to use conditional formatting for this. Thanks!
Very helpful video... I have 1 question. In my sheet I am using code 0.?? to align values in a column. Is it possible to hide "." when I type a whole number? I want to show "5" instead of "5."
So very useful and well presented, as ever! I've applied some custom number formatting to a calculated field on a pivot table, to show % difference in sales vs previous year, but some cells show errors (#DIV/0!) and some show -100% whenever a product has zero sales the year before or zero sales so far this year. I'd like to hide these from displaying if possible, is that possible with custom number formatting? Or do I somehow have to build an iferror check into the pivot table calculated field?
You can add the IFerror to it so =iferror(put your formula here, “”)
A minor error in the foluma, when the % is 5%, what its color will be? So the correct one should be "=IF(AND(N6>=5%,N6=3%,N610%,0,"t")))"
These videos are so useful. I have subscribed to the channel. Thank you. The workbook download bitly link doesn't seem to be working at the xelplus page.
Actually, I meant the workbook for the 1st two videos in this series.
Glad to have you onboard, Kimberley. I just tested the download for the workbook from the website and they worked fine for me. Maybe try with a different internet browser?
@@LeilaGharani I did and that worked. Thanks.
Great video! Is it possible to use custom formatting to apply a color fill to the cells based on their values? Or to set threshold dependent fill colors like with conditional formatting? Thanks!
Hi, you will to do it manually. With a max 4 conditional colors given the constrain of positive, negative, zeros & text: +;-;0;@
Courtesy of Ms Leila in her workbook to the type of colors you would like to display for the conditions:
Reference as per Microsoft Color codes for custom formatting: msdn.microsoft.com/en-us/library/cc296089(v=office.12).aspx
For Unicode symbols, you can reference alanwood's Unichar formula with the symbol numbers found at this website.
www.alanwood.net/demos/wingdings.html - for wingdings
Hi,How to have Orange /Amber colour (while doing 3 color custom formating - Green,Amber,Red)
Hi Leila, thks for the knowledge... I wonder whether this custom fornatting applicable for chart label? I would like to combine this trick with your udemy class material : add dynamic series labels inside excel charts
Is it possible to alignment numbers based on positive, negative and zero to right, left and middle alignment in the same column?
Awesome tips... Does this work for pivot table as well?
Also, can we do this to fill the cell color instead of text?
Glad you like it. Yes - it works for pivot table as well, but you can't use this to color the cell - you can use it to change the font color or use symbols but not the cell color.
Looking for the solution
Each day have different colour.
Like: red for sunday, blue for Saturday, green for Tuesday so on.
Could you or anyone please help to solve such case : I want to display calculated difference in absolute number (110-100=10), but to apply standard icons (either custom or conditional formatting) in same cell based on difference percentage (>=10% - green,
Hi Leila, i tried to link a formatted cell to my chart title. It works but the color was fixed to black and not following the eg red color for negative no.
Is there a way for this to work? Thanks and appreciate your help.
Hi I am trying this out and I am getting a strange result on the 3rd condition. My formatting looks like this [Color10][>=0]▲;[Red][
Did you ever figure this out? Im getting the same thing for one table, but my other table doesn’t have it. The difference between the two is one didn’t have a condition on the positive end. Only on the negative end
Instead of nesting IF functions, wouldn’t it be better and simpler to use IFS function?
Can the custom format work without any function as my data as Cubes file and all functions are automatically and only i can see the result, but i need to make custom format for each %