HyperFormula (v2.7.0)

# Compatibility with Google Sheets

Achieve nearly full compatibility wih Google Sheets, using the right HyperFormula configuration.

Contents:

# Overview

While HyperFormula conforms to the OpenDocument (opens new window) standard, it also follows industry practices set by other spreadsheets such as Microsoft Excel or Google Sheets.

That said, there are cases when HyperFormula can't be compatible with all three at the same time, because of inconsistencies (between the OpenDocument standard, Microsoft Excel and Google Sheets), limitations of HyperFormula at its current development stage (version 2.7.0), or limitations of Microsoft Excel or Google Sheets themselves. For the full list of such differences, see this page.

Still, with the right configuration, you can achieve nearly full compatibility.

# Configure compatibility with Google Sheets

# Array arithmetic mode

In Google Sheets, the array arithmetic mode is disabled by default.

To set up HyperFormula in the same way, set the useArrayArithmetic option to false.

useArrayArithmetic: false, // set by default

# Leap year bug

In Google Sheets, the year 1900 is correctly (opens new window) treated as a common year, not a leap year.

To set up HyperFormula in the same way, use the default configuration:

leapYear1900: false, // set by default

# Numerical precision

Both HyperFormula and Google Sheets automatically round floating-point numbers. To configure this feature, use these options:

# Separators

In Google Sheets, separators depend on your configured locale, whereas in HyperFormula, you set up separators through options (e.g., decimalSeparator).

In Google Sheets' en-US locale, the thousands separator and the function argument separator use the same character: , (a comma). But in HyperFormula, functionArgSeparator can't be the same as thousandSeparator. For this reason, you can't achieve full compatibility with Google Sheets' en-US locale.

To match Google Sheets' en-US locale as closely as possible, use the default configuration:

functionArgSeparator: ',', // set by default
decimalSeparator: '.', // set by default
thousandSeparator: '', // set by default
arrayColumnSeparator: ',', // set by default
arrayRowSeparator: ';', // set by default

Related options:

# Date and time formats

In Google Sheets, date and time formats depend on the spreadsheet's locale and are shared across all users (opens new window), whereas in HyperFormula you can set them up freely.

Options related to date and time formats:

# Full configuration

This configuration aligns HyperFormula with the default behavior of Google Sheets (set to locale en-US), as closely as possible at this development stage (version 2.7.0).

// define options
const options = {
  dateFormats: ['MM/DD/YYYY', 'MM/DD/YY', 'YYYY/MM/DD'],
  timeFormats: ['hh:mm', 'hh:mm:ss.sss'], // set by default
  currencySymbol: ['$', 'USD'],
  localeLang: 'en-US',
  functionArgSeparator: ',', // set by default
  decimalSeparator: '.', // set by default
  thousandSeparator: '', // set by default
  arrayColumnSeparator: ',', // set by default
  arrayRowSeparator: ';', // set by default
  nullYear: 30, // set by default
  useArrayArithmetic: false, // set by default
  leapYear1900: false, // set by default
  smartRounding: true, // set by default
};

← Runtime differences with Microsoft Excel and Google Sheets →