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).

FYI, the formula at time 7:30 is =\$H4=”Y”, it seems some people have trouble reading it.

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!

This entry was posted in Formatting and tagged , , , , . Bookmark the permalink.

88 Responses to Video Lesson: Conditional Formatting in Excel

1. Anonymous says:

Super useful, I have been following some lessons from your website. Now I feel a lot more confident using Excel. Thanks!

2. Anonymous says:

Hi Ben, Thank you so much. It’s really useful video.

• Ahmed Nur says:

Hello Ben,
This is excellent. I have just gone thru conditional formatting. It is of great importance. Usually, I repeat the process and redo it to confirm that I have understood.
This all is for free that is you are serving the nation. God bless you.

3. Evan says:

Hi Ben, Thank you so much for sharing!

I am having trouble on using the formula: =\$H4=*Y* that you describe in the video. It always appears a msg saying the formula has an error.

Can you help me with that?

• Anonymous says:

Hi. Make sure you highlight only the data not the title and your formula should look like this: =\$H4=”Y” You put * instead of ” Hope this helps

4. Jake S. says:

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!

• Jake S. says:

Nevermind, I figured it out. The \$ was throwing it off.

5. Ravi Prakash says:

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.

Thanks,
Ravi

• 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.

Cheers,

Ben C.
ExcelExposure.com

• Olga says:

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.

6. Stefan says:

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

7. This Was Really Awesome. Thank You!

8. I would like some help in identifying 3 days of the week with conditional formatting. Ex: I need Friday , thursday and sunday to be highlighted in the list. How can I do that?

• Anonymous says:

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

9. reena says:

Pretty good tutorial and easy to learn. Thanks 🙂

10. Anonymous says:

Awesome. Thank you!

11. Anonymous says:

THANK YOU 🙂

12. Rahul Koshy says:

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.

• Rahul Koshy says:

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.

13. Majuri says:

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:
=\$F4>DATE(2010,12,31)
The syntax is year, month, day.
This should highlight all rows with a Signup Date of greater than December 31, 2010.

14. stephanie says:

YOUR TUTORIALS ARE SO INFORMATIVE!! SERIOUSLY KEEP UP THE GOOD WORK, THIS IS SO USEFUL I’M SO EXCITED I STUMBLED UPON YOUR SITE

15. Anonymous says:

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!!!

16. Thandi says:

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.

17. chammy says:

Plz I hv bn trying to watch d videos but its ny coming up. ….I nid ur help cos I knw next to nothing about excel

18. Anonymous says:

Thank you! this was a good lesson!

19. Demin says:

Thanks! Great work! May Triple Gem Bless you!

20. Anonymous says:

Highlighting the whole row is very useful. Thanks so much!!!

21. Carl M. Dobbs says:

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?

• Anonymous says:

On some monitors when you press ctrl and the + sign it will zoom in, on the web page.

22. Anonymous says:

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.

23. Romane says:

Is there a conditional format to highlight if the cell has a formula in it vrs. typed #

24. Anonymous says:

Thank you, big help

25. Moka-asm says:

Thank you so much for your efforts, I liked excel before and now I love it.

26. Affan says:

Thank you very much Ben .. Really really helpful.

27. alexdickey says:

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!

28. jos says:

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?

29. Anonymous says:

I have been never seen excel tutorials like this. THANK YOU 🙂

30. lee says:

Hi,Do you have any similar tutorials for microsoft powerpoint 2010 ?

31. Arunkumar says:

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

• Josh says:

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
Else
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.

• Anonymous says:

nice !!!! must follow the site .. truly recommended 🙂

32. Anonymous says:

Thanks very useful

• Anonymous says:

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!

33. Anonymous says:

thanks very much

34. Heather says:

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

35. Anonymous says:

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,
L.

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!!!

36. Joshua Viner says:

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?

37. Anonymous says:

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

• Francesca Lucarino says:

excel-exposure-master-workbook-4-30-13.xlsx,
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.

38. Allan says:

Your lessons are clear and easy to replicate. It was very kind of you to post them. Thanks!

39. Anonymous says:

This is just the best!

40. Mary says:

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

• Ryan says:

I think I might be able to answer this one. Sorry about the delay to reply, I just found out about this awesome site! Anyways, as referenced in http://excelexposure.com/2012/10/02/excel-video-lesson-five-powerful-tips/ around the 12:00 mark. Highlight the area, go under the “data” tab, then “text to column” tab, select the “delimited” radio button, hit the “Next >” button, then select “Space”. This should do the trick 🙂

41. Kristy says:

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!!!

42. Tim Bertrand says:

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 🙂

43. Hpetey says:

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!!

44. ali says:

Thank you yours Excel training is so much helpful for everyone and easy to understand

45. Anushree says:

excellent thanx a lot God bless 🙂

46. Something easy things seems to be difficult 🙂

47. Kishor says:

I always find excel.difficult.why?

48. Anonymous says:

Excellent, Thank you

49. Dean says:

50. Thomas Jones says:

Thank You. People like you makes this world pleasent. God bless You

51. Anonymous says:

Thank u…excellent job……..:)

52. Fifi says:

Great tutorials – very easy to follow.

53. Anonymous says:

YOU ROCK. thanks for posting these. keep up the GREAT WORK.

54. Phillip Johnson says:

What if there is no Conditional Formatting option under the view tab??? Is there a way to get it. I tried the customize option and still didn’t see it.

55. Alma Qose says:

this is the best excel tutorial ever!!!

56. Elizabeth says:

Love these but, my conditional formatting option is found under the Home menu not View.

• 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!

Cheers,

Ben

• Naresh says:

Good observation Elizabeth…

57. amy says:

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?

58. Aanchal says:

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…………

• Sorry, Aacnhal, but I only cover Microsoft Excel in the course.

I’m sure there are plenty of similar resources online though. Good luck!

Cheers,

Ben

59. Anonymous says:

keep it up… excellent education spreading

60. pjthorman says:

Really helpful and delivered simply and effectively. Definitely something I’ll be able to use extensively in future.

61. Anonymous says:

I like this, highly educational site. 🙂

62. Anonymous says:

Very helpful – thanks SO much!!

63. Anonymous says:

64. Helga says:

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

65. shem says:

thanks very much……. may you be blessed for this commendable job.

66. Anonymous says:

Thanks you…Hope to see more updates

67. claire says:

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.

68. defrew says: