JavaScript Data GridNumeric cell type

Display, format, sort, and filter numbers correctly by using the numeric cell type.

Overview
Numeric cell type demo
Use the numeric cell type
Format numbers
Related articles
- Related guides
- Related API reference

Overview

The default cell type in Handsontable is text. The data of a text cell is processed as a string type that corresponds to the value of the text editor's internal <textarea> element. However, there are many cases where you need cell values to be treated as a number type. The numeric cell type allows you to format displayed numbers nicely and sort them correctly.

Numeric cell type demo

In the following demo, columns Year, Price ($), and Price (€) use the numeric cell type. Click on the column names to sort them.

{
    "name": "handsontable",
    "version": "1.0.0",
    "description": "",
    "dependencies": {
      "hyperformula": "^2.4.0",
      "handsontable": "14.6"
    }
  }

<!DOCTYPE html>
  <html>
    <head>
      <meta charset="utf-8" />
      <meta name="viewport" content="width=device-width, initial-scale=1.0" />
      <title>Handsontable</title>
    </head>
  
    <body>
      <div id="example1" class="hot "></div>
    </body>
  </html>

import './styles.css'
import Handsontable from 'handsontable';
import numbro from 'numbro';
import deDE from 'numbro/languages/de-DE';
import 'handsontable/dist/handsontable.full.min.css';

// register the languages you need
numbro.registerLanguage(deDE);

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

new Handsontable(container, {
  data: [
    { car: 'Mercedes A 160', year: 2017, price_usd: 7000, price_eur: 7000 },
    { car: 'Citroen C4 Coupe', year: 2018, price_usd: 8330, price_eur: 8330 },
    { car: 'Audi A4 Avant', year: 2019, price_usd: 33900, price_eur: 33900 },
    { car: 'Opel Astra', year: 2020, price_usd: 5000, price_eur: 5000 },
    { car: 'BMW 320i Coupe', year: 2021, price_usd: 30500, price_eur: 30500 },
  ],
  colHeaders: ['Car', 'Year', 'Price ($)', 'Price (€)'],
  columnSorting: true,
  height: 'auto',
  licenseKey: 'non-commercial-and-evaluation',
  columns: [
    {
      data: 'car',
    },
    {
      data: 'year',
      type: 'numeric',
    },
    {
      data: 'price_usd',
      type: 'numeric',
      numericFormat: {
        pattern: '$0,0.00',
        culture: 'en-US',
      },
      allowEmpty: false,
    },
    {
      data: 'price_eur',
      type: 'numeric',
      numericFormat: {
        pattern: '0,0.00 $',
        culture: 'de-DE',
      },
    },
  ],
  autoWrapRow: true,
  autoWrapCol: true,
});

import Handsontable from 'handsontable';
import numbro from 'numbro';
import deDE from 'numbro/languages/de-DE';
import 'handsontable/dist/handsontable.full.min.css';

// register the languages you need
numbro.registerLanguage(deDE);

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

new Handsontable(container, {
  data: [
    { car: 'Mercedes A 160', year: 2017, price_usd: 7000, price_eur: 7000 },
    { car: 'Citroen C4 Coupe', year: 2018, price_usd: 8330, price_eur: 8330 },
    { car: 'Audi A4 Avant', year: 2019, price_usd: 33900, price_eur: 33900 },
    { car: 'Opel Astra', year: 2020, price_usd: 5000, price_eur: 5000 },
    { car: 'BMW 320i Coupe', year: 2021, price_usd: 30500, price_eur: 30500 },
  ],
  colHeaders: ['Car', 'Year', 'Price ($)', 'Price (€)'],
  columnSorting: true,
  height: 'auto',
  licenseKey: 'non-commercial-and-evaluation',
  columns: [
    {
      data: 'car',
    },
    {
      data: 'year',
      type: 'numeric',
    },
    {
      data: 'price_usd',
      type: 'numeric',
      numericFormat: {
        pattern: '$0,0.00',
        culture: 'en-US',
      },
      allowEmpty: false,
    },
    {
      data: 'price_eur',
      type: 'numeric',
      numericFormat: {
        pattern: '0,0.00 $',
        culture: 'de-DE',
      },
    },
  ],
  autoWrapRow: true,
  autoWrapCol: true,
});

<script src="https://cdn.jsdelivr.net/npm/handsontable@14.6/dist/handsontable.full.min.js"></script>
<link type="text/css" rel="stylesheet" href="https://cdn.jsdelivr.net/npm/handsontable@14.6/dist/handsontable.full.min.css" /> 
<script src="https://cdn.jsdelivr.net/npm/numbro@2.1.2/dist/languages.min.js"></script>
<script src="https://handsontable.com/docs/scripts/fixer.js"></script>

<div id="example1" class="hot "></div>

Use the numeric cell type

To use the numeric cell type, set the type option to 'numeric':

// set the numeric cell type for each cell of the entire grid
type: 'numeric',

// set the numeric cell type for each cell of a single column
columns: [
  {
    type: 'numeric',
  },
]

// set the numeric cell type for a single cell
cell: [
  {
    row: 0,
    col: 0,
    type: 'numeric',
  }
],

Mind that Handsontable doesn't parse strings to numbers. In your data source, make sure to store numeric cell values as numbers, not as strings.

All positive and negative integers whose magnitude is no greater than 253 (+/- 9007199254740991) are representable in the Number type, i.e., as a safe integer. Any calculations that are performed on bigger numbers won't be calculated precisely, due to JavaScript's limitations.

Format numbers

To format the look of numeric values in cell renderers, use the numericFormat option.

In the following demo, columns Price in Japan and Price in Turkey use two different numericFormat configurations.

{
    "name": "handsontable",
    "version": "1.0.0",
    "description": "",
    "dependencies": {
      "hyperformula": "^2.4.0",
      "handsontable": "14.6"
    }
  }

<!DOCTYPE html>
  <html>
    <head>
      <meta charset="utf-8" />
      <meta name="viewport" content="width=device-width, initial-scale=1.0" />
      <title>Handsontable</title>
    </head>
  
    <body>
      <div id="example3" class="hot "></div>
    </body>
  </html>

import './styles.css'
import Handsontable from 'handsontable';
import numbro from 'numbro';
import jaJP from 'numbro/languages/ja-JP';
import trTR from 'numbro/languages/tr-TR';
import 'handsontable/dist/handsontable.full.min.css';

// register the languages you need
numbro.registerLanguage(jaJP);
numbro.registerLanguage(trTR);

// define formats
const formatJP = {
  pattern: '0,0.00 $',
  culture: 'ja-JP',
};

const formatTR = {
  pattern: '0,0.00 $',
  culture: 'tr-TR',
};

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

new Handsontable(container, {
  data: [
    {
      productName: 'Product A',
      JP_price: 1450.32,
      TR_price: 202.14,
    },
    {
      productName: 'Product B',
      JP_price: 2430.22,
      TR_price: 338.86,
    },
    {
      productName: 'Product C',
      JP_price: 3120.1,
      TR_price: 435.2,
    },
  ],
  columns: [
    {
      data: 'productName',
      type: 'text',
      width: '120',
    },
    {
      data: 'JP_price',
      type: 'numeric',
      width: '120',
      numericFormat: formatJP,
    },
    {
      data: 'TR_price',
      type: 'numeric',
      width: '120',
      numericFormat: formatTR,
    },
  ],
  autoRowSize: false,
  autoColumnSize: false,
  columnSorting: true,
  colHeaders: ['Product name', 'Price in Japan', 'Price in Turkey'],
  height: 'auto',
  autoWrapRow: true,
  autoWrapCol: true,
  licenseKey: 'non-commercial-and-evaluation',
});

import Handsontable from 'handsontable';
import numbro from 'numbro';
import jaJP from 'numbro/languages/ja-JP';
import trTR from 'numbro/languages/tr-TR';
import 'handsontable/dist/handsontable.full.min.css';

// register the languages you need
numbro.registerLanguage(jaJP);
numbro.registerLanguage(trTR);

// define formats
const formatJP = {
  pattern: '0,0.00 $',
  culture: 'ja-JP',
};

const formatTR = {
  pattern: '0,0.00 $',
  culture: 'tr-TR',
};

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

new Handsontable(container, {
  data: [
    {
      productName: 'Product A',
      JP_price: 1450.32,
      TR_price: 202.14,
    },
    {
      productName: 'Product B',
      JP_price: 2430.22,
      TR_price: 338.86,
    },
    {
      productName: 'Product C',
      JP_price: 3120.1,
      TR_price: 435.2,
    },
  ],
  columns: [
    {
      data: 'productName',
      type: 'text',
      width: '120',
    },
    {
      data: 'JP_price',
      type: 'numeric',
      width: '120',
      numericFormat: formatJP,
    },
    {
      data: 'TR_price',
      type: 'numeric',
      width: '120',
      numericFormat: formatTR,
    },
  ],
  autoRowSize: false,
  autoColumnSize: false,
  columnSorting: true,
  colHeaders: ['Product name', 'Price in Japan', 'Price in Turkey'],
  height: 'auto',
  autoWrapRow: true,
  autoWrapCol: true,
  licenseKey: 'non-commercial-and-evaluation',
});

<script src="https://cdn.jsdelivr.net/npm/handsontable@14.6/dist/handsontable.full.min.js"></script>
<link type="text/css" rel="stylesheet" href="https://cdn.jsdelivr.net/npm/handsontable@14.6/dist/handsontable.full.min.css" /> 
<script src="https://cdn.jsdelivr.net/npm/numbro@2.1.2/dist/languages.min.js"></script>
<script src="https://handsontable.com/docs/scripts/fixer.js"></script>

<div id="example3" class="hot "></div>

Mind that the numericFormat option doesn't change the way numbers are presented or parsed by the cell editor. When you edit a numeric cell:

Regardless of the numericFormat configuration, the number that's being edited displays its decimal separator as a period (.), and has no thousands separator or currency symbol.
For example, during editing $7,000.02, the number displays as 7000.02.
You can enter a decimal separator either with a period (.), or with a comma (,).
You can't enter a thousands separator. After you finish editing the cell, the thousands separator is added automatically, based on your numericFormat configuration.

Cell type

Configuration options:
- numericFormat
- type
Core methods:
Hooks: