AppGarden Selection-Scoped Formula Explainer logo AppGarden

Selection-Scoped Formula Explainer

Taking over someone else's Excel™ spreadsheet: a handover checklist

By Paul Kemp · Published

You have been handed a workbook, and the person who built it has moved on. You cannot trust what you cannot read, so before you change anything, build a picture of how it works.

The checklist

  1. Sheets and what each is for. List them, including hidden ones.
  2. Inputs. Which cells does a person type into?
  3. Names. What does each defined name refer to?
  4. Typed-in values. Which formulas have a rate or threshold typed inside them?
  5. Links. Does it read from other workbooks?
  6. Filled columns. Does any cell in a column differ from the pattern of its neighbours?
  7. Lookups and error handling. Where do VLOOKUPs and IFERRORs sit?
  8. Outputs. Which cells does somebody else read?

Do it by hand

These steps apply to Excel™ in Microsoft 365. Work on a copy.

  1. Right-click a sheet tab and choose Unhide to see hidden sheets.
  2. Open Formulas → Name Manager (Ctrl+F3). To list names on a sheet, use Formulas → Use in Formula → Paste Names → Paste List.
  3. Press F5, choose Special and tick Constants to highlight typed values among formulas, and Formulas to see the reverse.
  4. Check Data → Edit Links for external sources.
  5. Press Ctrl+` to show formulas, and scan a filled column for a cell whose formula differs.
  6. Write a line per sheet in a notes sheet at the front: what it does, where it reads from, what depends on it.

Explain cells without guessing

Selection-Scoped Formula Explainer explains each selected cell with its real headers and names and says how many other formulas on the sheet carry the same typed-in value. When you select a cell in a filled column, it names any cells that differ from the pattern. The paid tier adds whole-sheet explanation, a handover pack as Markdown, and a glossary of names and table columns. Explaining one cell at a time is free.

See Selection-Scoped Formula Explainer for Excel™

Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Selection-Scoped Formula Explainer is not affiliated with or endorsed by Microsoft.