Software

Use a formula to trigger Excel's Conditional Formatting feature

If you need a visual clue to hype a worksheet, use Excel's Conditional Formatting feature to help values stand out.

If you maintain inventory in an Excel worksheet, you probably need to know when stock runs low so you can reorder. Fortunately, you can let Excel warn you when an item is running low by applying a conditional format. You'll need at least two values: the current inventory and the reorder level.The simple worksheet (below) tracks the current inventory for three items and each item has a reorder amount. There are at least three ways Excel can alert you when inventory is running low for each item:

  • Highlight Current Inventory when it is less than the Reorder Level.
  • Highlight Item when Current Inventory is less than the Reorder Level.
  • Highlight the entire row when Current Inventory is less than the Reorder Level.

january2009blog1fig1.jpg

To highlight Current Inventory, do the following:

  1. Select cell B2 and choose Conditional Formatting from the Format menu. When applying this to your own worksheet, select the first value in the column (not the column's label text).
  2. In the resulting dialog box, choose Formula Is from the first control's dropdown list.
  3. Next, enter the following formula, =B2<=C2. In other words, when the value in B2 is less than or equal to the value in C2, apply the format.
  4. Click the Format button and select red from the Patterns tab, and click OK. january2009blog1fig2.jpg
  5. Click OK to close the Conditional Formatting dialog box. january2009blog1fig3.jpg
  6. With cell B2 still selected, click Format Painter.
  7. Select cells B3..B4 to apply the conditional format to the remaining items.

When the current inventory dips below (or is equal to) the reorder amount, Excel highlights that cell. With a quick glance, you can determine which items to order.

january2009blog1fig4.jpg

To highlight Item instead of Current Inventory, simply select cell A2 in step 1. You can highlight the entire row by selecting the entire row (A2..C2) in step 1. In step 3, enter the formula =$B2<=$C2. Then, in step 9, be sure to select the entire rows (A3..C4) when copying the conditional format.

january2009blog1fig5.jpg

january2009blog1fig6.jpg

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.

31 comments
cena23232
cena23232

i am working public relation department . i need sort out expire of visa example

column c2  01/01/2014 start visa expire  c100 10/8/2014 mixed date i filter 90 days this date visa expire can change color automatically highlight. what type of formula using conditional formatting in excel sheet 2010

cena23232
cena23232

i am working public relation department . i need sort out expire of visa example

column c2  01/01/2014 start visa expire  c100 10/8/2014 mixed date i filter 90 days this date visa expire can change color automatically highlight. what type of formula using conditional formatting in excel sheey 

Lost4now
Lost4now

I seem to remember that an older version of Excel allowed you to make the text blink or the field color blink when conditional formatting parameters were met. Can this be done in Excel 2003 and newer? I might be wrong, maybe it was another software???

john_r_l
john_r_l

I want to be able to format cells baced on the text in under the "Description of transaction" column. For Example, cells F8:F106 currently contain the description text. I want the conditional formatting to do the following: If any of trhe cells (F8 through F106)contain Preauthorized Credit US Treasury then format the corresponding row as follows: Highlight = bright green, Font = bold & date format= mm/dd/yyyy. To explain this further. Currently I have Preauthorized Credit US Treasury 310 Supp Sec in cells F8, F10, F23, F35, F48, F60, F68, F79, F86, F92 & F97. Therefore, cells; B8:J8, B10:J10, B23:J23, B35:J35, B48:J48, B60:J60, B68:J68, B79:J79, B86:J86, B92:J92 & B97:J97 should be Highlighted bright green & the font should be bold. Also, cells; E8, E10, E23, E35, E48, E60, E68, E79, E86, E92 & E97 should have the date formmatted as mm/dd/yyyy (6/30/2008). Is there a way that I can put the Condition Format using the If function in a cell outside the table (in cell K1, for example) so that it will check to see if text in the specified cells matches the requirements and then have it format the proper cells as described above?

cerVantage
cerVantage

I've used conditional formatting for years but never knew what the formula option was good for. Great tip!

ray2000t
ray2000t

How about the version is working ? Is it valided on 2003 or above ?

dba88
dba88

I must be blind, but I don't see anything that has "Formula Is." Which format menu are you refering to? I use Excel 2007. Thanks,

raymond.mccormick
raymond.mccormick

When I first read this I thought ???Wow!??? On further reading reference is made to only three formatting rules, still useful. I haven???t worked out how to apply more than one rule to a group of cells. What I want to do, in a block of cells, is to use different background colours for different ranges of a cell value, such as colour1 if the value is between 20 and 29, colour2 for 30 to 39 etc. Is this possible? I am using Excel 2000. Does that matter? Ray

eli.spitz
eli.spitz

Is conditional formatting still valid when using "R1C1 Reference Style", i.e. conditional row and column references? Are there any hints in this case, because I had some problems defining RC values in formulas. Thank you. eli.spitz@razlee.com .

mmoran
mmoran

I keep a spreadsheet to remind me of the calibration due date of several dozen electronic instruments. For each, three adjacent cells (for example K10, L10 and M10) show respectively the due date, the number of days until the due date, and a "monitor" which displays "OK" with a green background if the due date is more than 60 days in the future, "DUE" with a yellow background if the due date is within 60 days, and "OVERDUE" with a red background if calibration is past due. K10 contains the due date. L10 contains the formula: =DAYS360(TODAY(),K10) which calculates the difference in days between today and the due date, returning a positive number if K10 is in the future and a negative number if it is in the past. M10 contains the formula: =IF(L10>60, "OK", IF(L10

johns
johns

I use this daily for a list of job due dates. Due within a business week turns blue and past due turns red. Just have one cell =TODAY() for reference on your conditional formatting.

vincent.mcavoy
vincent.mcavoy

For Excel 2007, i use alt-O-D to get to Conditional Formatting Rules Manager. From there, you can create a new rule or edit an existing rule. You will then see the last of 6 options -- "Use a formula to determine which cells to format". Hope this helps.

j.j.lynn
j.j.lynn

Ray, I am using Excel 2003. The conditional formatting allows me to specify green background and white text for 60-90, yellow background with black text for 30-59, and red background with black text for 0-29. Is this sort of what you are thinking? jj

mike.mcburney
mike.mcburney

Ray, You can do what you are asking regarding different colors for different number ranges. In Excel 2000 you open the Conditional Formatting window by: 1. Select the first cell in the range you want to have the condition 2. Format menu > Conditional Formatting... For the example we will assume the following: The first cell in the range is B4 The entire range is B4:D6 3. Change where it says "Cell Value Is" and change it to "Formula Is". 4. Enter the following: =IF(B4 Copy 10. Select the range of cells you want this formatting to follow. 11. Go to Edit > Paste Special 12. Select "Formats" and click OK (the data will remain, only the formats will change). This should get you going. Hope this helps. Mike

mike.mcburney
mike.mcburney

Eli, For your scenario, are you talking about conditional formats used with values calculated/displayed by running a macro that uses relative references? If so, as long as the cell that holds the conditional format references the other cells (not over-written with a pasted cell) or if pasted data is for the values only and not formats (in VBA "Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False"), then the conditional formatting should work. Mike

ssharkins
ssharkins

Describe what you're trying to accomplish and share what you did and the results and let's see if we can't find a solution that works.

liljim
liljim

I've done spreadsheets for over 35 years now. Well, actually for the first 10 I was using paper and pencil. Back in the early days I'd often highlight critical data with a magic marker. Today you taught me something I'd completely overlooked. Now I can choose a highlight dynamically! I love it!!! This is a wonderful tip. Thank You and keep up the good work. There's something new to learn even now, after I already consider myself something of a wizard in the use of Excel. 73 -- N9ZLU The society which scorns excellence in plumbing because plumbing is a humble activity, and tolerates shoddiness in philosophy because philosophy is an exalted activity, will have neither good plumbing nor good philosophy. Neither its pipes nor its theories will hold water. -- John W. Gardner (1912-2002)

mike.mcburney
mike.mcburney

mmoran, You can simplify your formula into one cell by using one IF statement in the cell and 2 IF statements within conditional formatting: In cell L10: =IF((TODAY()-K10)0,TRUE) - select your desired formatting options In conditional formatting (condition 2): =IF((K10-TODAY())

kgunnIT
kgunnIT

I use this all the time, much the same as johns. For open invoices, they are blue, if I don't know the status (unknown) is orange, invoices that I need to send out are red, and date-specific invoices have different conditional formatting rules. For those that are coming up, they are highlighted, otherwise they are pink if more than a month away. My only issue with conditional formatting is that you can only have 3 formatting rules or less, no more than 3. In my case, invoices that are due monthly have seperate formatting rules than invoices that vary. Maybe some one can enlighten me to increasing number of conditional formatting rules.

raymond.mccormick
raymond.mccormick

Thank you for such easy to follow explicit instructions. It was the "Add>>" that I had missed when trying previously. I usually would want to apply this to a rectangular block of cells and I found that on these occasions I can apply the conditional formatting after selecting the block of cells. The problem with only three conditions is that I will need to think very carefully where the boundaries are. Thank you again, Ray

j.j.lynn
j.j.lynn

I have a spreadsheet to track renewals. Cell B10 has current end date. Cell C10 has the renewal PO number. Cell D10 has status: Complete, Submitted, In-work, Pending and Not Renewed. I have conditional formating for 1) >60 days, 2) 30-60 days, and 3) 0-30 days using today's date, the end of this month, the end of next month, and the end of the second month. Could this be expanded logically to include less than 0 days (Past Due) and a status other than "Complete"? You would only want to highlight maintenance you intend to renew, that has not yet been completed?

mmoran
mmoran

Works perfectly, Mike, thanks! Given that what I have now does what I need, I'll leave it as-is. But the next time I need similar functionality, I'll have some new options. Point taken on the number of rows/columns too, but in this case I'm the only one who accesses it (except read-only through a browser link) plus it's password-protected unless I'm actually working on it and backed up daily (lives on the server) so the risk is minimal. Mike

mike.mcburney
mike.mcburney

kgunnit, Unfortunately there really isn't a way to increase the number of formatting rules, but you can nest IF statements within the conditions. The result is that you can hold only 3 different formats (as there are only 3 conditions available), but have multiple reasons why one of those formats is to be used. The basics behind the IF statement within conditional formatting is very similar to an IF statement within a cell. The difference is that with a cell IF statement, if the condition is true or false you define the output as text, numbers or a formula. With the IF statements for conditional formatting, if the statement is true the format is applied, if the statement is flase it moves to the next condition until there are no other conditions listed. How to nest conditional IF statements: Basic Format: =IF(condition,true,false) The condition could be something like if the due date (for example showing in cell B3) equals today's date, then apply the formatting. eg: =IF((TODAY()-B3)>=0,TRUE,FALSE) To nest additional IF statements, replace "FALSE" in the example above with a second statement. Foe example, lets assume that you want the same format shown to flag an invoice to be reviewed if the invoice is due (using the example above) or if the customer was shorted on an item ordered on the invoice. Let's assume that the ordered quantity is in cell E4 and the shipped quantity is in cell E5. The formula would look something like this: =IF((TODAY()-B3)>=0,TRUE,IF((E5-E4)>0,TRUE,FALSE)) You can nest up to 7 IF statements within cells, so I assume this is the same with formatting. The best plan of action is to find out what the different conditions are that you want to use, group them into no more than 3 groups (as only 3 conditions are available) and nest those grouped arguments into IF statements. Hope this helps. Mike

j.j.lynn
j.j.lynn

Mike, The formula you recommended works as stated. It works very nicely. Thank you very kindly! jj

mike.mcburney
mike.mcburney

JJ, Change the "Red" condition to a formula something like the following: =AND((B6-B$2$)

j.j.lynn
j.j.lynn

Mike, The spreadsheet tracks maintenance renewals. We will either decide to renew the maintenance, or not. Assuming we desire to renew maintenance, the spreadsheet has conditional formating to indicate urgency: green, yellow or red. Green signifies time to start the renewal process. I approached this a little differently. Currently the spreadsheet has hidden rows which contain today's date ($B$2), the end of this month date ($B$3), the end of next month date ($B$4), and the end of the second month in the future date ($B$5). The non-hidden B column cells have the current end date. Similarly, the C column has 1) the PO number for the renewal, 2) "N/A" if it is not going to be renewed, or is blank if more than two months out. The D column has the status of the renewal: Pending, In Work, Submitted, Complete, or Not Renewed. The conditional formatting specifies: 1) cell value is between $B$2 ans $B$3, 2) cell value is between $B$4 and $B$3, or 3)cell value is between $B$5 and $B$4. Essentially it is red background with black text between today and the end of this month; yellow background with black text between the end of this month and the end of next month; or green back ground with white text between the end of next month and the end of the second month in the future. To convert to days, it would be red for 0-29 days; yellow for 30-59 days, and green for 60-90 days. This works fine as a tickler for within 90 days. The problem arises when a desired renewal goes less than 0 days and is not completed. If I change the conditional formating for red to be less than 0 days, then all renewals go red whether they are completed or not. The goal is to expand the logic to highlight in red only those renewals that are

mike.mcburney
mike.mcburney

JJ, I'm not sure if I am grasping your example properly in order to give you an answer that will meet your needs. The portion that I am having challenges understanding is the last sentence of the first pragraph regarding the conditional formatting specifically the relationship beween the conditional formats (1-3) using different dates. What's the purpose of these dates? Are these listed in different columns? Is it to flag when these items that are going to be coming due? As for having different options for when items are past due, you can nest up to 7 different IF statements together to come up with up to 8 different scenarios within one cell. Hopefully with some more details we can figure this out. Mike

kgunnIT
kgunnIT

Thanks for the tip...I think I found a solution. Previously, I was trying to set conditions for the entire sheet. I found that if I grouped my items together, then set conditions specific to each group, I can get the results I wanted. I have found that each cell is limited to 3 conditions max, but each cell can have different conditions. Thanks for the tip.