Excel builder map

What the map shows

An Excel builder is the workbook that sales engineers configure and price products from. Dropdown lists take the options, and formulas apply the plant's rules. Lookup tables hold the costs and times, and VBA macros often fill in the rest. The map lists each of those parts and how they connect.

• Sheets. The map lists every sheet with its formulas, typed values and dropdown cells, and it flags the hidden and very hidden ones.
• Inputs. These are the cells with a dropdown list, from data validation or a form control, and the typed values that formulas read directly.
• Lookup tables and named ranges. The map lists each range that a lookup function searches, with its column headings. It also lists each defined name and what it refers to.
• Outputs. An output is a formula that no other formula reads, such as the price at the bottom of the quote sheet.
• Formula chains. For each output, the map follows the longest path from an input through the formulas that lead to it, and counts its depth.
• VBA modules. Each module shows its procedures and its source code, which you can read on the page.
• Gaps. These are the places where a price can be wrong without an error showing. The next section describes each kind.

How to map your Excel builder

1. Drop the workbook on the box above, or click the box to choose it. The map reads .xlsx, .xlsm and .xls files up to 40 MB.
2. Read the overview. It counts the parts and shows how the deepest output, usually the price, is built from the inputs.
3. Open the other tabs to see the inputs, the tables and names, the outputs and chains, the gaps and the macros.
4. Download the map as a PDF or HTML report to share it. Download the rules as a CSV to sort and filter them in a spreadsheet.

The CSV lists every formula with its cell, the label beside it, its saved value, its depth and its gaps. Each formula in the CSV starts with an apostrophe. Remove it before you paste a formula back into a workbook.

The gaps the map flags

A gap is a place where the workbook can produce a wrong price and still look correct. Check each one with the person who maintains the builder.

• Lookups that fall back to zero. IFERROR returns the value you give it whenever its formula returns an error. A VLOOKUP that cannot find an exact match returns #N/A, so IFERROR(VLOOKUP(...),0) prices a missing row at zero. The map also flags IFNA, IF(ISERROR(...)) and the if_not_found argument of XLOOKUP when they return a fixed value.
• Numbers typed into formulas. A rate written into a formula, such as =B4*1.35, is not an input anyone sees, and it stays the same until someone edits the formula. The map groups these numbers by value. It skips 0 and 1, and settings such as the column number in VLOOKUP or the decimal places in ROUND.
• Links to other workbooks. Microsoft now calls an external reference a workbook link. When the source workbook is closed, the link carries its full path, as in 'C:\Reports\[Budget.xlsx]Annual'!C10:C25. The map lists each linked workbook and the cells that read it.
• #REF! errors. Excel shows #REF! when a formula refers to a cell that is not valid. That happens most often after the cells it read were deleted or pasted over. The map lists the formulas and names that contain one.
• Volatile functions. Microsoft's Excel recalculation page lists NOW, TODAY, RANDBETWEEN, OFFSET and INDIRECT as volatile, and INFO, CELL and SUMIF as volatile depending on their arguments. Excel reevaluates these functions and everything that depends on them every time it recalculates. The map flags the first five, because a price that reads TODAY can change between two openings of the same quote.
• Circular references. The same page explains that when a cell depends on itself, directly or through other cells, Excel detects the circular reference and warns the user. The map lists the formulas in each loop.
• Hidden and very hidden sheets. A hidden sheet can be unhidden from Excel's menus. Microsoft's VBA reference says the user cannot make a very hidden sheet visible, so the rules on it are easy to miss.
• Formulas showing an error. A formula that shows #N/A or #VALUE! with the inputs saved in the file is listed with its error.

How the file is read

Your workbook is read in this browser and never uploaded. Microsoft's list of Excel file formats describes .xlsx and .xlsm as XML-based, and .xls as the Excel 97-2003 binary format, BIFF8. The Open XML formats store their XML with ZIP compression.

SheetJS, an open source spreadsheet reader, reads the cells, the formulas and the names. It runs in a background worker, so the page stays responsive while a large file is read. The map reads the dropdown lists, the form controls and the workbook links from the file's own XML.

Excel stores each formula's last calculated value in the file. The map shows those saved values, so it shows the price the workbook last calculated without recalculating anything.

Microsoft's list of formats says an .xlsx workbook cannot store VBA macro code, while an .xlsm workbook can. In a macro-enabled file, the macros sit in a VBA project part defined by the [MS-OVBA] specification. Its data is stored in a structured storage, and each module's source code is compressed with run length encoding. The map opens the storage, decompresses each module and lists its procedures.

The first time you use the map, your browser loads SheetJS from cdn.jsdelivr.net. The page counts how often the map is used, with the file type and size. It never records the file, its name or anything read from it, and the results are hidden from PostHog's session recordings.

What the map leaves out

• The map does not run macros or recalculate formulas. A value that a macro writes when the workbook opens shows as it was saved.
• References built by INDIRECT or OFFSET are followed only as far as the cells their formulas name.
• Each cell is labelled with the nearest text to its left or above it. That is usually the field name, but not always.
• The map reads data validation lists and form control dropdowns. It does not read ActiveX controls, charts or pivot tables.
• It cannot open a workbook protected with a password to open. Save a copy without that password first.

Macros that run on their own matter as much as formulas. A Worksheet_Change procedure, for example, runs when cells on the sheet are changed, so it can rewrite a price after an input changes.

From a map to a quoting app

Excel's Trace Precedents and Trace Dependents commands draw tracer arrows between a formula and the cells it reads. The map lists the same relationships for the whole workbook, which is where moving the rules out of the file starts.

The guide to converting an Excel quoting workbook into a web app explains how a map becomes a tested app. Turning your Excel builder into a CPQ app describes what ThriveAI builds from it.