What is Checkbox in Excel?
A checkbox in excel is a square box used for presenting options (or choices) to the user to choose from. Usually, a selection is shown by the presence of a tick mark in the checkbox. The absence of the same indicates an option is deselected. A checkbox is inserted with the help of the Developer tab of Excel.
For example, an organization conducts a survey to measure the customer satisfaction level. The following questions appear in its questionnaire:
- How satisfied are you with our products and services?
Select one of the following options:
- Very satisfied
- Extremely unsatisfied
2. Select the products and services you like and are likely to recommend to others.
Select one or more of the following options:
- Product A
- Product B
- Service X
- Service Y
- Service Z
Every option of both the previous questions is preceded by a checkbox (not shown here). In the first question, the customer can select only one option at one time, while in the second question, multiple options can be selected.
The purpose of using checkboxes is to present a variety of predefined options to the user. Since excel checkboxes prevent the user from entering manual answers, data entry becomes easy.
Checkboxes are usually used in questionnaires, forms, feedback surveys, and so on. They are also used to create interactive checklists, reports, graphs, dashboards, and dynamic charts.
In a to-do list, the checkboxes in excel can be checked or unchecked to indicate whether a task has been completed or not respectively.
The Procedure to Enable the Developer tab
For inserting an excel checkbox, the first step is to enable the Developer tabDeveloper Tab Is Enabled In Your Excel Ribbon.Enabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu. on the Excel ribbon. Once enabled, it is visible, as shown in the following image.
The steps to enable the Developer tab are listed as follows:
- Go to the File tab of Excel.
- Click “options,” as shown in the following image.
- The “Excel options” window opens. Click the tab “customize ribbon.” In the second box (on the right), under “customize the ribbon,” select the checkbox of “developer.” Click “Ok.”
- The Developer tab appears on the Excel ribbon, as shown in the following image.
How to Insert a Checkbox in Excel?
Let us learn how to insert a checkbox and link it to a cell of Excel. Linking helps capture the current state of a checkbox (checked or unchecked).
A selected (checked) excel checkbox returns “true” in the linked cell. The “false” value appears in the linked cell if the checkbox is deselected (unchecked) or blank.
The steps to insert a checkbox and link it to a cell of Excel are listed as follows:
Step 1: In the Developer tab, click the “insert” drop-down in the “controls” group. Select “check box” under “form controlsForm Controls In ExcelExcel Form Controls are objects which can be inserted at any place in the worksheet to work with data and handle the data as specified. These controls are compatible with excel and can create a drop-down list in excel, list boxes, spinners, checkboxes, scroll bars..”
Step 2: Draw or insert the checkbox anywhere on the worksheet.
The checkbox appears with the label “check box 1,” which can be seen in the name box. This label will be visible (in cell C2) once the grey lines are dragged till the end of the text.
Step 3: Right-click the checkbox. Select “format control” from the context menu.
Step 4: The “format control” dialog box opens. Under the “control” tab, perform the following tasks:
- Select the “checked” option under “value.”
- Enter “$B$1” in the box to the right of “cell link.”
The same is shown in the following image.
Note 1: Either type “$B$1” manually or select the cell B1.
Note 2: The “checked” option under “value” displays a checkbox that is checked or selected. The “unchecked” option under “value” displays a checkbox that is unchecked or deselected.
Step 5: The checkbox insertion and linking are complete. The excel checkbox is linked to cell B1. So, selecting the checkbox shows “true” in cell B1.
Deselecting the checkbox shows “false” in cell B1.
Checkbox in Excel Examples
#1–Create an Interactive Checklist
To get married in a couple of months, one needs to carry out several tasks. It is essential to track all these tasks to ensure nothing is forgotten or missed. Create an interactive checklistChecklist In ExcelIn Excel, a checklist is a checkbox that represents whether or not a given task has been completed. Normally, the value returned by the checklist is either true or false, but we may improvise the results. For example, when the checklist is ticked, the result is true; when it is blank, the result is false. The insert option on the developer's tab allows you to insert a checklist. in Excel that shows the various tasks and their corresponding checkboxes.
In the final checklist, the completed tasks should be highlighted. Further, the “true” and “false” values (visible on the linking of cells) must be hidden.
The steps to create an interactive checklist with checkboxes in Excel are listed as follows:
Step 1: Create a checklist in Excel, as shown in the following image. The checklist shows the serial number and the tasks to be performed in columns A and B respectively.
Column C, which shows the status of the tasks, is currently blank.
Step 2: From the “insert” drop-down of the Developer tab, select “check box.” This is under “form controls.”
Step 3: Draw the checkbox in the “status” column (column C).
Step 4: Right-click the excel checkbox and select “edit text.” Delete the entire text displaying on the right side of the checkbox.
Step 5: Drag the checkbox to the remaining cells of column C.
Step 6: Right-click the first checkbox in cell C2. Select “format control.” In the “control” tab of the “format control” window, perform the following tasks:
- Select “unchecked” under “value.”
- Enter “$D$2” in the “cell link” box.
The same is shown in the following image.
Step 7: Link the checkbox in cell C3 to the cell D3. For this, perform the following tasks in the “control” tab of the “format control” window:
- Select “unchecked” under “value.”
- Enter “$D$3” in the box to the right of “cell link.”
Likewise, right-click every excel checkbox (in column C) and link it with the corresponding cell of column D.
Step 8: The checkboxes of column C have been linked with the corresponding cells of column D. We check or uncheck the checkboxes in excel for the given tasks randomly.
Accordingly, the “true” and “false” options appear in column D, as shown in the following image.
Every selected checkbox implies that the task has been completed. A deselected checkbox indicates the task is yet to complete.
Step 9: To highlight the completed tasks, apply conditional formatting. For this, perform the following tasks:
a. Select the range A2:C23.
b. Click the “conditional formatting” drop-down under the “styles” group of the Home tab.
c. Select “new rule.”
Step 10: The “new formatting rule” window opens. Select “use a formula to determine which cells to format” under “select a rule type.”
Enter the formula “=$D2=TRUE” (without the double quotation marks) under “format values where this formula is true.” The same is shown in the following image.
Step 11: Click “format.” From the “fill” tab of the “format cells” window, select the color for highlighting the “true” values. We select green. Click “Ok”.
Click “Ok” again in the “new formatting rule” window.
Step 12: All the tasks whose checkboxes are ticked, appear in green. Moreover, going forward, if a checkbox is selected, the entry will be highlighted in green.
Step 13: To hide the “true” and “false” values, select the range D2:D23 and press “Ctrl+1.” The “format cells” window opens, as shown in the following image.
Note: Alternatively, select column D and press “Ctrl+1.”
Step 14: In the “number” tab, select “custom” under “category.” Under “type,” enter three semicolons without any spaces, as shown in the following image. Click “Ok.”
Once the three semicolons are entered, the “true” under “sample” disappears.
Step 15: The “true” and “false” values are no longer visible. The final checklist is shown in the following image.
#2–Create an Interactive Chart
The following table shows the sales (in $) in all the quarters of years 2015-2018. Create three interactive Excel charts linked to checkboxes. The details of the charts are listed as follows:
- The first chart should be a dynamic column chart representing the sales figures of all the four years.
- The second chart should show stacked lines for the data of 2015-2017 and bars for the year 2018.
- The third chart should show a stacked line for the data of 2017 and bars for the year 2018. The sales figures of the years 2015 and 2016 must be omitted.
The steps to create interactive charts linked to checkboxes in excel are listed as follows:
Step 1: Insert one checkbox for one year. So, four checkboxes must be created. Name these checkboxes as “2015,” “2016,” “2017,” and “2018.”
Step 2: Link the checkbox of one year with one cell. The checkbox of 2015 is linked to cell B8, as shown in the following image.
The checkbox of 2016 is linked to cell B9. The same is shown in the following image.
The checkbox of 2017 is linked to cell B10, as shown in the following image.
The checkbox of 2018 is linked to cell B11. The same is shown in the following image.
Step 3: To link the data of the chart with the source data and the checkboxes, enter the following formula in cell B14.
If the value in cell B8 is “true” (implying the checkbox is selected), the value of cell B2 is picked. However, if the value in cell B8 is “false” (implying the checkbox is deselected), the “#N/A” error is returned.
Fill the range B14:E17 with the help of Ctrl+D (to fill downwards in the column) and Ctrl+R (to fill rightwards in the row).
With the given formula, any changes in the source data or any of the checkboxes will reflect in the range of the chart (B14:E17). Accordingly, the chart will update itself.
For instance, select only the checkboxes of 2016 and 2017. The output of the formula (with only 2016 and 2017 selected) is shown in the following image.
Step 4: Select all the four checkboxes. So, “true” appears in all the linked cells (B8, B9, B10, and B11).
Select the range B13:E17 and insert a column chartColumn ChartColumn chart is used to represent data in vertical columns. The height of the column represents the value for the specific data series in a chart, the column chart represents the comparison in the form of column from left to right.. Rename the legend as “2015,” “2016,” “2017,” and “2018.” For this, click “select data” from the Design tab. Edit each legend entry one by one.
Hence, the dynamic column chart representing the sales figures of all the four years appears, as shown in the following image.
Note 1: Alternatively, delete the word “year” from cell A13. Select the range A13:E17 and insert a column chart.
Note 2: To add a title to the chart, select the chart. Click “chart title” from the Layout tab. Select “centered overlay title” or “above chart.” Type the title. Alternatively, type the title directly (in the “chart title” box) in the newer versions of Excel.
Step 5: Select a column bar and change it to a stacked line chart. For this, click any column bar of 2015 (blue color) and select “change chart type” from the Design tab. Choose “stacked line” from the line charts.
Repeat this process for the bars of 2016 and 2017 as well. The bars of 2018 remain as is.
Hence, the chart showing stacked lines for 2015-2017 and bars for 2018 appears, as shown in the following image.
Step 6: Deselect the checkboxes of 2015 and 2016. The chart reflects the updated figures of the range B14:E17.
Hence, the chart showing stacked lines for 2017 and bars for 2018 appears, as shown in the following image. The sales figures of the years 2015 and 2016 are not shown. However, the legend is still showing the missing years.
Note: The legend can be revised to reflect the updated chart range. For this, click “select data” from the Design tab and remove the unwanted legend entries.
How to Delete a Checkbox in Excel?
To delete a single checkbox in excel, select it and press the delete key. For selecting a checkbox, hold the “Ctrl” key and press the left button of the mouse.
An alternative way of deleting checkboxes in excel is specified as follows:
Step 1: Click the “find and select” drop-down from the “editing” group of the Home tab. Select “selection pane,” as shown in the following image.
Step 2: The selection pane opens, as shown in the following image. It lists all the objects on the currently active worksheet including the checkboxes, shapes, and charts.
For deleting a checkbox in excel (or any other object), click the corresponding icon on the right side.
Note: The confusion while deleting can be avoided by assigning distinct names to all checkboxes from the beginning itself.
Frequently Asked Questions
An excel checkbox is a square box which helps select or deselect an option. Selection is indicated with the insertion of a tick mark in the checkbox. A blank checkbox indicates it has been deselected.
A checkbox helps track goals and tasks. For example, a project schedule can have checkboxes to indicate the completed tasks against the predetermined goals. This makes it easy to know the current status of every task. Moreover, one can assess the time left to reach the final stage of completion.
Checkboxes in excel are used to create dynamic charts, interactive checklists, graphs, reports, and so on. A checkbox is also known as a checkmark box or selection box. Checkboxes prevent users from entering unwanted answers in forms and questionnaires.
In Excel, the frame of the checkbox can be resized. However, the checkbox itself cannot be resized because its size is fixed. The steps to change the size of the object frame are listed as follows:
a. Right-click the checkbox whose frame is to be resized.
b. Select “format control” from the context menu.
c. In the “size” tab, set the desired size.
The frame of the checkbox is resized.
Note 1: To change the position of a checkbox, drag the four-pointed arrow. It moves the checkbox to the desired location on the worksheet.
Note 2: To fix the position of the checkbox, right-click it and select “format control.” Select “don’t move or size with cells” in the “properties” tab of the “format control” window. This prevents the checkbox from moving as the cells are resized.
The checkbox can be copied and pasted with the regular “Ctrl+C” and “Ctrl+V” shortcuts of Excel. The checkbox to be copied can be selected by right-clicking. Alternatively, the cell containing the checkbox can be copied and pasted at the desired location.
To fill a column downwards with a checkbox, press the shortcut “Ctrl+D.” Likewise, press “Ctrl+R” to fill a row rightwards with a checkbox. For these shortcuts to work, the preceding cell (immediately above or to the left) must contain a checkbox.
On copying and pasting a checkbox, the label appearing to the right of the box remains the same. However, the name in the name box changes with every new checkbox being pasted in the worksheet.
Note: All the pasted checkboxes are linked to the same cell as the first checkbox. To change the linked cell, every linkage needs to be changed manually and one-by-one.
This has been a guide to what is a checkbox in Excel. Here, we discuss how to insert a checkbox along with practical examples and downloadable templates. You may also look at these useful functions in Excel-