8 Expert Tricks for Conditional Formatting in Excel

Поділитися
Вставка
  • Опубліковано 25 сер 2024
  • MY FULL EXCEL VBA COURSE [80% Discount]: www.teachexcel...
    200+ Video Tutorials - 200+ Downloadable Excel Reference Files - 50+ Hours of Content
    Become a master of Conditional Formatting in Excel! This tutorial covers 8 tips and tricks that will have you making custom formulas for conditional formatting that can do everything from the most basic, to multi-conditional formulas across spreadsheets.
    Microsoft Excel Conditional Formatting Doc: support.micros...
    Excel File: www.teachexcel...
    Ask a Question: www.teachexcel...
    This is a great tutorial to watch to brush-up on your conditional formatting skills or to dramatically improve them. After viewing this, you should feel comfortable making almost any kind of custom formatting rule, and then implementing it, and troubleshooting it, if an error arises.
    Enjoy!
    TeachExcel.com

КОМЕНТАРІ • 92

  • @andrewbouc6015
    @andrewbouc6015 Рік тому +4

    I don't even know why I waste time on other Excel youtube videos. Your content is in a rare group of simply explained but not at all painfully obvious. Perfect for an intermediate user like me!

    • @GameAGuy
      @GameAGuy Рік тому +1

      Hey Lelia is great too😊

  • @TeachExcel
    @TeachExcel  2 роки тому

    MY FULL EXCEL VBA COURSE (Beginner to Expert) [80% Discount]: www.teachexcel.com/vba-course-update.php?src=yt_pinned_comment_lIqifDg2xfE
    200+ Video Tutorials - 200+ Downloadable Excel Reference Files - 50+ Hours of Content

  • @yickwong3776
    @yickwong3776 4 роки тому +2

    I have tried different channels and your tutorials are the best. THE BEST. Much appreciated.

  • @nickwhite2996
    @nickwhite2996 5 місяців тому +1

    Many thanks, your instructions taught me exactly what I have been searching for

  • @nsanerydah
    @nsanerydah 3 роки тому +1

    You solved my problem and I didn't even realize that your example worked. Great job! Thanks a bunch.

  • @sunglee2093
    @sunglee2093 2 роки тому +3

    You do a much better job of explaining this than others. Thanks.

  • @mohammedziyadpa
    @mohammedziyadpa 4 місяці тому

    Applying the conditional format to other sheets is crazy…. Thank you very much ❤

  • @jaxattax7357
    @jaxattax7357 4 роки тому +3

    thank you so much for this! definitely need this as a refresher n your voice is great for teaching this, very calm n articulate too :)

  • @Dharani_S
    @Dharani_S 8 місяців тому +1

    Excellent conditional formatting!!

  • @praveenkumar_0143
    @praveenkumar_0143 Рік тому +1

    Amazing video and got to know more about conditional formatting using dynamic formulas!! and got an opportunity to hands-on practice along!!😍😍

  • @thebesttechnical3608
    @thebesttechnical3608 5 років тому +2

    Great and easy way of explaining the
    topic, thank you very much.

  • @regularbloke7410
    @regularbloke7410 7 місяців тому +2

    I can't paste formula in new formatting rule

  • @Rais1959
    @Rais1959 3 роки тому +1

    Fantastic tutorial !! Very precise and accurate delivery. god bless for your act of kindness. Thank you very much.

  • @sereyvanny8123
    @sereyvanny8123 2 роки тому

    Thank you so much!!! I had to do this today, and I wasn't able to get it to work right, and your video helped me fix my issue.

  • @nairobi203
    @nairobi203 11 місяців тому

    By far the best explanation I found so far...

  • @barrygregg6058
    @barrygregg6058 10 днів тому

    You do a fantastic job. Thank you.

  • @lostinalgeria8492
    @lostinalgeria8492 3 роки тому +2

    It's not tedious at all !!! I find this sooo satisfying ❤️❤️ my only regret is that I have to wait till tomorrow to be able to apply this, cause I don't have a computer at home 😞

    • @TeachExcel
      @TeachExcel  3 роки тому +1

      What an awesome comment :)) I hope it goes well tomorrow!:)

  • @cocorajschaperderalderwere7784

    Amazing excel video, I could not figure out what is was doing wrong in my formula’s. Only to watch this video and it being explained perfectly. Thanks you, bravo.

  • @hmgkt
    @hmgkt 2 роки тому

    Knowing these simple really help upgrade the use of excel.

  • @user-kx4qg3xw5n
    @user-kx4qg3xw5n Місяць тому

    Great presentation and good contents to learn.
    Thank you !

  • @satishkumardommeti8282
    @satishkumardommeti8282 5 років тому +2

    Simple way to understand the power of conditional formatting

  • @anshul4370
    @anshul4370 11 місяців тому

    thanks bro

  • @tanjinasultana7420
    @tanjinasultana7420 Рік тому

    wow.. that's amazing tips and tricks.. thank you!

  • @michaeloloishuro6915
    @michaeloloishuro6915 6 років тому

    Very simplified demonstration on a hard conditional formatting topic.
    Thanks.

    • @TeachExcel
      @TeachExcel  6 років тому +1

      I'm really glad you like it! I was really worried about how long it was, but I'm glad you found it helpful :)

  • @MrAnas4
    @MrAnas4 4 роки тому

    Best video on conditional formatting

  • @krishnaprakash3422
    @krishnaprakash3422 3 роки тому

    Thanks for laying out conditional formating. Plz increase the audio from your side , for us to hear you clearly- Thank you!

  • @user-yk1wm3ke8b
    @user-yk1wm3ke8b 7 місяців тому +1

    I fuckin loved it , i'd seen Lodda clips but this 1izzzz. The best

  • @4-7th_CAV
    @4-7th_CAV 4 роки тому +3

    @TeachExcel, Thanks for your clear and understandable explanation of conditional formatting. I have used this type of formatting on several different spreadsheets and in varying ways. One thing I have a hard time understanding is when you use multiple formats on the same sheet and a given format essentially cancels out the other formats, i.e. if you have 6 formats and only one of the 6 is "true" and all of the remaining formats are "false", what is the purpose of the "Stop If True" option check-box on the right side of the dialog box, an how is it used?

  • @azizniazi2454
    @azizniazi2454 5 років тому +2

    Really informative video
    Special thanks

  • @jero007
    @jero007 5 років тому +2

    This is awesome, thank you!

  • @Rogue_Console
    @Rogue_Console 2 місяці тому

    Thank you for this.

  • @merajahmed3044
    @merajahmed3044 3 роки тому

    Thank you for the tutorial

  • @rajeevgosavi4514
    @rajeevgosavi4514 3 роки тому

    Good John, very impressive. Made complex things look very simple. Great!

  • @nahlas.3836
    @nahlas.3836 7 місяців тому

    Awesome as usual!!!

  • @jayantvedwan8157
    @jayantvedwan8157 4 роки тому

    Explained it SO Good.
    Completely Got it.
    Keep it UP 👍

  • @GeorgeAJululian
    @GeorgeAJululian 2 роки тому

    Many thanks very helpful

  • @unosec
    @unosec 4 роки тому

    thank you for teaching

  • @FibFract
    @FibFract 3 роки тому +2

    Hi, I have 11 sheets in a workbook and every week I add a new row of data to each sheet. I copy the last row and paste above since it has formulas then add the new weeks data. I have conditional formatting by color scale for each column, but the conditional formatting does not update to include the new row in comparison to all data in the column. So, I then have to manually update the conditional format rule to include the new row. Is this possible to automate? Any suggestions?

  • @tomaspalacios2828
    @tomaspalacios2828 Рік тому

    been all morning banging my head on the wall just to find out here why there are "$" signs, thank u!

  • @ravishingravi
    @ravishingravi 4 роки тому

    Thank you so much. I appreciate it.

  • @xpon7833
    @xpon7833 5 років тому +2

    thanks. you gave me the short version. lol... i always use the "If" and put lots of "IFs" to acomodate my need

    • @4-7th_CAV
      @4-7th_CAV 4 роки тому

      I do somewhat the same on one of my spreadsheets. I have a sheet to track recurring payments and their due date. I use the IF statement to populate the payment amount whenever I insert its payment date. I.E. if the recurring payment is $49.95 then when I insert the due date the payment amount will automatically be entered, if no date is entered the payment amount cell will remain blank. Then, I conditionally formatted the entire row to fill with a color which is dependent upon how many days it is from today to the due date. I.E. if the due date is 05-15-2020 and today is 05-01 then the cells have no fill color; if today is 05-09 the cells would fill with green; if today is 05-12 the cells fill with yellow; and if the due date is two days or less from today the cells will fill with red.

  • @cescarrachi
    @cescarrachi 4 роки тому

    thank you!!

  • @gayent01
    @gayent01 3 роки тому

    thank you for your tutorial this was really helpful. I am trying to find a way to highlight a cell on one tab and have the same information on a master tab highlight. For example I have people grouped by department on separate TABS and then all the departments list of people on the master tab. I would like to highlight the person on their department tab when I receive a reply and then have that match the name on the master tab and highlight it also.

  • @irfanmeghani
    @irfanmeghani 6 років тому

    Thanks. It's very informative. Looking forward to your next tutorial.

  • @miamited69
    @miamited69 6 місяців тому

    How to highlight one of three cells with lowest value that are not next to each other?

  • @aimanalalawneh5667
    @aimanalalawneh5667 4 роки тому

    Great job

  • @sevenl.maxwell1686
    @sevenl.maxwell1686 4 роки тому +4

    This was pretty simplified and exactly the refresher I needed! Thanks! 🙌🏾

  • @Fjuron
    @Fjuron 5 років тому

    Thanks!

  • @pratikdandekar2654
    @pratikdandekar2654 5 років тому

    Thanks a lot

  • @slslrilil
    @slslrilil 6 років тому +2

    The concept was super useful. Is it possible to use “Name Ranges” within formula in Conditional Formatting

  • @nimishpathela4630
    @nimishpathela4630 9 місяців тому

    can someone please tell me how does he copy the code from the first cell till the end of the data , i used double click but that copies the formula till the end cell of the sheet

  • @gonzinigonz
    @gonzinigonz Рік тому

    Great video :)

  • @toxiqrox
    @toxiqrox 4 роки тому

    So much to learn, I need to find a much more simple instruction, I am a beginner

  • @thriftymillionaire5968
    @thriftymillionaire5968 6 років тому +1

    i need help comparing two cells with a high and low value, one of the cells is blue. Return 1 if the lowest value is blue color or return zero if the higher value is blue color. how do i perform this task in excel?

  • @amygcowgill
    @amygcowgill 3 роки тому

    I need advise on a custom sheet. We have a bunch of options laid out with developer check boxes next to them. If a tech is offering that benefit, they select the tab which changes the cell value to TRUE. Right now I have it so that the whole sheet is a light grey font, with the idea that if they change the text box to true, the font color (not row) will change to the auto black color and become bold for the manager to look for. I cannot get this to work for conditional formatting. Is there a work around?

  • @mangalkayande8540
    @mangalkayande8540 2 роки тому

    Very nicely explained. But it did not work for me when I was trying to apply to one column based on whether the other col has value

  • @PawanKumar-nv8tb
    @PawanKumar-nv8tb 3 роки тому +1

    Nice video

    • @TeachExcel
      @TeachExcel  3 роки тому

      Thank you Pawan! I'm glad you liked it :)

  • @MegaTinni
    @MegaTinni Рік тому

    You didn't explain how the hierarchy works between multiple conditional formatting rules for the same cell.
    The 'stop if true' checkbox I see there. Thanks.

  • @kirandeepchoudhry9232
    @kirandeepchoudhry9232 5 років тому

    Hi, I want to highlight cells in two different columns when there total gets equal to eachother like ist column is not next to each other they are separated by three columns.I hope you understand my query. Plz reply when u get time

  • @dragoda
    @dragoda 5 років тому +1

    Hello, how can I color several random numbers from a series, like, i want all numbers 7 48 25 32 17 to be red. and then again, other numbers in other colors, blue for numbers 4,16,19 without having the need to set a new rule for each number, like 7 red, go again and set rule for 48, etc. Thx

    • @TeachExcel
      @TeachExcel  5 років тому +2

      Use a formula like this: =SUMPRODUCT(--(A1=$C$1:$C$3))>0
      C contains the list and A1 is the cell to check. Get the formula working in Excel first before putting it into a custom conditional format rule.
      If you need more help you can upload a sample file and ask in our forum: www.teachexcel.com/talk/microsoft-office?src=yt

  • @MsAncele
    @MsAncele 3 роки тому

    How protect it all from being change by users?

  • @enricopunta2819
    @enricopunta2819 4 роки тому

    Hi, I have a rather difficult question: I got a file Excel with activation macro. I use many conditional formatting and I wish that the interval of application would remain unchanged. I think that the problem are the macros but I obtain no effect registering a macro and setting the conditional formatting. Do you have you any ideas? Thanks

  • @bahaaomar9927
    @bahaaomar9927 6 років тому

    nice and cool, thanks

  • @tahirmanzoor1954
    @tahirmanzoor1954 Рік тому

    HI. hope you are doing great... I just want to know how to manage formula for dates in cell. i have two cell, one is plan and one is actual. How i determine that if the plan date is
    24-08-2023, & actual date is 25-08-2023 , so the delay of 1day is highlighted automatically how is possible ,

  • @HumanityMatters85
    @HumanityMatters85 Рік тому

    plz help....
    I am using this formula in conditional formatting in office 2019 version
    =SUM(B$3:INDEX(B$3:B3,ROWS(B$3:B3)-1))

  • @leighboyd9854
    @leighboyd9854 2 роки тому

    please add a table of contents

  • @inezgranada9954
    @inezgranada9954 3 роки тому

    Is it possible to use conditional formatting: instead of

    • @TeachExcel
      @TeachExcel  3 роки тому +1

      You can do that for sure. Just make a working formula in the spreadsheet and once that is good, pop it in a conditional formatting rule.

  • @neishac.d7781
    @neishac.d7781 4 роки тому

    Hi ! I am looking to add a formula in my excel spreadsheet to flag/ highlight duplicate data; however, the duplicate data has a criteria (e.g, flag all rows where the name, date, and number are the same as another row) is this possible ?

    • @neishac.d7781
      @neishac.d7781 4 роки тому

      However, there is no specific starting criteria. I would like to upload my data to excel and have it automatically flag rows that are duplicative based on the criteria mentioned above

    • @TeachExcel
      @TeachExcel  4 роки тому

      Ask this in our forum and upload a sample file. Depending on implementation, this could be done many different ways. But, my instinct tells me that you'll want to use a macro.
      Forum: www.teachexcel.com/talk/microsoft-office?src=yt_reply

  • @adityakumardhuria5280
    @adityakumardhuria5280 5 років тому

    i have a series of number in a cell let say 1,2,3,4,5,6,7,8,9,10,11,12 . now i want to separate them by giving certain values like if there are 1,6,10 these three should come out separately in different cell , 2,7,11 these three come together out in different cell , and 5,9 comes in different cell, similarly 3,4 separately and 8,12 separately. i mean i want a output like this 1,6, 10 2,7,11 5,9 3,4 8,12 . please help how to do it with a formula.

    • @Allinused
      @Allinused 3 роки тому

      =left(a1;3) =right(a1;3) might help? I dont know about the middle.

  • @vivekk9232
    @vivekk9232 4 місяці тому

    1:20

  • @dereje71
    @dereje71 Рік тому

    Please prepare free material for Ethiopian

  • @pankajgolakiya9666
    @pankajgolakiya9666 3 роки тому

    How to formulate present perfect sentences to conditional sentences? For example....
    Situation one ...
    (1)I have just eaten so, I can't go out with you for dinner....how to formulate this present perfect sentense(I have just eaten)
    If I did not eat, I would go out with you for dinner. Or
    If I had not eaten, I would go out with you for dinner.
    Any teacher has never discribed about how to convert conditional from present perfect....they always discuss about past situation but what about recent past which has connection with present....can we use second conditional or we have to use past perfect in those types of situations....please guide about recent past which has connection with present.....second situation
    (2)He has killed his wife so police will arrest him.
    If he did not kill(or had not killed)his wife, police would not arrest him.
    Situation three...
    (3)He has killed his wife so Police has arrested him.
    If did not kill( or had not killed) his wife, Police would not have arrested him.
    As every one says Second conditional is used for 'present' and 'future'.....dose the 'present' mean 'present simple' or' present perfect'?
    Third conditional used for past tense.does it mean 'past simple' and 'resent past(present perfect)?...
    Please guide me I am very eager to know your answer..

  • @dereje71
    @dereje71 Рік тому

    10Q

  • @fahadhameed-ne2dj
    @fahadhameed-ne2dj Рік тому

    SO MUC COMPLICATED DINT UNDERSTAND ANYTHING AFTER 5MINS ALL THIS HUGE LONG FORMULAS

  • @hbtuoputhmebaitrrhho1842
    @hbtuoputhmebaitrrhho1842 2 роки тому

    text