Skip to content
Handsontable

Autofill values

Copy a cell’s value into multiple other cells, using the “fill handle” UI element. Configure the direction of copying, and more, through Handsontable’s API.

Autofill lets users drag the fill handle to copy or extend values across adjacent cells. Use it to speed up repetitive data entry.

Autofill in all directions

Using the tiny square known as the ‘fill handle’ in the corner of the selected cell, you can drag it to repeat or extend values across adjacent cells.

You can also double-click the fill handle to autofill downward without dragging. Select the four cells from Symbol to Known Isotopes in the Lithium row (row header 3) and double-click the fill handle to see this in action.

How double-click autofill determines the range

Handsontable scans the rows below your selection and fills down to the last row where the column immediately to the left or right of your selection contains a value. In the example below, the Name column acts as the guide — the rows with row headers 4 and 5 have values there, so the fill extends through both rows.

Two conditions must be met for the fill to happen:

  • All cells below the selection in the filled column(s) must be empty. If any cell below the selection in those columns contains data, double-clicking does nothing.
  • At least one column adjacent to your selection must have data in the rows below.

Visual difference from drag-fill: When you drag the fill handle, a preview border shows the target range as you drag. When you double-click, no drag-preview appears — the cells populate immediately based on the adjacent column data.

JavaScript
import Handsontable from 'handsontable/base';
import { registerAllModules } from 'handsontable/registry';


// Register all Handsontable's modules.
registerAllModules();


const data = [
  ['Hydrogen', 'H', 1, 1.008, 7],
  ['Helium', 'He', 2, 4.003, 9],
  ['Lithium', 'Li', 3, 6.94, 9],
  ['Beryllium', '', '', '', ''],
  ['Boron', '', '', '', ''],
];


const container = document.querySelector('#example1');


const hot = new Handsontable(container, {
  rowHeaders: true,
  colHeaders: ['Name', 'Symbol', 'Atomic Number', 'Atomic Mass (u)', 'Known Isotopes'],
  colWidths: [80, 62, 110, 118, 110],
  stretchH: 'all',
  fillHandle: true, // possible values: true, false, "horizontal", "vertical",
  height: 'auto',
  autoWrapRow: true,
  autoWrapCol: true,
  licenseKey: 'non-commercial-and-evaluation',
});


// or, use `updateData()` to replace `data` without resetting states
hot.loadData(data);
TypeScript
import Handsontable from 'handsontable/base';
import { registerAllModules } from 'handsontable/registry';


// Register all Handsontable's modules.
registerAllModules();


const data: (string | number)[][] = [
  ['Hydrogen', 'H', 1, 1.008, 7],
  ['Helium', 'He', 2, 4.003, 9],
  ['Lithium', 'Li', 3, 6.94, 9],
  ['Beryllium', '', '', '', ''],
  ['Boron', '', '', '', ''],
];


const container = document.querySelector('#example1')!;


const hot = new Handsontable(container, {
  rowHeaders: true,
  colHeaders: ['Name', 'Symbol', 'Atomic Number', 'Atomic Mass (u)', 'Known Isotopes'],
  colWidths: [80, 62, 110, 118, 110],
  stretchH: 'all',
  fillHandle: true, // possible values: true, false, "horizontal", "vertical",
  height: 'auto',
  autoWrapRow: true,
  autoWrapCol: true,
  licenseKey: 'non-commercial-and-evaluation',
});


// or, use `updateData()` to replace `data` without resetting states
hot.loadData(data);

Autofill in a vertical direction only and creating new rows

In this configuration, the fill handle is restricted to move only vertically. New rows are automatically added to the bottom of the table by changing autoInsertRow to true.

JavaScript
import Handsontable from 'handsontable/base';
import { registerAllModules } from 'handsontable/registry';


// Register all Handsontable's modules.
registerAllModules();


const data = [
  ['Hydrogen', 'H', 1, 1.008, 7],
  ['Helium', 'He', 2, 4.003, 9],
  ['Lithium', 'Li', 3, 6.94, 9],
  ['Beryllium', '', '', '', ''],
  ['Boron', '', '', '', ''],
];


const container = document.querySelector('#example2');


new Handsontable(container, {
  data,
  rowHeaders: true,
  colHeaders: ['Name', 'Symbol', 'Atomic Number', 'Atomic Mass (u)', 'Known Isotopes'],
  colWidths: [80, 62, 110, 118, 110],
  stretchH: 'all',
  fillHandle: {
    direction: 'vertical',
    autoInsertRow: true,
  },
  height: 'auto',
  autoWrapRow: true,
  autoWrapCol: true,
  licenseKey: 'non-commercial-and-evaluation',
});
TypeScript
import Handsontable from 'handsontable/base';
import { registerAllModules } from 'handsontable/registry';


// Register all Handsontable's modules.
registerAllModules();


const data: (string | number)[][] = [
  ['Hydrogen', 'H', 1, 1.008, 7],
  ['Helium', 'He', 2, 4.003, 9],
  ['Lithium', 'Li', 3, 6.94, 9],
  ['Beryllium', '', '', '', ''],
  ['Boron', '', '', '', ''],
];


const container = document.querySelector('#example2')!;


