Automatically Filling In a Series of Data
One common and tedious data-entry task is entering a sequence of numbers or dates in a column or row. Excel's AutoFill feature can handle this chore automatically by filling in information as you drag the mouse along a column or row. Use AutoFill to copy formulas or values; enter the days of the week, months of the year, or any series of numbers or dates; and even fill in custom lists of departments, category names, part numbers, and other information that you define.
Because of its tremendous number of options, even Excel experts sometimes have trouble coaxing the correct results out of AutoFill. The addition of Smart Tags in Excel 2002 makes this task somewhat easier. If using AutoFill has the wrong result, click the AutoFill Smart Tag to see a list of other options that enable you to select a different result, such as changing a simple copy to a series.
→ For more details about Smart Tags, see "Changes in the Office Interface".
In general, using AutoFill will have one of the following results:
Copy Data from One or More Cells— If the selection is not a sequence that Excel recognizes—for example, if you select a cell that contains text—AutoFill copies the selection in the direction that you drag.
Tip from 
Using AutoFill is an excellent way to copy a formula from one cell across a row or down a column. This technique is especially useful for copying formulas that total columns or rows. As you drag, AutoFill copies the formula, adjusting relative references as needed.
Copy Formatting or Values Across a Row or Down a Column— Normally, AutoFill copies both formats and values from the cells that you start with. To choose one, make a selection and then hold down the right mouse button while dragging. When you release the mouse button, choose Fill Formats or Fill Values. Don't be confused by the latter option; if you select a formula in the starting cell, this option copies the formula without formatting.
Fill in a Series of Dates— If you enter a date in any recognizable format, such as 4/10 or 5-23-02, AutoFill will extend the series in one-day increments. AutoFill also recognizes long and short versions of days of the week and months. If you enter Jan in the first cell, for example, AutoFill will continue the list with Feb, Mar, Apr, and so on; start with Wednesday, and AutoFill will extend the list with Thursday, Friday, Saturday, and so on. Excel also recognizes calendar quarters. If you enter Q1 in a cell and use AutoFill, you get Q2, Q3, and Q4, at which point the series starts over with Q1.
Tip from 
When you reach the end of a finite AutoFill sequence, such as days of the week or months of the year, the sequence repeats. If you start with Monday, for example, the sequence starts over again after the seventh cell.
Fill in a Series of Numbers— This is probably the trickiest AutoFill option. If you start with a single cell that contains the number 1 and use AutoFill to extend it, Excel will copy the number 1 to the rest of the cells that you select. To instruct Excel to AutoFill a series instead of copying the number, hold down the Ctrl key as you drag.
Tip from 
When you insert a sequence of numbers, Excel assumes that you want to increment them by 1. Thus, if you start with 100, the sequence continues with 101, 102, and so on. To use a different sequence, enter values in at least two cells so that the sequence is apparent, and then select those cells and use AutoFill. For example, if you enter 100 and 200 in the first two cells and then select those cells and use AutoFill, Excel continues the series with 300, 400, and so on. You can also use this technique to enter a date series, such as every other day (Monday, Wednesday, Friday), every third month (Feb, May, Aug), or the 10th of each month (1/10, 2/10, 3/10). Enter the first two or three cells in the sequence, select the cells that you entered, and then extend the selection using AutoFill.
Fill in a Series of Numbered Items— If you enter any text plus a number (Chapter 1, Item 1, or Area 51, for example), AutoFill extends the selection by 1 (Chapter 2, Chapter 3, and so on). Confusingly, this option works exactly the opposite on a series of numbers without text: Hold down Ctrl to prevent Excel from extending the selection and copy the values instead.
Fill in a Custom List— If you've created a custom list (see the following section for step-by-step instructions), enter the first item from that list in any cell, and then use AutoFill to add the remaining items in the list.
Fill in a Trend Series— For this option, you must select a number of cells first and then drag with the right mouse button for more options. You can choose a linear series, in which Excel calculates the average difference between each value in the series that you selected and then adds it to (or subtracts it from) each succeeding value in the AutoFill range. Choose a geometric series to have Excel calculate the percentage of difference between items in the series and apply that amount to each new value. These options are useful when you're trying to project future patterns, such as sales or revenue growth, based on existing data.
To use AutoFill, follow these steps:
First, enter the initial value or values for the range. If the list begins a unique sequence—months of the year, for example, starting with Jan or January—you need to enter a value in only one cell. To AutoFill a sequence of numbers or dates with an increment value other than 1, enter the first two or three values in the series.
Point to Excel's fill handle—the small black square in the lower-right corner of the currently selected cell or range. When you point at the fill handle, the mouse pointer turns into a thin black cross.
Drag in any direction (up or down in a column, left or right in a row) to begin filling in values (see Figure 25.1). Hold down the Ctrl key as you extend the selection to switch the AutoFill action from copy to fill series, or vice versa.
Figure 25.1. As you drag, Excel automatically fills in values in your series—dates, in this example.
Note
AutoFill works only in one row or column at a time. To extend a selection down and to the right, you must perform the AutoFill action in two steps.
ScreenTips display the value that will appear in each new cell as you extend the series. When you reach the final cell, release the mouse button to fill in the data.
If the AutoFill results are not what you expected, click the AutoFill Smart Tag to display a menu with additional options (see Figure 25.2).
Figure 25.2.
Tip from 
You can also use AutoFill to remove items from a range without removing formatting. Select the range that contains the series, and then grab the fill handle and drag into the range.
For maximum control over AutoFill options, hold down the right mouse button while dragging. Choose the Series option at the bottom of the shortcut menu to display a dialog box that lets you choose any option, including starting points and step values for a series, as in Figure 25.3.
Figure 25.3. If Excel can't recognize the progression in an AutoFill series, use this dialog box to specify series settings manually.
Creating Custom AutoFill Lists to Fit Your Projects
You can also create a custom list, such as company divisions, budget categories, or product codes, and add the list to Excel. Excel adds custom lists to the Windows Registry, with each list appearing in the precise order in which you enter individual items. The result can be tremendous time savings for you if you regularly insert the same list into worksheets, such as names of regional offices or budget categories. AutoFill can insert any custom list in any row or column, anytime (and, as we'll demonstrate in the next section, you can also use a custom list as a sort key for the rest of your list).
Tip from 
How do you copy a custom list from one machine to another? Because this information is stored in a Windows Registry key, it's not easy. You can use a hideously complicated 18-step process to export the information from the Registry of one machine and import it to another. Believe it or not, Microsoft expects you to wipe out all your Excel preferences except the one that contains the custom lists, export the Registry file, restore the deleted keys to your machine, and then finally merge the exported file into the Registry on the other machine. Are they serious? The entire process is needlessly complex and dangerous. (The gory details are in Microsoft Knowledge Base article Q212245, if you insist on reading them.) But why go through all that pain? To share one or two lists with another Excel user, add them to a worksheet and let the other user import the lists by using the simple two-step process described next. It takes a few minutes at most, and it's foolproof.
To add a custom list to Excel, use either of the following procedures:
If the list is short, you can type it directly into a dialog box. Choose Tools, Options, click the Custom Lists tab, select New List, and start entering items in the List Entries box, as shown in Figure 25.4. Be sure to enter each item in the correct order, and press Enter at the end of each line. When the list is complete, click the Add button.
Figure 25.4.
If the list is already available in a worksheet, the process is even easier. Say that you've created a worksheet that contains all budget categories in the exact order that you want to enter them every time. Open that sheet and select the worksheet range (column or row) that contains the list. Choose Tools, Options, click the Custom Lists tab, and click Import.
Your list is now available in any Excel worksheet that you open on this machine. To automatically add the custom list to a worksheet range, enter the first list item, use the fill handle to complete the list, and click OK.