Excel 2007 vs. 2003: Simplify User Input by using Predefined Lists

By David H. Ringstrom, CPA

Building spreadsheets for others always presents challenges. No matter how you try to make things self-explanatory, users often try to exert their own will on your work. One way to combat this is to use predefined lists in your spreadsheets. For instance, you may want to have users select a part number, department name, unit of measure, or other list-based inputs. In this article I’ll compare and contrast three different approaches: Data Validation, Form Control Combo Boxes, and ActiveX Control Combo Boxes. Don’t let the technical names scare you away–these are all easy to incorporate into your spreadsheets.

Benefits of Predefined Lists

Predefined lists help users in several ways:

• The spreadsheet becomes self-documented because the list of permissible choices is always present.
• Users minimize keystrokes by making a choice from the list.
• Depending upon the method you choose, you’ll make it much harder for invalid data to creep into your spreadsheet.
• Inputs are always spelled consistently, since all users choose from the same list.

Now let’s look at the specific pros and cons of each approach.

 

Data Validation

                Pros:

o   This is the easiest drop-down list to implement.

o   Inputs are stored in the same cell as the drop-down list.

o   You can copy and paste your data validation settings to other cells.

o   You can include custom input and error instructions.

o   Data validation goes beyond in-cell drop-down lists: you can place other restrictions on a cell, such as a certain range of dates or numbers, a specified number of characters, or place other restrictions on a cell. However, you can only place one type of restriction on a cell at a time.

o   Short lists don’t have to reside in a worksheet cell, but instead can be entered in the Data Validation dialog box.

 

Cons:

o   User must manually select a choice from the drop down list or type the entire entry correctly, as Data Validation doesn’t allow partial matches.

o   User can eradicate the Data Validation within a cell by pasting the contents from another cell.

o   The drop-down list is only apparent when user clicks within the cell.

 

Figure 1: Data Validation provides an in-cell drop-down list that restricts the user’s inputs.

 

Combo Box Form Control (This is an object that floats above your spreadsheet, so you must specify a linked cell for the user’s input.)

                Pros:

o   Drop-down arrow is always present.

o   User cannot paste in alternate selections, so inputs are limited to the list.

 

Cons:

o   This drop down list is a little harder to implement in your spreadsheet.

o   The user’s choice is stored in a separate linked cell that you must specify.

o   The linked cell stores the number of input choice, such as 1 if the user picks the first item from a list, rather than the input itself, like Executive.

o   User must make a choice from the drop down list with their mouse, and cannot type entries. This is arguably a pro, but may frustrate keyboard oriented users.

o   This is an object that floats above the spreadsheet, so it’s harder than data validation to place in multiple cells. If you copy the combo box to other locations, you may need to manually adjust the linked cell location.

o   The user can easily move or delete the drop-down box if they choose.

 

Figure 2: A Combo Box Form Control provides a drop-down list that the user can navigate with their mouse. The user’s input is stored as a numeric value in a cell that you designate.

 

Combo Box ActiveX Control (This works similar to the Form Control, but gets the user to type in the combo box, rather than requiring the user to use their mouse.)

Pros:

o   Drop-down arrow is always present.

o   Match required property lets you determine whether to restrict the user to the list or allow an entry that is not on the list.

o   The linked cell contains the user’s actual choice or input, rather than the number like the Form Control.

o   Your list of items can reside anywhere in the workbook.

o   The user cannot easily move or adjust this type of dialog box, because you must first enter Design Mode in Excel to do so.

 

Cons:

o    Design mode makes adding the control more complex.

o   As with the other combo box, you may need to manually update the linked cell location if you copy and paste this object elsewhere in your spreadsheet.

o   You can’t click and browse to select your list.

Figure 3: A Combo Box ActiveX Control is more flexible than the Form Control–users can start typing an entry rather than be required to use their mouse, plus the linked cell contains their actual input.

Now that you’ve seen these different types of lists in action, let’s see how to implement each in a worksheet:

1. Create a new workbook and paste the list from Table 1 into cells F1:F6.

Departments

Executive

Marketing

Human Resources

Information Technology

Operations

 

Table 1: We’ll make this list available as a Data Validation drop-down list.

 

2. Next, add Data Validation as an in-cell drop-down list to our spreadsheet:

a.       Put the words Data Validation in cell A1.

b.      Click on cell B1, and then open the Data Validation window:

Excel 2007: Choose Data Validation from the Data Tools section of the Data ribbon.

Excel 2003 and earlier: Choose Data, and then Validation.

c.       Choose List from the Allow field, and then enter the list address: =F2:F6. You can also click the browse button within the field and select the range. Alternatively, you could enter the list in this fashion:

Executive,Marketing,Human Resources,Information Technology,Operations

d.      Optional: You can use the Input Message tab to provide input instructions — your notes will appear automatically when the user clicks on the cell.

e.      Optional: You can use the Error Alert tab to provide a customized error message, as well as modify the error style:

