Formula auditing

Visualize formula dependencies with trace precedents and dependents

Formula auditing allows you to visualize and understand the relationships between cells in your spreadsheet. You can trace which cells feed data into a formula (precedents) and which cells use a specific cell in their formulas (dependents).

Overview

The useSpreadsheetState hook provides three methods for auditing formula dependencies:

• onTracePrecedents - Shows arrows from cells that the selected cell depends on

• onTraceDependents - Shows arrows to cells that depend on the selected cell

• onRemoveArrows - Clears all trace arrows from the display

Basic Usage

import { SpreadsheetProvider, CanvasGrid } from "@rowsncolumns/spreadsheet";
import { useSpreadsheetState } from "@rowsncolumns/spreadsheet-state";

const MySpreadsheet = () => {
  const {
    activeCell,
    activeSheetId,
    arrows,
    onTracePrecedents,
    onTraceDependents,
    onRemoveArrows,
    ...rest
  } = useSpreadsheetState({
    sheets,
    sheetData,
    onChangeSheets,
    onChangeSheetData
  });

  return (
    <div>
      <div className="toolbar">
        <button onClick={() => onTracePrecedents(activeSheetId, activeCell)}>
          Trace Precedents
        </button>
        <button onClick={() => onTraceDependents(activeSheetId, activeCell)}>
          Trace Dependents
        </button>
        <button onClick={onRemoveArrows}>
          Remove Arrows
        </button>
      </div>
      <CanvasGrid
        {...rest}
        activeCell={activeCell}
        arrowComponents={arrows}
      />
    </div>
  );
};

API Reference

onTracePrecedents

Traces and displays arrows from cells that the selected cell depends on (precedents) to the active cell.

Signature:

onTracePrecedents(sheetId: number, cell: CellInterface): void

Parameters:

• sheetId: number - The ID of the sheet containing the cell

• cell: CellInterface - Object with rowIndex and columnIndex properties

Example:

If cell C6 contains the formula =SUM(A1:A5), calling onTracePrecedents on C6 will display arrows from the range A1:A5 pointing to C6, showing that C6 depends on those cells for its value.

// Trace precedents for cell C6 (rowIndex: 5, columnIndex: 2)
onTracePrecedents(activeSheetId, { rowIndex: 5, columnIndex: 2 });

onTraceDependents

Traces and displays arrows from the selected cell to all cells that depend on it (dependents).

Signature:

onTraceDependents(sheetId: number, cell: CellInterface): void

Parameters:

• sheetId: number - The ID of the sheet containing the cell

• cell: CellInterface - Object with rowIndex and columnIndex properties

Example:

If cell A1 contains a value and is referenced by formulas in cells C6 and D10, calling onTraceDependents on A1 will display arrows from A1 pointing to C6 and D10.

// Trace dependents for cell A1 (rowIndex: 0, columnIndex: 0)
onTraceDependents(activeSheetId, { rowIndex: 0, columnIndex: 0 });

onRemoveArrows

Clears all trace arrows from the spreadsheet display.

Signature:

onRemoveArrows(): void

Parameters: None

Example:

// Clear all arrows
onRemoveArrows();

arrows

The arrows property contains the rendered arrow components that visualize the cell dependencies. These must be passed to the CanvasGrid component via the arrowComponents prop.

Type:

arrows: React.ReactNode[]

Usage:

<CanvasGrid
  {...spreadsheetProps}
  arrowComponents={arrows}
/>

Use Cases

Debugging Complex Formulas

When working with complex spreadsheets containing many interrelated formulas, formula auditing helps you understand the data flow:

// User clicks on a cell with a complex formula
const handleCellClick = (cell: CellInterface) => {
  // Show what feeds into this formula
  onTracePrecedents(activeSheetId, cell);
};

Impact Analysis

Before changing a cell value, you can see which other cells will be affected:

// User wants to change cell A1
const handleBeforeEdit = (cell: CellInterface) => {
  // Show all cells that depend on this cell
  onTraceDependents(activeSheetId, cell);

  // Show warning if there are many dependents
  const dependents = getDependents(cell);
  if (dependents.length > 10) {
    alert(`Warning: This change will affect ${dependents.length} cells`);
  }
};

Interactive Formula Explorer

Create an interactive tool that lets users explore formula relationships:

const FormulaExplorer = () => {
  const [mode, setMode] = useState<'precedents' | 'dependents' | null>(null);

  const handleCellSelect = (cell: CellInterface) => {
    onRemoveArrows();

    if (mode === 'precedents') {
      onTracePrecedents(activeSheetId, cell);
    } else if (mode === 'dependents') {
      onTraceDependents(activeSheetId, cell);
    }
  };

  return (
    <div>
      <div className="mode-selector">
        <button onClick={() => setMode('precedents')}>
          Trace Precedents Mode
        </button>
        <button onClick={() => setMode('dependents')}>
          Trace Dependents Mode
        </button>
        <button onClick={() => {
          setMode(null);
          onRemoveArrows();
        }}>
          Clear Mode
        </button>
      </div>
      <CanvasGrid
        onCellClick={handleCellSelect}
        arrowComponents={arrows}
        {...spreadsheetProps}
      />
    </div>
  );
};

Visual Styling

The arrows are rendered with default styling, but they respond to cell positions and handle both single cells and ranges:

• Single Cell References: Arrows point from one cell to another

• Range References: A border is drawn around the range, with an arrow pointing from the range to the dependent cell

• Color: Default arrow color is #000000 (black)

The arrow components are absolutely positioned and overlay the grid without interfering with cell interactions.

Notes

• Arrows are automatically cleared when you call onTracePrecedents or onTraceDependents again

• Only call onRemoveArrows() when you want to clear all arrows completely

• The arrows are React components that must be rendered via the arrowComponents prop

• The tracing works with the built-in dependency graph maintained by the calculator

• Both single cell references and range references are supported