Formulas I: References, Names & Logic

Excel Mastery Track: White Belt · ~10 hours

The previous module taught you to move around the grid, enter data cleanly, and put it in a Table. What comes next is what makes the grid compute: more precisely, what makes one formula you write once fill a thousand cells correctly, all of them right. That last phrase hides the single most consequential idea a new analyst learns in Excel: a formula is not written for the cell it sits in. It is written to be copied, and as it travels across the grid, Excel silently rewrites the cell addresses inside it. Understand that rewrite and control it with the dollar sign, and you can build a commission schedule, a sensitivity table, or a three-statement model at the speed of thought. Miss it, and you will fill a column with a formula that looks right in the first row and is quietly, catastrophically wrong in every row below. That is the kind of error that has cost real analysts real jobs, because nothing on screen flags it.

Nothing on screen flags that kind of error, and it has cost real analysts real jobs.

Three more ideas build on that one and turn Excel from a calculator into a decision engine. Names and LET let you call a cell TaxRate instead of $B$1 and label the middle of a long calculation, so a formula reads like a sentence instead of a ransom note. Boolean logic with IF, IFS, AND, OR and NOT lets a formula branch: charge this rate above a threshold, that rate below it, flag the exceptions. Error trapping with IFERROR and IFNA lets a formula survive the blank cell, the divide-by-zero, and the lookup that finds nothing, without spraying #DIV/0! across your exhibit. Every one of these tools makes formulas longer, so you'll also learn the first four auditing tools: F2, F9, Trace Precedents, and Evaluate Formula. With them you can always answer the only question that matters about a formula. What is it actually doing, cell by cell?

Version note (M365 assumed). LET requires Excel 2021 or Microsoft 365. IFS needs Excel 2019 or later, or Microsoft 365. IFERROR dates from Excel 2007 and IFNA from Excel 2013. Relative/absolute/mixed references, the F4 key, named ranges, and IF/AND/OR/NOT work in every version ever shipped. Where a tool is version-gated, the text flags it and gives the older-version workaround in one line.

Learning objectives

By the end, you can:

1. Explain the difference between relative, absolute, and mixed cell references, and predict exactly how each one rewrites itself when a formula is copied one column right or three rows down.
2. Use the F4 key to cycle a reference through its four lock states (A1 → $A$1 → A$1 → $A1) and choose the correct state for a fill-down, a fill-right, or a two-way fill.
3. Create named ranges three different ways, read and audit them in the Name Manager, and write a LET formula that names its own intermediate results so a long calculation reads clearly and computes each piece only once.
4. Build decision logic with IF, IFS, AND, OR, and NOT, and state precisely which of these is the right instrument for a given branching problem.
5. Trap errors deliberately with IFERROR and IFNA, and explain why a blanket IFERROR(…, "") is one of the most dangerous habits a modeller can form.
6. Recognise when a nested formula has grown past the point of safety and refactor it into an IFS, a helper column, a lookup table, or a LET, and justify the choice.
7. Audit any formula you or a colleague wrote using F2 (edit and see precedents), F9 (evaluate a selected sub-expression), Trace Precedents/Dependents, and the Evaluate Formula dialog.

Prerequisites & connections

Builds on. EX0.01: the grid model (rows, columns, the A1 address system), ranges and how to select them, and the keyboard-first navigation you'll lean on constantly here. That means Ctrl+arrows to reach the edge of a data region and Ctrl+Shift+arrows to select to it. It also covers Tables (Ctrl+T) and their structured references, and number formatting, so that a rupee payout displays as ₹56,250 instead of 56250. If you can't yet fill a formula down a column with a double-click of the fill handle or Ctrl+D, revisit EX0.01 before starting here. Almost everything below assumes you can.

Feeds forward. EX1.01 (Formulas II: text, dates, and the SUMIFS/COUNTIFS aggregation family) reuses the same careful referencing on every criteria argument. EX1.02 (Lookups Mastery) gives the approximate-match VLOOKUP(…, TRUE) trap teased in the common-mistakes section its full, careful treatment, and shows IFNA wrapping a lookup that legitimately finds nothing. EX1.03 (Dynamic Arrays & LAMBDA) treats LET as the on-ramp: once you can name intermediate results, naming an entire reusable function with LAMBDA is a short step. EX2.01 (Data Tools & Integrity) builds formula-driven data validation and conditional formatting directly on the Boolean logic here. EX7.01 (Professional Modeling Standards) turns careful referencing and the auditing tools from good habits into non-negotiable law. The entire finance-modelling spine stands or falls on the $ you are about to master. That holds above all for M3.08's three-statement model, where a single unlocked reference propagates a wrong number through the balance sheet.

