spot_img

Checkbox

 

Insert a Checkbox | Link a Checkbox | Create a Checklist | Dynamic Chart | Delete Checkboxes | Powerful Checkboxes

Inserting a checkbox in Excel is easy. For example, use checkboxes to create a checklist or a dynamic chart. You can also insert a check mark symbol.

Insert a Checkbox

To insert a checkbox, execute the following steps.

1. On the Developer tab, in the Controls group, click Insert.

Click Insert

2. Click Check Box in the Form Controls section.

Insert a Checkbox

3. For example, draw a checkbox in cell B2.

Draw a Checkbox

4. To remove “Check Box 1”, right click the checkbox, click the text and delete it.

Checkbox in Excel

Link a Checkbox

To link a checkbox to a cell, execute the following steps.

1. Right click the checkbox and click Format Control.

Right Click, Format Control

2. Link the checkbox to cell C2.

Link Checkbox

3. Test the checkbox.

Test Checkbox

4. Hide column C.

5. For example, enter a simple IF function.

Checkbox True

6. Uncheck the checkbox.

Checkbox False

Note: read on for some cool examples.

Create a Checklist

To create a checklist, execute the following steps.

1. Draw a checkbox in cell B2.

2. Click on the lower right corner of cell B2 and drag it down to cell B11.

Copy Checkbox

3. Right click the first checkbox and click Format Control.

Format Control

4. Link the checkbox to the cell next to it (cell C2).

5. Repeat step 4 for the other checkboxes.

6. Insert a COUNTIF function to count the number of items packed.

COUNTIF function

7. Hide column C.

8. Insert an IF function that determines if you’re good to go.

IF function

9. Click all the checkboxes.

Checklist in Excel

Note: we created a conditional formatting rule to automatically change the background color of cell B16. Try it yourself. Download the Excel file and test the checklist (second sheet).

Dynamic Chart

Let’s take a look at one more cool example that uses checkboxes. A dynamic chart.

1. For example, create a combination chart with two data series (Rainy Days and Profit).

2. Add two checkboxes.

Chart with Checkboxes

3. Right click the first checkbox and click Format Control. Link this checkbox to cell B15.

4. Right click the second checkbox and click Format Control. Link this checkbox to cell C15.

Link Checkboxes

5. Uncheck the second checkbox. Cell C15 below changes to FALSE.

We’re now going to create two new data series.

6. Insert the IF function shown below. Use the fill handle to copy this formula down to cell F13.

7. Repeat this step for the new Profit data series.

New Data Series

Explanation: if the checkbox is checked, the old and new data series are the same. If the checkbox is unchecked, the new data series changes to a range with #N/A errors.

8. Use the new data series to create the combination chart. To achieve this, select the chart, right click, and then click Select Data.

Dynamic Chart

9. Uncheck the first checkbox and check the second checkbox.

Uncheck First Checkbox and Check Second Checkbox

Note: try it yourself. Download the Excel file and test the dynamic chart (third sheet).

Delete Checkboxes

To delete multiple checkboxes, execute the following steps.

1. Hold down CTRL and use the left mouse button to select multiple checkboxes.

Select Multiple Checkboxes

2. Press Delete.

Delete Checkboxes

Powerful Checkboxes

Finally, you can use VBA to create powerful checkboxes in Excel. Instead of inserting a Form control, simply insert an ActiveX control.

1. Insert a checkbox (ActiveX control).

2. At step 6, you can add your own code lines to automate all kinds of tasks. For example, add the following code lines to hide and unhide column F.

If CheckBox1.Value = True Then Columns(“F”).Hidden = True

If CheckBox1.Value = False Then Columns(“F”).Hidden = False

Use Checkbox to Hide Column

Use Checkbox to Unhide Column

Note: maybe coding is one step too far for you at this stage, but it shows you one of the many other powerful features Excel has to offer.

spot_img
Previous articleCapital Investment in Excel
Next articleActiveX Controls in Excel VBA