Trace precedents in Excel across every sheet

How to trace precedents in Excel

A precedent is a cell that a formula reads. In Microsoft's example, when cell D10 holds =B5, cell B5 is a precedent of D10. Excel shows precedents as tracer arrows, from the Formulas tab (Microsoft's steps):

1. Select the cell that holds the formula.
2. On the Formulas tab, in the Formula Auditing group, select Trace Precedents. A blue arrow points from each cell the formula reads.
3. Select Trace Precedents again to add the next level: the cells that those cells read.
4. Select Remove Arrows when you are done. Remove Precedent Arrows takes them away one level at a time.

A red arrow marks a cell that causes an error. Excel for Mac has the same commands on its Formulas tab, as Microsoft's steps to fix a circular reference show. To see a formula's precedents in color without arrows, select the cell and press F2.

Keyboard shortcuts for precedents and dependents

Microsoft's Go To Special dialog selects precedents from the keyboard:

• Press Ctrl+G, then select Special.
• Choose Precedents, then Direct only for the cells the formula reads, or All levels for every cell below them too.
• Choose Dependents instead to select the formulas that read the active cell.

For Excel for Mac, Microsoft's list of keyboard shortcuts gives keys for the same selections. Ctrl+Shift+{ selects every cell a formula reads, at all levels. Ctrl+] selects the formulas that read the active cell directly, and Ctrl+Shift+} selects them at all levels.

On Windows, a community answer on Microsoft's Q&A forum describes Ctrl+[ as a faster route. It selects the cells a formula reads on the same sheet, and Tab moves between them. A later answer confirms it in Excel for Microsoft 365 on Windows, but not in Excel for the web. Microsoft's Windows shortcut list leaves it out, so try it in your version first. To show every formula on the sheet instead of its result, press Ctrl+` (the grave accent), as Microsoft's steps to display formulas say. The shortcut list gives the same keys on a Mac.

Trace precedents to another sheet

When a precedent sits on another sheet, Excel cannot draw an arrow to it. A black arrow joins the selected cell to a small worksheet icon instead, a line Microsoft Press describes as dashed. Double-click that arrow to open the Go To dialog, then double-click a reference in the Go to list to jump to the cell. A precedent in another workbook works the same way, but that workbook must be open.

A formula that reads three other sheets means three trips through the Go To list, one level at a time. The tool above lists every precedent with its sheet name in one tree. A chain from the quote sheet through the cost sheet to the rate sheet reads from top to bottom.

Trace dependents in Excel

Dependents run the other way: they are the formulas that read a cell. Select the cell, then select Trace Dependents on the Formulas tab. Each click adds the next level of formulas, and Remove Dependent Arrows clears one level at a time. In the tool above, choose Dependents or Both before you trace.

Tracing the dependents of a rate shows every price that changes with it. That is the list to read before anyone edits the rate.

Why the arrows stop

Microsoft lists what the auditing tools cannot trace:

• references to text boxes, embedded charts, or pictures
• PivotTable reports
• references to named constants
• formulas in a closed workbook that refer to the active cell

Excel beeps when it has traced every level, or when you ask it to trace one of these. Two functions also hide their precedents. INDIRECT turns text into a reference, and OFFSET returns a reference a number of rows and columns away from a starting cell. Excel works both out when it calculates, and its recalculation guide lists both as volatile, recalculated every time.

The tool marks each place where a trace ends. A link to another workbook ends its branch, INDIRECT shows the cells its text comes from, and OFFSET shows its starting cell. Defined names and Excel tables are followed. A name shows what it refers to, and a table column such as Belts[Width] resolves to its cells.

How to use this tool

1. Drop a workbook on the box, or select Try the sample workbook. The tool reads .xlsx, .xlsm, .xlsb, and .xls files up to 40 MB.
2. It traces the deepest output first, usually the price. Type any other cell, such as Quote!F42, or pick an output from the list.
3. Choose Precedents, Dependents, or Both. Each row shows its level, the cell, its label, its saved value, and its formula.
4. Select a cell address in the tree to trace from that cell. Download CSV saves the whole tree for Excel.

Each cell appears once, at the first level where the trace reaches it. Later appearances say Repeat and link back to it. The values come from the last calculation saved in the file, because the tool does not recalculate.

A worked example: tracing a conveyor quote

The sample workbook prices a belt conveyor section, a product invented for this page. Its quote total sits in Quote!C16. Tracing it returns this tree:

• 18 formulas and 9 typed inputs, plus 32 values in 6 ranges on the Prices sheet
• 4 sheets: Quote, Costing, Prices, and Settings
• 4 defined names: Markup, SteelPrice, LabourRate, and FreightRate
• 8 levels, down to the steel price in another workbook

Two branches end early. The steel price comes from a second file, Steel prices.xlsx, and the finish surcharge comes through INDIRECT. In Excel, those are the places where the arrows stop and someone has to know where to look.

Traced the other way, the labour rate in Settings!B3 feeds 7 formulas over 6 levels, and the last of them is the quote total. That is the list to check before the rate changes.

Auditing a quoting or pricing workbook

A quoting workbook fails quietly: the price looks right, and one rule behind it is wrong. Trace the total and check these as you go down the tree:

• Numbers typed into formulas, such as =B4*1.35. A rate inside a formula never shows up as an input, so it stays the same until someone edits the formula.
• Lookups that fall back to zero. IFERROR returns the value you give it when its formula gives an error, so IFERROR(VLOOKUP(...),0) prices a missing part at zero.
• VLOOKUP on approximate match. With its last argument left out, VLOOKUP uses an approximate match, which can return a neighbouring row's price when a part number is missing.
• Links to other workbooks, which hold the value from the last time the link was updated.

The Excel builder map runs all of these checks but the approximate-match one across the whole workbook, and lists every gap it finds. The guide to converting an Excel quoting workbook into a web app explains what happens once the rules are written down. To read one of those formulas part by part, paste it into the Excel formula explainer.

How AI reads a whole workbook's rules

Tracing one cell answers one question. Moving a quoting workbook into software needs every rule written down, with the inputs and tables behind it. An AI model can walk each formula chain the way this tool does. It can then describe each step in the plant's own terms, such as "freight is the frame weight times the rate per kilogram."

That map is the first step of ThriveAI's AI CPQ offer, at no charge. It lists every input and option, the rules, every lookup table and where its numbers come from, and the gaps. In the app built from it, a named person approves every quote before it goes out. The guide to CPQ for manufacturing shows how that app configures, prices, and draws each quote.

What stays on your computer

The workbook is read in this browser and never uploaded. The first time you open a file, your browser loads SheetJS, an open source spreadsheet reader, from cdn.jsdelivr.net. It runs in a background worker, as it does for the Excel builder map. The page counts how often the tool is used. It records the file type, its size, and counts of sheets, formulas, and traced cells, never the file, its name, or anything in it.