__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.