Microsoft Office Tutorials and References
In Depth Information
Chapter 11: Getting on the Data List
Calculated field entries
When you want Excel to calculate the entries for
a particular field by formula, you need to enter
that formula in the correct field in the first record
of the data list. In the sample Employee Data list,
for example, the Years of Service field in cell
I2 of the first record shown in Figure 11-1 is
calculated by the formula =YEAR(TODAY())-
YEAR(H2) . Cell H2 contains the date of hire
that this formula uses to compute the number of
years that an employee has worked at the
company. Excel then inserts the result of this
calculation into the cell as the field entry.
Using the Form button
Instead of entering the records of a data list directly in the table, you can
use Excel’s data form to make the entries. The only problem with using
the data form is that the command to display the form in a worksheet with
a data list is not part of the Ribbon commands. You can access the data
form only by adding its command button to the Quick Access toolbar or a
custom Ribbon tab.
To add this command button to the Quick Analysis toolbar, follow these steps:
1. Click the Customize Quick Access Toolbar button at the end of the
Quick Access toolbar and then click the More Commands item at the
bottom of its drop-down menu.
Excel opens the Excel Options dialog box with the Quick Access Toolbar
The Form command button you want to add to the Quick Access toolbar
is only available when you click the Commands Not in the Ribbon option
on the Choose Commands From drop-down list.
2. Click the Commands Not in the Ribbon option near the top of the
Choose Commands From drop-down list.
3. Click Form in the Choose Commands From list box and then click the
Excel adds the Form button to the very end of the Quick Access toolbar.
If you so desire, you can click the Move Up and Move Down buttons to
reposition the Form button on this toolbar.
4. Click OK to close the Excel Options dialog box and return to the
worksheet with the data list.
Adding records via the data form
The first time you click the custom Form button you added to the Quick
Access toolbar, Excel analyzes the row of field names and entries for the first