Setting Up Checkboxes for Conditional Formatting

The checkboxes from the Form Controls-found under the Developer tab by selecting Controls → Insert (Forms toolbar for pre-2007 versions)- return either a TRUE or FALSE value (checked/not checked) to their linked cell. By combining a checkbox from the Form Controls with conditional formatting using the "Use a formula to determine which cells to format" option (Formula Is in pre-2007 versions), you can turn conditional formatting on and off via a checkbox.

When used in conjunction with a formula (such as the "Use a formula to determine which cells to format" 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 Form Controls, go to the Developer Tab Controls options and select Insert (pre-2007, go to the Forms toolbar by selecting View → Toolbars → Forms) and click the checkbox, then click near cell C1 on your sheet to position the check. Right-click the checkbox and select Format Control → Control. Type C1 in the Cell Link box.

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.