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