Adding a checkbox in Excel
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:
This formula checks to see if any cell in column C has text in it, and if it does, adds the corresponding value from column B to the order.
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.
Then, click on the Developer tab in the Ribbon, and click Insert. From the resulting drop-down menu, select the checkbox under Form Controls:
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.)
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:
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…
In the format control dialog box, 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:
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.
Now, when you check the box in C2, you’ll see a TRUE value appear in E2.
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.
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:
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.
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.
First click into cell B10 and type “Number of items ordered:”
Then, in E10, type the following formula:
Now, we see a count of how many items we ordered:
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:
Hover over Highlight Cells Rules, and select More Rules…
Among the available options you’ll find Use a formula to determine which cells to format. Select that one:
In the Format values where this formula is true box, click the upward-facing arrow and select cell E2:
For this particular example, make sure to remove the dollar signs from the Format values where this formula is true: box.
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:
Finally, click OK.
Now, use the fill handle to drag cell A2 down through A7:
In the pop-up menu, select Fill formatting only.
Now, any cell in column A that corresponds to a checked box in column C will receive the green highlight:
Adding a checkbox in Excel 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!