Incompatible Formulas Between Excel and Google Sheets

Overview

Certain formulas cannot be translated between Excel and Google Sheets. In this case, Data Everywhere will make the feed read-only (not publishable) in Google Sheets, and use the display value from the formulas rather than the formula itself. In order to resolve the issue, please have an editor modify the feed in Excel to use different formulas that are compatible.

Known Formula Issues

Structured References

When Excel formats data into tabular form with column headers, it is called a Data Table. Data Everywhere converts all data into Data Tables before creating feeds. Regular A1 or R1C1 references (e.g. "A1:A4") work as usual in Data Tables. However, there is an alternate form of referencing cells in Data Tables called "Structured References". These are of the format "TableName[ColumnName]" or "@[ColumnName]". These formulas cannot be translated into Google Sheets, as there is no concept of a Data Table yet in Google Sheets. Please update the feed in Excel to use A1 or R1C1 style references.

External References

If a formula in a cell within a Data Feed refers to a cell outside of that feed, Data Everywhere cannot translate the formula into Google Sheets. For example, if a feed refers to a column on a different sheet within Excel, that other sheet is not part of the feed that is inserted into Google Sheets, and therefore cannot be referenced properly. Please update the feed in Excel to only reference cells within the feed.

Array Formulas

Array formulas are not currently supported in Google Sheets, and have limited support in Microsoft Excel.

Google Sheets-specific Formulas

There are a handful of formulas specific to Google Sheets that will not be transferred to Excel. If a feed using these formulas is created in Google Sheets and then inserted into Excel, it will be read-only. This list of formulas includes (but may not be limited to):

  • ADD
  • ARRAYFORMULA
  • CONCAT
  • CONTINUE
  • COUNTUNIQUE
  • DETECTLANGUAGE
  • DIVIDE
  • EQ
  • FILTER
  • GOOGLEFINANCE
  • GOOGLETOURNAMENT
  • GOOGLETRANSLATE
  • GT
  • GTE
  • IMAGE
  • IMPORTDATA
  • IMPORTFEED
  • IMPORTHTML
  • IMPORTRANGE
  • IMPORTXML
  • JOIN
  • LT
  • LTE
  • MINUS
  • MULTIPLY
  • POW
  • QUERY
  • SORT
  • SPARKLINE
  • SPLIT
  • UMINUS
  • UNARY_PERCENT
  • UNIQUE
  • UPLUS

Other Differences

There may be other formulas that do not translate properly. For example, a function may be available in Excel but not in Google Sheets. If you run into an incompatible formula but you don't understand why or how to fix it, email  support@dataeverywhere.com and we will investigate it for you.

Still need help? Contact Us Contact Us