Excel: Fill functions

CUSTOM LISTS
As with the months of the year and days of the week, you can create your own custom fill lists. Click the Office button and choose Excel Options | Popular | Edit Custom Lists to display the Custom List dialog. You can copy a list from a range on the worksheet or click New List and type a list of entries in the aptly named List entries box, one per line. Click Add to add the list and OK twice to finish. Now type any entry from your list in a cell and drag the fill handle to create the sequence.

MORE-COMPLEX SERIES
To create a nonlinear series, type the first value in the series, right-click, and drag on the fill handle. When the Context menu appears, select Series to open the Series dialog. Here you specify a Growth series, for example. To test this, type number 1 in a cell, right-click it and drag, choose Series | Growth and set the step value to 2, and click OK. This creates a sequence in which each number is followed by itself multiplied by two.

DROP-DOWN EASE
A drop-down list is handy when you have a set of options you want your user to choose from—and it conveniently averts typos. On a new sheet, type a list of entries, select it, and type a name for the list in the Names box on the left of the Formula bar. On a new worksheet, select the cells in which the data is to appear. Click the Data tab on the Ribbon and choose Data Validation. From the Settings tab choose Allow | List and in the Source box type = and the name you gave to the selected list (for example, =employees). Click OK. Now when you click a cell you can select the cell entry from the drop-down list.

FORMS
The Form button is not on the Excel 2007 Ribbon, but you can add it to the Quick Launch bar. Click the Office button, choose Excel Options | Customize, and in the Choose commands from list pick Commands Not in the Ribbon. Locate and select the Form command. Click Add and then OK. To display the form, click in a table of data and click the Form button. You can now enter data into your table using the Form dialog, which is quicker and more convenient than navigating around a very large table.