Microsoft Office Tutorials and References

In Depth Information

**Creating Formula-Based Rules**

The Trend column contains a formula that references the Change column. The column has the Show Icon Only option

applied, which also centers the icon in the column.

In some cases, using icon sets can cause your worksheet to look a bit cluttered. Displaying

an icon for every cell in a range might result in visual overload. For the example of the test

results table, you could hide the level (right pointing) arrows by clicking the down arrow

beside that cell in the Edit Formatting Rule dialog box and clicking No Cell Icon in the

palette that appears.

Creating Formula-Based Rules

Excel’s conditional formatting feature is versatile, but sometimes it’s just not quite versatile

enough. Fortunately, you can extend its versatility by writing conditional formatting

formulas.

The examples later in this section describe how to create conditional formatting formulas to:

Identify text entries.

■

Identify dates that fall on a weekend.

■

Format cells that are in odd-numbered rows or columns (for dynamic alternate row

or columns shading).

■

Format groups of rows (for example, shade every two groups of rows).

■

Display a sum only when all precedent cells contain values.

■

Some of these formulas may be useful to you. If not, they may inspire you to create other

conditional formatting formulas.

To specify conditional formatting based on a formula, select the cells and then choose

Home
➪
Styles
➪
Conditional Formatting
➪
New Rule. The New Formatting Rule dialog box

appears. Click the rule type Use a formula to determine which cells to format, and then

specify the formula. You can type the formula directly into the box or enter a reference to

a cell that contains a logical formula. As with normal Excel formulas, the formula you enter

here must begin with an equal sign (
=
). Click OK to ﬁ nish creating the rule.

19

The formula must be a logical formula that returns either
TRUE
or
FALSE
. If the formula evaluates to
TRUE
, the

condition is satisi ed and the conditional formatting is applied. If the formula evaluates to
FALSE
, the conditional

formatting is not applied.