Core teaching content

Common mistakes & how experts think differently

1. Forgetting to lock a reference before filling. This is the signature beginner error, and the reason references come first here. You write =B2*F1, it's right in the first cell, you fill down, and every row below is wrong because the rate pointer slid off F1 into empty cells. An expert locks as they write. The instant a reference points at something that should stay put, F4 goes down before the fill does. Then the expert sanity-checks the last filled cell as well as the first, because the first cell always looks fine.

1. Trusting the first row of a fill. This one is related but distinct. A newcomer verifies the top formula, sees the right answer, and fills a thousand rows on faith. The expert's reflex is to click the bottom filled cell and press F2. If the precedents light up on the wrong cells, the whole column is wrong, and only the bottom row (or a middle one) reveals it. A column is only as correct as its worst row.

1. The VLOOKUP(…, TRUE) / approximate-match default trap. VLOOKUP and MATCH default to approximate match if you omit the final argument. They return the largest value not exceeding the lookup value without warning. And they're right only if the lookup column is sorted ascending. On unsorted data this returns confident garbage. It's the most costly default in Excel. If you keep only one default on your list of things to distrust, keep this one. For the full remedy, always specify exact match. When you do want banding, sort and do it deliberately. That remedy is the core of the lookups work later in the track. It's flagged here so that the moment you meet a lookup you already distrust its default.

1. Deeply nested IFs that should be IFS, a table, or a LET. A six-level IF nest may look clever. It's unmaintainable, and its closing-parenthesis count is a bug waiting to happen. An expert reads depth past three as a signal to refactor. Use IFS for ordered thresholds and a lookup table for value-to-result maps, so a rate change edits a cell and leaves the formula alone. Use a helper column for a multi-step calculation, and LET when a sub-expression repeats.

1. Swallowing real errors with a blanket IFERROR(…, ""). Wrapping everything so all errors become blank cells makes the sheet look clean and makes it a liability. A #REF! from a deleted column now hides in a blank cell identical to a legitimate empty result. Experts fix the cause and catch narrowly with IFNA when only a lookup miss is expected. When they do trap, they return a visible flag ("CHECK", 0) so a broken cell can never masquerade as an empty one.

1. Committing an F9 evaluation with Enter. You select a sub-expression, press F9 to see its value, get distracted, and press Enter. You've just replaced live logic with a frozen number, with no warning. A mechanical habit prevents it. F9 to look, Esc to leave, always, with Esc treated as part of the same gesture.

1. Hardcoding numbers inside formulas. Writing =B2*0.045 scatters the commission rate across a hundred cells, so changing it means a hundred edits and a near-certain miss. An expert puts every assumption in one labelled input cell, ideally a named range like CommRate, references it absolutely, and changes the business in one place. A number typed inside a formula is a number you'll forget you typed.

1. Merged cells. Merging cells for a pretty header breaks selection, filling, sorting, and references, and a formula pointed at a merged block behaves in ways that will cost you an afternoon. Professionals almost never merge. For centred headings they use Center Across Selection (Format Cells ▸ Alignment ▸ Horizontal), which looks identical and breaks nothing.

1. Treating AND/OR as if they worked across a range. AND(A2:A10>5) doesn't test each cell and give ten answers. It collapses to a single TRUE/FALSE, which is rarely what a beginner intends. When you need element-by-element logic across a range, use Boolean arithmetic ((A2:A10>5)*1) or the …IFS family covered later, because AND can't do it. Knowing the boundary now prevents a confusing class of wrong answers later.

1. Building without auditing. The beginner writes a formula, sees a plausible number, and moves on. An expert writes a formula and interrogates it straight away: F2 to confirm the precedents, F9 on any branch they are unsure of, Evaluate Formula when the answer surprises them. A formula that produces a plausible but unverified number is no asset. It's an unexploded assumption.

Worked examples

Worked example 1: India: the commission grid and the $F$1 that must not move

The situation. A regional sales manager pays every representative a flat 4.5% commission on their quarterly sales. The single commission rate is stored in one cell, F1. Eight reps' sales are in B2:B9. You must fill one payout formula down C2:C9 and total it, so that changing the rate in F1 later re-prices the entire team automatically.

