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 most-used 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.
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 the 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? Here’s what you need to remember:
When you enter your formulas in the New Formatting Rule dialog, your relative cell 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 circular-calculation 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 single-cell 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 Two-Cell 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 Three-Cell 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 single-cell 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 Two-Cell Alert
I had two choices when I created the conditional format for the two-cell 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 light-gray 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 semi-colons.)
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 semi-colons. 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 conditional-format rule, specify a bold font, and then choose OK.
