Ravindra BagaleCourses & study guides Track your progress
Hard challenge

Hard: Stale pivot / snapshot after append

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

Source gained South 200 and East +50 but PivotSnapshot still shows Grand 450. Refresh/rebuild to Grand 700.

About this data: Made-up orders for Dev Patel (Ahmedabad) and Claire Dubois (Paris). Practice only.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Pivot caches and pasted snapshots go stale when source data grows.

What: Broken grand 450; fixed 700.

How:

  1. Compare Source sum to snapshot.
  2. Refresh pivot or rebuild.
  3. Confirm Grand 700.

Ravindra Bagale’s Tip

After every append: Refresh, or use a Table as source.

Bugs explained (with mini examples)

Bug 1 — Stale snapshot

Missing South.

Bug 2 — Pasting pivot values

No refresh path.

Practice tasks (12)

  1. Task 1. Source SUM?
    Hint: 700.
  2. Task 2. Broken grand?
    Hint: 450.
  3. Task 3. Missing region?
    Hint: South.
  4. Task 4. East should be?
    Hint: 200.
  5. Task 5. Interview.
    Hint: Pivot not refreshed.
  6. Task 6. Refresh command.
    Hint: Right-click → Refresh.
  7. Task 7. Why Tables help.
    Hint: Auto expand.
  8. Task 8. Pastes vs live.
    Hint: Pastes go stale.
  9. Task 9. Document SOP.
    Hint: Yes.
  10. Task 10. North still 120?
    Hint: Yes.
  11. Task 11. Bonus schedule.
    Hint: Advanced.
  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. Refresh/rebuild snapshot.
  2. Grand 700.

Quick quiz

Q1. After appending source rows, pivots need:
Q2. Fixed grand:
Q3. Broken grand:
Q4. South amount:
Q5. Best source: