I wanted to show one other thing helpful thing that Excel can do when you need to highlight data called conditional formatting. (Note: this tutorial is image heavy!)
Conditional formatting basically allows you to use colors, font styles, and borders to highlight cells that meet certain conditions. Unlike IF and other Excel functions, conditional formatting is found as a pull-down menu item in Excel rather than a function that you can type into a cell.
With the same salesman example I’ve been using in the previous tutorials on using the IF function, let’s look at how this would work. First, here’s the raw data, unformatted:

Before we go further, if you’d like to work through the examples yourself, here’s the raw data. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. Go to cell A1 (or another empty cell, if you want to put the data elsewhere), and then select “Edit” from the menu bar. Select “Paste Special” and then “Text” from the popup box. Click “OK”. The data should appear in your Excel worksheet just as it does above.
Bonus | 12% | |
Minimum amount | 100000 | |
Maximum amount | 200000 | |
Sales | Bonus | |
Salesman A | 87925 | No bonus |
Salesman B | 100000 | 12000 |
Salesman C | 145000 | 17400 |
Salesman D | 200750 | No bonus |
Salesman E | 178650 | 21438 |
Salesman F | 99555 | No bonus |
Salesman G | 147000 | 17640 |
Salesman H | 213450 | No bonus |
Salesman I | 122680 | 14721.6 |
Salesman J | 92500 | No bonus |
Ok, now back to the tutorial.
Suppose you wanted to highlight all the sales that were above the minimum amount of $100K green, and any that were below the minimum amount red. How would you do that?
Step 1: Select the range of cells you want the formatting applied to with your mouse. Just click on cell B6 and drag your mouse to B15.

Step 2: Under the “Format” menu item, choose “Conditional Formatting”.

Step 3: A pop-up menu will appear like this (click to enlarge).
Step 4: Fill in your menu with the conditions. In this case, we want two conditions. Under “Condition 1”, and “Cell Value Is”, we choose “greater than” from the drop down menu (click to enlarge).
Then we enter B2 into the blank by either typing it in or clicking on the box next to that menu item and selecting B2 using the mouse (click to enlarge).
Step 5: Click on the “Format” menu item and yet another pop-up menu appears called “Format Cells”.
Click on the “Patterns” tab and choose the bright green color (click to enlarge).
Then hit “OK”, and your first conditional formatting should be complete, like so (click to enlarge):
At this point, you might be done, if you have only one condition. If so, you’d hit “OK” again and watch as your cells are automatically formatted. However, we said we also wanted to highlight any sales that were below $100K, so we repeat the process above one more time. You can add up to 3 conditions to check for in a conditional format.
Step 6: Click on the “Add>>” button, and another set of conditions to fill out, called “Condition 2” appears (click to enlarge).
This time, we choose “less than” and cell B2 again as the criteria (click to enlarge).
Step 7: Click on the “Format” button, the “Patterns” tab, and choose red as your color (click to enlarge).
Then hit “OK”, and your second conditional format should be added like so (click to enlarge):
Step 8: Finally, hit OK again, and watch your cells automatically change their background color based on the data that’s in them.

Notice that cell B7 remains white, because the number is equal to $100K and doesn’t fit either of our conditions. If we wanted to, we could change one of the criteria in our conditional formats to include the “equal to” case.
The thing about conditional formatting is that once it’s applied, it stays with the cells until you delete the condition. So, for example, if you changed cell B8 to “1”, the cell would automatically change colors to red because 1 is less than 100K:

(The cell next to it, under “Bonus” also changed, but that’s because it contains a conditional IF equation from the previous tutorial.)
To remove conditional formatting, you have go through a few steps. First, select the cells for which you want to remove the formatting. Then, go back under the “Format” and then “Conditional Formatting” menu items and click on “Delete”. At this point, another pop-up menu appears, asking which condition(s) you want to remove. To remove all formatting in this case, you’d check the boxes by condition 1 and condition 2 (click to enlarge):
Then we click “OK” on the popup, “OK” again on the conditional formatting popup menu, and voilĂ , all turns back to normal:

