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
FALSE value (checked/not checked) to their linked cell. By combining a checkbox from the Forms toolbar with conditional formatting using the
Is option, you can turn conditional formatting on and off via a checkbox.
Figure. The Conditional Formatting dialog with the Formula Is option
When used in conjunction with a formula (such as the
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
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 the Figure, and click OK.
Figure. The Format Control dialog
When you select the checkbox floating over cell C1, it will return
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
Is (it will initially read
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.
Figure. 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
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 the Figure, select Formula Is (it now should read Cell Value Is). Then, in the Formula box, type the following formula:
Figure. 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.updated