A1 References and Autofill

Formula references use authored row and column coordinates. Start with this compact fixture:

import { FormulaPlugin } from '@revolist/revogrid-pro';

grid.range = true;
grid.plugins = [FormulaPlugin];
grid.columns = [
  { prop: 'qty', name: 'Quantity' },      // A
  { prop: 'rate', name: 'Rate' },         // B
  { prop: 'charge', name: 'Charge' },     // C
];
grid.source = [
  { qty: 2, rate: 5, charge: '=A1*$B$1' },
  { qty: 4, rate: 8, charge: null },
];

Expected result: C1 displays 10. Filling C1 down writes =A2*$B$1 to C2 and displays 20: A’s row is relative, while $B$1 remains fixed.

Autofill formulas like Excel

Set grid.range = true to expose the fill handle. FormulaPlugin automatically installs or reuses AutoFillPlugin; applications should not add another formula fill strategy or duplicate the fill pipeline. AutoFill continues to own the drag, base sequence, preview, and commit. Formula only translates A1 references in seed cells whose raw source value starts with =.

import {
  AutoFillPreviewPlugin,
  FormulaPlugin,
} from '@revolist/revogrid-pro';

grid.range = true;
grid.plugins = [FormulaPlugin, AutoFillPreviewPlugin]; // Preview is optional.

Vertical copy by one row:

Reference	Seed	Result
Relative	=A1	=A2
Fixed column and row	=$A$1	=$A$1
Relative column, fixed row	=A$1	=A$1
Fixed column, relative row	=$A1	=$A2
Mixed range	=SUM(A1:B2,$C1:D$4)	=SUM(A2:B3,$C2:D$4)

Horizontal copy by one column:

Reference	Seed	Result
Relative	=A1	=B1
Fixed column and row	=$A$1	=$A$1
Relative column, fixed row	=A$1	=B$1
Fixed column, relative row	=$A1	=$A1
Mixed range	=SUM(A1:B2,$C1:D$4)	=SUM(B1:C2,$C1:E$4)

References translate in vertical, horizontal, upward, leftward, and two-dimensional fills. Multi-cell source ranges tile from their corresponding seed cells. Sheet qualifiers and anchor markers are preserved, including Products!A1 and 'Product List'!$A1:B$2. Named ranges, text literals, and existing #REF! values are not rewritten. If a relative reference moves before A1, that endpoint becomes #REF!.

Pinning, sorting, and filtering do not change these offsets: Formula resolves the source and destination in the authored A1 coordinate space described below. For non-contiguous projected selections, seed tiling follows the visible source order. Preview and commit use the identical translated formula matrix.

For a two-dimensional fill from =A1*$B$1, the exact destinations are:

Destination	Raw formula
One row down	=A2*$B$1
One column right	=B1*$B$1
One row down and one column right	=B2*$B$1

With a multi-cell seed, Formula tiles each destination from the corresponding seed rather than treating the whole selection as one formula.

How A1 references behave when the grid changes

RevoGrid separates a cell’s formula identity from its rendered position. Formula letters and row numbers describe the authored data coordinate. Pinning, hiding, sorting, filtering, grouping, sticky rows, and row drag-order can render that cell somewhere else without changing what its A1 address means.

This is the same principle as Excel Freeze Panes: freezing a column changes what stays visible while scrolling, but it does not rename the column or rewrite its formulas.

Authored formula coordinates

      A          B       C       D
  Product      Region  Price   Total
                              =C1*124

After pinning Price left (rendered order only)

      C          A          B       D
    Price      Product    Region   Total
                                  =C1*124

C1 still means the authored Price column. The raw formula and result are unchanged.

Authored order versus rendered order

Columns use the flattened leaf order supplied through grid.columns. In this example the authored order is [product, region, price, total], even while the Price column is rendered in the left-pinned viewport.

A1 letter	Authored property	Position after pinning Price left
A	product	Second
B	region	Third
C	price	First, pinned left
D	total	Fourth

Pinning or unpinning therefore never rewrites formula strings or named-range definitions. A formula such as =C1*124 remains bound to price, including when the formula column itself is pinned separately from its precedents.

Column occurrences are matched by identity before RevoGrid considers their prop. This matters when two authored columns intentionally use the same property. If an application replaces every column object and those replacement columns still have duplicate properties, there is no reliable way to tell the occurrences apart after pinning. RevoGrid therefore leaves that mapping unresolved instead of guessing from the rendered pin order. Preserve the column objects across presentation changes, or use unique properties when columns need independent A1 identities.

