How to edit checkbox in excel Advance Excel

Add Checkbox in Excel

Topics

• step 1:Add the Developer tab to Excel
• step 2: Add Checkbox in Excel
• Step-3: Work with linked cells

When you use a spreadsheet to manage information, adding a checkbox sounds like an advantage. Where you place a checkmark, you can simply type “x” or “1”.

But if many people are using your spreadsheet, or you are worried about the presentation, then it is a good idea to include a checkbox.

And once you have added that box, you can connect it to other cells to do some good things.

Let’s take a look at how to add a checkbox to your Excel spreadsheet, as well as how to make it functional.

Step 1:Add the Developer tab to Excel

Add the Developer tab in MS Excel

In Excel’s default display, the Developer tab is not displayed on Ribbon. In Excel you need this to add a checkbox. We are going to add it

Go to File> Options, then click Customize Ribbon. Now check or select Developer.

Now, when you come back to your spreadsheet, you will see the Developer tab.

To see how many total things you can do with the checkbox, I am placing an example spreadsheet with links here for you to use.

Step 2: Add Checkbox in Excel

Now make an example workbook like your image below or open it by downloading from the link above.

In the current state of the spreadsheet, we are using the following formula to aggregate the total for a formula:

=SUM(IF(ISTEXT(C2),B2),IF(ISTEXT(C3),B3),IF(ISTEXT(C4),B4),IF(ISTEXT(C5),B5),IF(ISTEXT(C6),B6),IF(ISTEXT (C7),B7))

We have typed this into the B11 cell number.

This formula checks if there is any typed text in a cell in column C, and if it is, it adds the total value of the corresponding B column to the total.

As you can see in this image, if someone types “x” in one of the cells in the C column, then his value is added to the total.

Unfortunately, this formula adds a value to the total even if someone types “none” or “N / A,” or anything else, which can ruin the accuracy of our formula.

Use the checkbox to make it more clear.

First, delete what you have typed in column C.

Then, click on the Developer tab in Ribbon, and click on Insert. From the drop-down menu, select the checkbox under Form Controls:

In the cell in which you want to create a box, you will need to click and drag with the mouse so that the checkbox appears.

Drag into the box in which you want to create the checkbox.

(In our case, that cell is C2.)

You will see that the checkbox comes with some text (this is labeled “Check Box 1”).

Now, if you click on that checkbox, a check will appear. Very good, isn’t it? But it is not particularly useful

for us, because it does nothing. We have to connect that checkbox to another cell.

Right-click the checkbox, and choose Format Control.

In the resulting window, you will see that the Cell link box is empty. Let’s fix it

Click in the Cell link box, and then click on a cell in the spreadsheet. Here we will use E2 so that you can see what is happening. Then click on Ok.

Now, when you check the box in C2, you will see TRUE in E2.

Note that after right-clicking the checkbox to change the options, you must click outside of that cell before checking or unchecking the box again.

Now you have to repeat this process seven times, then you will have seven checkboxes with six TRUE / FALSE cells next to each part.

Step-3: Work with linked cells

Now we have added some checkboxes in our spreadsheet. But what can we do with them? let’s find out.

First, rewrite our original total formula to work with the checkbox. Here’s how we’ll do it:

=SUM(IF(E2,B2),IF(E3,B3),IF(E4,B4),IF(E5,B5),IF(E6,B6),IF(E7,B7))

Isn’t it better than the formula we were using earlier?

Enter this formula in cell B11 and copy it and paste it. Now see what happens to the check box when some check the check box.

So you can see that the cells for which you select the check box, then the values ​​in the B column related to the cells are added to the total.

Now understand that if you have selected three check boxes, then the three values ​​of B column will be added to the total.

Now we will add the total order number in this sheet.

Click in cell B11 and type “Items Ordered:”

Then, in B12, type the following formula:

=COUNTIF(E2:E8, TRUE)

Now, you can see how many items you have ordered:

This formula counts the number of TRUE entries in column E, which corresponds to the number of checkboxes checked.

Adding a checkbox can be a simple matter, but it can improve the presentation of your spreadsheet. And if you are creative, you can find some good uses for linked cells!