If Checkbox Is Checked Then Apply Formula in Excel (4 Methods)

After you’ve created a checkbox, you might want to know how to use formulas in checkboxes to apply the condition. You’ll need to know how to use checkboxes for financial analysis and interactive visualization. If the checkbox is checked then we will apply a formula to differentiate data in Excel. In this tutorial, we will show you how to do it.

How to Apply Formula If Checkbox Is Checked: 4 Effective Examples

In the sections below, we will add a checkbox in the Excel worksheet first and apply formulas based on their particular cell value. Later on, we will apply conditions for multiple checkboxes and visualize data according to our needs. To do the task, we will apply several functions such as the IF function and the COUNTIF function. Moreover, we will use the VBA code to do the same task.  In the below image, there is a data set that we will use to accomplish the task.

1.  Apply Formula Based on Cell Value If Checkbox Is Checked in Excel

Let’s say, you want to get the value as ‘ExcelDemy’ if the checkbox is checked. Follow the steps below to do this.

Step 1:

• Click on the Developer tab from the Ribbon.
• Then, click on the Insert.
• Select the Check Box.

Step 2:

• After creating a Checkbox, place it in a cell.
• Click on the right-click.
• Select the Format Control.

Step 3:

• Then, click on the Checked.
• In the Cell link box, link a cell by clicking the desired cell. We have linked cell C5 in our example.
• Press Enter.

• Therefore, it will show ‘TRUE’ when the Checkbox is checked and ‘FALSE’ when the checkbox is unchecked.

Step 4:

• Apply the following formula of the IF function. It will show ‘Exceldemy’ for the ‘TRUE’ return and will keep it blank for the ‘False’.

• As a result, when the Checkbox is unchecked, it returns the blank value as shown in the image below.

• On the other hand, when the Checkbox is checked it returns the value ‘ExcelDemy’.

 Notes.  Sometimes, you may not activate the Developer tab. You need to activate it first. To activate the Developer tab, follow the instructions.

Step 1:

• First of all, right-click the Home tab or any other tab from the Ribbon.
• Select the Customize the Ribbon

Step 2:

• From the box, check the Developer (Custom) tab.
• Then, Click OK.

• Therefore, you will see the Developer Tab in the Ribbon.

Read More: What Can You Do with Cell Value If Checkbox Is Checked in Excel?

2. If Checkbox Is Checked Then Apply Formula to Make an Interactive Analysis in Excel

In this section, we will apply multiple checkboxes to analyze data. Follow the outlined steps below to accomplish it.

Step 1:

• Insert some Checkboxes in your desired cell.
• Link every Checkbox to different cells as before.

Step 2:

• Type the formula in cell D5 to apply the IF It will show ‘Sold’ for TRUE value and ‘unsold’ for the FALSE value.

• Press Enter to see the first result.

Step 3:

• Do the same for the rest of the cells, and you will get all the results as ‘Unsold’ as the Checkbox is unchecked.

Step 4:

• To count the ‘TRUE’ results or the ‘Sold’ result, apply the COUNTIF function with the following formula.

Step 5:

• To get the result as a percentage, divide the number of sold products by the number of total products by using the following formula.

• Press  Ctrl  + Shift  +  5  to convert the result into a percentage.

Step 6:

• Now, if you just check the Checkboxes, it will show the results with changes in multiple cells.

Step 7:

• Check the 5 checkboxes and it will show the percentage for 5 sold products interactively.

3. Create a Dynamic Chart If Checkbox Is Checked in Excel

From the following data set below, we will create a dynamic chart using checkboxes. In the following chart, we have shown the increase in the price of Mild Steel and High Carbon Steel. Follow the outlined steps below to create the chart.

Step 1:

• Link cells D8 and D9 to Check Box 1 and Check Box 2.

Step 2:

• In cell C12, type the following formula for applying the IF function.

• It will return the value of cell C5 if the argument is ‘TRUE’ which will come from the selection of the Check Box 1.

• Therefore, it will show the value of cell C5 as the Checkbox is checked.

Step 3:

• Use the Autofill handle tool to get all the values of the range C5:F5.

Step 4:

• Repeat the same procedure for the High Carbon Steel. Type the following formula in the cell.

• As a result, it will show the value of cell C6 for the ‘TRUE’ argument and N/A for the ‘FALSE’.

Step 5:

• Use the Autofill handle tool to fill up the rest cells.

Step 6:

• Uncheck the box to see the changes.

Step 7:

• Insert a chart for the range C12:F13.
• It will show no chart as the Check Box is marked as unchecked.

Step 8:

• Now, mark check the Check Box 1.

• Therefore, you will see the changes in the chart for the value of Mild steel.

Step 9:

• Now, mark check the Check Box 2.

• Consequently, you will see the chart will show both the results for the Mild Steel and the High Carbon Steel.

4. If Checkbox Is Checked Then Apply a Code Based on Cell Value in Excel VBA

We will use a VBA code to get results from the checkboxes. For example, we want the value “ExcelDemy” to show in cell C5 when the box is checked and ‘N/A’ when it remains unchecked. To do so, follow the instructions below.

Step 1:

• After creating a checkbox, press  Alt  + F11  to open the VBA Macro.
• Click on the Insert.
• Select the Module.

Step 2:

• Paste the following VBA.

Sub Check_New()
If ActiveSheet.CheckBoxes("Check Box 1").Value = xlOn Then
        Range("C5").Value = "ExcelDemy"
    Else
        Range("C5").Value = "N/A"
    End If
End Sub

Step 3:

• Then, save the program and press F5 to run it.
• Firstly, uncheck the checkbox, and you will see the result in cell C5 is N/A.

Step 4:

• Secondly, check the box and you will get the result ‘ExcelDemy’ as shown in the image below.

Read More: VBA to Check If CheckBox Is Checked in Excel

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

Finally, I hope you now understand how to use the formula when the checkbox is checked. To teach and practice with your data, you should use all of these tactics. Look over the practice book and put what you’ve learned to use. Because of your valuable support, we are encouraged to continue providing programs like these.

If you have any questions, please do not hesitate to get in touch with us. Please let us know what you think in the comments area below.

Related Articles

• Excel Checkbox: If Checked then Change Cell Color
• Excel VBA: Form Control Checkbox Value

<< Go Back to Excel CheckBox | Form Control in Excel | Learn Excel