Page 329 - Excel Workbook for Dummies

35_798452 ch24.qxp 3/13/06 7:47 PM Page 312
312 Part VIII: The Part of Tens
Freeze Column and Row Headings

When it comes to the size of your monitor versus the size of your worksheet, your
monitor loses hands down every time. This means that in all except the most concise
data tables, the column and row headings that identify the entries in their cells soon
disappear from view as you make or edit entries in rows lower and columns further to
the right than the original screen can contain. To prevent this from happening by keep-
ing a table’s or list’s headings on the screen at all times no matter how long or wide the
table or list becomes, you need to freeze these headings on the screen. To do this,
position the cell cursor in the cell located immediately to the right of the column con-
taining the row headings and immediately below the row with the column headings.
Then, choose the Window➪Freeze Panes menu command.

Don’t forget that you can save the frozen panes you set up in a spreadsheet as part of a
custom view for the worksheet that you can resume at any time simply by selecting its
name in the Custom Views dialog box (View➪Custom Views).

Prevent Data Entry Errors

with Data Validation

Excel’s Data Validation feature makes it easy to prevent users from entering the wrong
type of data or invalid values in a particular cell range of the spreadsheet. You can also
limit data entry errors by using Data Validation to compel the user to select the entry
for a cell from only those values you display on the cell’s drop-down list.

To restrict data entry for the selected cell, you select the type of entry that is allowed
in the cell on the Allow drop-down list (Whole Number, Decimal, List, Date, Time,
Text Length, or Custom) on the Settings tab of the Data Validation dialog box (Data➪
Validation). Depending upon the type of entry you allow, you then restrict the range of
allowable values. For example, if you select Date as the allowed entry type, you then
specify the start and end dates that the cell entry must be between.

By selecting List as the entry type, you’re able to create a cell drop-down list that con-
tains the complete list of allowed entries. That way, the spreadsheet user doesn’t have to
type anything in order to make the cell entry. This type of cell data validation is perfect
when its data entry is restricted to a relatively short list whose values must be consis-
tent and spelled correctly (as when sorting or filtering data).

Note, however, that you must enter the list of allowed entries you want to appear on
the cell’s drop-down list in a cell range somewhere in a worksheet prior to setting up
this kind of List entry in the Data Validation dialog box. You then specify the allowable
entries for the cell drop-down list by selecting the Source text box on the Settings tab
and then selecting the cell range containing the allowed entries in the worksheet.
When setting up Data Validation for a cell, you can also define an input message dis-
played when the user selects the cell and an error alert message that is displayed
when the user attempts to make an invalid data entry. The input message instructs the
user on what range of values are allowed in the cell just as the error alert message
informs the user as to why the entry he tried to complete was not allowed.

After setting up Data Validation for a single blank cell, you can then copy the validation
settings to an entire range of cells in the spreadsheet using any of the standard copy
methods (copy and paste or drag-and-drop).