As much as formatting does for image, Conditional Formatting enhances the effect with dynamic relevance. It automates outlier highlighting; it offers special formats, like traffic lights and icons; it makes a sheet interactive, responding to input or control values.
Conditions are input criteria that produce special formatting. See the section on Logical Formulas for more detail on Boolean conditions. In Conditional Formatting there are numerous preset conditions and condition builders, as well as a formula option, that are all worth exploring. I most often create custom formula rules, but occasionally use presets for convenience.
Select any data range and open the Conditional Formatting menu, Alt-H,L. The menu has five types of preset rules in two sections, which I’ll call highlighting rules and visualization rules. The third section of the menu has tools to create and manage rules.
Highlighting Rules
Highlighting rules highlight the data based on how they compare to a reference value. There are two groups of highlighting rules called Highlight Cells Rules and Top/Bottom Rules. Most of these rules have a default reference value which can be changed, and a default format with a short picklist and access to custom formatting with a slightly reduced set of the Format Cells dialog.
Highlight Cells Rules
These rules highlight cells by comparison to a static value, a cell or a formula. Within this group there are four numeric rules and three other rules.
The four numeric rules start you off with a calculated default value. When the rule is invoked with a numeric cell range selected, the selection is analyzed to default the middle value of the range, or the middle half of the range in the case of Between, as the rule reference. As a starting point this gives a quick and reasonable view of the data.
The default value can be overwritten with any value, or with a cell reference or a formula. If the data won’t change, a constant reference value can be used. To make it dynamic, change the value to a cell reference or a formula such as =AVERAGE(range).
The Text rule defaults the active cell value, if it is text, and can be overwritten with any text or cell reference. For example, this could be used with an input cell labeled “enter search text:” to highlight any cell containing the input string.
The Date rule has a picklist of ten periods relative to today; Yesterday, Today, Tomorrow, In the last 7 days, Last week, This week, Next week, Last month, This month, Next month. To customize further, use a “Between” rule. For example, to highlight the past year enter between =TODAY()-365 and =TODAY().
The Duplicate rule is a picklist with two options, duplicate values or unique values. These can be replicated or customized as a formula condition. For example, =COUNTIF(absolute range, relative active cell) returns how many of the active cell value are within the range. This count can then be made Boolean by =1 (unique cells), >1 (duplicates), etc.
The New Formatting Rule builder, Alt-H,L,N, adds more options to most rules. For example, in addition to duplicate or unique cells, by using the Rule builder or Rules Manager you can highlight blank or error cells.
Top/Bottom Rules
These rules are based on statistics of the data range selected. Even though the four Top/Bottom rules say “10”, that’s just the default and can be overwritten.
Above/Below Average rules have no input, but the New Formatting Rule builder, and Rules Manager, add the option of 1, 2 or 3 standard deviations in each direction.
Visualization Rules
We’ve seen how highlighting rules use conditions to select the cells to apply a format. With visualization rules, conditions select the format to apply to the cells. With highlighting rules, the formats available are a subset of standard cell formats. With visualization rules, a whole new set of formatting choices is introduced.
Color Scales use standard fill color with gradual shading between two or three colors; Data Bars and Icon Sets are unique to Conditional Formatting. The objective of visualization is to see the numbers without reading them; to give a stratified, visual summary, or “heat-map” of the data.
On the Alt-H,L Conditional Formatting menu, with the highlighting rules, you could get a preview of the rule before saving it, but you could not go back to the menu from each rule. In the visualization rules section, since there are no inputs, you can browse around all of the presets without ever closing the menu. Thus you can window-shop to settle on the look you want.
Select the data, open the menu, and mouse-hover over Data Bars to open the gallery. Hover over the gallery, watching the sheet change, then back to the menu, and do the same with Color Scales and Icon Sets. To use the keyboard, arrow down the menu, then right to and around each gallery; hit Esc to return to the menu.
Visualization presets use default thresholds to assign values to formats. For example, each traffic light color gets a third of the range. These thresholds and other options can be customized in the New Formatting Rule builder, or in the Rules Manager. Bars and Icons can be set to hide the cell value; Bar direction and negative axis can be changed; Icon order can be reversed, and each Icon can be customized.
In this section I have most often used Data Bars, sometimes Icon Sets. Over-use of visualization can look busy and detract from elegance, but tasteful use adds sizzle.
Custom Rules
I’ve noted above how the New Formatting Rule dialog builds on the preset rules with more customizing options. There are six options; the first contains all the visualization rules, the next four correspond to the highlighting rules. It is worth some time to browse each to see what it can do. Now I’ll describe the last option, the formula.
Formula conditions are not intuitive at first, but it’s what I use most often. With a formula, you can base the condition on a cell outside the format range, use hybrid range referencing, and more.
Boolean Conditions
As I’ve previously noted, conditions are Boolean, having only true or false state. When you select the formula option, the formula bar says “Format values where this formula is true:”. The word “true” is the clue that the formula must be Boolean. If you think about it, each of the preset conditions discussed can be expressed as true or false; the cell value is greater than the average, or not. Some combine several conditions; “if bottom third then red, else if mid third then yellow, else green.” Each “if” is true or false.
Boolean formulas are discussed further in the Logical Formulas section. For now, note that the formula itself does not need to return true or false in order to be evaluated as true or false. An equation that compares two values, such as =A=B, or =A>B, or =A<B can only be true or false. Math functions return numbers, but also evaluate as true or false when used as a condition; any non-zero number is true, zero is false. True/False can be converted to 1/0 by multiplying by 1. Either type of formula is a valid Boolean condition.
To practice condition formulas, start by replicating a preset. Say you have a sheet, A1:A20 is filled with numbers 1 to 20. Copy the range to C1, then format A1:A20 with the preset “Greater Than...”, which gives you the default threshold 10.5. Then format C1:C20 with the formula =C1>10.5, and select any highlighting format. Note that C1 is relative, and is the active cell when the formula is entered. You should see the same rows, 11 to 20 highlighted in both columns. Try others as needed to gain confidence, like “Above Average...” and =C1>AVERAGE($C$1:$C$20). Change some numbers in both ranges to verify that the conditions match.
Some common math functions used as Booleans are =COUNTIF (range, value) = “value exists in range”; =SUM(ref1, ref2) = “ref1 or ref2 (or both) are non-zero (presuming they are not negative of each other)”; =PRODUCT(ref1, ref2) = “ref1 and ref2 are both non-zero”.
Reference Type
Preset conditions apply to each cell in the selected range. This makes them mostly applicable to a single column. Even though they can be applied to multiple columns, this would only be appropriate if all the columns have similar ranges. For example, if you have several columns of indicators or percentages, you might want to highlight the same values in each column. Don’t be fooled by relative conditions like “Top 10%”, which applies to the whole range of selected data, not column by column.
Consider what kind of hybrid reference to use in the rule. For example, if you select a single column it makes sense that the reference is relative, so that any cell that meets the condition is formatted. For this you can use any preset condition or create your own. If you use a formula condition, enter the active cell in the formula, as a relative reference (no “$”). To check which is the active cell, it’s the one in the Name Box.
In a table, each row is a record of related data that should be treated as a unit, not column by column. But you can’t just select all columns and set a relative condition, it will evaluate each column based on all columns’ values, and you’ll have a mess. You want all columns, whether text or number, to be formatted based on a condition applied to a single column. You can do this with hybrid referencing to draw attention to the full record, resulting in a more elegant look.
The way to do this is to select the whole table and use a formula condition. In the formula, enter the cell with the evaluated column as absolute, and the active row as relative.
For example, say you have a ten-column table of demographic data, headers in row 1 and income in column G, and you want to highlight the rows where income meets certain criteria. Select all ten columns, with the active cell in row 2, then use a formula to compare $G2 to a calculated or constant value, such as = $G2 > 100000, or = $G2 > PERCENTILE ($G$2:$G$101,0.9). The result is that each row in which income meets the criterion is highlighted all the way across.
When writing formula conditions there are a few disadvantages. Table references and references to names scoped to other sheets are not accepted. To get around this, use Name Manager to create an alias name for the reference as a Boolean formula, then use the new name as the formatting condition.
In the example above using a table, I could create the name: InTop10Pct =Data[@Income]>= PERCENTILE (Data[Income],0.9). Then in Conditional Formatting I would select the whole table and enter the condition formula =InTop10Pct, with an appropriate fill or font effect. It is implicit that the name InTop10Pct is only relevant and valid within the Data table context.
If you have a sheet background format and several areas on it with different formats, changing the background can be cumbersome. You would need to select all ranges avoiding the foreground areas, or format the whole sheet, and reformat the foreground areas. Another way is to set the foreground formats in Conditional Formatting, with the condition =TRUE, that is, always on. Then you can format the whole sheet and the foreground formats will remain undisturbed.
With formula conditions, always be aware of the formula range reference in relation to the applies-to range. If the formula reference is relative, it should match the first cell in the applies-to range.
Rules Manager
Conditional Formatting rules are managed in the Conditional Formatting Rules Manager, Alt-H,L,R, an important tool to master. It gives you access to all the rules in the workbook, one sheet at a time, or only those in the current selection or table. Here you can maintain the conditions, the formats, and the ranges of individual rules, and the order and dominance between the rules.
When you first open the Rules Manager, check the “Show formatting rules for:”, and select or accept the default scope of the rules. The first rule is gray-selected. This means you can Alt+E to edit the rule, or Ctrl+↓ to change its priority.
To select any other rule, Tab or → to the toolbar, then use → or ↓ to move across the toolbar, and continue to the first rule and down the list of rules. Now the rule is blue-selected. This means you can Tab to the “Applies to” bar, and navigate the sheet to change the range, and Tab again to the “Stop If True” box, Space to set or unset. The next Tab will land on the OK button, or Shift+Tab to backup and select another rule.
Audit becomes important as the number of rules mounts. Rules Manager gives the main points, including a preview of the format. With a long list of rules it is worth maximizing the window, but this window exhibits some odd behavior. The double-click-the-header trick to maximize the window doesn’t work, like it does in Name Manager. Double-click on the top or bottom edge maximizes the height, and the sides or corners can be dragged to manually resize. I have also written a macro ListFormatConditions which collects formula conditions from all sheets and tabulates them, with some of the most commonly used formats listed and displayed. There are too many properties to list them all, but this helps if you are mainly using formula conditions.
Conditional Formatting becomes a powerful tool when combined with user interaction, making the interface dynamic and interesting. For example, when you click a button an area lights up, or when you complete a record it changes color, or when you click a checkbox select data are hidden. It can also provide some governance, with or without Data Validation, to help the user to recognize errors and missing inputs.
Comments