How to Insert a Checkbox in Excel

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

When you use a spreadsheet to manage information, adding a checkbox seems like overkill. You can just type an “x” or a “1” wherever you might place a checkmark.

But if many people will be using your spreadsheet, or you’re concerned about the presentation, inserting a checkbox is a nice touch.

And once you’ve added that box, you can connect it to other cells to do some cool things.

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

Kasper Langmann, Co-founder of Spreadsheeto

Adding the Developer tab to Excel

In Excel’s default display, the Ribbon doesn’t display the Developer tab, which you need for inserting checkboxes. We’re going to change that.

Go to File > Options, then click on Customize Ribbon. Make sure Developer is checked.

excel-developer-tab

Now, when you go back to your spreadsheet, you’ll see the Developer tab displayed.

developer-tab-in-ribbon

To see some of the cool things you can do with checkboxes, I’ve put together an example spreadsheet for you to use.

Get your FREE exercise file

To learn how best to use checkboxes, you should follow along.

Download the free sample workbook below to get the sheets I’ll be using as examples throughout this article!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Adding a checkbox

Open up the example workbook, and make sure you’re looking at the first worksheet, Parts.

As you can see, we’ve put together a pretty nice-looking spreadsheet. You might use something like this for a presentation or a tool to distribute to your co-workers.

In the spreadsheet’s current state, we’re using the following formula to calculate the total for a single order:

=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))

This formula checks to see if any cell in column C has text is in it, and if it does, adds the corresponding value from column B to the order.

If you’re not sure how this formula breaks down, check out our full guide to the SUM function!

Kasper Langmann, Co-founder of Spreadsheeto

So if someone types “x” in column C, we get a value added to the total.

Unfortunately, this formula also adds a value to the total if someone types “none” or “N/A,” which could throw off the accuracy of our formula.

Let’s use checkboxes to make it more clear.

First, we’ll delete the values from column C.

clear-column-c

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

insert-form-control-checkbox

You’ll need to click and drag to create a box in which the checkbox will appear. Drag around the cell that you want the checkbox in to get it near where you want it.

(In our case, that’s cell C2.)

click-drag-checkbox

You’ll see that the checkbox comes with some text (this one is labeled “Check Box 1”). You can delete that, so you’re left with a simple checkbox:

checkbox-no-text

Now, if you click on that checkbox, a check will appear. Pretty cool, right? But not especially useful, because it doesn’t do anything. We need to connect that checkbox to another cell.

Right-click on the checkbox, and select Format Control…

checkbox-format-control

In the resulting window, you’ll see that the Cell link box is blank. Let’s fix that.

Click into the box, and then click a cell in the spreadsheet. We’ll use E2 so you can see what’s happening:

checkbox-cell-link

If you’re trying to make a professional-looking form, you might want to link the checkbox to a cell in another worksheet. You can also hide the column that contains the TRUE/FALSE values.

Kasper Langmann, Co-founder of Spreadsheeto

Now, when you check the box in C2, you’ll see a TRUE value appear in F2.

checkbox-link-true

Note that after you right-click on a checkbox to change options, you’ll need to click out of that cell before you can check or uncheck the box again.

If we repeat this process five times, you’ll have six checkboxes next to each part, along with six TRUE/FALSE cells.

excel-checkboxes

Working with linked cells

We’ve now added some checkboxes to our spreadsheet . . . but what can we do with them? Let’s check it out.

First, let’s rewrite our original totaling formula to work with the checkboxes. Here’s how we’ll do that:

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

Doesn’t that look nicer than the formula we were using before?

Try entering this formula into cell D9 and checking a few of the boxes to see what happens.

checkboxes-linked-updated

Just like with any other type of true or false cells, you can count the number of responses. Let’s count the number of items we ordered.

Kasper Langmann, Co-founder of Spreadsheeto

First click into cell B10 and type “Number of items ordered:”

number-ordered

Then, in E10, type the following formula:

=COUNTIF(E2:E7, TRUE)

Now, we see a count of how many items we ordered:

count-linked-cells

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

Using checkboxes with conditional formatting

You’ve seen how checkboxes can control other cells—now let’s look at improving the presentation a bit.

We’ll apply conditional formatting so it’s easy to see exactly which items on the list have been ordered.

First, select cell A2, go to the Home tab on the Ribbon, and click Conditional Formatting:

conditional-formatting

Hover over Highlight Cells Rules, and select More Rules…

conditional-more-rules

Among the available options you’ll find Use a formula to determine which cells to format. Select that one:

formula-conditional

In the Format values where this formula is true box, click the upward-facing arrow and select cell E2:

new-formatting-rule

For this particular example, make sure to remove the dollar signs from the Format values where this formula is true: box.

Kasper Langmann, Co-founder of Spreadsheeto

Alternatively, you can save a bit of time by simply typing =E2 in the box.

Now, we’ll choose a format. Click the Format… button, and change the highlighting to a light green color, the text color to dark green, and the text style to bold:

formatting-details

Finally, click OK.

Now, use the fill handle to drag cell A2 down through A7:

formatting-details

In the pop-up menu, select Fill formatting only.

fill-formatting-only

Now, any cell in column A that corresponds to a checked box in column C will receive the green highlight:

fill-formatting-only

Wrapping things up…

Adding a checkbox is a simple thing, but it can improve the presentation of your spreadsheet. And if you’re creative, you can find some cool uses for the linked cells!

2018-08-28T07:53:41+00:00