Hands-On Excel

Hands-On Excel Challenges References instead of typed numbers

Hard-coded numbers in Excel formulas

A formula like =B3*0.07 has the tax rate typed into it. It works until the rate changes: the rate cell says 8%, and the formula still uses 7%. Point at the cell instead: =B3*$G$1.

CoreAbout 3 minutesFree, no sign-upBy Updated

Try it yourself

25 · References instead of typed numbers · Core

Mistake hunt: hard-coded numbers

The tax rate in G1 went up from 7% to 8%, but the quote didn't follow. Four tax formulas in column C have a rate typed in instead of pointing at G1: three still show the old tax, and one is right only until the rate changes again. Fix all four.

Click each tax cell and read the formula bar. A number typed into a formula never changes by itself. Point at the rate cell instead, locked as $G$1 so you can fill down.

quote.xlsx
Ready

Write in the green cells, then press Check. Click a cell while typing to insert it, drag to select a range, F4 adds $, Esc cancels.

The guide · References instead of typed numbers

What "hard-coded" means

A hard-coded value is a number typed straight into a formula. =B2*0.08 works out the tax, but the 8% lives inside the formula, where nobody can see it on the sheet. When the rate changes, someone has to find every formula that contains it. The ones they miss stay wrong, and Excel shows no error.

Why it's hard to spot

A hard-coded formula gives the right answer on the day it's written. In this hunt, C4 is =B4*0.08: correct at today's 8%, so nothing looks off. It breaks at the next change. The cell doesn't tell you; only the formula bar does.

How to find them

The fix

Put each assumption in its own labeled cell, like Tax rate in G1, and point every formula at it. Lock it with $ so you can fill down: =B2*$G$1. Now changing G1 updates the whole quote. The absolute reference challenge covers the $.

Which numbers are fine to type in

Numbers that can't change: 12 months in a year, 7 days in a week, the 1 in =C2/B2-1. Anything that could change, like a rate, a price, a target or a threshold, belongs in a cell of its own.

Common mistakes

FAQ

What is a hard-coded value in Excel?
A number typed directly into a formula, like the 0.08 in =B2*0.08, instead of a reference to a cell that holds it.
Why is hard-coding values in formulas bad?
The value is hidden inside the formula. When it changes, every formula that contains it has to be found and edited, and any you miss stay wrong without showing an error.
How do I find hard-coded numbers in Excel?
Press Ctrl+` to show formulas and look for digits that aren't cell references. Or change an input cell and see which results don't follow.
How do I replace a number in a formula with a cell reference?
Put the number in a labeled cell, then edit the formula to point at it, locked with $ if you will copy the formula: =B2*$G$1.

All challenges

  1. 01 Add it upSUM Starter · 3 min
  2. 02 Find the averageAVERAGE Starter · 3 min
  3. 03 Write once, fill downRelative references Starter · 3 min
  4. 04 Lock it with $Absolute references Core · 3 min
  5. 05 Lock just the row or the columnMixed references Advanced · 3 min
  6. 06 Percent of a totalPercentages Starter · 3 min
  7. 07 Up or down by how much?Percent change Core · 3 min
  8. 08 Select to the end in one moveCtrl + Shift + arrow Core · 3 min
  9. 09 Pass or fail?IF Core · 3 min
  10. 10 Both or either?AND, OR Core · 3 min
  11. 11 Grade bands with nested IFNested IF Core · 3 min
  12. 12 Count the ones that matchCOUNTIF Core · 3 min
  13. 13 Add up the ones that matchSUMIF Core · 3 min
  14. 14 Use a cell as the conditionCriteria in a cell Core · 3 min
  15. 15 Count with two conditionsCOUNTIFS Core · 3 min
  16. 16 Add up with two conditionsSUMIFS Core · 3 min
  17. 17 Look it upVLOOKUP Core · 3 min
  18. 18 Find the right bracketVLOOKUP approximate match Core · 3 min
  19. 19 XLOOKUP, the modern wayXLOOKUP Core · 3 min
  20. 20 Where is it in the list?MATCH Core · 3 min
  21. 21 INDEX and MATCH togetherINDEX + MATCH Advanced · 3 min
  22. 22 Catch the error, not the bugIFERROR Core · 3 min
  23. 23 Split first and last namesLEFT, MID + FIND Core · 3 min
  24. 24 Mistake hunt: #N/A everywhereFixing #N/A Core · 3 min
  25. 25 Mistake hunt: hard-coded numbersReferences instead of typed numbers Core · 3 min
  26. 26 Mistake hunt: numbers stored as textNumbers stored as text Core · 3 min

Pro · coming soon

From first formula to job-ready.

100+ challenges on lookups, pivot tables, dates and text cleanup, an interview track modeled on the Excel tests employers use, and your progress saved across devices.

$29 one-time · early-bird price

You won't be charged today.

You're early!

Pro isn't open yet. We're building it right now. Leave your email and we'll hold the $29 early-bird price for you and tell you the day it launches.

We'll email you a link to confirm, then one email when Pro launches. Privacy