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.
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.
|
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
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
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.
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).
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...
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
|
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
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
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)
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.