Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101


Home > Jan's CompLit 101 > Working with Numbers > Basics > AutoFill > Formulas
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Numbers

    Excel Basics: AutoFill: Formulas

Formulas do calculations using values from other cells on the sheet, or even from other sheets or other workbooks. You can copy and paste a formula or use AutoFill when you need a row or column 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/paste or AutoFill 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 row. 

Show Formulas

The Formula Bar can show only one formula at a time. You can proof your formulas faster if you show them in the sheet all at once.

 Icon: Keyboard CTRL + `  shows all formulas.
The symbol ` is the grave accent key `, not the apostrophe '. On most keyboards it is to the left of the number keys.


Example: Show Formulas

Example: Statistics - Percentages-All, Normal view  Example: Statistics - Percentages-All, showing formulas

Normal view and showing formulas

Download Icon: On Site the example statistics.xlsx and click on the Percentages-All sheet to try this out for yourself.

What changes when you show formulas

  • Column widths automatically enlarge, usually enough to show the complete formulas. Particularly long formulas may be cut off. Check to see!

    Icon: TipWidth changes: Suppose you change the column widths while showing formulas so you can see the whole formula. When you hide the formulas, your columns do not go back to the original size. You can use Undo to get back to the original widths or you can adjust them manually. 

  • Text that was overlapping empty cells in Normal view may be cut off. 

  • Dates and times are converted to the underlying numbers that Excel uses for dates. Times are decimal values.
    January 1, 2010 9:00 AM becomes 40179.375, where the integer part is the number of days since day 1, January 1, 1900. The decimal part is the time as a fraction of the day. The arithmetic can get complicated quickly! 

  • Number styles are changed to General, temporarily.

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
to show all formulas at once

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.

Note - In real life trip costs might vary based on age or other factors. Formulas can get complex quickly.

Start with: Icon: Class disk trips5-Lastname-Firstname.xlsx (saved in previous lesson)

Formula: Enter

  1. Icon: Class diskSave As  trips6-Lastname-Firstname.xlsx  in the excel project2 folder on your Class disk.

  2. Select cell E5.
  3. Enter formula: click on cell C5Type = , then click on cell C5.
    Once you type the equals sign, Excel expects a formula. When you click on a cell, Excel put that cell reference into the formula.
    D5 selected and put into the formula.
  4. Type an asterisk * (the one on the keypad is easier) and then click on cell D5.
    Value of formula shows in cell E5
  5. Click the Enter button Button: Check mark - Enter (Excel 2010) on the formula bar.
    The formula calculates the value and show that value in the cell E5. But the Formula bar still shows the underlying formula.

    Warning Clicking while editing a formula: If you forget to press ENTER or to click the Enter button, your mouse clicks will continue trying to put cell references in the

  6. Icon: Class diskSave.
     [trips6-Lastname-Firstname.xlsx]


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 neatly organized!

    Icon: TroubleProblem: Some numbers look like ######
    The hash marks mean that the column is not wide enough to show all the digits in the numbers.
    Solution: Make the column wider.  

  3. Icon: Class diskSave.
     [trips6-Lastname-Firstname.xlsx]


Show Formulas

  1. Icon: Keyboard Use the key combo CTRL + ` to display all of the formulas.

    trips6-Lastname-Firstname.xlsx - Sheet1 - showing formulasWhat changed?

    • Column widths increased
    • All cells are aligned left
    • Dates in B28:B35 changed to numbers.
    • Column E shows formulas
      You can scan down this column quickly to see if you have all of the formulas copied correctly.
       
  2. trips6-Firstname-Lastname-xlsx in Normal viewIcon: Keyboard Use the same key combo to return to normal display.