The layout (₹).

with F1 = 4.5% (stored as 0.045).

The wrong formula, and why. Type =B2*F1 in C2. It returns 56,250, which is correct for Aarav (1,250,000 × 0.045). Fill it down and the wheels come off. C3 becomes =B3*F2, but F2 is empty and worth 0, so Bhavna's payout shows 0. C4 is =B4*F3, also 0. Everyone below row 2 is zeroed. Your relative reference moved both pointers down, and you needed only the sales pointer to move.

The right formula. In C2 type =B2*$F$1, or type =B2*F1 and tap F4 once with the cursor on F1 to lock it. Fill down C2:C9 by double-clicking the fill handle, or select C2:C9 and press Ctrl+D. Now every row reads =B{row}*$F$1. Each sales pointer walks down while the rate pointer stays nailed to F1. Cleaner still, name F1 as CommRate (click the Name Box, type CommRate, Enter) and write =B2*CommRate, which documents itself and is absolute by nature.

The payouts (each = sales × 0.045, Python-verified):

Total payout in C10 is =SUM(C2:C9) = ₹4,72,275. One cross-check proves the whole grid at once. Total payout must equal total sales × rate, and 1,04,95,000 × 0.045 = ₹4,72,275, so the grid ties. The payoff of the absolute reference shows when you change F1 to 5.0%. Every payout and the total re-price in one keystroke, because one locked cell feeds all eight formulas. Esha's payout jumps to 1,05,000 and the total to 5,24,750.

Eight formulas, and one locked $F$1 to change. Build it once, and you can re-price forever.

Worked example 2: US: the tiered bonus with nested IFS and an IFERROR wrapper

The situation. A US sales organisation pays an annual performance bonus whose rate steps up with annual sales. Sales of $1,000,000 or more earn 12%, $600,000 or more earn 8%, $300,000 or more earn 5%, and anything below $300,000 earns 0%. The bonus in dollars is annual sales × the applicable rate. Eight salespeople's sales are in B2:B9. Compute each bonus, wrap it so a stray blank or text input can't poison the column, and total it.

The IFS formula. The rate depends on which band the sales figure falls in. That makes it an ordered-threshold problem, which is what IFS is for. In C2:

=IFS(B2>=1000000, B2*0.12, B2>=600000, B2*0.08, B2>=300000, B2*0.05, TRUE, 0)

IFS tests top-down and returns the first hit, so the thresholds run highest first. The trailing TRUE, 0 is the catch-all that pays 0% to anyone below $300,000 and prevents the #N/A that an unmatched IFS would otherwise return. The same logic as three nested IFs is =IF(B2>=1000000, B2*0.12, IF(B2>=600000, B2*0.08, IF(B2>=300000, B2*0.05, 0))), and the IFS version is flat and readable where the nest is a tangle of parentheses.

The IFERROR wrapper: done right. A sales cell might be accidentally blank or hold text, and then the comparisons can misfire. In Excel, text sorts as greater than any number, so text would wrongly trip the top tier and then error on the multiplication. Guard against that, and do it visibly, with no silent "":

=IFERROR(IFS(B2>=1000000, B2*0.12, B2>=600000, B2*0.08, B2>=300000, B2*0.05, TRUE, 0), "CHECK INPUT")

Now a bad cell shows CHECK INPUT, a flag a human won'tice and fix. A blank would hide the problem, and a number could be quietly wrong. If you dislike repeating B2, name it once with LET: =LET(s, B2, IFS(s>=1000000, s*0.12, s>=600000, s*0.08, s>=300000, s*0.05, TRUE, 0)).

The bonuses (Python-verified):

Total bonus in C10 is =SUM(C2:C9) = $674,550. The boundary cases are what make this a real test, and Marcus is the row to watch. Marcus at $585,000 falls below the $600,000 line, so he earns 5% and misses the 8% band. A beginner who wrote > instead of >=, or who mis-ordered the bands, would misprice him. Diego at $275,000 falls below every threshold and is caught only by the TRUE, 0 catch-all. To read a filled IFS column, press F2 on a boundary row and F9 on the branch you doubt. It shows you TRUE or FALSE in place, so you can see which band fired. On that row, auditing and logic do one job together.

Practice set