new Handsontable(container, {
  data,
  rowHeaders: true,
  colHeaders: ['Name', 'Symbol', 'Atomic Number', 'Atomic Mass (u)', 'Known Isotopes'],
  colWidths: [80, 62, 110, 118, 110],
  stretchH: 'all',
  fillHandle: {
    direction: 'vertical',
    autoInsertRow: true,
  },
  height: 'auto',
  autoWrapRow: true,
  autoWrapCol: true,
  licenseKey: 'non-commercial-and-evaluation',
});

Altering and tracking autofilled values

Use the beforeAutofill hook to change the values Handsontable is about to fill in, and the afterAutofill hook to react once the fill completes.

In the example below, select the four cells from Symbol to Known Isotopes in the Lithium row and drag the fill handle down through the rows with row headers 4 and 5. The afterAutofill hook logs the affected range and direction to the output box below the grid. The logged row and column numbers are 0-based indices, so the two filled rows appear as 3-4, not 4-5.

The beforeAutofill hook in this example rounds every filled number up to the nearest multiple of 5. This is a demonstration of what the hook can do, not the default autofill result: without the hook, Handsontable repeats the selected values as they are.

When Handsontable fires beforeChange or afterChange as part of an autofill operation, their source argument is Autofill.fill. Read more about the source argument in Events and hooks: Definition for source argument.

Drag the fill handle to see the affected range logged here.
JavaScript
import Handsontable from 'handsontable/base';
import { registerAllModules } from 'handsontable/registry';


// Register all Handsontable's modules.
registerAllModules();


const data = [
  ['Hydrogen', 'H', 1, 1.008, 7],
  ['Helium', 'He', 2, 4.003, 9],
  ['Lithium', 'Li', 3, 6.94, 9],
  ['Beryllium', '', '', '', ''],
  ['Boron', '', '', '', ''],
];


const container = document.querySelector('#example3');
const output = document.querySelector('#output');


new Handsontable(container, {
  data,
  rowHeaders: true,
  colHeaders: ['Name', 'Symbol', 'Atomic Number', 'Atomic Mass (u)', 'Known Isotopes'],
  colWidths: [80, 62, 110, 118, 110],
  stretchH: 'all',
  fillHandle: true,
  height: 'auto',
  autoWrapRow: true,
  autoWrapCol: true,
  licenseKey: 'non-commercial-and-evaluation',
  beforeAutofill(selectionData) {
    // Round every filled number up to the nearest multiple of 5.
    return selectionData.map((row) =>
      row.map((value) => (typeof value === 'number' ? Math.ceil(value / 5) * 5 : value)),
    );
  },
  afterAutofill(fillData, sourceRange, targetRange, direction) {
    output.innerText =
      `Filled rows ${targetRange.from.row}-${targetRange.to.row}, ` +
      `columns ${targetRange.from.col}-${targetRange.to.col} (direction: "${direction}").`;
  },
});
TypeScript
import Handsontable from 'handsontable/base';
import { registerAllModules } from 'handsontable/registry';


// Register all Handsontable's modules.
registerAllModules();


const data: (string | number)[][] = [
  ['Hydrogen', 'H', 1, 1.008, 7],
  ['Helium', 'He', 2, 4.003, 9],
  ['Lithium', 'Li', 3, 6.94, 9],
  ['Beryllium', '', '', '', ''],
  ['Boron', '', '', '', ''],
];


const container = document.querySelector('#example3')!;
const output = document.querySelector('#output') as HTMLElement;


new Handsontable(container, {
  data,
  rowHeaders: true,
  colHeaders: ['Name', 'Symbol', 'Atomic Number', 'Atomic Mass (u)', 'Known Isotopes'],
  colWidths: [80, 62, 110, 118, 110],
  stretchH: 'all',
  fillHandle: true,
  height: 'auto',
  autoWrapRow: true,
  autoWrapCol: true,
  licenseKey: 'non-commercial-and-evaluation',
  beforeAutofill(selectionData) {
    // Round every filled number up to the nearest multiple of 5.
    return selectionData.map((row) =>
      row.map((value) => (typeof value === 'number' ? Math.ceil(value / 5) * 5 : value))
    );
  },
  afterAutofill(fillData, sourceRange, targetRange, direction) {
    output.innerText =
      `Filled rows ${targetRange.from.row}-${targetRange.to.row}, ` +
      `columns ${targetRange.from.col}-${targetRange.to.col} (direction: "${direction}").`;
  },
});
HTML
<output class="console" id="output">Drag the fill handle to see the affected range logged here.</output>
<div id="example3"></div>

Autofill and formulas

With the Formulas plugin enabled, autofill delegates to the HyperFormula engine instead of copying cell contents literally:

  • Relative references adjust per target cell. Filling =A1+B1 down from row 1 to row 2 produces =A2+B2 in the new cell, the same way a spreadsheet application adjusts formulas on fill.
  • Absolute references stay fixed. Filling =$A$1+B1 down keeps $A$1 unchanged in every filled cell, while the relative B1 part still adjusts.
  • The fill is cancelled if the engine reports that the target cells can’t be written to (for example, because they’re part of another formula’s dependency chain), leaving the target range unchanged.

Result

The fill handle appears on the selected cell. Dragging it copies or extends values into adjacent cells in the configured direction.

Configuration options

fillHandle

Hooks

afterAutofill
beforeAutofill
modifyAutofillRange

Plugins

Autofill