Ms Office Tune Up

If you are constructing worksheets that other people will use for data entry, ensuring that data is entered as accurately as possible should be your main concern. No matter how solid the rest of the model is, if the data it relies upon is faulty, the results the model generates will be faulty as well. When constructing a worksheet, here are a few notes to bear in mind to help ensure accurate data entry:

• Format the worksheet text in a large, clear, legible font.
• Provide labels that indicate to the user the data type or units a cell requires. For exam­ple, if a cell requires a value in months, add the label months in the cell to the right of the input cell.
• Provide instructions that tell the user how to input the data. These instructions can be in a text box at the top of the worksheet or in comments that you insert into each input cell.
• Take advantage of Excel’s powerful data validation features to ensure the right types of data get entered.

Another useful technique for ensuring accurate data entry is to add an extra worksheet layer that gives the user an interface for entering the data. This interface takes the form of dialog box controls such as lists, check boxes, and option buttons. You associate certain values with these controls, which then ensure that the user can enter only the values you want.

Before you can work with dialog box controls, you need to display the Ribbon’s Developer tab:

1. Choose Office, Excel Options to open the Excel Options dialog box.
2. Click Popular.
3. Click to activate the Show Developer Tab In the Ribbon check box.
4. Click OK.

You add the dialog box controls by choosing Developer, Insert and then selecting tools from the Form Controls list, shown in Figure 1. Note that only some of the controls are available for worksheet duty. I discuss the controls in detail a bit later in this section.

Figure 1 On the Developer tab, click Insert to see the Form Controls that you can use for data entry on your worksheets.

Follow these steps to add any control to a worksheet:

1. Choose Developer, Insert, and then click the form control you want to create. The mouse pointer changes to a crosshair.
2. Move the pointer onto the worksheet at the point where you want the control to dis­play.
3. Click and drag the mouse pointer to create the control.
4. To edit the control caption, right-click the control, choose Edit Text, adjust the text accordingly, and then click outside the control.
5. Right-click the control, and then click Format Control to display the Format Control dialog box.
6. Click the Control tab, and then use the Cell Link box to enter the cell’s reference. You can either type the reference or select it directly on the worksheet.
   • Checked (Check Box and Option Button)-Click this option to display the control as either checked or activated.
   • Unchecked (Check Box and Option Button)-Click this option to display the control as either unchecked or deactivated.
   • Input Range (List Box or Combo Box)-Enter a reference to the worksheet range that contains the items you want to display in the list.
   • Current Value (Scroll Bar or Spin Button)-The initial value of the scroll bar or spin button.
   • Minimum Value (Scroll Bar or Spin Button)-For a scroll bar, the value when the scroll box is at its leftmost position (for a horizontal scroll bar) or its topmost position (for a vertical scroll bar); for spin button, the smallest possible value.
   • Maximum Value (Scroll Bar or Spin Button)-For a scroll bar, the value when the scroll box is at its rightmost position (for a horizontal scroll bar) or its bottommost position (for a vertical scroll bar); for spin button, the largest possi­ble value.
   • Incremental Change (Scroll Bar or Spin Button)-For a scroll bar, the amount that the value changes when the user clicks on a scroll arrow; for spin button, the amount the value changes when the user clicks an arrow.
   • Page Change (Scroll Bar)-The amount that the scroll bar’s value changes when the user clicks between the scroll box and a scroll arrow.

NOTE

When working with option buttons, you have to enter only the linked cell for one of the buttons in a group. Excel automatically adds the reference to the rest.

Configure other options for the control:

7. Choose OK to return to the worksheet.

Figure 2 shows a worksheet with a few dialog box controls added.

Figure 2 You can help ensure accurate data entry by using dialog box controls linked to worksheet cells.

Technorati Tags: dialog box controls, input data, excel 2007

Popularity: 2% [?]

If you liked this post, would you please buy me a twelve-ounce lattes for only $2