Excel Basics:
AutoFill Data

Step-by-Step: AutoFill Data

---

What you will learn:

to copy cell(s) with AutoFill to copy cell above with key combo to AutoFill a standard sequence to AutoFill a patterned sequence

---

Start with: trips4.xls (saved in previous lesson)

The special offer trips all have a fixed price. So there are several duplications in the column labeled Cost Each. With AutoFill you don't have to type all of those separately.

AutoFill: Drag Copy

1. In cell D5 type  1500  as the Cost Each for a Tahiti trip.

   You don't have to press ENTER for the next step to work.
    

   
2. Move the pointer over the lower right corner of cell D5 and drag downward to cell D10, in the last row with a Trip value of "Tahiti".

   Note the popup tip that shows what value it is that you will copy.
    

   The AutoFill Options button appears automatically in Excel 2002/2003 whenever you drag the AutoFill handle. Move your mouse over the button to see the arrow that opens the menu.
    

3. Release the mouse button to complete the drag.
    
4. Type  3000  in cell D11 as the Cost Each of the New Zealand trip.
    
5. Fill the Cost each for the rest of the New Zealand rows by dragging the fill handle of cell D11.

---

AutoFill: Keys Copy

1. Type 6000 in cell D16 as the Cost each of the World trip and press ENTER. Now cell D17 is the selected cell.
    
2. Use the key combo CTRL + ' = +   (that's a quote mark ' on the same key as a double-quote mark ") to copy into cell D17, the value above it in the column. This is a very useful trick. If the cell above has a formula in it, this key combo copies the formula. If you want to copy just the value and not the formula, use CTRL + SHIFT + ".
    
3. Press ENTER.
    

4. 2000 2400 2000 1500 3000 1500

   Type in the following values, in order, for the Cost each of the trips in the Other category.
   

   

5. Save As  trips5.xls 
   How to handle a full disk

---

AutoFill: Simple sequence

World Travel's spreadsheet doesn't yet have a place that uses a series of values. You will add a section to the spreadsheet that does. This new part will show the number of trips sold and their total value for each week that the special offers were available.

1. Scroll down to blank row 27 (You may need to use the scroll arrow instead of the box)
    
2. Type the following in cells A27 through E27:

    Week   Date    # of People    Total 

   
    
3. In cell A28, underneath the label Week, type  1 .
    
4. Drag by the fill handle of cell A28 down to cell A35. Hmmm. The 1 was copied into each cell. Not what you need this time. You want to number the weeks that the special offers were available.
    
   
5. Undo.
    
6. Hold the CTRL key down and drag the fill handle again to cell A35.

   Ah! Better! Now each cell is numbered in order from 1 to 8.

   Did you notice how the pointer changed?   Did you see the popup tip showing what value was being put in each cell as you dragged?
   

---

AutoFill: Patterned Sequence

1. In cell B28, underneath  Date , type  June 1  and press ENTER.
   Once you enter a date, the cell will remember the format it used. Suppose you first type the date and it uses the default format:   1-Jun . Then you decide you want to see the date as  June 1, 1998 . If you retype it with the new format, it may be displayed as  1-Jun-98  or back to  1-Jun  instead! Frustrating!
    
   To change the formatting of the date you must use the Format cells dialog, discussed in the next project. You cannot just retype differently.
    
2. Select B28 again and drag cell B28's fill handle down to B35. Hmmm. This counts up a single day at a time rather than the week at a time needed to match column A. You need to establish a pattern for Excel to read.
    
3. Undo.
    
4. In cell B29 type  June 8 , which is a week after June 1.
   
5. Now select both B28 and B29 and drag the fill handle of the selection down to cell B35.

   Aha! Now the cells increase the dates by a week at a time by using the two cells to define the pattern for the series. More complex patterns would need more cells filled in to define the pattern.

    Notice that the numbers and dates are lined up on the right. This is the default for all numbers, including dates and times. If Excel does not recognize what you entered as a date, it will be lined up on the left.
    

6. Center range A27:B35 and Column C by selecting them and clicking the Center button. Now the labels and the numbers are lined up better.
    
7. Save [trips5.xls]