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.


Download Practice Workbook

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


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

In the sections below, we will insert a checkbox 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.

Sample Data

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.

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

Step 2:

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

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

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.

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

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

Effective Examples of How to Apply Formula If Checkbox Is Checked ThenStep 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’.
=IF(C5,"ExcelDemy", " ")

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

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

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

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

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

 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

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

Step 2:

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

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

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

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

Read More: How to Count Checkboxes in Excel (3 Easy Methods)


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.

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

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.
=IF(F5,"Sold","Unsold")

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

  • Press Enter to see the first result.

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

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.

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

Step 4:

  • To count the ‘TRUE’ results or the ‘Sold’ result, apply the COUNTIF function with the following formula.
=COUNTIF(F5:F11,TRUE)

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

Step 5:

  • To get the result as a percentage, divide the number of sold products with the number of total products by using the following formula.
=(D13/7)

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

  • 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.

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

Step 7:

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

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

Read More: How to Filter Checkboxes in Excel (with Easy Steps)


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.

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

Step 1:

  • Link cell D8 and D9 to check box 1 and check box 2.

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

Step 2:

  • In cell C12, type the following formula for applying the IF function.
=IF($D$8,C5, "N/A")
  • It will return the value of cell C5 if the argument is ‘TRUE’ which will come from the selection of the check box 1.

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

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

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

Step 3:

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

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

Step 4:

  • Repeat the same procedure for the High Carbon Steel. Type the following formula in the cell.
=IF($D$9,C6, "N/A")

Effective Examples of How to Apply Formula If Checkbox Is Checked Then

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

Sample Data

Step 5:

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

Sample Data

Step 6:

  • Uncheck the box to see the changes.

Sample Data

Step 7:

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

Sample Data

Step 8:

  • Now, mark check the check box 1.

Sample Data

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

Sample Data

Step 9:

  • Now, mark check the check box 2.

Sample Data

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

Sample Data

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


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.

Sample Data

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

Sample Data

Step 3:

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

Sample Data

Step 4:

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

Sample Data

Read More: VBA to Check If CheckBox Is Checked in Excel (3 Ways)


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.

The Exceldemy team will respond as quickly as possible.

Stay with us and keep learning.


Related Articles

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo