Fill, copy, and when a cell reference should stay put

Once one formula works, you should not have to retype it on every row. Microsoft's fill page says you can copy formulas into adjacent cells by dragging the fill handle — the small square at the corner of the selected cell. Select the cell that has the formula, then drag the fill handle across the cells you want to fill. When you fill down, relative references adjust for each row, unless you used an absolute or mixed reference first.

If you do not see the fill handle, Microsoft says it may be hidden. On Windows desktop Excel, display it again with File > Options > Advanced, and under Editing Options check Enable fill handle and cell drag-and-drop. After you drag, a small Auto Fill Options icon may appear so you can change how the fill worked.

You can also fill without dragging:

1. Select the cell with the formula and the adjacent cells you want to fill.
2. Select Home > Fill, and choose Down, Right, Up, or Left.

Microsoft also lists keyboard shortcuts: Ctrl+D fills down a column, and Ctrl+R fills to the right in a row. On Excel for the web, Microsoft describes dragging the fill handle or using those same shortcuts.

If a filled formula shows the old result and does not change, automatic calculation may be off. Microsoft's fill page says to choose File > Options > Formulas and, under Calculation options, set Workbook Calculation to Automatic. You do not need to change this if your formulas already update when you press Enter.

By default, a cell reference is relative. Microsoft explains it this way: when you refer to A2 from C2, you are referring to a cell two columns left and in the same row. If you copy =A2+B2 from C2 to C3, the references shift down one row and become =A3+B3. The overview page gives another picture: if you copy a relative reference in B2 that points at A1 down to B3, it adjusts from =A1 to =A2.

That is why filling =C15*2 from D15 to D16 becomes =C16*2. Excel assumes you want “twice *this* row's amount,” not “twice the first amount, over and over.” For hours, a relative formula on each row is usually what you want.

Relative references are helpful — and they can surprise you. If cell H1 holds a sample extra rate and you write =C2*H1 in E2, that works on row 2. If you fill it down to E3, the formula becomes =C3*H2. H2 is probably empty, so the result may be 0 or look blank. The formula did not “break” at random. It moved H1 down one row because H1 was relative. Lesson 7's next chapter shows how to stop that move.

When something looks wrong after a fill, click each result cell and read the formula bar. Ask: “Did every reference shift the way I meant?” If one reference should have stayed on H1, that reference needs to be locked.

If you want a reference to stay on one cell when you copy or fill, Microsoft says you “lock” it by putting a dollar sign ($) before the column letter and the row number. That is an absolute reference. Their example: if you copy =$A$2+$B$2 from C2 to D2, the formula stays exactly the same. The overview page uses $A$1 as the pattern: copy or fill it and it still points at A1.

Microsoft also describes mixed references, which lock only the column ($A2) or only the row (B$3). Those are less common in a short everyday list. This course only asks you to lock both parts when one input — such as a single sample rate — should be used on every row: $H$1.

To change a reference, Microsoft says to select the cell that contains the formula, click the reference in the formula bar, and then use the keyboard shortcut shown in your Excel version to cycle the $ signs — or simply type the $ characters yourself. Typing $H$1 is enough for this course and works the same on Windows, Mac, and the web.

Use this power carefully with money. A later try-it will put a sample extra rate such as 0.05 in one cell and multiply household amounts by it. That rate is a practice number only. It is not tax advice, not a CRA rule, and not the extra you must charge or pay. Do not treat the result as GST, payroll, or a real invoice. (Any specific public rate can change; we keep the number inside a labelled sample cell and out of the quizzes.)

1. Recreate worked example 1 on the Practice sheet (double 10, 20, and 30).
2. Put 0.05 in Household H1 with the “not tax advice” label, write =C2*$H$1 in E2, and fill down to E6.
3. Click E4 (feed, 45.00) and confirm the formula uses $H$1 and the result is 2.25.
4. Change H1 from 0.05 to 0.06 and watch the column update. Then put 0.05 back if you want the file to match the model answer.

When have you seen a “total” copied down a list that suddenly turned into zeros? How would reading the formula bar have explained it?