All data synthetic unless named. Figures in ₹ where noted, otherwise generic. Each problem is followed immediately by its full solution, so work it first, then check. Write every formula out in full, because writing the $ signs is the skill.

P1 (guided). Predict the copy. Cell C2 contains =$B2*C$1. Without Excel, state what the formula becomes when copied to cell E5, and explain each half.

Solution. Copying from C2 to E5 moves the formula two columns right (C to E) and three rows down (2 to 5). Apply that offset only to the unlocked coordinates. In $B2 the column is locked ($B), so it stays B. The row is free, so it moves down 3 to 5, giving $B5. In C$1 the column is free, so it moves right 2 to E. The row is locked ($1), so it stays 1, giving E$1. The whole formula becomes =$B5*E$1. This is the mixed-reference pattern of a sensitivity grid. $B5 always reads its row's value from column B, and E$1 always reads its column's value from row 1.

P2 (guided). The absolute rate. A commission rate of 3.5% is in B1. Sales figures in A5, A6, A7 are 8,40,000; 11,20,000; 6,50,000. Write the payout formula for A5 so it fills down correctly, and give the three payouts.

Solution. In the payout cell for A5, write =A5*$B$1 (type =A5*B1, then F4 on B1). Filling down keeps $B$1 fixed while the sales pointer walks: =A6*$B$1, =A7*$B$1. You get payouts of 8,40,000 × 0.035 = 29,400, 11,20,000 × 0.035 = 39,200, and 6,50,000 × 0.035 = 22,750. Had you written =A5*B1 unlocked, rows 6 and 7 would multiply by the empty B2 and B3 and return 0. That's the commission-grid failure in miniature.

P3 (guided). Build the two-way sensitivity grid. Loan principals ₹15,00,000; 25,00,000; 40,00,000 run down A2:A4. Annual rates 8.5%, 9.0%, 9.5% run across B1:D1. Write one formula for the top-left body cell B2 (annual interest = principal × rate) that fills across and down the whole 3×3 body, and give the nine results.

Solution. In B2 write =$A2*B$1. The $A2 locks the column to A, so every body cell reads its principal from column A, while the row floats. The B$1 locks the row to 1, so every body cell reads its rate from row 1, while the column floats. Fill B2 across to D2 and then down to row 4, or select B2:D4 and fill. The nine annual-interest figures:

Spot-check C3 = =$A3*C$1 = 25,00,000 × 0.09 = 2,25,000. One formula, filled two ways, gives nine correct answers. That's the whole power of mixed references in a single exercise.

P4. The IFS tier. Use the bands of 1,000,000 or more at 12%, 600,000 or more at 8%, 300,000 or more at 5%, and 0 otherwise. Write the IFS bonus formula for sales in B2, and compute the bonus for B2 = 720,000.

Solution. =IFS(B2>=1000000, B2*0.12, B2>=600000, B2*0.08, B2>=300000, B2*0.05, TRUE, 0). For 720,000, the test >=600000 passes and >=1000000 doesn't, so the rate is 8% and the bonus is 720,000 × 0.08 = 57,600. Note that 720,000 also satisfies >=300000. IFS returns the first match, and 600,000 is listed first, so the thresholds must run highest first.

P5. The AND gate. A bonus requires both sales of at least 600,000 and quota attainment of at least 90%. Write the eligibility formula, then classify these four people: P (640,000, 95%), Q (720,000, 85%), R (540,000, 98%), S (810,000, 91%).

Solution. With sales in B2 and attainment in C2: =IF(AND(B2>=600000, C2>=0.9), "Eligible", "Not eligible"). AND is TRUE only when both tests hold. P passes both, with 640,000 at or above 600,000 and 95% at or above 90%, so P is Eligible. Q passes on sales but fails on attainment, since 85% is below 90%, so Q is Not eligible. R passes on attainment at 98% but fails on sales, since 540,000 is below 600,000, so R is Not eligible. S passes both, at 810,000 and 91%, so S is Eligible. Two pass and two fail, each failing on a different condition. That pattern is why AND, which requires both, is the correct operator here, and OR, which accepts either, isn't.

P6. Guard the division. Conversion rate = deals ÷ leads. Write a formula that returns the rate but shows 0 when leads is zero, then evaluate it for (deals 48, leads 600) and (deals 48, leads 0).

Solution. With deals in A2 and leads in B2: =IFERROR(A2/B2, 0). For (48, 600), 48 ÷ 600 = 0.08 = 8.0%. For (48, 0), the division gives #DIV/0!, which IFERROR catches and replaces with 0. Better still, guard the cause explicitly so the intent is legible: =IF(B2=0, 0, A2/B2). This traps only the zero-lead case. Any other problem, say text in A2, still surfaces as an error you can see, where a blanket IFERROR would hide it.

P7. Refactor with LET. The formula =(A2*B2) - (A2*B2)*C2 computes revenue net of a discount but writes the revenue sub-expression A2*B2 twice. Rewrite it with LET, then evaluate for A2 = 180 units, B2 = ₹2,400, C2 = 15%.

Solution. =LET(rev, A2*B2, rev*(1-C2)) names the revenue once as rev, then returns rev reduced by the discount fraction. It reads as one clean thought and computes A2*B2 a single time. Evaluating it gives rev = 180 × 2,400 = 4,32,000, and net = 4,32,000 × (1 − 0.15) = 4,32,000 × 0.85 = 3,67,200. That's the same answer as the repetitive version, with half the surface area for a typo.

P8. Nested IF to IFS. Rewrite =IF(x>=90, "A", IF(x>=75, "B", IF(x>=60, "C", "D"))) as an IFS, and confirm both give the same grade for x = 82 and x = 59.

Solution. =IFS(x>=90, "A", x>=75, "B", x>=60, "C", TRUE, "D"). The TRUE catch-all replaces the innermost IF's final "else". To check, take x = 82. The first true test is >=75, so both formulas return "B". For x = 59 no threshold is met, so both fall to the final branch and return "D". Same grades either way. The IFS is flat and has one closing parenthesis instead of three. You can extend it with another band by inserting one pair of arguments, with no nest to open surgically.

P9 (timed, 6 min). Diagnose the disaster. A colleague paid commission at 5% on five reps (sales 4,00,000; 5,50,000; 6,10,000; 3,00,000; 4,80,000) by writing =B2*E1 in the top payout cell (with the rate in E1) and filling down. Their reported total is far too low. Explain the bug, give the total they got, and give the total they should have got.

Solution. The bug is an unlocked rate reference. =B2*E1 is correct only in the top row. Filled down, it becomes =B3*E2, =B4*E3, and so on, and E2:E5 are empty, so every payout below the first is sales × 0 = 0. Their total is therefore just the first rep's payout, 4,00,000 × 0.05 = 20,000. The correct formula is =B2*$E$1 (or =B2*CommRate). It gives payouts of 20,000; 27,500; 30,500; 15,000; 24,000 and a correct total of 1,17,000, verified as total sales 23,40,000 × 0.05. One check would have caught it in two seconds. Click the bottom payout cell and press F2, and the rate pointer lights up on an empty cell instead of E1.

P10 (timed, 8 min). The combined calculator. Each rep earns a flat 4% commission on sales (rate in a locked cell) plus a tiered bonus (1,000,000 or more at 12%, 600,000 or more at 8%, 300,000 or more at 5%, else 0). For reps A (1,150,000), B (720,000), C (340,000), give the commission formula, the bonus formula, and the total pay for each, plus the three grand totals.

Solution. Commission (rate in $G$1): =B2*$G$1. Bonus: =IFS(B2>=1000000, B2*0.12, B2>=600000, B2*0.08, B2>=300000, B2*0.05, TRUE, 0). Total pay: =commission + bonus. Results:

Grand totals: total commission 88,400, total bonus 212,600, total pay 301,000. This is a miniature of the applied project below. One absolute reference (the commission rate) and one piece of ordered logic (the bonus tiers) are combined and totalled, the two central ideas doing one useful job together.

Applied mini-project

Build a one-sheet commission-and-bonus calculator.

You've practised the pieces. Now assemble them into a small tool a sales manager could actually use, one that re-prices the whole team when a single assumption changes and that a stray blank can't silently break.

Set up (one sheet, a Table plus an assumptions block):

1. An assumptions block in a labelled corner, say F1:G4, holds the commission rate and the three bonus thresholds with their rates. Name the rate cell CommRate (Name Box ▸ type CommRate ▸ Enter). The habit this project exists to build is keeping every assumption in one labelled place, referenced absolutely.
2. A data Table (Ctrl+T) of reps with columns: Rep, Region, Sales, Quota attainment %.

Build the calculation columns:

