Skip to content

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 DataGrid’s API.

Autofill lets users drag the fill handle to copy 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 copy and repeat the selected values across adjacent cells.

The fill handle appears only when the selection is a single range. When you select several separate ranges, for example with Ctrl/⌘+click, the fill handle is hidden.

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 follows one guide column: the column immediately to the left of your selection, or the column to its right if the left one is empty in the first row below your selection. It scans the rows below your selection, one row at a time, and fills down as long as the guide column 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 copies the selected values through both rows.

The fill stops at the first row where either of these is true:

  • A cell in the filled column(s) already contains data. The fill stops above that row, so double-clicking never overwrites existing values.
  • The guide column is empty. A gap in the guide column ends the fill, even if more values follow further down.

If the first row below your selection meets one of these conditions, double-clicking does nothing.

A few cells count differently from what they display:

  • A formula counts as data, even if it evaluates to an empty string.
  • A merged cell in the guide column counts as one value across all the rows it spans.
  • A hidden row never ends the fill. A value in a hidden row of the filled column still stops the fill above it.
  • A hidden column next to your selection is skipped: the guide column is the nearest visible one.

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.

Vue
<script setup lang="ts">
import { ref } from 'vue';
import { HotTable } from '@handsontable/vue3';
import { registerAllModules } from 'handsontable/registry';
import type { GridSettings } from 'handsontable/settings';
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 hotSettings = ref<GridSettings>({
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: 'your-license-key', // get a key at https://handsontable.com/pricing/
});
</script>
<template>
<div id="example1">
<HotTable :settings="hotSettings" />
</div>
</template>

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.

Vue
<script setup lang="ts">
import { ref } from 'vue';
import { HotTable } from '@handsontable/vue3';
import { registerAllModules } from 'handsontable/registry';
import type { GridSettings } from 'handsontable/settings';
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 hotSettings = ref<GridSettings>({
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: 'your-license-key', // get a key at https://handsontable.com/pricing/
});
</script>
<template>
<div id="example2">
<HotTable :settings="hotSettings" />
</div>
</template>

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.

Vue
<script setup lang="ts">
import { ref } from 'vue';
import { HotTable } from '@handsontable/vue3';
import { registerAllModules } from 'handsontable/registry';
import type { GridSettings } from 'handsontable/settings';
registerAllModules();
const output = ref('Drag the fill handle to see the affected range logged here.');
const data: GridSettings['data'] = [
['Hydrogen', 'H', 1, 1.008, 7],
['Helium', 'He', 2, 4.003, 9],
['Lithium', 'Li', 3, 6.94, 9],
['Beryllium', '', '', '', ''],
['Boron', '', '', '', ''],
];
const hotSettings: GridSettings = {
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: 'your-license-key', // get a key at https://handsontable.com/pricing/
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.value =
`Filled rows ${targetRange.from.row}-${targetRange.to.row}, ` +
`columns ${targetRange.from.col}-${targetRange.to.col} (direction: "${direction}").`;
},
};
</script>
<template>
<div id="example3">
<output class="console" id="output">{{ output }}</output>
<HotTable :settings="hotSettings" />
</div>
</template>

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 canceled if the engine reports that the target cells can’t be written to (for example, because the target range includes part of an array formula, or extends past the sheet size limits), leaving the target range unchanged.

In the example below, the Total column holds =A1*B1, and the Total with tax column holds =C1*(1+$E$1), where $E$1 is an absolute reference to the tax rate, stored once in cell E1 of the Tax rate column. Select the cells from Total to Total with tax in row 1 and drag the fill handle down. The row references adjust in each new row (=A2*B2, =C2*(1+$E$1)), and $E$1 stays fixed. Double-click a filled cell, or press Enter, to see its formula.

Vue
<script setup lang="ts">
import { markRaw } from 'vue';
import { HotTable } from '@handsontable/vue3';
import { registerAllModules } from 'handsontable/registry';
import { HyperFormula } from 'hyperformula';
import type { GridSettings } from 'handsontable/settings';
// register Handsontable's modules
registerAllModules();
const data: (string | number | null)[][] = [
[12, 4.5, '=A1*B1', '=C1*(1+$E$1)', 0.2],
[8, 12.25, null, null, null],
[20, 3.8, null, null, null],
[5, 29.99, null, null, null],
[15, 7.2, null, null, null],
];
// create an external HyperFormula instance.
// `markRaw` prevents Vue from wrapping the instance in a reactive proxy.
const hyperformulaInstance = markRaw(
HyperFormula.buildEmpty({
// this demo uses the GPLv3 key,
// use your own HyperFormula license key in your app
licenseKey: 'gpl-v3',
})
);
const hotSettings: GridSettings = {
data,
colHeaders: ['Quantity', 'Unit price', 'Total', 'Total with tax', 'Tax rate (E1 only)'],
rowHeaders: true,
stretchH: 'all',
fillHandle: true,
formulas: {
engine: hyperformulaInstance,
sheetName: 'Sheet1',
},
height: 'auto',
autoWrapRow: true,
autoWrapCol: true,
// licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/
};
</script>
<template>
<div id="example4">
<HotTable :settings="hotSettings" />
</div>
</template>

Result

The fill handle appears on the selected cell. Autofill copies the selected values into the adjacent cells, and repeats them to fill a larger range.

  • Dragging the fill handle copies the selected values into the cells you drag across, in the direction allowed by the fillHandle option. If the target range is larger than the selection, the selected values repeat.
  • Double-clicking the fill handle copies the selected values down only, to the last row determined by the adjacent column, as described in How double-click autofill determines the range.

Use the beforeAutofill hook to change the values before they’re written, and the afterAutofill hook to react once the fill completes. With the Formulas plugin enabled, relative references in copied formulas adjust to each target cell.

When you use beforeAutofill together with the Formulas plugin, the order of the callbacks decides the result. The last callback that returns a value wins. Global hooks (Handsontable.hooks.add()) run before the hooks of an instance. Within the instance, callbacks run in ascending orderIndex order, and callbacks with the same orderIndex run in the order they were registered. The plugin registers its own beforeAutofill callback with the default orderIndex of 0 when it’s enabled.

  • These run after the plugin and override it: the beforeAutofill option set at initialization, and an addHook() call made after the plugin is enabled.
  • These run before the plugin and are overridden by it: a global hook, an addHook() call made before the plugin is enabled, and the beforeAutofill option when you enable the plugin later with updateSettings().

Disabling and re-enabling the plugin registers its callback again, after your callbacks that have an orderIndex of 0. Passing a new beforeAutofill function through updateSettings() replaces the previous one in place, so it keeps its position. This is what happens when a wrapper re-renders with the same option.

To always override the plugin, add your callback with addHook() and an orderIndex greater than 0. Such a callback runs after the plugin’s callback, whenever you add it.

hotTableRef.value?.hotInstance.addHook('beforeAutofill', (selectionData, sourceRange, targetRange, direction) => {
return [['n/a']];
}, 1);

Configuration options

Hooks

Plugins