In this guide 6 sections
Start with the workbook’s structure, not its numbers. List every tab, hidden ones included, then named ranges, outside links and macros, and trace one output back through its nested IFs, writing each decision as a plain rule. Then have the people who rely on the sheet confirm every rule before anything is replaced.
Map the workbook before you read a single formula
Work only on a dated copy, because one stray keystroke in a pricing sheet can quietly change next week’s quotations for every customer on the list. Keep the original read-only, in a folder nobody else touches.
Then list what the file contains. Open Name Manager, Edit Links and the macro list, then check the Visual Basic editor for very hidden sheets, which the right-click Unhide menu never shows. Write every finding on one page. Hidden tabs hold the surprises.
- Every tab, including hidden and very hidden ones
- Named ranges and where each one points
- Links to other workbooks, often on a former employee’s drive
- Macros, and the buttons that run them
- Data validation lists that feed drop-down cells
Trace the price backwards from one output
Take the unit price on a quotation, then use Trace Precedents and Evaluate Formula to step backwards from it one click at a time. Each arrow ends at an input cell, a lookup table or another formula, and you should follow them all until none is left.
Nested IF statements are the usual trap. A formula seven levels deep is a decision tree squeezed onto one line, so pull it apart on paper as condition, result, condition, result. Read the final else with care. It often hides a default nobody remembers choosing, such as a freight surcharge or a rate card for one old distributor.
Watch for VLOOKUP with approximate match, which quietly returns the nearest lower row when a code is missing, and for multipliers typed straight into a formula. A bare 1.18 inside a cell is a rule with no label. Circle every one. Array formulas such as SUMPRODUCT and INDEX-MATCH pairs deserve the same patience, since each can encode a whole price table.
Write each rule as a sentence a colleague could check
For every decision, write one line in the business’s own words. When the customer is in tier B and the order passes the quantity break, the discount comes from column F of the Rates tab. Add the cell address so anyone can audit the line later. Where a formula refers to a lookup table, copy the table’s column headings into the log as well, so the reader can see which row the formula actually matched.
Keep exceptions in a separate list. Yellow rows, typed values pasted over formulas and cell comments like “ask Priya” all mark places where the rule failed and a person took over. Each one deserves its own entry.
- The rule in one plain sentence
- The cell or range it came from
- The inputs it reads
- One worked example with a known answer
- The person who can confirm it
Get the rules confirmed by the people who use them
Take the written list to the estimator, the sales coordinator and the accounts clerk, and sit beside them instead of emailing. Ask each for three recent quotes, then compare what the sheet produced with what they would have charged by hand if the sheet had been switched off. Note the quote number, the date and the customer on each line so that any difference can be traced back to one input, one tab or one typed override.
Differences are findings. Sometimes the sheet is simply wrong. A stale tax rate is the common culprit. Sometimes staff have worked around it for years and nobody told the owner, so the business must pick the real rule before any new system copies it.
Ask a manager to sign the final list. Unsigned rules drift. Date the signature.
Test your reading against past orders before replacing anything
Build test cases from old orders: ordinary ones, boundary ones sitting exactly on a quantity break, and odd ones for a special customer. Include one quote from before the last price rise and one from after it, since a sheet edited mid-year often returns different answers for the same customer. Run each through the original sheet and record the answer, because a replacement must reproduce those answers or explain every difference in writing.
Leave the old file running until a parallel run has matched. Keep a short list of every difference found during the run, with the row, the old answer and the new one, so the manager can sign off each. This week, pick ten past quotes and write the rule behind each price.
Formula reading log
A one-page log with a line for each decision, with columns for sheet name, cell or range, formula as written, plain-English rule, inputs, exceptions seen, confirmed by and date. A block at the top lists the hidden sheets, named ranges, outside links and macros found, each with a tick when checked.
Open a blank worksheet to printQuestions owners ask
How do I find hidden sheets in an Excel workbook?
Right-click a tab and choose Unhide for ordinary hidden sheets. Sheets set to very hidden only appear in the Visual Basic editor’s project pane, where their visibility property can be changed on your copy.
What if a macro has no comments at all?
Read it line by line and note which cells it reads and writes, then run it on a copy with a known input and compare the result. Describe its effect as a rule rather than copying its code.
Should I rebuild the spreadsheet or replace it?
Decide after the rules are written and confirmed. If the rules are few and stable, a cleaned sheet may do, but if many people edit it or errors cost money, a database system fits better.
Who should confirm the pricing rules?
The people who quote and invoice from the sheet daily, plus a manager who owns pricing, since staff know the workarounds and the manager decides which version counts.