• Stop: Prevents the user from making an invalid entry.
• Warning: Gives the user the option to bypass the restriction.
• Information: Simply informs the user that their entry didn’t match the list.

f.        Click OK to close the Data Validation dialog box. As you can see in Figure 1, a drop-down arrow appears when your cursor is in cell B1. This drop-drown arrow vanishes when you move to another cell. Users can either make a choice from the list or type in their choice, but a prompt will appear if the input does not match the list. No prompts appear if the input matches the list.

Turn it off: You can eliminate Data Validation from a cell by clicking the Clear All button within the Data Validation dialog box.

3.       Now let’s add a Combo Box Form Control to our spreadsheet:

 

a.       Enter the word Form Control in cell A5.

b.      Locate the proper toolbar for your version of Excel:

Excel 2007: Click the Insert button in the Controls section of the Developer ribbon, and then choose the second icon in the Form Controls section, as shown in Figure 4. If the Developer tab is not present, you can easily enable it:

                                                                                                         i.      Click the Office button, and then choose Excel Options.

                                                                                                       ii.      Click Show Developer tab in the Ribbon in the Popular section, and then click OK.

Excel 2003 and earlier: Right-click on any toolbar, and then choose Forms. Click the Combo Box icon (typically the fourth icon down in the second column). Tip: A label appears when you hover over the icons on the toolbar.

c.       When your cursor turns into a small crosshair, draw a small rectangle that covers cells B3 and C3, as shown in Figure 2.

 

Figure 4: The Form Control Combo Box control in Excel 2007.

 

Resizing: Combo boxes are objects that float above the worksheet. To resize or move a combo box, you must first right-click on it. Press Escape once to close the menu, and then use one of the sizing points to resize the combo box, or grab the center of the box and move it to a new location. Depending upon how you place the combo box, you may end up with a giant arrow on your worksheet. Simply resize the object to display the combo box itself.

d.      Right-click on the combo box, and then choose Format Control. Complete the fields shown in Figure 5:

 

Figure 5: Use this window to tie your list to the combo box, as well as set the linked cell for capturing the user’s input.

 

·         Specify the Input Range, which in this case can either be the list address or a range name. Don’t use any equal signs here, but do note that you can click the browse button and navigate to the Lists worksheet, instead of having to type the address in manually.

·         Set the Cell Link to be the cell where you want to store the choice made by the user. Typically I have this be the cell under the combo box, but this can be any location you choose.

·         Optional : You can set the number of rows that the drop-down list should contain, such as 20 or 30 for a long list of items, such as part numbers.

·         Optional: Click the 3D checkbox to make the drop-down list appear as if it is embedded within your worksheet.

e.      Click OK to dismiss the Format Control dialog box.

Turn if off: To eliminate this type of drop-down list, right-click on the object, and then choose Cut.

4.       Finally, we’ll add a Combo Box ActiveX Control to our worksheet:

a.       Enter the word ActiveX Control in cell A5.

b.      Locate the Controls toolbar for your version of Excel:

Excel 2007: Click the Insert button in the Controls section of the Developer ribbon, and then choose the second icon in the ActiveX Controls section, as shown in Figure 6.

Excel 2003 and earlier: Right-click on any toolbar, and then choose Control Toolbox. Click the Combo Box icon–typically the fourth icon down in the second column. Tip: A label appears when you hover over the icons on the toolbar.

 

Figure 6: The ActiveX Control Combo Box control in Excel 2007.

 

c.       When your cursor turns into a small crosshair, draw a small rectangle that covers cells B5 and C5, as shown in Figure 3.

d.      Right-click on the combo box and choose Properties. Set the following properties, as shown in Figure 7:

·         LinkedCell: C7

·         ListFillRange: DeptList (if you assigned a range name, or F2:F6 if you didn’t)

·         MatchRequired: True

·         Optional: You can set other properties, such as the List Rows, Back Color, Font Color, and so on. Scroll down to find the Special Effect property, where you can change the appearance of the combo box.

 

Figure 7: The Properties window for an ActiveX Combo Box.

 

e.      Turn off Design Mode:

Excel 2007: Click the Design Mode button on the Developer ribbon.

Excel 2003 and earlier: Click the Design Mode button on the Control Toolbox toolbar–it’s the first icon on the toolbar.

f.        Close the floating Properties window.

g.       You’ll have to enable Design Mode any time you want to make changes to an ActiveX Combo Box:

Excel 2007: Click the Design Mode button on the Developer ribbon.

Excel 2003 and earlier: Display the Control Toolbox toolbar as described above, and then click the Design Mode button.

 

Turn it off: To eliminate this type of drop-down list, you must first enter Design Mode, and then right-click on the object and choose Cut.

 

David Ringstrom heads an Atlanta-based software and database consulting firm serving clients nationwide since 1992. Long ago David recognized that either you work Excel, or it works you, so much of his work involves creating Excel-based tools to streamline accounting and other processes. David shares his knowledge with others as a freelance writer, professional speaker and technical editor, and is proud to have served in the United States Navy.

 

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.