In this lesson you will learn how to insert check box to the worksheet In Excel.
Why you need one?
Check box enables or disables a value indicating alternatives with a specific meaning. In the worksheet or in a group box you can tick more than one box at a time. The check box can be used, for example in the order form containing a list of available products or inventory tracking application to indicate whether a particular product has been discontinued.
Go to the Ribbon > Developer tab.
How to insert?
Click Insert and select a form control check box and place it on the sheet.
By right-clicking on a control you can edit the name of the button, the assigned macro, and other parameters. Click the Format Control.
You will see a window object formatting. Go to the tab control fill in the cell link pointing to a cell in the spreadsheet link eg C2. You can specify the cell link that will be updated with the checkbox status (either True or False), and set other options such as the checkbox size, font, and font color.
You connected a link to cell C2, which according to click on a checkbox appears logical value "TRUE" or "FALSE".
False
True
To handle such a logical value eg to display text information you can use IF function formula:
=IF(C2=TRUE,"AVAILABLE","UNAVAILABLE")
How to insert a chart with checkbox?
Let's use a knowledge and insert a chart with checkboxes. Let's start by creating a simple chart with the data.
In this chart, the main product1 is the most important, which must always be shown, and the others are optional.
Select the data and create a chart. Change the chart type to combo and make products 2 and 3 as line chart with secondary axis.
Finally, the chart will look like this, and products 2 and 3 will have a secondary axis on the right. As it has different values than the main product, it is better to show it on the secondary axis. Now we will create the checkboxes to show and unshow product 2 and product 3, as these are optional.
Let's define two names: series1 and series2 as follows.
Here is how to define the first name:
And the second one:
In cells F2 and F3, enter the following values as TRUE:
Create a checkbox and format controls as follows:
Link series to series names created:
We can now remove and re-add products 2 and 3 to the list using checkboxes.
Further reading: How to Add Dynamic Range to Chart? How to Break Chart’s column?