Ravindra BagaleCourses & study guides Track your progress
Hard challenge

Hard: Typed-over formula in a shared sheet

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

D7 holds 1650 instead of =B7*C7. Looks fine until Price/Units change. Restore the formula.

About this data: Made-up forecast for Vivek Menon (Kochi) and Hannah Berg (Oslo). Practice only.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Shared workbooks: one paste/type destroys a formula unnoticed.

What: Four months; Mar revenue hard-typed when broken.

How:

  1. Show Formulas (Ctrl+`).
  2. Find D7 without =.
  3. Restore =B7*C7.
  4. Change C7 — D7 should move.

Ravindra Bagale’s Tip

Protect formula cells; use Show Formulas to audit.

Bugs explained (with mini examples)

Bug 1 — Typed-over formula

D7 dead number.

Bug 2 — No audit before share

Ctrl+` catch.

Practice tasks (12)

  1. Task 1. Which cell lacks formula?
    Hint: D7.
  2. Task 2. Restore.
    Hint: =B7*C7
  3. Task 3. C7=120 fixed D7?
    Hint: 1800.
  4. Task 4. Broken after same edit?
    Hint: Stays 1650.
  5. Task 5. Interview.
    Hint: Someone typed over Mar revenue.
  6. Task 6. Show Formulas.
    Hint: Ctrl+`
  7. Task 7. Total formula.
    Hint: =SUM(D5:D8)
  8. Task 8. Protect D.
    Hint: Optional.
  9. Task 9. Comments culture.
    Hint: Yes.
  10. Task 10. Bonus CF audit.
    Hint: Advanced.
  11. Task 11. Document owner.
    Hint: Yes.
  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. Restore =B7*C7 on D7.
  2. Edits to Units/Price update Revenue.

Quick quiz

Q1. Revenue number with no = in formula bar is:
Q2. Best quick audit:
Q3. After typing over formula, changing price:
Q4. Shared sheets need:
Q5. Mar row number: