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.
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.
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.
Normal view and showing formulas
Download the
example statistics.xlsx and click on the Percentages-All
sheet to try this out for yourself.
Column widths automatically enlarge, usually enough to show the complete formulas. Particularly long formulas may be cut off. Check to see!
Width 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!
![]() |
Step-by-Step: AutoFill Formulas |
![]() |
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: trips5-Lastname-Firstname.xlsx (saved in previous lesson)
Save
As trips6-Lastname-Firstname.xlsx in the excel project2 folder on your Class disk.
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
Save.
[trips6-Lastname-Firstname.xlsx]
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!
Problem:
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.
Save.
[trips6-Lastname-Firstname.xlsx]
What changed?