Excel Basics:
AutoFill Data

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

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.

Fill handleThe fill handle is the small black square in the corner of a selection. 

Fill Handle - black crossIf 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:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel Basics  
Getting StartedTo subtopics      
    ArrangeTo subtopics  
    AutoFill arrow to open subtopics
    Icon - FootprintData
    Icon - FootprintFormulas reference table 
    Icon - FootprintAutoSum
    FinishTo subtopics
    ExercisesTo subtopics

Project 3: Format & ArrangeTo subtopics

Project 4: Groups & FormulasTo subtopics

Project 5: DesignTo subtopics


Icon Step-by-Step 

Step-by-Step: AutoFill Data

 Icon Step-by-Step

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: Class disk 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. Cost each = 1500In 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.

    AutoFill Cost each of 1500
  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.

    Button: AutoFill Options expandedIcon: Excel 2002Icon: Excel 2003 The AutoFill Options button Button: AutoFill Options 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 + ' = Key: CTRL + Key: quote and double-quote  (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
    Type in the following values, in order, for the Cost each of the trips in the Other category.

    Trips5.xls - first save - with Cost Each filled in

  5. Class disk Save As  trips5.xls 
    full floppy diskHow 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 

    Columns labeled
      Whoops: AutoFill put 1 in all cells
  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.
      AutoFill numbered the weeks
  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? Pointer: CTRL drag of fill handle  = fill series  Did you see the popup tip showing what value was being put in each cell as you dragged?

AutoFill: Patterned Sequence

  1. AutoFill put in consecutive days instead weeksIn cell B28, underneath  Date , type  June 1  and press ENTER.
    WarningOnce 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.
      June 1 to Jun 8 selected
  4. In cell B29 type  June 8 , which is a week after June 1.
    AutoFill dates
  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.

    Tip 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.
     Trips5.xls after adding section of weeks and dates
  7. Class disk Save [trips5.xls]

LessonsWorking with Numbers Previous Page Next Page

Teachers: Request permission to use this site with your class

Copyright © 1997-2012 Jan Smith   <jegs1@jegsworks.com>
All Rights Reserved

Icon: DonwloadIcon: CDWant a local copy with no ads? - Download/CD
Want to help?

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God. ~~

Last updated: 02 May 2012