How To Create A Toggle In Excel 2016

A toggle button allows selecting from two options: They are like on/off switches or Play/Pause buttons. In this guide, we're going to show you how to insert a toggle in Excel.

Download Workbook

Excel supports toggle buttons as development tools.You can assign them into a cell to return a logical value (TRUE/FALSE) and use them in your workbook without any actual programming involved.

Developer Tab and Toggle Button command

The command can be found under the Developer tab, which is not active in Excel by default. This tab contains macro tools and other advanced controls, like toggle buttons.

If you are missing this tab, follow the steps below.

Open Excel Options under File.
Select Customize Ribbon.
Find and enable the toggle button for Developer in the list box on the right.
Click OK button to see the tab in the Ribbon.

Inserting a toggle button

Once the Developer tab is visible, you can find the Buttoncommand under the Insert button in the Controls section. When you click the Insert button, you will see the Toggle Button command under the ActiveX Controls section.

How to insert a toggle in Excel 03 - Ribbon

Clicking the Toggle Button changes the cursor into a plus. Click anywhere to insert a default toggle button, or hold and drag the cursor to define the toggle button size.

If you only need this control as a visual element, you can start to use your toggle button right away. To bind the toggle button value to a cell, you need to exit the Design Mode. Design Mode is a state where you can insert and modify ActiveX Controls. This is a toggle button, so click on the Design Mode button to exit.

A toggle button in Excel returns a Boolean value based on its state: Checked = TRUE, Unchecked = FALSE.

Binding a toggle button to a cell

Enter Design Mode.
Right-click on your toggle button.
Select Properties.
Type in cell reference into LinkedCell in the Properties window and press Enter.
Close the Properties.
Click Design Mode to turn it off.

After binding, you can see the value of the toggle button in the cell.

Modifying and multiplying after inserting toggle buttons

We are going to go over some tips in this last section of inserting a toggle button guide. First, you can always change the default "ToggleButton1", "ToggleButton2", etc. names from the Properties window. Change the string for Caption property for the toggle labels.

Our second tip is for populating multiple toggle buttons. You can use Excel's drag and drop feature for copying a check box multiple times. However, you need to fit the toggle button into a cell first.

Use the circles around to adjust its size. You can also use Snap to Grid feature to fit object to cell.
Once the toggle button is in a cell, select the cell.
Copy it down to by using tiny square at bottom right, like copying cells.

insert a toggle

Automated button label

With a single line VBA code, you can program your toggle button label to show the status.

Enter Design Mode.
Right-click on your toggle button and select View Code.
In the VBA window copy and paste the following line between Private Sub ToggleButton1_Click() and End Sub lines:
Caption = IIf(ToggleButton1.Value, "On", "Off")
Return to Excel and turn off the Design Mode.