Control Conditional Formatting with Checkboxes

Although conditional formatting is one of Excel's most powerful features, it's a nuisance to turn it on and off through the menus and dialog boxes of the GUI. Adding checkboxes to your worksheet that turn formatting on and off makes it much easier to read data in any way you want, whenever you want.

Conditional formatting, a feature available since Excel 97, applies formats to selected cells that meet criteria based on values or formulas you specify. Although conditional formatting is usually applied based on cell values, applying it based on formulas provides the flexibility to extend the conditional formatting interface all the way to the spreadsheet grid.

Setting Up Checkboxes for Conditional Formatting

The checkboxes from the Forms toolbar return either a TRUE or FALSE value (checked/not checked) to their linked cell. By combining a checkbox from the Forms toolbar with conditional formatting using the Formula Is option (shown in Figure 2-1), you can turn conditional formatting on and off via a checkbox.

Figure 2-1. The Conditional Formatting dialog with the Formula Is option

When used in conjunction with a formula (such as the Formula Is option), conditional formatting automatically formats a cell whenever the formula result returns TRUE. For this reason, any formula you use in this tutorial must return either TRUE or FALSE.

To see what we mean, try this simple example, which hides data via the use of conditional formatting and a checkbox. For this example, we will use the range $A$1:$A$10, filled consecutively with the numbers 1-10. To obtain a checkbox from the Forms toolbar, select View Toolbars Forms and click the checkbox, then click near cell C1 on your sheet to position the checkbox. Right-click the checkbox and select Format Control Control. Type C1 in the Cell Link box, as shown in Figure 2-2, and click OK.

Figure 2-2. The Format Control dialog

When you select the checkbox floating over cell C1, it will return TRUE or FALSE to cell C1. As you do not need to see these values, select cell C1 and change the font color to White.

Now select cells $A$1:$A$10, starting with A1. Select Format Conditional Formatting..., and then select Formula Is (it will initially read Cell Value Is). In the Formula box to the right, type =$C$1. Next, click the Format tab of the Conditional Formatting dialog, then the Font tab, and change the font color to White. Click OK, then OK again.

Select your checkbox so that it is checked, and the font color of the data in range $A$1:$A$10 automatically will change to white. Unchecking the checkbox will set it back to normal.

Toggling Number Highlighting On and Off

The ability to automatically highlight numbers that meet certain criteria can make it a lot easier to find the data you need in a spreadsheet. To do this, start by selecting cell E1 (or any other cell you prefer) and name this cell CheckBoxLink using the name box at the far left of the Formula toolbar (see Figure 2-3).

Figure 2-3. Cell E1 named CheckBoxLink

Add a checkbox from the Forms toolbar to a clean worksheet, call this sheet Checkboxes, and position it in cell A1. Set the cell link of this checkbox to the cell CheckBoxLink by right-clicking the checkbox and selecting Format Control... Control. Then type CheckBoxLink in the Cell Link box and click OK.

Right-click the checkbox again, select Edit Text, and enter the words Show Me. In column A on another worksheet, enter the numbers 25 to 2500 in increments of 25. Name this range Numbers and hide this sheet by selecting Format Sheet Hide.

To enter these numbers quickly, enter the number 25 in cell A1. Then, left-click the fill handle (which appears as a small black square at the bottom right of the selection) and, while holding down the left mouse button, drag down to about row 100. Now release the left mouse button, select Series from the pop-up shortcut menu, enter 25 as the step value, and enter 2500 as the stop value. Then click OK.

Select cell B1 of the Checkboxes worksheet and name this cell FirstNum. Select cell D1 and name this cell SecondNum. In cell C1, type the word AND. Now, select cell B1 (FirstNum), and press the Ctrl key while selecting cell D1 (SecondNum). Select Data Validation Settings, and from the Allow: box, select List, and in the Source: box, type =Numbers. Ensure that the In-Cell drop-down item is checked and then click OK. This will give you a drop-down list of numbers 25 through 2500 in both cells.

In cell A1, type the heading Amount. Immediately below this, fill the range A2:A20 with any numbers that fall between the range 25 and 2500. Select cells A2:A20 (ensuring that you start from cell A2 and that it is your active cell in the selection), and select Format Conditional Formatting....

In the dialog box that appears, shown in Figure 2-4, select Formula Is (it now should read Cell Value Is). Then, in the Formula box, type the following formula:

=AND($A2>=FirstNum,$A2<=SecondNum,CheckBoxLink)

Figure 2-4. The Conditional Formatting dialog box

Click the Format tab and set any desired formatting or combination of formatting. Click OK, and then click OK again to dismiss the dialog boxes. Change the font color for cell CheckBoxLink (E1) to White so that True or False will not show. From cell FirstNum (B1), select any number and then select another number higher than the first from cell SecondNum (D1).

Check the checkbox, and the conditional formatting you just set will be applied automatically to the numbers that fall between the range you specified earlier. Deselect the checkbox and the formatting will revert it to its default.

As you can see, by using a checkbox in combination with conditional formatting, you can do things most people would think is possible only through the use of VBA code.