Conditional Formatting

Conditional Formatting was introduced with Excel 97 and is a terrific feature, but there is a limit of 3 conditional sets per cell grouping (like 3 wishes).  Conditional Formatting, while in effect for a cell, will override the text colors that can be produced for numeric values by normal cell formatting. 

The limit of 3 does not apply to the sheet but to the grouping of cells.  You cannot include a cell already in one conditional cell grouping in another. (refined definition 2001-03-27)

You can identify which cells have Conditional Formatting by using Edit, Goto, Conditional Formatting.  You can see what the Conditional Formatting is for a specific cell or a group by selecting the cell(s) and Format, Conditional Formatting, which will show the already existing Conditional Formatting.

The idea behind Conditional Formatting and filters for that matter is to test for a condition, which will provide either TRUE or FALSE.
  =1=1 will produce TRUE because 1=1
  =1=2 will produce FALSE because 1<>2  (not equal to)

Whatever is not covered with Conditional Formatting will be covered by normal cell formatting so if you want the default to be black italic you can format the column as such with normal cell formatting.

The format painter will both normal cell formatting and Conditional Formatting to additional cells.  The format painter is an optional button on the toolbar that looks like a paint brush.

Coloring date in a column based on today's date -- TODAY()

 

Select Column B Format --> Conditional Formatting     [add format] for condition 2     [add format] for condition 3 condition 1    Formula is: =AND(B1<TODAY(),B1>TODAY()-8) format: font is red and italic condition 2    Formula is: =AND(B11>TODAY(),B1<TODAY()+8) format font is blue and italic condition 3    Formula is: =B1=TODAY() format font is black and bold

A B C 1   Date Testing   2   03/16/2001   3   03/17/2001   4   03/18/2001   5   03/19/2001   6   03/20/2001   7   03/21/2001   8   03/22/2001   9   03/23/2001   10   03/24/2001   11   03/25/2001   12   03/26/2001   13   03/27/2001   14   03/28/2001   15   03/29/2001

A B C 1   Date Testing 16   03/30/2001   17   03/31/2001   18   04/01/2001   19   04/02/2001   20   04/03/2001   21   04/04/2001   22   04/05/2001   23   04/06/2001   24   04/07/2001

Color Row based on value in a Column

Select all cells (Ctrl+a)
menu: Format, Conditional Formatting, Formula is on drop down box
Condition 1:  =$D1<=1.00   [interior Color 35 Light Green]
Condition 2:  =$D1<=1.05   [interior Color 36 Light Yellow]
Condition 3:  =$D1>1.05     [interior Color 38 Rose]

Since the conditions are checked in order it is frequently unnecessary to have more than one comparison in a condition.  For example in Condition 2 above, we do not need to write the condition as
  =AND($D1>1.00,=$D1<-1.05)

You might for instance change the first condition to =AND($D1<=1.00,$D1>0)   so that normal cell formatting will be seen for zero and negative numbers.

 

	A	B	C	D	E
1	description	Col(B)	Col(C)	=B/C	=GetFormula(Dn)
2	description 1	33	200	0.165	=IF(ISERROR(B2/C2),"",B2/C2)
3	description 2	500	475	1.053	=IF(ISERROR(B3/C3),"",B3/C3)
4	description 3	105	100	1.050	=IF(ISERROR(B4/C4),"",B4/C4)
5	description 4	-5	4	(1.250)	=IF(ISERROR(B5/C5),"",B5/C5)
6	description 5	560	400	1.400	=IF(ISERROR(B6/C6),"",B6/C6)
7	description 6	75	160	0.469	=IF(ISERROR(B7/C7),"",B7/C7)
8	description 7	0	1	0.000	=IF(ISERROR(B8/C8),"",B8/C8)
9	description 8				=IF(ISERROR(B9/C9),"",B9/C9)
10	description 9		-1	0.000	=IF(ISERROR(B10/C10),"",B10/C10)