1. Commission = Sales × CommRate. It's a named-range reference, so it's absolute by nature and fills perfectly down the Table.
2. Bonus is an IFS on Sales against the thresholds, highest band first, ending in a TRUE, 0 catch-all. Wrap it in IFERROR(…, "CHECK INPUT") so a blank or text Sales cell shows a visible flag, never a silent blank or a wrong number.
3. Eligible? is an AND gate, =IF(AND(Sales>=600000, Attainment>=0.9), "Yes", "No"), using your Table's structured column names.
4. Total pay = Commission + (Bonus if eligible, else just Commission). Decide the rule deliberately and write it with IF. If the expression starts repeating a sub-total, refactor it with LET.
5. A totals row below the Table summing commission, bonus, and total pay with SUM.

Then audit your own work, and treat the audit as the graded habit:

• Click the bottom Commission cell and press F2. Confirm the rate precedent lights up on the assumptions cell and not on a stray blank.
• On a rep whose sales land on a bonus boundary, press F2, select the tier test inside the IFS, press F9 to see which band evaluates TRUE, then press Esc (never Enter).
• Toggle Ctrl+` `` (Show Formulas) and sweep the calc columns for any cell where a number was typed over a formula.
• Change CommRate in its one cell and confirm every commission and every total re-prices. That proves your absolute references are correct.

Rubric. Score each row yourself: 0 (missing or wrong), 1 (partial) or 2 (correct and defended). Pass at 8 of 10 with no zero on any row.

A row scores zero when its own check fails, whatever the sheet looks like. A bonus column that happens to return the right numbers from bands ordered the wrong way passes by luck and breaks on the next rep you add, so it earns nothing here.

Keep the file. Later in the track you'll extend it by pulling the bonus rates from a lookup table instead of hardcoding them in the IFS, and then add data validation so a bad Sales figure can't be entered in the first place.

Lab: open the Clean the messy sales export workbook lab in Practice ▸ Excel and pass its checks.

Reading & resources

Function and shortcut references (keep these open while you work):

• ExcelJet (exceljet.net) is the cleanest short reference on the planet for IF, IFS, IFERROR, IFNA, named ranges, LET, and every function here. Each page has a plain-English example. Bookmark the "Excel functions" and "keyboard shortcuts" indexes. [Free] [Beginner]
• Microsoft Support has the official pages for LET, IFS, and "Switch between relative, absolute, and mixed references" (search those titles word for word). It's authoritative on version availability. [Free] [Beginner]
• Chandoo.org has tutorials on absolute vs relative references, named ranges, and formula auditing, written for analysts building real models. [Free] [Beginner–Intermediate]

Video (for the ideas that are easier watched than read):

• Leila Gharani (YouTube) makes LET, IFS, and dynamic-formula videos that are the clearest walkthroughs of when and why to use each. Start with her LET explainer. [Free] [Intermediate]
• ExcelIsFun (Mike Girvin, YouTube) covers references and logical functions exhaustively, from first principles. The "highline Excel class" playlists are a full free course. [Free] [Beginner–Intermediate]

Practice and reference depth:

• exceljet's "F4 key" and "Formula auditing" articles take three minutes each, and they turn the auditing section above into muscle memory. [Free] [Beginner]
• Wingman / Excel Practice Online and similar free drill sites are where to hunt for "absolute reference" and "IF/IFS" exercises. References only stick once you've filled enough grids that locking the right coordinate is automatic. [Free] [Beginner]
• John Walkenbach, Excel Bible (or Excel Formulas volume) is the reference-shelf book. Its chapters on formulas, names, and logical functions are the canonical written treatment when you want depth beyond a web page. [Paid] [Intermediate]

The Modern Analyst's Addendum

Everything above teaches this skill from first principles, by hand: that is how you learn it, and the mastery check still tests it that way. This addendum shows how a working analyst amplifies the same skill today. It adds; it never replaces. (R1/R10)

AI-Augment this skill

{
  "skill": "Reference discipline -- relative vs absolute vs mixed addressing, named ranges, IF/IFS/AND/OR/NOT logic, LET, and formula auditing (F2, F9, Trace Precedents/Dependents, Show Formulas)",
  "use": "Ask an assistant to propose a named-range and $-locking scheme for a new formula, or to explain in plain English what an inherited, unfamiliar formula does cell by cell -- then confirm the explanation against the sheet's own Trace Precedents arrows and an F9 spot-check before you trust it.",
  "tools": ["Microsoft Copilot in Excel (formula explain and suggest)", "a general-purpose LLM chat (Claude or ChatGPT) for reading an unfamiliar formula aloud in plain English", "Excel's own Formula Auditing pane and Name Manager -- not AI, but the ground truth any AI explanation must be checked against"],
  "prompt": "Here is a formula I inherited and don't fully trust: <PASTE>. Explain what it computes, cell reference by cell reference, and flag any hardcoded number in it that looks like it should be a named input instead.",
  "verify": "Select the formula, run Trace Precedents to see exactly which cells feed it, and press F9 on one sub-expression to see its live intermediate value. An assistant's prose explanation is a hypothesis about the formula; the auditing tools, pointed at the actual live references, are the check.",
  "diy": "You must be able to $-lock a reference by hand with F4, build and use a named range, and read a nested IFS -- with no assistant in the room. The mastery gate is unaided and tests exactly this."
}

Modern Data Analysis

By hand first. Worked Example 1 built the commission grid. It showed that =B2*F1 filled down zeros every row but the first, and fixed it with =B2*$F$1 or a named CommRate, so that one locked cell, or one name, feeds all eight formulas correctly.

Today's workflow. Decades of published spreadsheet audits keep finding the same category of result. The standing references are Raymond Panko's research programme at the University of Hawaii and the European Spreadsheet Risks Interest Group (EuSpRIG). Once actually audited cell by cell, the overwhelming majority of the real-world spreadsheets they examine contain at least one material formula error. An unlocked reference filled down a column is one of the most common single causes, because it produces no error value at all, just a quietly wrong number. Instead of repeating a headline figure from that literature, recompute the case above. You'll see the mechanism directly.

Tools & sources (IN + US). Excel's Formula Auditing pane (Trace Precedents/Dependents, Show Formulas, Error Checking) and Name Manager (Ctrl+F3) cover the habit itself. That principle scales up in regulation: name your numbers and don't let them hide anonymously. India: the MCA's XBRL filing mandate, under the Companies (Filing of Documents and Forms in XBRL) Rules, forces every disclosed figure to carry a structured, machine-checkable tag instead of appearing as a bare number in a document. The Companies (Accounts) Amendment Rules, 2021 require an audit-trail (edit-log) feature in accounting software. That rule asks the same "who changed what, and can I trace it" question that Trace Precedents/Dependents answers at cell level. US: the SEC's XBRL mandate (EDGAR companyfacts API) does the same job for US filings. Sarbanes-Oxley Section 404's requirement for documented, auditable internal controls over financial reporting is the regulatory-scale version of "can you Trace Precedents on this number."

# recomputed in-session — R3
import re

# WE1's own commission grid (module text): correct payouts at 4.5%
reps = [("Aarav", 1250000), ("Bhavna", 980000), ("Chetan", 1540000), ("Divya", 720000),
        ("Esha", 2100000), ("Farhan", 1375000), ("Gauri", 640000), ("Harish", 1890000)]
rate = 0.045
correct_total = sum(round(sales * rate) for _, sales in reps)
assert correct_total == 472275  # matches the module's own WE1 total exactly

# =B2*F1 filled down 8 rows: only row 2 (Aarav) reads the real F1; every row below reads an
# empty F-cell as 0 -- the module's own "the wheels come off" demonstration, made numeric.
broken_total = round(reps[0][1] * rate)
print(f"Correct team total: {correct_total}   Unlocked-reference total: {broken_total}   "
      f"Silently missing: {correct_total - broken_total} ({(correct_total-broken_total)/correct_total:.1%})")

# Audit-surface scan of the module's own WE2 IFS formula: how many hardcoded literals does
# even the "correct" formula carry, once cell references are stripped out?
formula = "IFS(B2>=1000000,B2*0.12,B2>=600000,B2*0.08,B2>=300000,B2*0.05,TRUE,0)"
ref_re = re.compile(r'\$?[A-Z]{1,3}\$?\d+')
lit_re = re.compile(r'(?<![A-Za-z$])\d+\.?\d*')
hardcoded = lit_re.findall(ref_re.sub("", formula))
print(f"Hardcoded literals in the module's own WE2 IFS formula: {hardcoded}")

Verify. The recomputed team total is ₹4,72,275, matching the first worked example's stated total. The unlocked-reference version collapses to ₹56,250, Aarav's row alone, understating the true total by 88.1% with no #REF!, no red cell, and a SUM that still runs without complaint. The formula scan finds seven hardcoded literals (four thresholds, three rates) inside the bonus example's own IFS. That count comes from inspecting the formula directly, and it confirms that even a formula built the "right" way still leaves real audit surface on the table.

Quantitative lens

The unlocked-reference loss grows with the team, and the shape is exact. Take an idealised team of equal per-row payouts where only the first row survives. The fraction silently lost is (N-1)/N: 50.0% at N=2, 87.5% at N=8, 96.9% at N=32, 99.0% at N=100. The first worked example's unequal payouts give 88.1% at N=8. That's close to the idealised curve but a little off it, because Aarav's surviving row happens to be a slightly below-average earner in this dataset.

The direction is the lesson. A bug that looks survivable on a four-row test sheet becomes almost total on the two-hundred-row payroll it will actually run against, and a small test sheet is where this class of bug is least likely to be caught.

A magic number is a second failure mode, independent of an unlocked reference, and naming an expression doesn't by itself remove it. The bonus example's LET refactor names the repeated cell reference (s = B2), so it's typed once instead of six times. It leaves the seven business constants alone: 1000000, 0.12, 600000, 0.08, 300000, 0.05, and the catch-all 0. All of them remain typed directly into the formula body. Name Manager can't see them, and neither can anyone auditing which cell feeds the bonus rate. LET names the structure of a calculation. A defined name or an input cell names the inputs. This formula has done only the first.

You might think the footing check covers you. It covers you against the big failure, but even the first worked example's cross-check would miss the smaller version of this bug. That example teaches a real defence: total payout must equal total sales × rate. It catches the unlocked-reference collapse instantly, because an 88.1% miss is impossible to overlook. It would not catch a single mistyped rate, say 5.4% typed where 4.5% was meant on one rep's row. That error moves the team total by only about 4%, comfortably inside the range a busy analyst's eye calls "close enough" on a footing check. A cross-check that verifies a total isn't the same as one that verifies every input, and a plausible, small, wrong number survives in the gap between them.

Limits: the (N-1)/N curve assumes equal per-row payouts and a specific failure pattern, in which only the first row survives. Real broken references fail in whatever pattern the specific formula and fill direction produce, so the curve is a shape to reason with and no number to quote for any other spreadsheet. The 4% figure for a mistyped rate illustrates a mechanism, in which a small per-row error is diluted by the total it is summed into. It makes no claim about how often such typos occur in practice. That base rate is the kind of figure Panko's and EuSpRIG's audits report, and it isn't restated here without the paper in hand.

Where this goes next: galaxy cross-links

• G2 · CS2.02 Version Control with Git: a named range and a $-locked reference are a spreadsheet's only defence against silent drift; Git's diff and blame give code the same defence with a permanent, searchable history a workbook cannot keep.
• X1 · AI0.06 Failure Modes, Verification & the Primary-Source Guardrail: an unlocked reference that fills down to zero and a language model that answers fluently and wrongly share one failure shape. There's no error and the result looks confident, so a verification step is the only thing standing between the two.

Teach it back & journal

Feynman prompt. Write a one-page explainer for a smart 15-year-old titled "The dollar sign that saved the payroll." A shopkeeper pays every worker the same 5% bonus on what they sold, and keeps that one "5%" written on a single sticky note by the till. Explain, without using the word "absolute," why the formula for each worker's bonus has to point back at that one sticky note and not at "the note next to me," and what disaster happens on the second worker's row if it points at the wrong place. Only in the last paragraph may you use the words relative reference, absolute reference, $, and F4, attaching each to the part of the story it explains.

Journal prompt. Open any spreadsheet you already keep (a budget, an expense tracker, a project sheet) and find one formula you copied down a column. Press F2 on the bottom cell and look at where its references actually point. Were the ones that should have stayed fixed locked with a $? Were any that should have moved accidentally frozen? Write down three things. First, one place your own past self got a reference wrong, or got lucky. Second, one assumption currently typed inside a formula that should be pulled out into a labelled input cell. Third, the one habit here that would most reduce errors in the work you actually do: locking as you write, auditing the bottom row, or returning a visible flag instead of a silent blank. End with a sentence on why you'll never again fill a column without checking its last row as well as its first.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.