> For the complete documentation index, see [llms.txt](https://docs.rowsncolumns.app/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.rowsncolumns.app/configuration/features/formula-auditing.md).

# Formula auditing

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

```tsx
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:**

```typescript
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.

```typescript
// 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:**

```typescript
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.

```typescript
// 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:**

```typescript
onRemoveArrows(): void
```

**Parameters:** None

**Example:**

```typescript
// 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:**

```typescript
arrows: React.ReactNode[]
```

**Usage:**

```tsx
<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:

```typescript
// 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:

```typescript
// 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:

```tsx
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