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

### Like this:

Like Loading...

*Related*

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!

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

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

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.

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

Pingback: 8 tricks to boost Excel productivity | Resultize

This Was Really Awesome. Thank You!

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?

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

🙂

Pretty good tutorial and easy to learn. Thanks 🙂

Awesome. Thank you!

THANK YOU 🙂

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.

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.

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

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

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.

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

Thank you! this was a good lesson!

Thanks! Great work! May Triple Gem Bless you!

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

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?

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

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.

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

Thank you, big help

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

Thank you very much Ben .. Really really helpful.

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!

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?

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

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

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

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.

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

Thanks very useful

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!

thanks very much

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

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

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?

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:

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.

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

This is just the best!

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

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 https://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 🙂

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

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 🙂

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

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

excellent thanx a lot God bless 🙂

Something easy things seems to be difficult 🙂

I always find excel.difficult.why?

Excellent, Thank you

Your Excel training is Excellent

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

Pingback: A Complete Introduction to Excel | MBAx.me

Thank u…excellent job……..:)

Great tutorials – very easy to follow.

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

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.

this is the best excel tutorial ever!!!

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

Good observation Elizabeth…

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?

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

keep it up… excellent education spreading

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

I like this, highly educational site. 🙂

Very helpful – thanks SO much!!

Very helpful tutorials…thank you 🙂

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

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

Thanks you…Hope to see more updates

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.

THANK YOU!!! Glad to see more updates!

Excellent tutorial, thanks so much for making these!