Data that repeats down a column or
across a row can be filled in with AutoFill easily. You just select the cell or
cells to repeat and drag the fill handle across the cells you want to use.
The fill handle is the small black square in the corner of a selection.
If you drag on the fill handle, the pointer changes to a small black
cross. Excel will fill each cell that you drag across with either a copy of the original cell or with a continuation of the pattern
in your selection.
If the cells are not all the same but repeat in a pattern,
you want to fill series.
Where you are:
Project 1: Excel Intro
Project 2: Excel Basics
Project 3: Format & Arrange
Project 4: Groups & Formulas
Project 5: Design
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
trips4.xls (saved in
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
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.
- 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.
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.
- Release the mouse button to complete the drag.
- Type 3000 in cell D11 as the Cost Each of the New Zealand trip.
- Fill the Cost each for the rest of the New Zealand rows by dragging the fill handle of cell D11.
AutoFill: Keys Copy
- Type 6000 in cell D16 as the Cost each of the World trip and press ENTER. Now cell D17 is the selected cell.
- 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 +
Type in the following values, in order, for the Cost each of
the trips in the Other category.
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.
- Scroll down to blank row 27 (You may need to use the scroll arrow instead of the box)
- Type the following in cells A27 through E27:
Week Date # of People
- In cell A28, underneath the label Week, type 1 .
- 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.
- 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
AutoFill: Patterned Sequence
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
To change the formatting of the date you must use the Format cells dialog, discussed in the next
project. You cannot just retype differently.
- 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.
- In cell B29 type June 8 , which is a week after June 1.
- 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.
- 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.