dis8 com

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

U.S. /

Search TechRepublicCXO Software Startups Cloud Data Center Mobile

SOFTWARE

10 cool ways to use Excel's conditional formatting feature By Susan Harkins in 10 Things, April 11, 2012, 7:06 AM PST

25

Excel's conditional formatting feature can do a whole lot more than many

35 Comments Save 7 11 4

More

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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 work book (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.

Editor's Picks

We-commerce: The sharing economy's uncertain path

'PayPal Mafia' redefined success in Silicon Valley

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

WHIT E PAPERS // FROM DELL SOFT WARE

A New Set of Network Security Challenges

This new IDG survey reveals optimism about the ability of next- generation firewalls to help IT balance

productivity and security. Download it today and find out more.

LEARN MORE

WEBCAST S // FROM HP & INT EL® XEON® PROCESSOR

HP Software-Defined Data Center Webinar

VIEW THIS NOW

DOWNLOADS // FROM BROCADE

You're A Few Steps Away From Future-

How Aaron Levie and his childhood friends built Box

Conflict minerals funded a war that killed millions

White Papers, Webcasts, and Downloads

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

Proofing Your Career

DOWNLOAD NOW

WHIT E PAPERS // FROM FORGEROCK

A Strategic Approach to Identity Relationship Management

FIND OUT MORE

Cloud Computing Companies

Free Spyware Removal

Free Web Hosting

MS Excel

Image Formats

Don't Miss Our Latest Updates

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

Editor's Daily Picks Subscribe

Week in Review Subscribe

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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.

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

Figure O

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

Figure T

This rule compares two values.

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

Figure V

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

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

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

Recommended

value.

Full Bio // Contact // See all of Susan's content //

About Susan Harkins 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.

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

Why you keep failing at big data (hint... it's your vendors' fault)

New Site Exposes Anyone's Past Online! Sponsored

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

5 Internet of Things startups to watch

Newest | Oldest | Top Com m ents

Log In 50 people following

35 comments

+ Follow Conversation ShareAlso post to

Post comment as...

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

Like Reply

Like Reply

Aug 5, 20141of5kites 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

Apr 18, 2014Tina 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?

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

Like Reply

Like Reply

Like Reply

Mar 28, 2014daviddstubbs 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!

Feb 24, 2014smahank 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

Feb 24, 2014smahank Hi this tip is very useful

Nov 1, 2013david.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

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

Like Reply

Like Reply

the AccountNumber changes..., is that even possible?

Thanks in advance!

Oct 22, 2013rudzaoun 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!

Oct 7, 2013nou- Hi there,

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

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

Like Reply

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

Sep 13, 2013isoares 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

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

Like Reply

Like Reply

Like Reply

Aug 11, 2013jm.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).

Apr 25, 2013EleanorSugden 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!

Nov 28, 2012excel noob I'm either incredibly dense, or Excel is exceedingly poorly designed, but I've been struggling for half a day to

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

Like Reply

Like Reply

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.

Oct 4, 2013joziel1217 @excel noob

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

Mar 27, 2014MaggiePi @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

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

Like Reply

Like Reply

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!

Nov 5, 2012chuck Is there a formula to update a cell fill color when it is updated or changed?

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

Like Reply

Like Reply

Like Reply

Nov 6, 2012Suresh Mukhi Not exactly a formula but you could try the Track Changes command. It will highlight the row and column of changed cells.

Oct 19, 2012pgmtharakan 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

Sep 8, 2012kerrilu 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!

Sep 9, 2012Suresh Mukhi Yes the first one is possible. Just use

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

1 Like Reply

Like Reply

Like Reply

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.

Aug 6, 2012cab1060 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.

Aug 21, 2012Suresh Mukhi Like, what are your conditions?

Apr 17, 2012Standss 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

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

Like Reply

Like Reply

Like Reply

Like Reply

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

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

May 16, 2012jeffpattersonis 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,""))=A 1

May 18, 2012TobiF

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

Like Reply

1 Like Reply

Like Reply

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

Apr 16, 2012stn564 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?

May 2, 2012alan.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

May 3, 2012ssharkins Thanks Alan, I think conditional

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

Like Reply

Like Reply

Like Reply

2 Like Reply

formatting is underused -- it can do so much more than you might expect!

May 21, 2012ruggb 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.

Oct 4, 2012lacey.a Or just right click the screen and click 'convert to PDF' instead of doing all these other more complicated things.... :/

Apr 12, 2012TobiF 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.

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

Conversation powered by LiveFyreAdd your Comment

Like Reply

Like Reply

Like Reply

Apr 12, 2012Suresh Mukhi I agree. Excellent tips indeed! Thanks! Do these apply to Pivot Tables as well?

Jan 22, 2014lando666 @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.

Apr 11, 2012lehnerus2000 Excellent tips. :D

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

Re late d Ads

1 Brand New IPhone

2 Excel Help

3 Malware Removal

4 Free Spyware Removal

5 Mobile Security

6 Free Web Hosting

7 Image Formats

8 Cloud Computing Companies

Latest on Tech Pro Research

BYOD Policy

BYOD (Bring Your Own Device) Policy

BYOD spotlight: Mitigating risk and implementing policies and solutions

Explore

Blogs

Downloads

Q&A

Discussions

Meet the Team

Tech Pro Research

Resource Library

Services

About Us

Membership

Newsletters

RSS Feeds

Site Map

Site Help & Feedback

FAQ

pdfcrowd.comopen in browser PRO version Are you a developer? Try out the HTML to PDF API

© 2014 CBS Interactive. All rights reserved. Privacy Policy | Cookies | Ad Choice | Terms of Use | Mobile User Agreement

A ZDNet site | Visit other CBS Interactive sites: Select Site

Cybersecurity spotlight: The critical labor shortage Photos

Videos

Advertise

Reprint Policy