Ravindra BagaleCourses & study guides Track your progress
Hard challenge

Hard: Stale named range

By · Free ·

Absolute refsNumbers as textLeading spacesFilter left onChart range% vs decimalDuplicate headerROUND vs displayFreeze panesPaste valuesVLOOKUP & textVLOOKUP col indexHLOOKUP TRUESUMIF spacesCOUNTIFS ORIF nest orderDate serialINDEX MATCHWildcard COUNTIFMixed currencyCircular & datesCircular interest3D sum renameNamed range staleCF vs IFPivot staleHard-coded NPVValidation movedPQ step orderTyped-over formulaSUM wrong anchorsFiscal year Apr–MarFind all bugsInterview oral

SalesAmt points at B5:B9 so SUM misses W6–W7. Broken 750 → fixed 1060 when range is B5:B11 (LibreOffice verified).

About this data: Made-up sales for Kabir Rao (Nagpur) and Elena Petrova (Moscow). Practice only.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Named ranges do not auto-expand unless they are Tables.

What: Seven weeks; broken name stops at week 5.

How:

  1. Formulas → Name Manager.
  2. See SalesAmt → B5:B9.
  3. Extend to B5:B11.
  4. SUM → 1060.

Ravindra Bagale’s Tip

Prefer Excel Tables (Ctrl+T) so totals expand with new rows.

Bugs explained (with mini examples)

Bug 1 — Static named range

Misses new rows.

Bug 2 — Trusting SUM(Name)

Re-audit after appends.

Practice tasks (12)

  1. Task 1. Broken SUM?
    Hint: 750.
  2. Task 2. Fixed?
    Hint: 1060.
  3. Task 3. Open Name Manager.
    Hint: SalesAmt.
  4. Task 4. Extend refers-to.
    Hint: $B$5:$B$11
  5. Task 5. Interview sentence.
    Hint: Named range was stale.
  6. Task 6. W6+W7?
    Hint: 310.
  7. Task 7. Convert to Table.
    Hint: Better.
  8. Task 8. Dynamic INDEX end.
    Hint: Optional.
  9. Task 9. Insert mid-range help?
    Hint: No.
  10. Task 10. Document owner.
    Hint: Yes.
  11. Task 11. List all names.
    Hint: Name Manager.
  12. Task 12. Save.
    Hint: Yes.

In-browser formula check

Type the fixed formula you would put in the named cell. We check formula text — open the workbook for full recalculation.

Step-by-step solution (try first)
  1. Extend SalesAmt to B5:B11.
  2. SUM 1060. LO: 750 → 1060.

Quick quiz

Q1. Static named ranges when rows append:
Q2. Fixed LO sum:
Q3. Broken LO sum:
Q4. Safer pattern:
Q5. Name Manager under: