Excel Basics · Interactive lesson 6 of 12
Copying formulas & cell references
Write a formula once, then copy it down a whole column — Excel quietly rewrites each cell's address to match its new row. That automatic shift is called a relative reference, and it's why spreadsheets scale to thousands of rows without thousands of hand-typed formulas.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialCopying Formulas and Cell References in ExcelSkill check
Try these first:
- Use the fill handle to copy a formula down a column.
- Copy and paste a formula into a new cell.
- Explain what changes automatically in
=A2*B2when it's copied to the row below.
All familiar? Skip ahead to M7 — Sort & Filter.
Teach
What you're learning
- Fill handle
- The small square at the bottom-right corner of a selected cell — drag it to copy the formula into the cells next to it.
- Copy / paste formulas
- Ctrl+C then Ctrl+V works on formulas the same way it works on plain values — references shift the same way either method.
- Relative references
- A plain reference like
A2shifts automatically when the formula is copied to a new cell. - How
=A2*B2changes when copied down - Copied one row down it becomes
=A3*B3; two rows down,=A4*B4— same shape, new row.
Where to find it in Excel
Fill handle›or Home › Copy / Paste
| A | B | C | D | E | F | G | ||
|---|---|---|---|---|---|---|---|---|
| 1 | Item | Qty | Price | Total | ||||
| 2 | Pens | 12 | 1.50 | 18.00 | ||||
| 3 | Paper | 3 | 6.00 | 18.00 | ||||
| 4 | Tape | 4 | 2.25 | 9.00 | ||||
| 5 | Folders | 10 | 0.80 | 8.00 | ||||
| 6 | Clips | 6 | 1.10 | 6.60 | ||||
| 7 | ||||||||
| 8 |
Swipe sideways to see the whole window.
- 1Drag the small square at the corner of the cell (the fill handle) down, or double-click it.
- 2Or Copy (Ctrl+C) and Paste (Ctrl+V) into the cells below.
- 3Each copy shifts its relative references by the same number of rows.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided practice
Write the Total formula for the first row, then fill it down the rest of the table in one click.
$A$1, which stays locked to one cell instead of shifting when copied — worth knowing exists, but not something you need for a table like this one.Real-world exercise
Fill a real table
Build a Price × Quantity table with at least six rows. Write the Total formula once, then use the fill handle to copy it down every remaining row — check that each row's answer is actually correct, not just present.
Spot a mistake or have an idea for this lesson? Send feedback