Conditional Formatting in Microsoft Excel

Conditional Formatting allows the user to change the format of a cell depending on the cell value. Select Format > Conditional Formatting and the following dialog box will appear.

You can have up to three conditions - the Cell Value Is for each condition can use one of the following operators:

Once you have decided upon your condition, you need to decide what format to apply - click Format... and then chose the desired format for your condition.

Formatting cells using the Cell Value Is criteria is easy - this tutorial will concentrate on the Formula Is criteria.

With the Formula Is criteria you can use any standard Excel worksheet formula, with the following exceptions: 

• The formula cannot reference a range in another worksheet or workbook - although it is possible to get around this by inserting a Name Range.
• You cannot use functions in an Add-In module. But you can use User Defined Functions (custom VBA functions) that are stored in the same workbook.

The formula that you enter must return a value that is either True (condition applied) or False (condition not applied).

If the formula you enter equals either text, a zero value or an error then this resolves to a False - if the formula equals any other value (positive or negative) then its value is True.

Example 1

To format a cell if the value in cell A1 is greater than 10 then type =A1>10 into your Formula Is criteria and then choose your formatting.

Example 2

To format a cell if the average value of the range A1:A10 is greater than 10 then type =AVERAGE(A1:A10)>10 into your Formula Is criteria and then choose your formatting.

Example 3

To format cell A1 if it contains an error type then =ISERROR(A1)=TRUE into your Formula Is criteria and then choose your formatting. You can use this method to hide errors by formatting your font color to match the background color.

Example 4

If you have a list of data in a column and would like to hide those cells that contain data that is the same as the data in the cell immediately above then type =A3=A2 into your Formula Is criteria - where A3 is the cell reference of the cell that you are formatting and A2 is the cell above. Set the Conditional Formatting so that the font color is the same as the background color and then copy this formatting down to all the other cells in your range.

Example 5

Lets say your range is A1:F10 and you would like to format all the cells that contain duplicate entries within this range. Type =COUNTIF($A$1:$F$10,A1)>1 into your Formula Is criteria for cell A1 and then copy this formatting to all the other cells in the range.

Example 6

To format upcoming expiry dates type =AND(A1-TODAY()>=0,A1-TODAY()<=30) into your Formula Is criteria - where A1 is the cell you are formatting. This criteria will format cells when the date value in the cell is between today's date and today's date plus 30 days.

Example 7

Suppose you would like to highlight all the cells in the worksheet that contain formulas. First we need to create a Name Range that contains the cells that we want to format - to do this select Insert > Name > Define. The Define Name Dialog Box appears -create a Name Range called HasFormula and then type =Get.Cell(48,INDIRECT("RC",FALSE)) into the refers to field.

Now select all the cells in the worksheet and set the Formula Is criteria to =HasFormula and then select your desired format. Any cell that starts with an = sign will have this formatting applied.

Example 8

Suppose that we have two lists of data one column wide (range A1:A10) and each list is in a separate worksheet. We want to compare both lists with each other and format those cells that do not have matching values in the other list.

Create a Name Range for each list and then type =ISNA(VLOOKUP(A1,NameRange,1,FALSE))=TRUE into your Formula Is criteria and then set your formatting. Copy the formatting down to the other cells in the range. Do this on both worksheets making sure that NameRange is the range containing data on the other worksheet.

Example 9

To format a date if it is a Saturday or Sunday type =IF(ISBLANK(A1)=TRUE,0,IF(WEEKDAY(A1)>1,IF(WEEKDAY(A1)=7,1,0),1))=1 into your Formula Is criteria and then set your formatting - assuming you are entering your date into cell A1.

This is the end of our tutorial on using Conditional Formatting in Microsoft Excel.

Return to Excel Exchange homepage.