Software

10 cool ways to use Excel's conditional formatting feature

Excel's conditional formatting feature can do a whole lot more than many people realize. Susan Harkins runs through 10 practical ways to put this tool to work.

Formatting, such as currency, alignment, and color, determines how Excel displays a value. But conditional formatting is more flexible, applying specified formatting only when certain conditions are met. Here are some creative ways you can push conditional formatting beyond its expected uses.

This article assumes a basic knowledge of Excel's conditional formatting feature. In Excel 2007 and 2010, Conditional Formatting is in the Styles group on the Home tab. In Excel 2003, Conditional Formatting is on the Format menu.

Note: This list is also available as a photo gallery, and you can download a sample workbook (xls and xlsx format) that includes examples of the techniques described here.

1: Distinguish business rule violations

Using conditional formatting, you can visually discern when something is breaking a business rule. For example, Figure A shows a simple timekeeping sheet that highlights a workday that's greater than eight hours. Why? Because your organization requires approval for anything over an eight-hour day.

Figure A

Alert users to overtime hours requiring approval.
Working with the time values complicates things a bit, as you can see in Figure B. This solution uses >.34 to represent time values greater than eight hours, which will work in most cases -- you can't use the value 8 or even the time value 8:00. Or you could use the predefined Greater Than rule in Excel 2007 and 2010, which will automatically use the more accurate value of 0.333333....

Figure B

This formula returns True when a time value is greater than eight hours.

2: Display simple icons

Using conditional formatting (in 2007 and 2010), you can display icons that are often easier to interpret than the values they represent. For instance, a simple checkmark might be quicker to discern than the text value yes, on, true, and so on. Figure C shows an icon solution for the same rule violation in #1.

Figure C

Use icons instead of traditional formats to represent conditional rules.
First, select the values in column E and apply one of the default icon sets. Then, use Manage Rules to manipulate the results. Figure D shows the final settings (click Reverse Icon Order first).

Figure D

These settings display green and red icons to alert users to an overtime violation.

3: Highlight a row based on a single value

Filters are great for limiting what you see, but sometimes you want to compare records. When this is the case, conditional formats can distinguish records. Figure E shows a data set of products with a conditional format highlighting only Condiment records.

Figure E

You can distinguish records based on a single value.
Select the entire data range (not the column headings) so Excel can format the entire record (row). Figure F shows the formula-based settings. The $G2 component creates a relative address, which updates with each row: G4, G5, G6, and so on. When the value in the referenced cell equals the string "Condiment," Excel highlights the entire row.

Figure F

You can highlight a row by applying the conditional format to the entire data range instead of a single column.

4: Create a dynamic record highlight

