Trace Precedents and Dependents in LibreOffice Calc

Managing complex formulas in LibreOffice Calc requires auditing tools to verify data accuracy and locate calculation errors. The built-in Detective feature provides visual arrows that map out cell relationships, allowing you to see which cells supply data to a formula (precedents) and which formulas rely on a specific cell (dependents). This guide explains how to access and utilize the Detective tools in LibreOffice Calc to inspect and troubleshoot your spreadsheets.

Accessing the Detective Menu

The Detective feature is located in the main application menu. To open it, navigate to Tools > Detective. From this submenu, you can trigger specific trace commands, remove arrows, or enable advanced auditing modes.

How to Trace Precedents

Precedent cells are the cells referenced by the formula in your currently active cell. Tracing precedents helps you verify where a formula is pulling its values from.

1. Select the cell containing the formula you want to inspect.
2. Go to Tools > Detective > Trace Precedents or press Shift + F7.
3. A blue arrow will appear on the sheet, pointing from the referenced input cells directly to the active formula cell.
4. If a referenced cell is itself a formula that depends on other cells, run the Trace Precedents command again to display the next level of indirect precedents.

How to Trace Dependents

Dependent cells are cells containing formulas that rely on the value in your currently selected cell. Tracing dependents is useful before editing or deleting a cell to ensure you do not break downstream calculations.

1. Select the cell whose impact you want to check.
2. Go to Tools > Detective > Trace Dependents or press Shift + F5.
3. A blue arrow will appear, pointing away from the active cell to all cells that use its data in their calculations.
4. Run the command again to reveal subsequent levels of dependent formulas across the sheet.

Removing Detective Arrows

Trace arrows remain visible on the spreadsheet until explicitly removed, but they do not alter cell values or print by default.

• Remove Specific Arrows: Navigate to Tools > Detective > Remove Precedents or Tools > Detective > Remove Dependents to remove traces level by level.
• Remove All Arrows: Navigate to Tools > Detective > Remove All Traces to clear every precedent and dependent arrow from the sheet simultaneously.

Additional Detective Tools

LibreOffice Calc includes several additional auditing features within the Detective menu:

• Trace Errors: Located under Tools > Detective > Trace Errors, this tool draws red arrows pointing to all cells causing an error (such as #DIV/0! or #VALUE!) in the selected formula cell.
• Mark Invalid Data: Highlights all cells containing values that violate established Data Validity rules by placing a red circle around them.
• AutoFill Mode: Allows you to click continuously on various cells to instantly draw precedent or dependent traces without reopening the menu each time.