Excel Alerts
Conditional Formatting with Formulas
Conditional formatting with formulas offers power and
flexibility for your Excel reports. Here's how to get started.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

Excel offers two types of conditional formatting. Although
both types are very useful, one of them is more widely used than
the other…probably because more Excel users understand it.
The
mostused method conditionally formats a
range based on each cell's value. This type not
only can apply normal formats to cells, it also can display data
bars and icons.
The other type conditionally formats a range based on
formulas that can reference any cells. It's the type I'm going
to cover in this article.
(You can follow along by downloading the
Conditional Formatting With Formulas workbook here.)
To illustrate formatting with formulas, this figure shows a
simple Excel Table with two settings above it.
Suppose we want to set up conditional formatting so that each
day’s Profit cells are highlighted if any profit percentage is
less than the Day Alert Limit in cell E1,
And
suppose we want two more alerts when the total profit percentage
is less than the Total Alert Limit in cell E2.
This figure illustrates the effect of those three conditional
formats.
Actually, it also illustrates a fourth conditional format,
one that's not obvious. That one is about...
An Excel Formatting Bug
I used Tables in this example for two reasons. First they're
a great Excel feature, and most Excel users need to know more
about them.
Second, when we add additional days of data below the last
row of a Table, the Table expands automatically to include
that data. So, in actual practice, you probably would use a
Table to contain data like this.
But if you don't use Tables very much, when you download this
workbook you won't know about the "structured references" that
Tables use by default. That's why, in the cells shown, I used
formulas like...
C5: =SUM($C$7:$C$13)
...rather than structured references like...
C5: =SUM(Sales[Amount])
However, at this writing, Excel has a minor formatting bug:
Each time you add a new row to the bottom of a Table, Excel
removes all font formatting from cells that reference the
Table...but only when we use standard references, not structured
references.
That is, because I used standard references in the range
C5:E5, Excel removes the bold formatting from the range whenever
we add data. To get around this problem, I used conditional
formatting to restore the bold font.
I've notified person at Microsoft who's responsible for this
issue, and I'm sure the problem will be fixed eventually.
Now let's see how to set up each of the four conditional
formats using formulas...
How to Get Started with Your Conditional Formats
Here are the first steps to assign any conditional format
using formulas...
1. Select the range you want to format. For example, in the
figure above, you might select the range E7:E13.
2.
Choose, Home, Styles, Conditional Formatting to
display a context menu of formatting options. And then choose
New Rule from the list.
3. In the New Formatting Rule dialog, choose
Use a formula to determine which cells to format.
After you do so, the New Formatting Rule dialog will change
to display this version.
You can enter the formula and formats in either order, but
for this example, we'll deal with the formulas first.
How to Set Up the Formulas for Each Conditional Reference
Let's
look at the formulas that support each of the four conditional
formats used in this display. Then, after we look at the formulas, we'll look at the
formats.
The Column of Alerts
This is an easy formula to set up, if—and only if—you handle
one issue correctly.
Here's the formula that you'd enter in the New Formatting
Rule dialog above:
=E7/C7<$E$1
The conditional formats in each cell of the range E7:E13
first find the profit margin by dividing the Profit in the
current row by the Amount in the current row. And then the
formula returns TRUE if that profit margin percentage is less than the
value in cell E1.
But here's the critical thing: The references to cells E7 and
C7 are relative references. That is, they're relative
to another cell. But which cell? In fact...
First, when you look at the formula after you've entered and
closed the dialog, the formula will be relative to the topleft
cell of the range that uses the same setting for the conditional formats
applied to each cell in the range.
Second, and more importantly, when you enter your
formula, the references must be relative to your
active cell.
Therefore, for relative references—which are the references
without dollar signs in the formula above—always enter your formula as though you
were entering it in your active cell. If you do so, you'll be fine.
By the way, when I entered my formula, my active cell was
cell E7. But notice that the formula referenced that cell. If I
were entering a normal formula in cell E7, that formula would
have generated a circularcalculation error. But because
conditional formatting with formulas isn't part of normal
worksheet calculation, that's not a problem here.
The Single Alert Cell
Cell E5 turns pink if its profit margin is less than the
percentage in cell E2.
This singlecell conditional format uses an easy formula:
=$E$5/$C$5<$E$2
That is, if the overall profit margin is less than the Total
Alert Limit, display the alert.
The TwoCell Alert
This is an even easier one.
The formula that calculates the total profit margin for the
Table is simply...
E4: =$E$5/$C$5
...in the cell shown. So the formula for its
conditional format is merely...
=$E$4<$E$2
If you wanted to do so, you could calculate the profit
margin in your conditional format formula, much like I did for
the Single Alert Cell example.
The ThreeCell Bold Text
We want the range C5:E5 ALWAYS to have bold text. But
unfortunately, an Excel bug removes all normal font formatting in those
cells whenever Excel recalculates. We must therefore use conditional formatting to maintain
that bold font.
Therefore, the formula for the condtional formatting is easy.
We just enter...
=TRUE
...in the New Formatting Rule dialog. As required, this
formula always returns TRUE, because that's the value to
which the formula is set.
Now let's take a look at the formats that each of the four
ranges use...
How to Set Up the Formats for Each Condition
After you enter your formula in the New Formatting Rule
dialog, choose the Format button to specify the format to apply
when your formula returns a value of TRUE.
Excel
will launch the Format Cells dialog, which
allows you to assign the Number, Font,
Border, and Fill formats.
That is, unlike the normal Format Cells
dialog, Excel doesn't allow you to assign Alignment
or Protection formats when you use conditional
formats.
Warning...
After I enter my formula, I sometimes get in a hurry and
forget to specify a format. Therefore, when I create a
conditional format rule that doesn't seem to be working, I
always check whether I actually set a format for the rule in the first place.
Keep this in mind, because I guarantee you'll have the same
problem occasionally.
The Column of Alerts
In
these cells, I only set up a fill format. So in the
Format Cells dialog, choose the Fill
tab. Because I wanted the alert cells to be pink no matter what
color theme I might use, I first selected the bright red option
shown here. Then, to lighten the color, I chose More
Colors, which launched the Colors
dialog.
In
the Colors dialog, choose the Custom
tab.
To change the tint or shade of the color, move the
triangular control up or down to lighten or darken it.
I chose the color shown here, but you could choose any color
you want.
After you've assigned the Fill color, choose OK
repeatedly until you return to the Edit mode.
The Single Alert Cell
The format setting for the singlecell alert in cell E5 is
the same as the format setting for the column of alerts.
I assigned two separate conditional formats with this color for two
reasons. First, their formulas are different. And second, I want
to have the freedom to make cell E5 a different color from the
pink cells in the column of alerts.
The TwoCell Alert
I had two choices when I created the conditional format for
the twocell alert...so I went with the easiest approach.
I entered the "Alert!" text in cell D4 and the formula...
E4: =$E$5/$C$5
...in the cell shown. Then I assigned both cells a white
font...which turned the text and percentage invisible against the
white background.
Then, I assigned a conditional format that applies the bright
red fill color...which makes the white text and percentage
visible to the reader.
The only risk to using this approach is that some printers
won't hide white text on a white background completely. Instead, some printers will
print a lightgray outline of those white characters.
So here's the other approach...First hide the text and
percentage using a number format. To do so...
1. Select the two cells.
2. Press Ctrl+1 to launch the Format Cells dialog.
3. In the Custom Category of the Number tab, assign the
number format: ;;; (That is, enter three
semicolons.)
Second, in your conditional format setting, assign both a red
fill and a percentage number
format. That number format will format the percentage value correctly, and not
affect the text at all...other than causing Excel to ignore the
cell formatting of the three semicolons. This causes the
content of both cells to be displayed correctly.
The Three Cells with Bold Fonts
This is the easiest setting of all. In the Font
tab of the Format Cells dialog for your
conditionalformat rule, specify a bold font, and
then choose OK.
To get started with conditional formatting, you can
download the
Conditional Formatting With Formulas workbook here.