Rows use one initial authored sequence. Each source can contribute zero, one, or many rows:

pinnedTopSource → source → pinnedBottomSource
   first rows      next rows          final rows

After initialization, moving an existing row between those partitions keeps its A1 identity. The same applies to sorting, filtering, grouping, sticky-row duplicates, and row drag-order projections. Tracking is automatic and does not require an id property in application rows. Applications that replace rows with new immutable objects can optionally provide a stable row key; see Immutable row replacement.

Authored identity                     After pinning Noah to the top

  Row 1  Maya     C1 = 10               Noah     still C2 = 20
  Row 2  Noah     C2 = 20               Maya     still C1 = 10
  Row 3  Total    =SUM(C1:C2)            Total    still =SUM(C1:C2) = 30

Rendered position changed; the underlying row addresses did not.

Presentation changes and structural changes

Operation	What happens to A1 identity?	Are raw formulas rewritten?
Pin or unpin a row/column	Preserved	No
Hide a column	Preserved; the hidden column still calculates	No
Sort, filter, group, trim, or restore rows	Preserved	No
Render a sticky-row duplicate	Resolves to its underlying authored row	No
Drag-order existing rows	Preserved as a runtime projection	No
Insert or delete rows/columns	Following coordinates shift	Yes, unless autoUpdateRefs is false
Explicitly reorder the authored column schema	Letters follow the new authored order	Existing formulas are not currently rewritten for the move
Replace all rows with new immutable clones, without rowIdProp	Treated as a newly authored dataset	No speculative rewrite
Replace rows with immutable clones, with rowIdProp	Existing IDs retain A1 identity; genuine inserts/deletes remain structural	Only for genuine inserts/deletes
Call evaluateRawValuesFormula directly	Uses the row and column arrays supplied by the caller	Not applicable; the helper only evaluates

Immutable row replacement (advanced)

Most applications do not need row identity configuration. Built-in pinning, sorting, filtering, grouping, sticky rows, and drag ordering reuse the existing row models and are tracked automatically.

If an application replaces source rows with fresh immutable clones, object identity is no longer available. Set rowIdProp only when the configured field is present, stable, and unique for every row:

grid.formulaNames = {
  rowIdProp: 'id',
  names: [{ name: 'Revenue', kind: 'range', ref: 'C2:C20' }],
};

grid.source = previousRows.map(row => ({ ...row }));

With that key, a cloned row keeps its authored A1 identity even if the new source order changes or the row moves between pinned and body sources. If the same immutable update genuinely inserts or deletes IDs, references shift using the normal structural-edit rules. Rows with a missing key are not assumed to match, and duplicate keys are ambiguous; use a real record key rather than a row index.

Explicit schema reorder and standalone evaluation

The explicit column-reorder case is intentionally separate from pinning. If the authored schema changes from [A, B, C] to [B, A, C], A1 now means the first column in that new schema. Existing formula text is currently retained; Excel-style move rewriting is not part of the pinning contract.

The standalone evaluateRawValuesFormula helper has no grid viewport or authored schema to consult. Its A1 coordinates always follow the row and column arrays passed to that call.

What deletion does to formulas and ranges

Deletion is structural, so surviving references are normalized to the new row numbers. For example, begin with:

Row 1  A1 = 10
Row 2  A2 = 20
Row 3  A3 = 30

Direct reference: =A3
Range formula:    =SUM(A1:A3)

After deleting row 2, the old row 3 becomes row 2:

Row 1  A1 = 10
Row 2  A2 = 30

=A3          → =A2
=SUM(A1:A3)  → =SUM(A1:A2)

If the deleted row was itself the target of a direct reference, that reference becomes #REF!. A range that only partially overlaps the deleted block shrinks to its surviving cells. Named ranges and named formulas follow the same rules.

Disable structural rewriting

Set autoUpdateRefs: false when the application owns structural ref updates:

grid.formulaNames = {
  autoUpdateRefs: false,
  names: [],
};

Expected result: inserting or deleting rows/columns does not rewrite existing raw formula strings or named refs. Presentation operations still behave as described above.

Recommended mental model

Treat pinning, hiding, and runtime projections as viewport mapping. Treat genuine insertion and deletion as spreadsheet structure changes. This keeps formulas stable during ordinary grid interaction while keeping their visible A1 text correct after the dataset structure changes.

Next steps

Previous: Quick Start · Next: Named Ranges · Deeper reference: Autofill guide