This formula is broken on purpose. Why?
Today's lesson is a little different. I'm going to put a broken formula on the screen on purpose, and we're going to work out why it's broken before I fix it.
I could just show you formulas that work every time and you'd nod along and feel good about it. But that isn't really how you learn this. You learn it when something's wrong and you have to go find the trouble. So today we do that on purpose, in a low-stakes way, instead of you doing it by accident on your real budget at nine at night.
The setup
Open a sheet. Make three columns: Item, Price, Quantity. Fill in a few rows, it doesn't matter what, I did coffee, socks, and a lamp because that's what was near me.
Now add a fourth column called Total. In the first row, type a formula that multiplies price by quantity. Something like =B2*C2. Fine so far.
Here's where we break it on purpose. Instead of using the fill handle to copy that formula down the column (which you already know how to do from a couple lessons back), I want you to retype the formula in row 3, but this time point it at the wrong row. Type =B2*C3 instead of =B3*C3.
Now look at your Total column. Row 3 is wrong. It's multiplying the price from row 2 against the quantity from row 3. The number that shows up looks perfectly normal. It's just wrong.
Why this matters more than it seems like it should
This is close to the exact thing that happened to me early on, at my first real job. I had a formula pointing at the wrong column for two weeks and nobody caught it. Not my manager, not me. When I finally found it my stomach dropped for a second, the way it does when you realize a number's been wrong in front of people. Then I looked closer and the wrong numbers hadn't actually mattered for what we were using them for, and I laughed out loud, alone at my desk. Lucky, not smart. But it taught me something: broken formulas don't look broken. They look like numbers. That's the whole trouble with them.
So the skill I really want you to build today isn't "type formulas correctly." It's "notice when a number looks a little too convenient, or a little off, and go check the formula behind it instead of trusting the cell just because it's got a number in it."
How to actually check
Click on the cell. Look at the formula bar up top, not the number in the cell. That's the only way to see what a cell is actually doing versus what it's showing you.
Then, and this is the part people skip, look at the cell reference letters and numbers in that formula and point at the cells they're naming. Is B2 actually the price you meant? Is C3 actually the quantity you meant? Your eyes will slide right past a wrong row number if you don't make yourself check it against the actual sheet.
The fill handle thing, again
This is also, I'll admit, a bit of a pitch for why I keep after you about the fill handle instead of retyping formulas row by row. When you drag a formula down with the fill handle, the sheet adjusts the row references for you on its own. B2 becomes B3 becomes B4, correctly, every time. When you retype it by hand, you're doing that adjustment in your head, and heads are exactly where typos and off-by-ones live.
I had a student, Casey, who I was trying to teach to sort a table. He pressed the button that sorts just one column instead of the whole table, and it scrambled everything, names attached to the wrong numbers entirely. Took me ten seconds to fix. He still brings it up as if it were my fault, which, sure, fine, I'll take it. But the lesson's the same as today's: the sheet will do exactly what you tell it, even when what you told it no longer makes sense. It won't stop you. You have to be the one who notices.
My opinion here, and I'll say it plainly: formulas beat retyping, always, even for something small like this. The moment you're retyping a formula by hand from row to row, you've volunteered for a mistake down the line. You just haven't met it yet.
Fix it
Go back to your broken row 3. Delete that formula and either retype it correctly as =B3*C3, or better, delete both formulas in your Total column and use the fill handle to drag one correct formula down from row 2. Watch the numbers change. That's the sheet working now, instead of quietly telling stories.
Before next time
Go find one formula you already have somewhere, in this class or your own work, and click into it just to actually read what it's pointing at. Not because I think it's wrong. Just to build the habit of checking.