Even after knowing some of these, I found that I have to know more of these. Thanks chandoo, your pipe formatting I still remember with Arial bold font and rept formatting.
Anna garu, insurance market chala peddadi .. many are not having skills in Excel and wanted to learn how to deal with Claims Data, TAT, Predictive Analysis / Forecasting, performance of individual team member etc etc. Please make a video on it, ASAP. Thank you in advance.
Cool... As always. Every time I get nothing new as feature as such but I get a new way to look at known feature(s). This time I found 2 new approaches. I would have created longer function to highlight 5 rows together alternatively. But your way is much much simple.
Hi Chandoo. Nice tips! Here is my version of your #3 for row groupings using ROWS with an expanding range: =ISODD(INT((ROWS(B$5:B5)-1)/5)+1). Same result.. dlightly different method. Thanks for sharing your great tips and tricks :)) Thumbs up!!
@@Excelambda Excellent insight! I was focused on getting to an integer value to get nice even 1s, 2s, 3s, etc.. I didn't stop to think about the how EXCEL interprets the "odd-ness" or "even-ness" of non-integers. ISODD and ISEVEN treat decimal numbers as the integer values to the left of the decimal place, so no INT is required. Thanks for sharing that wisdom.. will make my formulas that much more efficient in the future! Thumbs up!!
sir I have worksheet of deposit table in this i have created columns like maturity amount,due date induedate coloumn i have applied conditional formatting,so according to date Red, yellow and green colour is applied but now I want that according to due date column colour in maturity coloumn colour should be changed, means red, yellow, green,so how to do this using conditional formatting, please guide me ASAP and also create a video asap
Hi, just not able to do this using conditional formatting: if you have A,B,C,D product and 2019,20,21,22,23 sales then how can we insert two different colours for cells having value more and less than average sales for respective years. pls help
Hey i had question , plz sir if u can answer, how the data bars or icons in another column ? When i am applying they are in same column? Plz answer sir
Hello Chandoo, How are you, you are sharing only half part of your excel chart, you did not show the formula bar, also you did not mention the icon setting rules, formula, how to set ??
How to do conditional use formatting if for example a vendor has weekly limit of Invoice posting limit how to highlights on which week the limit got exceeded
Hi sir I do facing issue in excel - There are several years & dates in that year suppose 2004, 2005 , 2006 years which contains Dates starting from Jan till Dec each year ... I have to found the Every first day of each month for different years without using any helper column ..How do we do that ?
Do you know how to find particular rule applied on column or particular set of rules when there are like 70-80 rules apllied. When i hover over the rule, it displays the rule in tooltip. But i can't expand the column like normal excel column. So I've to hover over all 70-80 rules to search for the specific rule.
You can change the option in conditional formatting rule manager to show rules on "selected cells". If you have that many rules, you may want to start documenting them in a separate sheet for maintenance purposes (I would even question the need for so many rules as that looks like a perfect place to optimize the workbook).
@@chandoo_ thanks for your response. I'll try what you said. I've data of some 70+ stocks (shares) . And I've added formula like and(a4=$j$4, b4=$k$4). Applied on column A. These i am going to condition format. A column has price of stocks that will match with $j$4 where I've set up my price. B4 has name of stock which will match with$k$4. These values $j$4, $k$4 they'll have 70 values up till 74th cell. Which will match with a n b column respectively. And when i mess up something here. It's hard for me to find where did i go wrong. Is there better way to do this?
Sorry about that Ajit... Please use headphone and may be add "subtitles". I am sure the techniques are worth it. I have improved my audio process in future videos.
I can’t learn enough. My hunger for knowledge is never satisfied. You are my go to videos! Thank you for your awesome videos
Even after knowing some of these, I found that I have to know more of these. Thanks chandoo, your pipe formatting I still remember with Arial bold font and rept formatting.
I train people about CF but you suprised me with the last trick.
Thanks for this very informative video...
Thanks chandoo, have been using excel for past 10 years now, learnt that conditional formatting has "no icons" options also.
Great 👍
Awesome ...................... I am never before see
You are a Genius Chandoo, many thanks
I love to play with Excel and share my ideas. I am glad to hear you find them useful too.
You inspire me
Very helpful
great tips, chandoo
Thanks Respected Teacher
Superb... Thank you Chandoo 🙏
thank you sir
Very useful tutorial
Great tricks. Some I knew but never had seen them used and some I had no idea
Awesome tricks.
Glad you like them!
Great video, as usual. But suggest you bump up the audio a bit, as you sound too feeble!
Noted!
You are amazing Chandoo
Thank you.
Thanks for the video. It's very useful for me.
Glad it was helpful!
❤
Thanks for the video. All are very useful and interesting. Thanks for sharing. :) :)
Super :)
Thank you !
You're welcome!
Thanks for the video! Very usefull like always.
My pleasure!
God bless you man
Thanks a lot
Thank you too 🙏
Thank you so much for your nice & strategic tutorial. All of your trick is really nice & very useful for the professionals. Allah bless you brother.
Great tutorial and very useful too. Thanks for sharing
Glad it was helpful!
Good informative session, good keep it up..
Keep watching
Now me too , fall in love with conditional formating :)
Perfect!
Very useful excellent job
Thanks a lot
Anna garu, insurance market chala peddadi .. many are not having skills in Excel and wanted to learn how to deal with Claims Data, TAT, Predictive Analysis / Forecasting, performance of individual team member etc etc. Please make a video on it, ASAP. Thank you in advance.
Very useful and thanks
Welcome 😊
Cool... As always. Every time I get nothing new as feature as such but I get a new way to look at known feature(s). This time I found 2 new approaches. I would have created longer function to highlight 5 rows together alternatively. But your way is much much simple.
Thanks Rajan.. :)
Hi Chandoo. Nice tips! Here is my version of your #3 for row groupings using ROWS with an expanding range: =ISODD(INT((ROWS(B$5:B5)-1)/5)+1). Same result.. dlightly different method. Thanks for sharing your great tips and tricks :)) Thumbs up!!
Great tip!
=ISODD((ROWS(B$5:B5)-1)/5) is no need for INT if you use ISODD 😉
@@Excelambda Excellent insight! I was focused on getting to an integer value to get nice even 1s, 2s, 3s, etc.. I didn't stop to think about the how EXCEL interprets the "odd-ness" or "even-ness" of non-integers. ISODD and ISEVEN treat decimal numbers as the integer values to the left of the decimal place, so no INT is required. Thanks for sharing that wisdom.. will make my formulas that much more efficient in the future! Thumbs up!!
Great! Thanks a lot.
You are welcome!
Great video bro...you could also change the icons and.color bars using the custom formatting function, ctrl 1 which I use on a daily basis...
Thanks for sharing
sir I have worksheet of deposit table in this i have created columns like maturity amount,due date induedate coloumn i have applied conditional formatting,so according to date Red, yellow and green colour is applied but now I want that according to due date column colour in maturity coloumn colour should be changed, means red, yellow, green,so how to do this using conditional formatting, please guide me ASAP and also create a video asap
always great ♥
Glad you think so! Thanks Hazem for the comments :)
I want a help on conditional formatting concept , please help me out
Hi, just not able to do this using conditional formatting:
if you have A,B,C,D product and 2019,20,21,22,23 sales then how can we insert two different colours for cells having value more and less than average sales for respective years. pls help
Hey i had question , plz sir if u can answer, how the data bars or icons in another column ? When i am applying they are in same column? Plz answer sir
after zebra shading can we remove true false column
so, it's seems nice.
You can move the entire condition to a formula inside the "conditional formatting formula rule". I used a separate column to demo the concept.
Hi sir in sheet 1 if >=50% then ideally no symbol should come for 50% but why down arrow is showing
Hello Chandoo, How are you, you are sharing only half part of your excel chart, you did not show the formula bar, also you did not mention the icon setting rules, formula, how to set ??
Wow❤😂
my icons are not working like it should, its using one icon for all data instead of using the differentiating icons , help
use this to highlight every 4th row, this one is easier =MOD(ROW($B2)-1,5)
How to do conditional use formatting if for example a vendor has weekly limit of Invoice posting limit how to highlights on which week the limit got exceeded
You gotta set up a new formula
@@FunFactopias-Facts please advise which formula
@@2310546 it’s the “if” formula right?
I caught you out at 06:33 starting to write the mod formula. That is what I use :-)
Yes, but ISEVEN is better to explain so have been using it for a while. Old habits though :)
Hi sir I do facing issue in excel - There are several years & dates in that year suppose 2004, 2005 , 2006 years which contains Dates starting from Jan till Dec each year ... I have to found the Every first day of each month for different years without using any helper column ..How do we do that ?
Do you know how to find particular rule applied on column or particular set of rules when there are like 70-80 rules apllied. When i hover over the rule, it displays the rule in tooltip. But i can't expand the column like normal excel column. So I've to hover over all 70-80 rules to search for the specific rule.
You can change the option in conditional formatting rule manager to show rules on "selected cells". If you have that many rules, you may want to start documenting them in a separate sheet for maintenance purposes (I would even question the need for so many rules as that looks like a perfect place to optimize the workbook).
@@chandoo_ thanks for your response. I'll try what you said. I've data of some 70+ stocks (shares) . And I've added formula like and(a4=$j$4, b4=$k$4). Applied on column A. These i am going to condition format. A column has price of stocks that will match with $j$4 where I've set up my price. B4 has name of stock which will match with$k$4. These values $j$4, $k$4 they'll have 70 values up till 74th cell. Which will match with a n b column respectively. And when i mess up something here. It's hard for me to find where did i go wrong. Is there better way to do this?
😀😀😀
I have my expert exam tmr 😅
Hi Chandoo. Please check your video on time calculations. I left urgent message. Thanks.
Your voice is very low !
Sorry about that Ajit... Please use headphone and may be add "subtitles". I am sure the techniques are worth it. I have improved my audio process in future videos.
Awesome ...................... I am never before seen
Thank you.
Awesome tricks .