Software

Show users how Conditional Formatting can dynamically update their Excel spreadsheets

The Microsoft Excel Conditional Formatting feature allows users to apply a set of conditions to a group of cells so that when data changes, the results' appearance changes. John Iosub describes how you and your users can use this handy feature.


With the Conditional Formatting feature, Microsoft Excel allows users to format automatically the contents of a cell based on a formula or set of formulas. This feature is very useful for highlighting certain areas of the worksheet based on cell contents that may dynamically change. For users who routinely format data sets, Conditional Formatting could boost their productivity tremendously. I’ll describe the Conditional Formatting feature so you can show users how to change the look of their data on the fly.

Conditional Formatting in a nutshell
Here’s a quick way to understand how this feature works. Select a blank cell in a worksheet. Go to Format | Conditional Formatting. Click on the first empty dialog box and enter any number. Next, click inside the last empty dialog box and enter a number higher than your first entry. Click the Format button. Make the font style bold and the color red. Click OK to accept this formula. Type in the cell numbers inside and outside the range you selected to see how the cell format changes.

Applying Conditional Formatting to a column of values
Applying Conditional Formatting to one cell is easy, but how do you apply it to a whole table of values that have multiple conditions? Let’s consider the table in Figure A.

Figure A


Our goal is to highlight the values in the Days Past Due column. We want to use bold, green formatting for invoices between 30 and 90 days old. We want to use bold, blue, single-underline formatting for invoices between 90 and 120 days old. The last condition is to have bold, red, double-underlined cells for invoices older than 120 days.

To apply Conditional Formatting, select the first cell in the range. Add the first condition just as in the “Conditional Formatting in a nutshell” example above. Use values of 30 and 89, and in the Format section, choose bold and green. For this table example, you’ll need to add more conditions to complete the formula. So, to add another condition, you would simply click on the Add button and use 90 and 119 as your values. Click Format and change the font style to bold, the underline to single, and the color to blue. Click Add again to add the third condition to your formula. Change the Condition field to Greater Than and use 120 as the value. Don’t forget to change the formatting to bold, red, and double underline. Selecting OK will accept the formula and apply it to the current cell.

Using Format Painter, copy the formatting by selecting the cell with the conditional formatting and clicking the Format Painter icon. Next, select the remaining cells in the Days Past Due column. Everything should look similar to the table in Figure B.

Figure B


To delete a condition, first select the range of cells. Go to Format | Conditional Formatting and use the Delete button.

Limitations of the Conditional Formatting feature
You probably noticed that you’re limited to three conditions when you use this feature. However, there is actually a fourth condition to consider when no conditions match. In this case, you can choose the formatting of the cell manually by selecting Format | Cells. In Figure B, an entire range of cells was selected and manually changed to white. Values less than 30 will “disappear,” while the other values are visible based on one of the three conditions.

Also, it’s possible to have formulas that would be true in multiple conditions. In this situation, the first true condition wins. Keep in mind that in cases where more than one condition is true, the order of the formulas is important.

Tip
Another limitation of the Conditional Formatting feature is that fonts cannot be changed. Besides style, underlining, and color, you can change only background color and borders.

Advanced Conditional Formatting
So far, we’ve used the conditions based on “cell value is…” combined with options such as equal, greater than, or less than. For more advanced Conditional Formatting techniques, there is a Formula Is option in the first drop-down list of the dialog box.

We’ll further format the table from our prior example, so that all rows will present similar behavior with the Days Past Due column. This is where understanding the Formula Is option becomes critical. We can use this option to format a cell based on values contained in fixed or adjacent cells.

In our example, cell D2 contains the first value in the range. We’ll start with cell C2, and then we’ll copy the formatting to the rest of the cells in the range. The first formula is =AND($D2>=120) with red, bold, double-underline formatting. The second formula is =AND($D2>=90,$D2<120) with bold, blue, single-underline formatting. The third formula is =AND($D2>=30,$D2<90) with bold, green formatting. The dollar sign in $D2 was used to preserve the column reference, because we’ll copy the formula to the left to other cells on the same row.

Tip
D2 means relative column + relative row reference. $D2 means absolute column + relative row reference. $D$2 means absolute column + absolute row reference. For example, if we copy the formula =AND($D2>=120) from C2 to B2, the formula stays the same because of the absolute column reference. If we copy the formula down to C3, the formula becomes =AND($D3>=120) because the row reference is relative.

Once the formulas are in place, we’ll use Format Painter again to copy the conditional formatting to the rest of the table. There is one problem, though, because along with the conditional formatting, all other formats (such as date or currency) are also applied. You can overcome this issue by selecting each column and applying the correct number format. To do so, select the Invoice Number column, right-click on the selection, and choose Format Cells from the menu. In the Number tab, select the Text category and choose OK. Repeat the sequence with the Invoice Amount, but this time change it to currency. Do the same with the Due Date column, only change its number format to the appropriate date style. The final look should be similar to the table in Figure C.

Figure C


When values change, the formatting will always respect the imposed conditions automatically without the need for user interaction or manual formatting.

Tip
In our example, we used constant values such as 30, 90, and 120 days. But such fixed values can be placed in cells elsewhere in the worksheet and then referenced in the Conditional Formatting formulas. This allows for future changes of such values without the requirement to change the Conditional Formatting formulas.

Get logical about Conditional Formatting
You may want to experiment by using more logical Excel functions with Conditional Formatting. These logical functions can be represented as:
  • AND—Returns true when ALL arguments are true. The AND function can have up to 30 arguments.
  • OR—Also has 30 arguments and will return true when any of the arguments is true.
  • IF—Has exactly three arguments. The syntax is =IF(test, value_if_true, value_if_false).

These logical functions can also be nested in complex structures that allow for more advanced conditional formatting.

Editor's Picks