How to use a checkbox form control in Excel as a toggle switch:
- Click on the developer tab in the ribbon of Office Excel 2007
- Select insert
- Click on the checkbox icon under the form or activex controls
- Finally drag with the mouse in any cell on the worksheet to get a checkbox control.
- Tip: If you don’t see the developer tab, click on the office 2007 button on the top left of your Excel window, select ‘options…’ and then click ‘show developer tab in the ribbon’ under ‘top options for working with Excel’.
- Now right-click on the checkbox area
- In the format control window that pops up click inside the text box next to the ‘cell link’
- Type the cell address, for example, $A$1
- Under ‘value’ click the ‘checked’ option-button.
- Change the text next to the checkbox button from ‘checkbox1’ to an appropriate name of your choice like ‘turn conditional formatting off or on’.
- When you now click on the checkbox, you will observe ‘TRUE’ in the cell address and ‘FALSE’ when the checkbox is unchecked.
- Now select the data under ‘salary’ from b3 to b11 and then under the ‘home’ tab select ‘condtional formatting’, ‘manage rules’ and under the ‘conditional formatting rules manager’ window define a new rule as shown in the Excel training video.
As you can see you wish two conditions to be true: the value in cell A1 must be true and the salary must be greater than 10000 for any formatting to happen. Now when you check (TRUE) the checkbox button values greater than or equal to 10000 are formatted ‘blue’ and double underlined. When you uncheck (FALSE) the checkbox the conditional formatting of the data is not active. This can be of great convenience when you have formatted large amounts of data in different colors to get different important information from your data but you now no longer wish to display the formatting. You can also use the checkbox toggle switch during a presentation to make your point!