Excel Basics:
AutoFill Formulas

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

Formulas use numbers from other cells on the sheet, or even from other sheets or other workbooks to do calculations. You can use AutoFill when you have to create a set of similar formulas. What a time saver!

Excel is really, really smart when it comes to formulas. But don't let Excel out-smart you! When you copy a formula into another cell, Excel will modify the formula. If you copied into the next cell down the column, for example, all the cell references in the formula are changed down one cell. 

What does this look like? Suppose you have in cell G3 the formula = A3*B3/D4. If you copy the formula down one cell into G4, Excel changes the formula to =A4*B4/D5. All the cell references are changed down one, so that they are in the same relative location, compared to the cell with the formula.

For more on formulas, read the reference table extra material in the Appendix and do the review exercises.


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
    Summary
    Quiz
    ExercisesTo subtopics

Project 3: Format & ArrangeTo subtopics

Project 4: Groups & FormulasTo subtopics

Project 5: DesignTo subtopics

Search
Glossary
Appendix
Icon Step-by-Step 

Step-by-Step: AutoFill Formulas

  Icon Step-by-Step

What you will learn:

 to create a simple formula
to AutoFill a formula down a column

When a trip is sold at World Travel Inc., the Total Sale is calculated by multiplying the number people going on the trip by the cost per person. You will create the formula that calculates this. Then you will copy the formula to other Total Sale cells.

Start with: Class disk trips5.xls (saved in previous lesson)

Formula: Enter

  1. Select cell E5.
     
  2. Enter formula: click on cell C5Type = , then click on cell C5.
    D5 selected
  3. Type an asterisk * (the one on the keypad is easier) and click on cell D5.
    value of formula shown
  4. Press ENTER. The value is calculated and is shown in the cell E5. In the Formula bar you see the formula used.

    Warning If you forget to press ENTER, your mouse clicks will continue trying to put cell references in the Formula Bar. This creates quite a mess!

AutoFill: Formula

  1. Formula for Total sale filled in for all CustomersWith cell E5 still selected, drag the fill handle down to copy the formula into cells E6:E23.
     
  2. Click on each cell in the range E6:E23 and check the formula in the formula bar.

    AutoFill changed the formula for each cell. This only works when the values used in the calculations are neatly arranged in rows and columns. It pays to be neat!

  3. Class diskSave As  trips6.xls .
    full floppy diskHow to handle a full disk

TipIf you find some of your numbers look like ######, the column is just not wide enough to show all the digits in the numbers. Simply make the column wider.

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