In order to not color the vast unused rows and cells without numbers the actual Conditional Formatting used in the above example is as follows:
Condition 1:  =AND(TRIM($D1)<>"",$D1<=1.00)
Condition 2:  =AND(TRIM($D1)<>"",$D1<=1.05)
Condition 3:  =AND(TRIM($D1)<>"",$D1>1.05,ISNUMBER($D1))

hint:  When filling in the formulas press F2 so that you can use backspace to correct mistakes, also a good idea when working on cells in a spreadsheet.

While setting up each condition before completion of that condition, you change the text color, interior color pattern, borders as desired.  Since interior color will wipe out gridlines, you might also want to include borders.

When a condition is removed, it is like it never existed. Conditional Formatting therefore will not leave garbage such as colored cells laying around once removed.  You may have a problem finding your Conditional Formatting though.

Multiple formatting

Select all cells (Ctrl+a)
menu: Format, Conditional Formatting, Formula is on drop down box
Condition 1:  =COUNTIF(A1:I1,"completed")>3   [interior Color 35 Light Green]
Condition 2:  value equal to: completed   [interior Color 36 Light Yellow]

If you wanted to be more specific with the C.F. you could to something like:

Select ALL cells for condition 1:
    format --> Conditional Formatting -->
    formula1 is: =SUM($c1="completed",$e1="completed",$g1="completed",$i1="completed")>=4

Select columns: C, E, G, I for condition 2:
    format --> Conditional Formatting -->
        Formula2 is: ="completed"

Color Banding

A B C 1 Prod.  Color  Price  2 AB0010 Black 13.50 3 AB0011 White 13.75 4 AB0012 Yellow 14.00 5 AB0013 Green 14.25 6 AB0021 Black 14.50 7 AB0022 White 14.75 8 AB0023 Yellow 15.00 9 AB0024 Green 15.25 10 AB0035 Black 15.50 11 AB0036 White 15.75 12 AC0010 Black 16.00 13 AC0011 White 16.25

A B C 1 Prod.  Color  Price  2 AB0010 Black 13.50 6 AB0021 Black 14.50 10 AB0035 Black 15.50 12 AC0010 Black 16.00 16 AC0021 Black 14.00 20 AC0035 Black 15.00 24 AD0010 Black 15.50 28 AD0021 Black 13.75 32 AD0035 Black 14.75   The above table is a representation of the results of Data, Filter, Auto Filter.  For more information on auto filtering in this example see my sumdata.htm page.

Additional information on Color Banding can be found in the Related Area at the bottom of this page.

More Notes on Conditional Formatting

 

Here is a nice summary presented by Leo Heuser 01-07-28, misc. Please note the use of mixed absolute ($$) and relative (no dollars) references. Select column B (click the B in the column bar) Choose Formats > Conditional formatting With "Condition 1" choose "Formula is" Enter this formula:  =COUNTIF($B$1:B1,B1)>1 Click the button "Formats" and choose a proper format. OK.OK In another column, C1, enter this formula:      =IF(COUNTIF($B$1:B1,B1)>1,"Number exists in row " & MATCH(B1,$B$1:B1,0);"") Drag C1 down with the fill handle (the little square in the lower right corner of the cell)		B C D 1 Name Leo's marking Peo's marking 2 Allen     3 Jack     4 Casey     5 Jack ... exists in row 3 ... exists in cell B3 6 David     7 Casey ... exists in row 4  ... exists in cell B4  8 Jerome      conditional format:  =COUNTIF($B$1:B1,B1)>1   Chip Pearson has specific information on Checking for Duplicates
alternate formula: Peo Sjoblom in the same thread. =IF(COUNTIF($B$1:B1,B1)>1,"Number exists in cell" &ADDRESS(MATCH(B1,$B$1:B1,0),COLUMN(B1),4),"")

Some specific examples, as they appear in newsgroups:

These examples tell what to select before invoking Format --> Conditional Formatting and all are Formula is, and after coding your formula, choose your format (pattern and font color), the first you see without selecting a tab is the FONT.  When choosing an interior color you might also choose the borders because gridlines disappear with interior color and the borders chosen will only exist while C.F. is in effect.

