The checkbox is a fantastic feature of Excel. Using these checkboxes, we can modify the cell values according to your desire. This article will demonstrate three suitable examples of what you can do with cell value if the checkbox is checked in Excel. If you are curious about it, download our practice workbook and follow us.
Things You Can Do with Cell Value If Checkbox Is Checked in Excel
To demonstrate the examples, we consider a dataset of 10 people. Their name is in the range of cell B5:B14. The checkboxes and their linked cells will be in columns C and D.
1. If Checkbox Is Checked Then Set Cell Value
In the first example, we will add a checkbox in Excel worksheet and use it to set our cell value. It means if the Excel checkbox is checked the cell will show the data, and vice-versa. The steps to complete this task are given as follows:
📌 Steps:
- First of all, go to the Developer tab. If you don’t have that, you have to enable the Developer tab on ribbon from Excel Options.
- After that, click on the drop-down arrow of the Insert command and choose the Check Box (Form Controls) option.
- You will see that the mouse cursor will change. Now, drag the cursor in cell C5, and you will get the check box.
- Then, keep the mouse cursor on the Checkbox and right-click on your mouse.
- A context menu list will appear.
- Afterward, choose the Format Control option.
- As a result, a small dialog box called Format Control will appear.
- Now, in the Control tab, change the option from Unchecked to Checked in the Value section.
- Moreover, set the Cell link. In our case, we choose cell D5.
- Finally, click OK.
- The current status of the checkbox will show in that cell.
- Similarly, follow the same procedure for the rest of the entities.
- After that, write down the following formula in cell E5 using the IF function. This formula will check the checkbox status and show the corresponding result.
=IF(D5,B5,"")
- Press Enter.
- At last, double-click on the Fill Handle icon to copy the formula up to cell E14.
- Now, if you check some boxes you will see we are getting the final employee list in column E.
Thus, we can say that our formula worked perfectly, and we are able to get the desired cell value if the checkbox is checked in Excel.
2. If Checkbox Is Checked Then Change Cell Value
In the following example, we are going to use the checkbox for changing our cell value. If the Excel checkbox is checked, then our current cell value will change, and it will be in another cell, and vice-versa. The procedure to complete this task is given below:
📌 Steps:
- First, go to the Developer tab. If you don’t have that, you have to enable the Developer tab from Excel Options.
- Then, click on the drop-down arrow of the Insert command and choose the Check Box (Form Controls) option.
- You will see that the mouse cursor will change. Now, drag the cursor in cell C5, and you will get the check box.
- After that, keep the mouse cursor on the Checkbox and right-click on your mouse.
- As a result, a context menu list will appear.
- Next, choose the Format Control option.
- A small dialog box called Format Control will appear.
- After that, in the Control tab, change the option from Unchecked to Checked in the Value option
- In addition, set the Cell link. For our dataset, we choose cell D5.
- Finally, click OK.
- The current status of the checkbox will show in that cell.
- Similarly, follow the same procedure for the rest of the entities.
- Now, write down the salary of the employees in the range of cells E5:E14.
- After that, write down the following formula in cell F5 using the IF function. This formula will check the checkbox status and show the corresponding result in this cell.
=IF(D5,E5*1.1,E5)
- Press Enter.
- Finally, double-click on the Fill Handle icon to copy the formula up to cell F14.
- Thus, if you check some boxes you will see that the bonus is added for the chosen employees.
At last, we can say that our formula worked precisely, and we can get the desired cell value if the checkbox is checked in Excel.
Read More: If Checkbox Is Checked Then Apply Formula in Excel
3. If Checkbox Is Checked Then Remove Cell Value
Now, we will use the checkbox to remove our cell value. It signifies that if the Excel checkbox is checked the cell will not show the data, and vice-versa. The step-by-step procedure to complete this task is given below:
📌 Steps:
- In the beginning, go to the Developer tab. If you don’t have that, you have to enable the Developer tab from Excel Options.
- Then, click on the drop-down arrow of the Insert command and choose the Check Box (Form Controls) option.
- Now, drag the cursor in cell C5, and you will get the check box.
- Afterward, keep the mouse cursor on the checkbox and right-click on your mouse.
- A context menu list will appear.
- Next, choose the Format Control option.
- As a result, a small dialog box called Format Control will appear.
- Now, in the Control tab, change the option from Unchecked to Checked in the Value option.
- Moreover, set the Cell link In our case, we choose cell D5.
- At last, click OK.
- The current status of the checkbox will show in that cell.
- Similarly, follow the same procedure for the rest of the entities.
- Now, write down the following formula in cell E5 using the IF function. This formula will check the checkbox status and show the corresponding result.
=IF(D5," ",B5)
- Press Enter.
- Finally, double-click on the Fill Handle icon to copy the formula up to cell E14.
- Now, if you check some boxes you will see that the values are disappeared from the cells.
Hence, we can say that our formula worked successfully, and we are able to remove cell values if the checkbox is checked in Excel.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and that you will be able to get the desired cell value if the checkbox is checked in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
