What is “Conditional Formatting”?

By using conditional formatting, we can format cells, based on the content of the cell.
In other words, by using “Conditional formatting” for the data in our table, we can visually analyze and detect possible problems.
An example: if the cell has a value less than 1,000, then the cell is colored red.

“Conditional Formatting” is found in the “Home” tab, “Styles” group.

The “Conditional Formatting” command, has been improved vastly in the “Excel 2007” version.
In earlier versions of Excel we were limited to a maximum of 3 conditions, in Excel 2007 there are no limits anymore.

Clicking on the “Conditional Formatting” unfolds a menu with various options.

The most commonly used rules are now placed in different groups:

“Highlight Cells Rules”, “Top / bottom rules”.
The different formats are divided into different groups, we can even add icons to our cells.

We can enter a “New Rule”,  “Clear rules”, and “Manage rules”, rules which allow us to delete and modify.
All these are discussed in the next lesson.

Highlight Cells Rules

“Highlight Cells Rules” is new in Excel 2007, and has rules for numbers, dates and text.

To give an example, you convert the results of your representatives in a table.

First select the range.
Select “Conditional Formatting” in the “Ribbon”.
Select “Highlight Cells Rules” and click “Greater than”.
The “Greater than” dialog appears.

Once this dialog box appears, our numbers are already given “Conditional formatting”. By default, this is a light red fill with dark red text.
You can manually enter a number in the space provided, or you can click the “Open” button, then you can select a cell in which the “Conditional formatting” is to be used..
We choose a format by clicking the downward pointing arrow, and click OK.

If you do not like any format , Click “Custom Format” in the bottom of the list.
This opens the “Format cells” dialog where you can set options for “Number”, “Font”, “Border”, and “Fill”.

The following conditional format is based on three rules:
Greater than 25,000 is green.
Less than 10,000 is red.
Between 10,000 and 25,000 is yellow.
Apply these rules accordingly.
“Conditional formatting” has really become very easy in “Excel 2007”.

Other options to “Highlight Cells Rules” are: “Equal to”, “Text that contains…”, “A date occuring …”, and “Duplicate values”.
All options except for text, date and duplicate values have the same dialog box, so I don’t think that I need to spend much time explaining these.

In the dialog box that appears when you select the “Text that contains…” option, specify the text or a portion of the text that must occur in the cell.

In the dialog that appears when you select the “A date occuring …” option, click the downward pointing arrow, and make a choice.
If the data you want is not on the list, you can always work with the “Greater than”, “Less Than” or “Equal to” commands.
Add a date, instead of a number in the space provided .

In the dialog box that appears when you select the “Duplicate values” option, we have the choice between duplicates or unique values.I think this does not need much explanation.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats