Skip to content
Handsontable dev

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. Double-click the fill handle in cell B4 where the value is 30 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 year column (column A) acts as the guide — rows 2020 and 2021 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 { HotTable } from '@handsontable/react-wrapper';
import { registerAllModules } from 'handsontable/registry';


// register Handsontable's modules
registerAllModules();


const ExampleComponent = () => {
  const data = [
    ['', 'Tesla', 'Nissan', 'Toyota', 'Honda'],
    ['2017', 10, 11, 12, 13],
    ['2018', 20, 11, 14, 13],
    ['2019', 30, 15, 12, 13],
    ['2020', '', '', '', ''],
    ['2021', '', '', '', ''],
  ];


  return (
    <HotTable
      data={data}
      rowHeaders={true}
      colHeaders={true}
      fillHandle={true} // possible values: true, false, "horizontal", "vertical",
      height="auto"
      autoWrapRow={true}
      autoWrapCol={true}
      licenseKey="non-commercial-and-evaluation"
    />
  );
};


export default ExampleComponent;
TypeScript
import { HotTable } from '@handsontable/react-wrapper';
import { registerAllModules } from 'handsontable/registry';


// register Handsontable's modules
registerAllModules();


const ExampleComponent = () => {
  const data = [
    ['', 'Tesla', 'Nissan', 'Toyota', 'Honda'],
    ['2017', 10, 11, 12, 13],
    ['2018', 20, 11, 14, 13],
    ['2019', 30, 15, 12, 13],
    ['2020', '', '', '', ''],
    ['2021', '', '', '', ''],
  ];


  return (
    <HotTable
      data={data}
      rowHeaders={true}
      colHeaders={true}
      fillHandle={true} // possible values: true, false, "horizontal", "vertical",
      height="auto"
      autoWrapRow={true}
      autoWrapCol={true}
      licenseKey="non-commercial-and-evaluation"
    />
  );
};


export default ExampleComponent;

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 { HotTable } from '@handsontable/react-wrapper';
import { registerAllModules } from 'handsontable/registry';


// register Handsontable's modules
registerAllModules();


const ExampleComponent = () => {
  const data = [
    ['', 'Tesla', 'Nissan', 'Toyota', 'Honda'],
    ['2017', 10, 11, 12, 13],
    ['2018', 20, 11, 14, 13],
    ['2019', 30, 15, 12, 13],
    ['2020', '', '', '', ''],
    ['2021', '', '', '', ''],
  ];


  return (
    <HotTable
      data={data}
      rowHeaders={true}
      colHeaders={true}
      fillHandle={{
        direction: 'vertical',
        autoInsertRow: true,
      }}
      height="auto"
      autoWrapRow={true}
      autoWrapCol={true}
      licenseKey="non-commercial-and-evaluation"
    />
  );
};


export default ExampleComponent;
TypeScript
import { HotTable } from '@handsontable/react-wrapper';
import { registerAllModules } from 'handsontable/registry';


// register Handsontable's modules
registerAllModules();


const ExampleComponent = () => {
  const data = [
    ['', 'Tesla', 'Nissan', 'Toyota', 'Honda'],
    ['2017', 10, 11, 12, 13],
    ['2018', 20, 11, 14, 13],
    ['2019', 30, 15, 12, 13],
    ['2020', '', '', '', ''],
    ['2021', '', '', '', ''],
  ];


  return (
    <HotTable
      data={data}
      rowHeaders={true}
      colHeaders={true}
      fillHandle={{
        direction: 'vertical',
        autoInsertRow: true,
      }}
      height="auto"
      autoWrapRow={true}
      autoWrapCol={true}
      licenseKey="non-commercial-and-evaluation"
    />
  );
};


export default ExampleComponent;

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, drag the fill handle from cell B4 down through rows 2020 and 2021. The beforeAutofill hook rounds every filled sales figure up to the nearest multiple of 5, and the afterAutofill hook logs the affected range and direction to the output box below the grid.

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.

JavaScript
import { useState } from 'react';
import { HotTable } from '@handsontable/react-wrapper';
import { registerAllModules } from 'handsontable/registry';


// register Handsontable's modules
registerAllModules();


// Defined outside the component so autofill can mutate this array in place
// without a re-render (triggered by `setOutput`) resetting it to its initial values.
const data = [
  ['', 'Tesla', 'Nissan', 'Toyota', 'Honda'],
  ['2017', 10, 11, 12, 13],
  ['2018', 20, 11, 14, 13],
  ['2019', 30, 15, 12, 13],
  ['2020', '', '', '', ''],
  ['2021', '', '', '', ''],
];


const ExampleComponent = () => {
  const [output, setOutput] = useState('Drag the fill handle to see the affected range logged here.');


  return (
    <>
      <output className="console" id="output">
        {output}
      </output>
      <HotTable
        data={data}
        rowHeaders={true}
        colHeaders={true}
        fillHandle={true}
        height="auto"
        autoWrapRow={true}
        autoWrapCol={true}
        licenseKey="non-commercial-and-evaluation"
        beforeAutofill={(selectionData) =>
          // This dealership reports sales in batches of 5 cars, so round every
          // filled value up to the nearest multiple of 5.
          selectionData.map((row) => row.map((value) => (typeof value === 'number' ? Math.ceil(value / 5) * 5 : value)))
        }
        afterAutofill={(fillData, sourceRange, targetRange, direction) => {
          setOutput(
            `Filled rows ${targetRange.from.row}-${targetRange.to.row}, ` +
              `columns ${targetRange.from.col}-${targetRange.to.col} (direction: "${direction}").`
          );
        }}
      />
    </>
  );
};


export default ExampleComponent;
TypeScript
import { useState } from 'react';
import { HotTable } from '@handsontable/react-wrapper';
import { registerAllModules } from 'handsontable/registry';


// register Handsontable's modules
registerAllModules();


// Defined outside the component so autofill can mutate this array in place
// without a re-render (triggered by `setOutput`) resetting it to its initial values.
const data = [
  ['', 'Tesla', 'Nissan', 'Toyota', 'Honda'],
  ['2017', 10, 11, 12, 13],
  ['2018', 20, 11, 14, 13],
  ['2019', 30, 15, 12, 13],
  ['2020', '', '', '', ''],
  ['2021', '', '', '', ''],
];


const ExampleComponent = () => {
  const [output, setOutput] = useState('Drag the fill handle to see the affected range logged here.');


  return (
    <>
      <output className="console" id="output">
        {output}
      </output>
      <HotTable
        data={data}
        rowHeaders={true}
        colHeaders={true}
        fillHandle={true}
        height="auto"
        autoWrapRow={true}
        autoWrapCol={true}
        licenseKey="non-commercial-and-evaluation"
        beforeAutofill={(selectionData) =>
          // This dealership reports sales in batches of 5 cars, so round every
          // filled value up to the nearest multiple of 5.
          selectionData.map((row) => row.map((value) => (typeof value === 'number' ? Math.ceil(value / 5) * 5 : value)))
        }
        afterAutofill={(fillData, sourceRange, targetRange, direction) => {
          setOutput(
            `Filled rows ${targetRange.from.row}-${targetRange.to.row}, ` +
              `columns ${targetRange.from.col}-${targetRange.to.col} (direction: "${direction}").`
          );
        }}
      />
    </>
  );
};


export default ExampleComponent;

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