Video Lesson: Conditional Formatting in Excel

Conditional Formatting can be very useful when presenting data in a spreadsheet, or for allowing information to jump out to you easily.

In the following video lesson I go over the following uses of it:

– Formatting based on numerical values (greater than, less than, between, etc).

– Formatting based on text contained in cells.

– Quick overview of Data Bars, Color Scales, and Icon Sets

– How to use custom formulas with Conditional Formatting to highlight entire rows or other cells (extremely useful / versatile).

Make sure to download the most recent Master Workbook to follow along!

Correction: The ‘Conditional Formatting’ option usually is located under the ‘Home’ tab and not the ‘View’ tab as referenced in the video.  That is due to my custom group that I have on my home tab.  To learn more about customizing your ribbon (aka the top menu area), see my Five Powerful Excel Tips video.  Sorry for any confusion, and thanks to Elizabeth in the comments for pointing it out!

83 thoughts on “Video Lesson: Conditional Formatting in Excel

  1. Hi Ben, thanks for doing these! Not sure why some people are complaining about free information… Anyways, I’m having a conditional formatting issue I was hoping you could help me with. I am trying to highlight rows where one column is greater than the other, for instance if O6 > M6 I want all of row 6 to be highlighted. I tried making a rule with the formula =$O6>$M6, but it is not working. Both are filled with numbers, but the numbers in M are from a formula, so I’m wondering if that might be the issue. Please let me know what you think. Thanks!

  2. Hi Ben, thank you for teaching excel. Is there any way where I can do conditional formatting where I need to highlight data using two conditions at a time?
    Let’s say I want to highlight data where participant’s age is between 25 – 30 yrs and they have a rating of 5.
    So, (1) In column ‘A’ I have age value. (2) In column ‘C’ I have rating value.


    • Hey Ravi,

      Yes, it is certainly something you can accomplish with conditional formatting. You’ll just need to use the AND() function to check for multiple criteria.

      In your case, we’ll actually use 3 conditional statements, as follows:

      =AND(A1>=25, A1<=30, C1=5)

      That will result in TRUE when all of those criteria are met, and if you include that under the 'New Rule' section of conditional formatting, it would do the formatting if the age is between 25-30 and the rating is 5.

      Hopefully that's understandable,but let me know if not.


      Ben C.

      • This is message to Ben
        I tried to copy exactly what you do and the formula you insert in the tab (=$h4=*y*), but no matter what, i have an error message. What do i do wrong??
        I can send you a print screen so you can look it. Pl provide email.

  3. Hello,
    I tried to copy exactly what you do and the formula you insert in the tab, but no matter what, the entire selected area becomes highlighted in yellow. What am I doing wrong here? I cant manage to highlight only the whished rows as you do.
    Thank you

  4. Pingback: 8 tricks to boost Excel productivity | Resultize

    • Let’s say you have days of the week in column J. =OR($J4=”Thursday”,$J4=”Sunday”,$J4=”Friday”)

  5. Hi I use Excel 2016 and it does not give me the option of conditional formatting. Please assist me with this issue as I found this tool very helpful.

    • I actually found the option now in the ‘HOME’ option instead of the ‘VIEW’ option. I need to thank you for your videos as they have been very very helpful.

  6. Hi,

    Thanks for the great tutorials. I was wondering how to highlight the rows but with dates greater than, less than, etc.? I wasn’t able to do that with any functions I tried. If you get to read this and answer, that would be so much appreciated, I need that function at my work a lot. 🙂

    • Hi Majuri,
      Try this with formula according to above example:
      The syntax is year, month, day.
      This should highlight all rows with a Signup Date of greater than December 31, 2010.


  8. I was not familiar with Excel and you make it so easy and fun. Great Lessons!!! Keep it Up!!! and Thank You for providing us with the Excel format to get hands on as you go through the lessons!!! Best Tutorial Ever!!!

  9. Great tutorial. I have a question though: If I want to show the highest achievement for students but the grades are in different units, for example some in percentages, some reported using a scale from 1 to 4 where 4 is excellent, some a scale of 1 to 7 where 7 is excellent. How would I show that.

  10. I can’t follow the lessons because the images are way too small. Your printed words are obscured by the size of my pixels. My monitor is 24 X 36 inches and it is still too tiny. Boo on you. What about all those people who have to use a magnifying glass and squint their eyes to see what you are talking about?

  11. Great presentation except for one thing: I have a very large monitor and your image of the spread sheet is so minute it is hard to see even with a 24 inch * 36 inch monitor. If I were to look at it on my regular computer screen it would look tiny and unreadable. You certainly have a broken scenario. You spend a lot of time and energy explaining and teaching something but forget to enlarge the image sufficiently to make it readable. Boo on you. I couldn’t follow any of it.

  12. For people using Macs getting lost creating a rule using a new formula: after selecting “New Rule,” under the drop down menu for “Style,” select “Classic.” In the first drop down under the “Style” selection, you can choose “Use a formula to determine which cells to format.” Hope that helps!

  13. Hi, thank you very much, this is an excellent tutorial! By the way, would you mind providing some after-lesson practice assignment after each video lesson?

  14. Hi,
    I have an question which is I am using conditional formatting in a row of D3:D12 with different colors. I want to calculate count of how many red, Green, Blue & yellow cells in the particular row. Please do the needful

    • 1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

      2. Click Insert > Module, and paste the following code in the Module Window.

      Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
      Dim rCell As Range
      Dim lCol As Long
      Dim vResult
      lCol = rColor.Interior.ColorIndex
      If SUM = True Then
      For Each rCell In rRange
      If rCell.Interior.ColorIndex = lCol Then
      vResult = WorksheetFunction.SUM(rCell, vResult)
      End If
      Next rCell
      For Each rCell In rRange
      If rCell.Interior.ColorIndex = lCol Then
      vResult = 1 + vResult
      End If
      Next rCell
      End If
      ColorFunction = vResult
      End Function

      3. Then save the code, and apply the following formula:

      Count the colored cells: =colorfunction(A,B:C,FALSE)

      Sum the colored cells: =colorfunction(A,B:C,TRUE)

      A: is the cell with the particular background color you want to calculate the count and sum.

      B:C: is the cell range where you want to calculate the count and sum.

    • Thank you! I’m learning about things that I’ve simply not used before because I didn’t know what the were for! Very beneficial!

  15. You really put together quite a few terrific items inside ur blog,
    “Video Lesson: Conditional Formatting in Excel | Free Excel Training!
    ”. I will remain heading back to ur web site eventually.

    With thanks ,Kassandra

  16. Hi,

    A while ago I worked on a spreadsheet where I had a list of shop names in the first column, then the next 8 columns were each allocated to a month and the data for each shop name in each month was either Over (shaded RED), Under (shaded BLUE) or N/A (no shading, where no data was available for that shop that month).

    I wanted to be able to highlight in RED any shop names that had 4+ months noted as Over, and in BLUE any shops that had 4+ months noted as Under but did not know how to do conditional formatting so scrolled down the list manually scanning and highlighting as I saw clumps of red or blue. There were hundreds!

    Is there a formula that might have worked to be able to “average” the 8 months for each shop name and decipher if it was mostly Over or mostly Under and highlighted the shop name automatically in the corresponding colour? Would I have somehow needed to create an extra column that kind of “averaged” out the Overs and Unders (not sure if you can average text?)

    Thanks so much for your help!

    All the best,

    P.S. I’ve been using your tutorials to improve my Excel skills and am finding them so valuable – thank you for sharing your knowledge!!!

  17. Wondering if you have any tutorials for excel for Mac…the basics are the same, but there seem to be some slight differences when trying to create a new formatting rule. Any suggestions?

  18. I am doing these awesome lessons for my new job but I need to know how to get a work sheet or the master to work on with the instruction I keep downloading but it is only the video itself

    • you have to download a file with many tabs called:
      which contains a table of contents, the course content, links to all video tutorials and all the worksheets referenced in the tutorials, one for each tab. It also contains a useful list and explanation of all Excel functions.

  19. Love how you take your time, going step by step. It’s like i’m in a classroom. Thank you. Please, how do you delete repetitive words from rows. For example, i have a list of names, but i want to remove all the Mr., Mrs, Miss from the front of the names without doing them one at a time? Would appreciate a feedback. Thanks

  20. I just graduated with my BBS in Public Accounting. I had taken my excel courses at the very beginning of my studies. Since then, I havent been using excel much. So I remember the basics, but I don’t remember the formulas. I would have had such a huge problem if it were not for your workbook and videos. I thank you so much! Thanks to you I can now refresh myself on excel and start to focus on building my Accounting career. I truly appreciate all the hardwork you have put into this. It helps so much!!!

  21. In your example, I tried to use the date instead of the email, such as if someone last logged on after a certain date the cell would be highlighted. I used 1/2/2012 and “1/2/2012” and it highlighted the all of the names in Name. How should that be formated, date, text, or what. Great tutorial though 🙂

  22. Hi-

    Thank you so much for your wonderful tutorials. When I go in to do the formula formatting I do not have choice to select “use a formula.” Is this a mac/pc issue, am I not selecting the cells correctly or is this possibly related to a newer version of excel?

    Any thoughts would be much appreciated!

    Thanks so much!!

  23. Pingback: A Complete Introduction to Excel |

    • That is an excellent point, Elizabeth.

      This is due to the fact that I have a custom group on my ‘Home’ tab (which actually includes Conditional Formatting), so I modified my ribbon to include ‘Styles’ under the ‘View’ tab.

      I’ll make a note in the lesson that most people will find this under the ‘Home’ tab as you mentioned. Thanks for catching that!



  24. HI, why did you not have to put in the $ sign when you did the original rule for the high score, but you did for the Y/N column and in the AND formula?

  25. do you have any of such tutorials for MS ACCESS as well..?????? wish to have them with same excellence as you gave excel tutorials. would be waiting for your response…………

  26. Probably one of the most helpful tutorials on excel I have had in a very long time! Thank you so much! I am passing this site on to those around me

  27. good stuff. thanks for the tutorial. i need help with what i think is a super simple function, however, I can’t figure it out. how do i copy and paste cells with formulas and general text onto another workbook without the hidden rows and columns. i want to isolate only certain rows and columns (which I have hidden) and copy only those onto another workbook. everytime i try it, it copy’s both the hidden and non hidden rows and columns.

Post any comments, feedback or questions below!

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s