Highlighting an entire record (#3) is convenient, but you might want the conditional format to be more... conditional. For instance, suppose you want users to choose the category on the fly, as shown in Figure G. First, use the Advanced Filter feature to copy a unique list to an out-of-the-way spot, as shown in Figure H. Then, use the Data Validation feature to create a list, also shown in Figure H.

Figure G

Let users choose the highlighted category from a validation list.

Figure H

You'll need a unique list and a validation list.
With the list in place, update the conditional format formula to reference the input list cell, as shown in Figure I. Instead of referencing a cell within the row, the formula references the validation list in B2. Selecting an item from the validation list updates the conditional formatting.

Figure I

This rule references the validation list instead of a literal string value.

5: Compare values

It's common to compare values. For instance, you might track inventory levels by comparing the stock on hand to a reorder level. Using conditional formatting, you can alert users when it's time to reorder, as shown in Figure J. Select the values you want to format -- in this case, that's B2:B46. (You could highlight the entire row or one of the inventory values.) Then, apply the format shown in Figure K.

Figure J

Highlighted items that are running low.

Figure K

This rule highlights the product in column A when the in stock value goes below the reorder level.

6: Compare lists

You can find discrepancies between two lists using a conditional formatting rule, as shown in Figure L. This rule, shown in Figure M, compares each value in column A to its counterpart in column B. If they're not the same, Excel highlights the value in column A. To highlight the values in column B instead, select the values in column B and update the rule formula to reference the values in column A.

Figure L

This rule highlights values in column A when they differ from the values in column B.

Figure M

Apply this rule to compare the two side-by-side lists.

7: Create alternating bands

Many sheets highlight every other row (banding) to improve readability. The Table feature offers several predefined formats that include bands, but you end up with a table instead of a plain data set, and that might not be what you want. When you don't want a table, use conditional formatting to create alternating bands, as shown in Figure N. The rule shown in Figure O highlights cells to achieve the alternate band effect.

Figure N

Banding can improve readability, but you'll probably want to highlight even or odd rows and not both, as shown here.

Figure O

Apply either of these rules to create an alternate band effect.

8: Find duplicates

To find duplicate values or records, you can use a filter, but conditional formatting can pinpoint duplicate values on the fly. For instance, the sheet in Figure P shows duplicate values in a single column. Select the values you want to format and apply the formula-based rule shown in Figure Q.

Figure P

Highlight duplicate values for a quick alert.

Figure Q

This rule highlights all occurrences of the same value.

To ignore the first occurrence and highlight only subsequent values, use this formula:

=COUNTIF($A$2:$A2,A2)>1
If you want to check for duplicate values across multiple columns, concatenate the values and apply a similar rule to the results, as shown in Figure R. You can also hide duplicates (which I don't always recommend) by selecting a font color that matches the sheet's background.

Figure R

The concatenating formula in column C finds duplicates across multiple columns.

You can use Excel 2007 and 2010's built-in Find Duplicates rule, but it has a few limitations. First, you don't have much flexibility with the actual formats. Second, this rule selects all duplicate values; you can't choose to ignore the first occurrence and highlight just the subsequent values, as this more flexible formula-based rule can.

9: Find discrepancies

Verifying data is an important task, and Excel's conditional formatting can help by alerting you to inconsistencies. Figure S shows a common accounting tool known as cross-footing -- the process of double-checking totals by comparing subtotals across rows and columns -- in cell F16. Adding the conditional format makes the discrepancy hard to miss when the two totaling values don't match. Select either of the cross-foot formulas and apply the rule shown in Figure T.

Figure S

Combine conditional formatting with cross-footing for a hard-to-miss alert.

Figure T

This rule compares two values.

10: Find the smallest or largest duplicate

It's easy to find the smallest or largest value using the predefined rule shown in Figure U, but you'll need a more complex rule to highlight the smallest or largest duplicate value. As you can see in Figure V, the value 3 is the smallest value in the column, but Excel highlights each occurrence of the value 5.

Figure U

This option finds the smallest or largest value in a range.

Figure V

A more complex rule highlights the smallest or largest duplicate value in a range.
This rule is unstable if you use normal referencing, so apply a range name to your data set before applying the conditional formatting rule. The rule shown in Figure W will highlight the value 3 in the range named List only if 3 is also a duplicate. To find the largest duplicate value, substitute the MIN() function with MAX().

Figure W

This rule ignores the smallest value unless it's also a duplicate value.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

35 comments
1of5kites
1of5kites

I have two columns. Column A is a value "A" or "F" (Actual/Forecast). Column B is a date "xx/xx/xx". I can figure out how to format column B based on Column A ="A". However, I need a more complex formula for if Column A=F.

Here is what I am looking for

If A=F and B<=Today() Highlight Red

If A=F and B>today()+7 Highlight Yellow

Please Advise:

John

Tina Thomas
Tina Thomas

I am putting together a project to do list using conditional formatting, I have chosen an excel template in Microsoft Office. The list needs to be date sensitive and I need it to be a red icon for overdue, yellow for within a week and green for more than a week away. I have the icon and the date in the same cell but the colours are not corresponding to the correct conditions. This is how the template was set I have not set these formulas myself. Can anybody help please?

daviddstubbs
daviddstubbs

Good Stuff!  Thanks!  Would like to light up a heading cell if any of the cells beneath contain an error (my problem is when cells are neither true nor false, i.e., #Value).  Thanks again!

smahank
smahank

Hi this tip are very useful please update more possible way to find the duplicate and unique values using conditional formatting

Thank you very much for posting this

smahank
smahank

Hi this tip is very useful

david.rowland
david.rowland

Hello,

I have a spreadsheet that's sorted by AccountNumber.  An AccountNumber typically occupies from 1 to 8 rows.  For readability, I'd like to toggle row highlighting on/off as the AccountNumber changes..., is that even possible?

Thanks in advance!

rudzaoun
rudzaoun

Hello,

I am trying to add a condition..

I have a cell that i have to update everyweek depending on the other cell that someone updates. So lets say cell B is updated by someone. And everyweek i have to update my cell C depending on what is B updated at.

Thing is, the cell is a list, that contains a text and a colour...

How can i let my C cell be the same as the B cell that gets updated (With text and colour)...

Thank you!

nou-
nou-

Hi there,


I'm trying to tidy up my spreadsheet and I'm having problem with FORMULAS in Conditional Formatting.

Can anyone tell me why Excel does not like my formula please?

=VLOOKUP(IF($F3="Euro III","NOx e3",IF($F3="Euro IV","NOx e4",IF($F3="Euro IV+","NOx e4",IF($F3="Euro V","NOx e5",IF($F3="Euro V EEV","NOx EEV",IF($F3="Euro VI","NOx e6","Unidentified")))))),Table4,3, FALSE)/Limits!$B$2>W3

The whole formula until (and except) the ">W3" gives a number, so I would like to compare that number with a value in cell W3 to highlight if I'm over the limit. I even tried to put number instead of words "Unidentified" and FALSE, but does not work as well:/


Is it also possible for Conditional Formatting to recognized highlighted cell and put a comment into other cell, e.g. if formula from above marks a cell in RED I want Excel to enter text "FAIL" in Status cell.


Thanks

isoares
isoares

Is there a conditional formatting formula that checks to see if formulas in a cell have been deleted and highlights/formats the cells if that is the case. The format is not based on the value of the cell, which is quite often the case. Thanks

jm.ten.brink
jm.ten.brink

Great tips, although they do not immediately solve my problem....

I want to make a conditional format rule that applies whenever an input in a cell (in text!) is equal to the contents of any of say three other cells. Example: name (=input) one of the 3 biggest cities in country X (all three possible answers are in a specific cell somewhere hidden).

EleanorSugden
EleanorSugden

These tips are great and saved in my favourites, but do you know how I can get excel to highlight cells where the vlookup formula returns #N/A? I want the "errored" cells to turn red so my users can easily spot the cells to work on, in the same way as those where I have a Countif with a return of 0 highlight in green. Thanks!

excel noob
excel noob

I'm either incredibly dense, or Excel is exceedingly poorly designed, but I've been struggling for half a day to find how I can colour cells that contain the same data the same colour. The problem is that my data isn't whole numbers or percentages - it's ranges (of age and income). So I'm trying to highlight every cell that has 18-24 the same colour, for example. Every single bit of help I've found is useless. I have tried the suggestions here, and the don't work - or I don't understand them. Could someone explain to me how to do this? And explain it without using the program language which I'm illiterate to? The talk of formulas and ranges and things goes over my head because I have no idea what it means. I just want to know what to click and what to fill in so I can get on with my work efficiently, rather than having to do every single box manually.

chuck
chuck

Is there a formula to update a cell fill color when it is updated or changed?

pgmtharakan
pgmtharakan

Amazing, but some problem arises from 10 cool ways. I am trying the discrepancies, but I didn't understand. Thanks once again for your valuable page. Mathew

kerrilu
kerrilu

Hi, I'm new, and just dabble with excel. Is there a way to make my classroom jobs calendar show every kid's name a different color? So each time it says Johnny, he's blue, Jill is always green, and so on. On a related note, is there a way to make excel randomly populate my 11 job categories with my 34 student names, without having a kid with 2 jobs in one week? Like just autofill the calendar with their names. I do it by hand, but I know there has to be a way. Thanks!

cab1060
cab1060

Can anyone assist with 3 color scale in conditional formatting? I have a spreadsheet that I need to assign conditional formatting to certain cells. I am using Green, Yellow, and Red for value ranges. Thank you.

Standss
Standss

Excellent tips. I have used conditional formatting in the past but this really shows how much more we can do. Will also share with others ... I came across a team member using a calculator to add numbers in an Excel spreadsheet displayed on his screen. Sanjay Outlook Tips for Your Business http://blog.standss.com

Proportal
Proportal

Excellent article. Can you please explain what you mean by 'unstable rule' in reference to 10: Find the smallest or largest duplicate?

stn564
stn564

I've used simple conditional formatting before now, but this is a very fine tutorial on what it is capable of and how to use it. Many thanks Susan. PS would be nice if it could be in PDF format?

TobiF
TobiF

A formula for alternating bands (number 7) can be much easier: =ISEVEN(ROW()) vs =ISODD(ROW()) Or, more generically: =MOD(ROW()),2) = 0, =MOD(ROW()),2) = 1 The second version may be adjusted to alternate more than 2 formats.

Suresh Mukhi
Suresh Mukhi

I agree. Excellent tips indeed! Thanks! Do these apply to Pivot Tables as well?

MaggiePi
MaggiePi

@excel noob Are you in Excel 2007? If it's older this may not be available, and newer it should be but the buttons may not be in the same spot. To create conditional formatting based on cell value, which is what it sounds like you are trying to do, try this. 

Click the column(s) or row(s) that contain the numbers you are looking for. You need to make sure the whole area you want included is highlighted when you start (that is your range). Then go to conditional formatting in the ribbon  (top bar in the Home tab) and choose the first one "Highlight Cell Rules" and then choose "Between." Type 18 in the first box and 24 in the second box and hit ok. 

That should do it. 

If you want to edit the values (18,24) later or change the color that it highlights them, go to Conditional Formatting and "Manage Rules." You may need to change the drop down option for "Show formatting rules for:" at the very top in order to display the rule you want to edit. Then you can select the rule and hit "Edit Rule."

I hope this helps! 

joziel1217
joziel1217

@excel noob 

Don't know if it would work, but how about listing "18-24" as a string instead of a number?

Suresh Mukhi
Suresh Mukhi

Not exactly a formula but you could try the Track Changes command. It will highlight the row and column of changed cells.

Suresh Mukhi
Suresh Mukhi

Yes the first one is possible. Just use conditional formatting to equate the cell to a color for each student. Click Format, then conditional format and put in the appropriate conditions. For your second issue, I think you would need a VBA Macro to do that.

ssharkins
ssharkins

I meant that used with a cell reference rather than a range name, it updates inconsistently and slowly.

lacey.a
lacey.a

Or just right click the screen and click 'convert to PDF' instead of doing all these other more complicated things.... :/

alan.farr
alan.farr

I took a screenshot myself - using "webscreencapture.exe" - available free here: http://www.webscreencapture.com/ , then used an image program to extract the part I wanted. Following that I made up a small webpage with easy links to the relevant parts of the image; thus it's easy to view offline. Yeah, the article is excellent. I've only ever used Conditional Formatting in a small way before. Thank you, Susan, for this. Alan

lando666
lando666

@Suresh Mukhi HI, I just can't figure out how to how to custom format my cells. I have around 20 employees. I want to type in the name and have the cell become a color assigned to them, so its easy to see their name on the schedule. i got it to work once then it won't work after that.

jeffpattersonis
jeffpattersonis

I know this works, but I don't know why. This seems to be counting the number of lists that are in list, which should be one. Why does Excel interpret the first "list" as the list, but the second "list" as the current cell? =MIN(IF(COUNTIF(list,list)>1,list,""))=A1

ruggb
ruggb

Why all the webscreencapture-- I just copy the article (selectively) and past it into Word. I can edit out comments that are unrelated, highlight points, etc.

ssharkins
ssharkins

Thanks Alan, I think conditional formatting is underused -- it can do so much more than you might expect!

TobiF
TobiF

The second argument to COUNTIF can only be a single value, i.e. a single cell.