Lesson 26: How to use Checkbox in Excel

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

Join the channel Telegram belong to AnonyViet πŸ‘‰ Link πŸ‘ˆ

Lesson 26: How to use Checkbox in Excel

How to use Checkbox in Excel

First, open the Developer tab, in the Control section, select Insert.

Lesson 26: How to use Checkbox in Excel 28

Click Check Box in the Form Controls section.

Lesson 26: How to use Checkbox in Excel 29

I will insert a checkbox in cell B2.

Lesson 26: How to use Checkbox in Excel 30

To remove β€œCheck Box 1”, right-click the checkbox and click the text.

Lesson 26: How to use Checkbox in Excel 31

Checkbox link

To associate a checkbox with a cell, perform the following steps.

Right-click the checkbox and select Format Control.

Lesson 26: How to use Checkbox in Excel 32

Link the checkbox to cell C2.

Lesson 26: How to use Checkbox in Excel 33

Check checkbox.

Lesson 26: How to use Checkbox in Excel 34

Next, I will hide column C.

And in this example, I will use the IF function.

Lesson 26: How to use Checkbox in Excel 35

Uncheck the checkbox.

Lesson 26: How to use Checkbox in Excel 36

Explain a bit that cell C2 I have hidden, so you will not see, when selecting the checkbox, cell C2 will have the value True, so the if function in cell D2 will have the result 100. Conversely, when unchecking the checkbox, the cell C2 will have a value of False so cell D2 will have a result of 5.

How to create a Checklist

To create a checklist, you need to follow these steps.

First, insert the checkbox in cell B2.

Click the small square in the lower right corner in cell B2 and drag it down to cell B11.

Lesson 26: How to use Checkbox in Excel 37

Right-click the checkbox and select Format Control.

Lesson 26: How to use Checkbox in Excel 38

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

See more:  How to show the ruler bar in Word 2013, 2016, 2019...

Repeat the above action for all checkboxes.

Use the COUNTIF function to count selected checkboxes.

Lesson 26: How to use Checkbox in Excel 39

Hide column C. Then insert the IF function to check if all checkboxes are checked.

Lesson 26: How to use Checkbox in Excel 40

Select all checkboxes.

Lesson 26: How to use Checkbox in Excel 41

Note: I used conditional formatting to automatically change the background color of cell B16.

Dynamic chart

Now I will apply checkboxes and dynamic charts to show you the practicality of checkboxes.

First, create a combo chart with two data series rainy day and profit (Rainy Days and Profit).

Next, insert 2 checkboxes.

Lesson 26: How to use Checkbox in Excel 42

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

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

Lesson 26: How to use Checkbox in Excel 43

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

Now we will create two new data series by inserting the IF function below and copying this formula down to cell F13.

Repeat this step for the new Profit data.

Lesson 26: How to use Checkbox in Excel 44

Explanation: if the checkbox is checked, the old and new data series are the same. If the checkbox is not selected, the new data series will change to the range with the #N/A error.

Use the new data series to create a combo chart. To do so, select the chart, right-click and select Select Data.

Lesson 26: How to use Checkbox in Excel 45

Uncheck the first checkbox and select the second checkbox.

Lesson 26: How to use Checkbox in Excel 46

How to clear checkbox

To delete multiple checkboxes at once, you just need to follow these steps.

Hold down Control and select the checkboxes you want to delete.

Lesson 26: How to use Checkbox in Excel 47

Then press Delete.

Lesson 26: How to use Checkbox in Excel 48

The power of checkboxes

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

See more:  Lesson 225: Data Bars in Excel

First, insert checkbox (ActiveX control).

Next, add the following two code snippets to hide and show column F.

If CheckBox1.Value = True Then Columns("F").Hidden = True
If CheckBox1.Value = False Then Columns("F").Hidden = False

Lesson 26: How to use Checkbox in Excel 49

Lesson 26: How to use Checkbox in Excel 50

In addition, you can also see many other excel articles here.

The article won: 1/5 – (999 votes)

Source: Lesson 26: How to use Checkbox in Excel
– TechtipsnReview

, ,

Leave a Reply

Your email address will not be published. Required fields are marked *