You never fail to amaze me. Just too short of time right now but as soon as I am a bit available, I would like to sign up for your excel class. Thanks for the wonderful work
Really Awesome 😀.... I need this one on Conditional formatting... Love to hear pop up music🎵 coming in during this video... Nicely arranged video... Thank you ...Sir..👍
Thank you Chandoo. Very informative. Is there a way to compare the values of 2 two rows with each other with conditional formatting to highlight the ones that do not meet the condition e.g. If the customer's days are larger than the supplier's days for a specific month, it should be highlighted. I know I can apply this condition for each month separately, but to do the conditional formating with one formula for the complete row is the preference. Thank you
Hi, how can I hide rows based on a cell value specifically a drop down menu, using excel 2016. Using conditional formatting as an alternate to macros but my version does not have the "hide" option in the "font" tab. Thanks
Sir my company is using one cash flow excel sheet and once i edit numbers or any data it showing a differennt formate which means i need to make each and every one need to format painter . My question is how to set the whole excel sheet in particular format? If i edit any thing i need it same format before.
Hi Chandoo !! A question regarding applying conditional formatting in a pivot with multiple slicers. I want a particular cell (Say D4) to show the text in Green when the value is > 0 when we select "Debit total" as the slicer option. However, the same cell should show red show values < 0 & we select "Credit total". Both these data sets will show the final output in cell D4 basis on the type of slicer we select (Debit total or credit total). My question: Can we apply conditional formatting in this same cell to show the text as Green when "debit total" slicer is selected and as Red when "credit total" slicer is selected? Please do note that the range where the conditional formatting needs to be applied is a part of a pivot table. Apologies for the lengthy question !!
I want data depending on cell status i have Active and Closed. So i have data in A,B and in C i have Active or Closed, So If i select Closed then data in A,B should add up and be displayed in column D, how can we do it?
Hope you can assist. I have data set and the first column is Planning date. i want to conditional format the entire row with colour fill if a date is inserted, if blank nothing happens. Then there is another column Delivered Date. Once a date is entered there is need to over write the Planning date with colour green (entire row) . Please advise if you can advise how this is done, been battling for a while now to get this right. Many thanks
Sure. Assuming your planning date is cell A2, delivery date is in cell F2, select all your data (excluding headers) add a new conditional formatting, formula rule type the rule as =$A2"" and set formatting as red color. Now add one more rule type the rule as =$F2"" and set the formatting as green color This should give you the result you want. If you notice some inconsistencies, go to manage rules and make sure that the rules are in the order of red first then green (so green will always overwrite red).
hi chandoo, i tried highlighting the top 3 sales using sample document provided. I used the same $B2>150000 ( data starts from 2nd cell, B column) but top 3 data is not highlighted? Can you tell me where I went wrong.
Hello Chandoo, I am big fan of your work. I wanted to know is there any way to alter the values in formula bar? Eg. I input 10,00,000. Then I convert that value as '1' through some custom formatting as my client wanted to see this value as scaled. Now his requirement is they wanted to see '1' in formula bar as well. Is there any way to show such conversion in formula bar? If there any, please suggest me. Thank you.
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
Thanks Kartavya... Here is a brief outline of that edit. 1) Duplicate the screen recording layer and place it on top the original (at exact frame) 2) Crop the second layer (holding down ALT) to just the formula box 3) Add custom animation to the second layer and zoom it up. 4) Add any shadow / highlight effect if needed 5) Restore the animation once the emphasis is over. Hope that helps.
You could have kept the rule of top 3 then added a new rule to the adjacent column which format green based on the colour of your D column. So if it was a top 3 then the other cells in that row will go green picking up the colour change in column D
Very cool tips Chandoo! Every time you end up enriching your viewers with something practical and useful! Keep up the great work! 😊👍
Thanks Vijay 😀
I am a mis Executive In a organization. After See your amazing Videos now I am developing My Skills Into Data Analyst.. ❣️❣️
Wow.. More power to you Ankit...
you are really amazing and down to earth and humble guy
Highly overwhelmed getting in touch with you. Wishing for your greater progress.
Best Excel teacher. You give example data sets to download as well as show exactly how it is done. Amazing!!!!!.
You make me love excel more and more
You are welcome!
You never fail to amaze me. Just too short of time right now but as soon as I am a bit available, I would like to sign up for your excel class.
Thanks for the wonderful work
Thanks Sarika... Sure. my course is ready whenever you are...
This is awesome Sir..! Could you do an Vedio on Interview Qestions in Excel? .....That helps a lot Sir
You are welcome Shashank.. I have an *exciting* video coming up next week. Stay tuned...
Really Awesome 😀.... I need this one on Conditional formatting... Love to hear pop up music🎵 coming in during this video... Nicely arranged video... Thank you ...Sir..👍
yes, 👌that pop up music is fun😊
Glad to *hear* that... 😎
Brilliant as always! Thank you.
My pleasure!
Thank you Chandoo. Very informative. Is there a way to compare the values of 2 two rows with each other with conditional formatting to highlight the ones that do not meet the condition e.g. If the customer's days are larger than the supplier's days for a specific month, it should be highlighted. I know I can apply this condition for each month separately, but to do the conditional formating with one formula for the complete row is the preference. Thank you
Excellent, excellent, excellent! You are a wonderful teacher!
Thank you! 😃
Great video! I loved the content and the engaging and easy presentation as well!!!
Glad you enjoyed it!
Thanks for the data bar tip!
📊🙂
These were great tips, thank you Chandoo!
Glad you like them Chris...
Great CF tips! Thanks Chandoo. Thumbs up!!
You are welcome Wayne... 😀😀😀
GOOD STUFF!
That makes our report look "yummy". LOL I like that.
😎
very interesting sir great explanation want to learn more excel tips from u sir hope u share more videos 😊
Excellent teaching Chandoo. I enjoy learning Excel. Learned a few new things today. 🙏
Loaded with value.
Thanks for your kind help.
Regards.
Super! Thank you for sharing!
This was great. Super helpful to show in incremental steps
Lovely Tips!
always great, always you present new Technics
You are welcome Hazem...
Helping us to improve as always, thank you.
That is my mission really. Helping one million people every month...
Splendid video
Chandoo my man, real respect for you.
Hi! From London.
Great Tutorial.....is there a way of formatting the font size and font type?
Thank you Chandoo. That was great. 👌
Glad you liked it!
very informative video!
Hi, how can I hide rows based on a cell value specifically a drop down menu, using excel 2016. Using conditional formatting as an alternate to macros but my version does not have the "hide" option in the "font" tab. Thanks
This is great. Thank you
Hi Chandoo, I have a question for you, I want to apply [Color10]0.0"▲";[Red]-0.0"▼"; plus #,,.00 formatting to a single cell how can I do that?
Sir my company is using one cash flow excel sheet and once i edit numbers or any data it showing a differennt formate which means i need to make each and every one need to format painter . My question is how to set the whole excel sheet in particular format? If i edit any thing i need it same format before.
Thanks for video.
Very useful ❤
🤘Rock On! Thanks for a great lesson!
😎😍😎
Hi Chandoo !! A question regarding applying conditional formatting in a pivot with multiple slicers. I want a particular cell (Say D4) to show the text in Green when the value is > 0 when we select "Debit total" as the slicer option. However, the same cell should show red show values < 0 & we select "Credit total". Both these data sets will show the final output in cell D4 basis on the type of slicer we select (Debit total or credit total).
My question: Can we apply conditional formatting in this same cell to show the text as Green when "debit total" slicer is selected and as Red when "credit total" slicer is selected? Please do note that the range where the conditional formatting needs to be applied is a part of a pivot table.
Apologies for the lengthy question !!
Wonderful as Always. A new Subscriber
Welcome aboard Karim...
I want data depending on cell status i have Active and Closed. So i have data in A,B and in C i have Active or Closed, So If i select Closed then data in A,B should add up and be displayed in column D, how can we do it?
How to format those cells whose sum constitute defined % of the total sum? .
Thanks Sir 🙏💕
But what's difference between Excel n Adv.Excel; Requesting Kindly share your knowledge through the Videos
Hi how we can find top sales figure multiple department in each articles (dept /subdept/articles)
great tip to get more attention from dashboard audience... thank you..👍👌
Glad it was helpful!
Amazing
Super to superb
Hope you can assist. I have data set and the first column is Planning date. i want to conditional format the entire row with colour fill if a date is inserted, if blank nothing happens. Then there is another column Delivered Date. Once a date is entered there is need to over write the Planning date with colour green (entire row) . Please advise if you can advise how this is done, been battling for a while now to get this right. Many thanks
Sure. Assuming your planning date is cell A2, delivery date is in cell F2,
select all your data (excluding headers)
add a new conditional formatting, formula rule
type the rule as =$A2""
and set formatting as red color.
Now add one more rule
type the rule as =$F2""
and set the formatting as green color
This should give you the result you want. If you notice some inconsistencies, go to manage rules and make sure that the rules are in the order of red first then green (so green will always overwrite red).
hi chandoo, i tried highlighting the top 3 sales using sample document provided. I used the same $B2>150000 ( data starts from 2nd cell, B column) but top 3 data is not highlighted? Can you tell me where I went wrong.
Nice trick for rank avg
You are welcome Mutlunun...
You're the best one in explaining Excel so far, Thanks a million!
Hello Chandoo, I am big fan of your work. I wanted to know is there any way to alter the values in formula bar?
Eg. I input 10,00,000. Then I convert that value as '1' through some custom formatting as my client wanted to see this value as scaled. Now his requirement is they wanted to see '1' in formula bar as well.
Is there any way to show such conversion in formula bar?
If there any, please suggest me.
Thank you.
Thanks Suraj... As far as I know, this is not possible. Why not hide the formula bar instead? You can do it from the "View ribbon"
@@chandoo_ Thank you Chandoo. I will hide the formula bar instead.👍
As usual awesome...
Thank you so much 😀
Nice Job 👍. Keep it up
Thanks 😀
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
please how can i extract a set of given data from a group data, all having unique id number?
Nice sir can u please also make videos on SAP
Can use large(cell,3) function to sweet three large cells value.
Great tip 😎
@@chandoo_ thanks 🙏🏻
Please make more videos related to advanced power bi
Excellent!
I was to write the same idea!
haha, I thought I knew conditional formatting, then I watched this :) Nice one Chandoo!
Wow. Thanks Danzi...
hey chandoko your videos is very nice but your editing is also nice
i want to know how you edit this effect on camtasia 3:30
Thanks Kartavya... Here is a brief outline of that edit.
1) Duplicate the screen recording layer and place it on top the original (at exact frame)
2) Crop the second layer (holding down ALT) to just the formula box
3) Add custom animation to the second layer and zoom it up.
4) Add any shadow / highlight effect if needed
5) Restore the animation once the emphasis is over.
Hope that helps.
could uoyplease make a video on latitude and longitude in excel
Thanks
How to exclude cells with zero or no values in top bottom rules? Thank you!
Thank you sir.
Most welcome
Great
Thank you :)
You could have kept the rule of top 3 then added a new rule to the adjacent column which format green based on the colour of your D column.
So if it was a top 3 then the other cells in that row will go green picking up the colour change in column D
Hmm.. not sure you can do that. There is no "CF" rule that can change color based on the color of other cells (not yet). Or am I missing something?
Power BI kaha say milay ga
Download it from powerbi website.. search it up.
I need excel training
Sir kindly reply my query regarding move cammand of cmd.
I really do not want to meet Gunar 💀
🤣
Conditional formatting.. the Devil’s tool….
Sorry your formatting is really not pleasant. Won't work for business setting
Feel free to format in whatever way works for you... There is no "RIGHT" way to do these things.
Another formula for top 3: =COUNTIF($B$4:$B$13,">" & $D4) < 3
Goodone.. I think the first ref. should be $D$4:$D$13...
You're the best one in explaining Excel so far, Thanks a million!
You're very welcome!