Check boxes:

In this example, we use “Checkboxes”.

Consider the data of the cells B1, B3 to B8, C1, C3 and C8 and E10 in the figure below.

Select the rows, B3 to B8 and, adjust the height to 20 as this makes it easier for us to align the check boxes.

Select the “Developer” tab in the “Ribbon” (see Course automate Excel Lesson 3).
On the “Developer” (1) tab, click on the “Insert” (2) button and in the menu that appears, select “Check Box” (3). The cursor now changes to a small cross.

Now, with Alt key pressed, click the upper-left corner of cell D3 and drag the cursor diagonally to the right and towards the bottom of the cell. If the entire cell is covered, release the mouse button and the Alt key, move the cursor to the right border of the box and reduce the box size(1) (2).

With the box still selected, right-click in the box (sometimes you need to click a second time) and select “Edit Text”.  Pressing the “Delete” key will erase the text (in this example, the text is not necessary since our text is in cell B3).

With the box still selected, right-click and select “Format Control” (1) again.

On the “Control” (1) tab, select the “3D shading” (2) check box.

On the “Properties” (1) tab, select the “Print Object” (2) check box, to make it easier to print these objects when you print the file, and click OK.

With the box still selected, press Alt + Ctrl and go to the bottom of the box.
Now, a sign next to the arrow cursor (1), is displayed. Click and drag it while still keeping the Ctrl Altbuttons pressed, to cell D4 and when the box fits in the cell, release the mouse button (2).

Keep the Alt + Ctrl keys pressed, go to the bottom of the box in cell D4 and drag it to the next cell. Repeat this in cell D8 and then in a cell with no box. (why Alt + Ctrl? The Alt-key moves the box each time a cell is selected and it is neatly aligned to the left side of the cell, and the Ctrl key places it one below the other).
Tip: If you find it difficult to copy the boxes, then zoom into the worksheet, eg: 200%.

With the Ctrl key pressed, click the box in cell D3.  Then right-click and select “Format Control”.  In the “Format Control” dialog box, select the “Control” (1) tab, click in the “Cell Link” box and then click the cellM3.  If the “Value” is “Unchecked” by default, we leave it that way.
(To find out which cell the box is linked, click on the box, keeping Ctrl key pressed and, you can read the cell reference in the formula bar).

Link the boxes from cell D4 to cell M4 and so on till cell D8.
We test our checkboxes by clicking them and the linked text appears as TRUE or FALSE.

Click on the column header of column D and make the column narrower, eg: column width 3.

Select cell E10 and align the text, “Total”, to left.
Select the cells E3 to E8 and align the line of text towards the center.
Select cell F3 and enter “Currency” and type the formula = IF (M3 = TRUE; C3 * E3, “”) The formula is as follows: if M3 is True, multiply the contents of cell C3 with E3, otherwise don’t do anything.  In the Excel 2007 Course, in Lesson 35, the “If” statement always needs text in a formula to be placed in quotation marks (” “).  This is not for the words TRUE and FALSE.
Copy the formula down to cell F8, using the fill handle.
In cell F10, enter the formula = SUM (F3: F8) and also format it to “Currency”.

Right-click the column heading of column M(1) and select “Hide” (2) in the menu.

We are ready to order a pizza.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats