The Critical Spreadsheet Only One Person Understands: Extracting the Knowledge Before You Automate

What is missing from a spreadsheet like this is not documentation but the decisions nobody ever wrote down: when a row gets skipped, which number overrides another, and what happens when the file that should have arrived did not. Extracting that knowledge starts with reading the file itself, and only then with talking to the person who runs it.

The real risk is not that the employee leaves

Leaving is only the date the risk materialises. The risk itself is present every single day, and it looks like this: nobody but one person knows which file a particular number comes from, at which point it gets pasted in, and what to do when it looks wrong. As long as that person is around, the machine works and nobody feels anything is missing.

This is why knowledge extraction is not a documentation project. It is the step that precedes automation, because you cannot program a decision nobody has articulated. Converting a spreadsheet like this straight into a system produces something that looks right most of the time and fails on precisely the cases the spreadsheet was built to handle in the first place.

Read the file before you interview anybody

An hour with the file saves three conversations. The tools are inside Excel already, and they answer questions a person does not always remember to answer:

• Switch the sheet to display formulas instead of values and scan it by eye. A cell that looks like data and turns out to be a formula is your first finding.
• Use Trace Precedents and Trace Dependents to see where each number comes from. Note the documented limitation: the arrows do not trace named constants, and they cannot show formulas in another workbook while that workbook is closed.
• Open the Name Manager. A named range pointing at an area that no longer exists is a quiet time bomb.
• Check the list of links to external workbooks. Every one of them is a dependency on a file that lives with somebody else, on a path that can change.
• Search for formulas containing a hardcoded number. A coefficient written directly into a formula is a business decision disguised as a technique.
• Search for INDIRECT. A reference assembled from a text string does not exist until the formula evaluates, so dependency tracing cannot follow it.
• Check for hidden tabs and hidden rows or columns. They exist almost every time, and usually in the important sheets specifically.
• Check the calculation mode. A workbook set to manual calculation displays stale values without warning anybody.

What do you ask to get a real answer?

Not "walk me through the process". The answer to that always describes the happy path, which is exactly the part you could have inferred from the file without asking. The questions that return new information are aimed at deviation:

• When was the last time the number came out wrong, and how did you notice?
• What do you do when the file that should arrive on Sunday does not arrive?
• Is there a customer or an item you handle differently from all the others? Why?
• Which cell do you eyeball before sending, even when everything looks fine?
• What happens if this gets run twice on the same day?
• What information do you bring in from outside the file: a message, a phone call, a screen in another system?

That last question surfaces what will never appear in the spreadsheet. A significant part of the work in critical spreadsheets rests on information living in a completely different channel, and the person running it does not see that as part of the process - just as part of their day.

The mapping sheet: one row per tab

Turning the conversation into something you can work against means a table with fixed fields. The identical fields for every tab, so gaps become visible by comparison.

That last field is the only one you cannot fill in by reading the file. It comes only from a conversation, and it is the single reason the conversation is needed.

A worked case: the tab holding one exception

A pricing workbook with four tabs. The price list tab pulls a discount from a customer table with a lookup, and the lookup is wrapped in IFERROR returning the list price when no match is found. So far, reasonable. The finding turns up in the formula scan: on one row, for one customer, there is no lookup at all but a coefficient written directly into the formula.

What had happened: two years earlier a special price was agreed with that customer which did not fit the structure of the discount table, and the fix was to write it into the formula. Nobody documented it, because it was not a change to the process - it was a two-minute correction.

Now count what the wrapper concealed. IFERROR converts every lookup failure into a full list price, so a customer deleted from the discount table keeps receiving a plausible-looking price with no warning at all. An automation that converts this tab as-is will reproduce both faults: it will drop the exception and it will hide the error. The discovery cost twenty minutes of displaying formulas. Not discovering it would have surfaced on the first invoice. Handling the errors that a scan like this exposes is covered in recovering from SPILL, REF and N/A errors in Excel.

When is the knowledge actually extracted?

When a second person runs a real cycle end to end without asking a question, and their result matches the original owner's. A document is not a test; execution is a test. Three conditions complete it:

• You shadowed at least one full cycle in real time, including the steps that happen outside the file.
• You documented one real exception that occurred, not a hypothetical one described in conversation.
• A sample input exists alongside its expected output, so any future implementation can be compared against them.

Only after those three is there a basis for automation, because there is something to check it against. The next decision - whether and when to convert the spreadsheet into a system - is covered in knowing when a spreadsheet has been outgrown. And if the spreadsheet holds customer data, that carries consequences for how it is stored and shared, set out in Privacy Law Amendment 13 and the customer spreadsheet.

Sources

• Microsoft - Display the relationships between formulas and cells
• Microsoft - Excel specifications and limits