To get smallest half (rounded up) of the entries in bold, change the formatting formula to
    Formula is:  =A1<=SMALL($A1:$T1,(COUNTA(A$1:T$1)+1)/2)

Cells with Formulas:  You will need a User Defined Function for this and your UDF must be in the same workbook or referenced in your VBE (tools, reference). 
You cannot use =personal.xls!HasFormula(A1)  for instance. 
    Formula is:  =HasFormula(A1)

Function HasFormula(cell)
  HasFormula = cell.HasFormula  'in same  workbook  for C.Formatting
End Function

Alternative:  You can temporarily see which cells have formulas with Ctrl+A, Edit, GoTo, [Special], Formulas, (check all: Numbers, Text, Logicals, Errors)

Cells with Formulas -- without using a User Defined Function:  Ian Brown (2001-11-10)  Here's a way to do it without using VBA!  Select cell A1 on any sheet in the workbook, Define the name CellHasFormula (using Insert | Name | Define) as
    =GET.CELL(48,!A1)   -- In Format | Conditional Formatting use a "Formula Is" setting of
    =CellHasFormula       -- to trigger the defined formats.

Consecutive Duplicates:  To compare value in column A with value below, but not include the difference being blank or empty.  Select ALL cells then enter the following conditional formatting formula: 
=AND($A1<>"",OR($A1=$A2,OFFSET($A1,-1,0)=$A1))

Duplicated Anywhere in Column:  Need not be sorted
    Formula is:  =COUNTIF($A:$A,$A1)>1
    which, of course, is similar to using a cell in another column to convey the same information: =IF(COUNTIF($A$1:$A1,$A1)=1,"","Duplicate")

Highlight the top 4 numbers in a range -- can result in missing numers, if 4th is tied with 5th numbers if the the 4th highest number is tied
=A1>LARGE($B$2:$B$200,5)

Highlight the top 4 numbers in a range -- can result in duplicate numbers, if 4th is tied with 5th
=A1>=LARGE($B$2:$B$200,4)

Highlight the maximum in a specific column
=$C1=MAX($C:$C)

Highlight the maximum in each of specific columns -- preselect columns i.e. C:H
--no solution yet--

Finding each of several numbers in a table , Debra Dalgleish.  The example involved 100 people named in A2:A101 each with six numbers in columns B:H.  The numbers to be checked for are entered in $J2:$J$100.  To enter the Conditional Format, first select B2:H101 then for C.F. use formula is: =ISNUMBER(MATCH(B2,$J$2:$J$100,0)) and select a color on the Pattern tab.  Be sure to use absolute references for the arguments in J2:J100 in the C.F.

Finding Conditional Formatting Formulas afterwards

Function CondFormula(myCell, Optional cond As Long = 1) As String
  'Bernie Deitrick programming 2000-02-18, modified D.McR 2001-08-07, 2002-02-07
  Application.Volatile
  CondFormula = ""
  On Error Resume Next
  CondFormula = myCell.FormatConditions(cond).Formula1
End Function

Example:
Column A set up for conditional formatting
Formua is: $A1 > 3

G1:   =CondFormula(a1,1)
H1:   =CondFormula(a1,2)
I1:   =CondFormula(a1,3)

And then this still isn't much of a start if you want to know if the Conditional Formatting actually applies and want to know what resulting coloring or other formatting actually was applied.

If you used A1>3 you would see G1>3 in your G1 cell, so there are still some quirks in this.

Preparation of examples on this page

Conditional Formatting / Normal Formatting / Event macros

If you have more than 3 sets of conditions then you probably want to use an EVENT macro.

Hopefully this provided sufficient information to get you started.  It is a bit tricky at first.  Note the mixed reference $D1 meaning that on row 1 only the $H column will be checked.  This example is assuming that your active cell is on row 1 when you start.

If you do not want to color the entire row but only columns B, G:I for instance then only select those columns before you start doing your Conditional Formatting.  It will be harder to find any of your 3 Conditional Formatting though (warning).  See function later on this page.

It is easier to work with Conditional Formatting if you always work form all cells selected if you can work with having an entire row selected. However there are functions and subroutines to help you find them see my [sumdata.htm page. -- This will be included here.]

Normal Cell Formatting

Here is an example using the "Webdings 2" font. 

 

	A	B	C	D	E	F
2	wanted		value	=B	Format
3	green/circle		1	Ä	[Color50][=1]"Ä";[Color46][=2]"ã";[Red]"¢";[Color13]"³"
4	orange/asterisk		2	ã	[Color50][=1]"Ä";[Color46][=2]"ã";[Red]"¢";[Color13]"³"
5	red/block		3	¢	[Color50][=1]"Ä";[Color46][=2]"ã";[Red]"¢";[Color13]"³"
6	any text entry		A	³	[Color50][=1]"Ä";[Color46][=2]"ã";[Red]"¢";[Color13]"³"
7
8	wingdings 2	X	=	=	code: Alt+nnnn from numeric keypad
9		Ä	Ä	Ä	0196	Ä
10		³	³	³	0179	³
11		¢	¢	¢	0162	¢
12		ã	ã	ã	0227	ã
13		Ì	Ì	Ì	0204	Ì
14		Ó	Ó	Ó	0211	Ó
15		G	G	G	0071	G
16		H	H	H	0072	H
17		I	I	I	0073	I
18	Color	50	46	Red
19
20	Colors	http://www.mvps.org/dmcritchie/excel/colors.htm
21	symbols	http://www.mvps.org/dmcritchie/rexx/htm/symbols.htm

The following table and additional information can be found on my Colors page.

Related Areas

Older articles archived on former Deja are now available back to Mar 29, 1995.  Between May 2000 and mid April 2001 things had been bad with articles before 19May1999 missing since before 9July2000, see my Newsgroups page.

• Alternate-Row Shading in XL97 and above using filters. : =MOD(ROW(),2)=0   J.Walkenbach
• Alternate-Row Shading using a macro
• [broken link] AutoFilter, Display AutoFilter Criteria in Excel
  Steve Bullen's code to show the criteria actually in use when Autofilter is in Effect.  Article in PC World also has information on use of RANK when tied, and use of alternating stripes via conditional formatting.
• AutoFilter, Find Excel 2000 Data Quickly With AutoFilter -- MS Office, Update
• Color Palette -- the 56 Excel Colors
• Color Stripes, Range, Conditional Formatting, Banding, Greenbar stripes (also see autofilter)
  An example of coloring lines when the value in Column D (4th column) is negative. Some information on ranges. A quick look at conditional formatting available with XL97 and later.
• Color Banding With Conditional Formatting also separate topics C.F., and it's subtopic C.F., Using cells on other sheets.  -- Chip Pearson
• Color-Matching Blues  [http://www.zdnet.com/pcmag/issues/1507/pcmg0103.htm]-- f this link fails to work then search on the title.
• Combine rows, based on Column A posted by Patrick Molloy combines B column as comma separated values for rows that have same column A value (merging cells then deleting rows).
• Conditional Formatting, David Hager's "Excel Experts E-Letter Archives" letters 3, 8, and 14.  Letter #8 includes a Narrative on Conditional Formatting in a Word Document.
  (some techniques for those still on XL95 can be found at John Walkenbach's tip 25).
• Number Formatting, normal cell formatting should not be overlooked, it is easy to get carried away with Conditional Formatting that one loses track of the obvious.  Also see format, "About Worksheet Formatting" in HELP.
• Pivot Tables, in addition to Harald Staff's excellent Introduction To Pivot Tables already mentioned. 
  • Microsoft Excel PivotTable Resource Center
  • Tushar Mehta http://www.tushar-mehta.com in his RDBMS (Relational Database) area demonstrates relational database and then creates a Pivot table from the data base.  Suggest going through the RDBMS Tutorial without annoying frames.
• SUMIF Examples
• SUMIF, Tip 74:Summing and Counting Using Multiple Criteria
  using SUMIF, COUNTIF, SUM that should solve most counting and summing problems. -- John Walkenbach

Please send your comments concerning this web page to: David McRitchie mailto:DMcRitchie@msn.com