One of the most useful ways I’ve seen conditional formatting used is in corporate finance, when Excel worksheets are sent around to be filled in by various people. You can set up sheets so that cells automatically change colors when certain criteria are met.
For example, suppose you’re trying to track a product’s cost. As long as a product’s cost is at or below target, the cell stays green. If the cost is over the target 5% or less, you want to highlight this yellow. If the cost is greater than 5% above target, you want to call this out using red.
Here’s a simple excel spreadsheet that demonstrates conditional formatting using that scenario as an example. In this case, someone who isn’t comfortable with using Excel can enter in the product’s current cost and see how far off target it is automatically. Feel free to download the spreadsheet, give it a try and see for yourself!
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
frank
One thing I don’t like about V/HLOOKUP is that the data needs to be sorted. I generally find myself using an OFFSET/MATCH combination to get stuff to work. Not as clean, but get’s the job done.
Ricemutt
Hey Franky: You should write some of these types of posts. You’ve got the expertise, and you’d be surprised at the interest there is out there on some Excel functions! :P
Steve
V/HLOOKUP doesn’t require data to be sorted if you set the last argument to False.
frank
I wrote about the pmt function and how to use it to build an amortization table for your loan. I’ve been thinking about writing some other ones, but it’s just a matter of time.
Steve, thanks for the tip. I thought it had to be sorted no matter what.
Mrfl
Thanks for the tutorial..it was well presented and easy to follow.
srisailam
Very happy to learn new things.thank you very much.
Nathan
Hey Frank. this really helped My Girlfriend out with her weekly status tracking worksheet. I was wondering if there was a way to change the target of a conditional statement. e.g. I want the color of cell A to change dependant upon the value of cell B. Instead of having Cell A be dependant on the value in Cell A.
Again, great tutorial. thank you
Chiranjeeva Rao
I want to know conditional formating for the following case:
I have a range of cells. If date is entered in the cell it should fill with green colour. If date is not filled it should be filled with red colour. The colour of the cell automatically change with date. How to do it in excel.
Alexander
Supposing today is January 28,2008
and i need to find out how many
mondays
tuesdays,wed,thu,fri,sat,sun
are there between Jan 1 to 28
How should i code it in excel? :)
Alison
If you want to do a conditional count you can use the countif() command. For example:
=Countif(array, “mondays”)
=Countif(array, “mon*”) =4″, “<4”, etc. for the second input if you are dealing with numbers
Note: Not case sensitive.
Alison
Nevermind, I reread your question and realized I answered a different one. Here’s a function to get you the number of mondays:
=FLOOR(([Day2]-[Day1]-WEEKDAY([Day1])+2)/7,1)+1
[Day1] would be a cell containing January 1,2008
[Day2] would be a cell containing January 28,2008
The 2 corresponds to Monday. (1: Sunday, 3: Tuesday, etc.)
Alfredo
This was great thanks. I recently learned how to use the INDEX/MATCH commands as an alternate to “vlookup” when the information you want as output is not to the right in the array (if that makes sense). Anyway, a much more eloquent explanation can be found here: http://www.mrexcel.com/tip021.shtml (not my website).
thanks ricemutt!
Freezebox
wow.. thanks man.. it helps me a lot..
Davies Sumaili
Excellent information
Char Fenske
I just wanted to THANK YOU for doing these excellent excel tutorials. I worked through the IF function, the IF, AND, and OR functions, and the CONDITIONAL formatting tutorials. It helped me immensely. Without your helpful tutorials, I probably couldn’t have solved what turned out to be rather simple.
May God Bless you for sharing!
S
Excellent , thank you
Gerald Celarta
Please send me samples on how to use conditional statement on getting the same value from a various numbers in a group
aureola
Electronic cameras have taken the world by storm and it’s not simply because
they look better. If you know that you have a really important shoot the next day,
make sure that you get ample rest. Hand development in film can be “pushed” to achieve
increases of film exposure as necessary.
mukhtar
please send excel programe.
mukhtar
send excel program.