Utah Community Learning

Copying formulas down without breaking them

Okay sooo. Last lesson we broke a formula on purpose and figured out why. This lesson is kind of the opposite energy. We're going to copy a formula down a whole column and make sure it does NOT break, because this is the thing that actually saves you the most time in a spreadsheet and it's also where I see people get tripped up the most.

Here's the thing. You already know the fill handle, from a couple lessons back, that little square in the corner of a cell you drag down. We used it for copying data. Today we're using it for copying a formula, and formulas behave a little differently when you drag them, in a way that's actually smart once you see it.

The setup

Open a sheet, doesn't matter which, or make a new one. Put some numbers in column A, rows 1 through 6. Anything. In B1, write a formula that uses A1, something like:

`` =A1*2 ``

Hit enter. You'll get whatever double A1 is. Now click B1 again so it's selected, grab that fill handle in the bottom right corner, and drag it down to B6.

Look at what happened. B2 isn't showing double A1, it's showing double A2. B3 is double A3. The formula moved WITH the row. That's not a glitch, that's the whole point, and it's called a relative reference. When you copy a formula down, the spreadsheet assumes you want it to "shift" and point at the row next to it, not stay locked on the original cell.

Not gonna lie, the first time I really understood this it felt like a small magic trick. You write one formula and the sheet quietly rewrites it five more times for you, correctly, without you retyping anything.

Where it goes wrong

Now let's break it, on purpose, so you see the failure mode before it happens to you at home.

Say instead of doubling a number, you want every row to be divided by a tax rate sitting in one specific cell, like D1. You write in B1:

`` =A1/D1 ``

That works fine for row 1. But drag it down and B2 becomes =A2/D2. D2 is empty, because your tax rate only lives in D1. Now you've got an error creeping down your column, or a zero, or something weird, and if you're not paying attention you won't catch it for a while.

This is the same shape of mistake as the broken formula from last lesson, just a new flavor of it. The fix is a dollar sign. If you write =A1/$D$1 instead, those dollar signs tell the sheet "no, don't shift this one, keep it locked on D1 no matter where you drag me." That's called an absolute reference. Drag it down now and every row divides by D1, exactly like you wanted.

You don't have to memorize the term. Just remember: dollar sign means "stay put." No dollar sign means "shift with me."

Try it yourself

Column A: put in some sample numbers, like grocery totals for six months.
Cell D1: put a single number, like a budget goal, say 500.
In B1, write =A1-$D$1 to show how far each month was from the goal.
Drag B1 down to B6.
Check that D1 stayed the same in every formula and A1 shifted to match each row. Click on B4 and look at the formula bar to confirm.

If a row looks wrong, click into it and just look at what it's actually pointing at. That's the whole debugging process. Not fancy, just look.

Real talk on formulas versus retyping

I'll say the opinion straight out because it matters here: if you're ever tempted to just retype a number six times instead of building one formula and dragging it down, don't. Even for small stuff. The moment you type the same number in twice by hand, you've planted a future mistake, you just haven't met it yet. One typo in month four and now your whole sheet is quietly wrong and you won't notice until it matters.

This is actually the same lesson from the chart I built one Saturday showing our grocery spending by month. I spent the whole day on it, felt very proud of myself, dragged the formulas down clean, color coded it, the works. Showed Casey. He looked at it for about four seconds and said "okay so we spend a lot in December." And yeah. That's it. That's the whole chart. I told him that's the chart working, that's the point, it's supposed to look obvious and boring when it's right. Same thing here. A column of correctly dragged formulas should look boring. Boring is correct.

Before next time

Build a small sheet with one locked reference (a rate, a goal, a total) and drag a formula down at least five rows using it. See if you can get through it without a single stray zero showing up.