# Importing Excel Spreadsheet containing Array Formulas

Bug introduced in ?.?.? and fixed in 11.0.0

Has anyone had any success with importing an Excel Spreadsheet holding an array formula into Mathematica?

The Syntax I'm using is

Import["test.xlsx", {"Sheets", "SheetWithArrayFormula"}]

The error that is returned is: Import::fmterr: Cannot import data as XLSX format. >>

EDIT

A sample excel file can be found here: http://temp-share.com/show/gFHKQZS0Y

• Please provide a small sample Excel file for testing. Jul 19 '13 at 1:48
• I've made an edit to contain a link with a sample excel file. Jul 19 '13 at 1:58
• I have been informed by WRI that the reported issue has been resolved; a quick check done by me revealed no difficulties with array formulas in excel files being imported. I thus added an updated bug header.
– gwr
Sep 26 '16 at 17:46

It seems there are some troubles with the XLSX format, whereas using the old XLS it works fine. Look the following tests:

Import["c:\\tempmath\\test.xlsx", "Sheets"]


{"SheetWithArrayFormula"}

Import["c:\\tempmath\\test.xlsx", {"Sheets", "SheetWithArrayFormula"}]


Import::fmterr: Cannot import data as XLSX format. >>

$Failed Import["c:\\tempmath\\test.xlsx", "Formulas"]  {{{"", "", ""}, {"", "", "A2:A4*B2:B4"}, {"", "", "A2:A4*B2:B4"}, {"", "", "A2:A4*B2:B4"}}} Import["c:\\tempmath\\test.xlsx"]  Import::fmterr: Cannot import data as XLSX format. >>$Failed

after converting the XLSX to XLS

Import["c:\\tempmath\\test.xls"]


{{{"X", "Y", "Formula"}, {1., 0.1, 0.1}, {2., 0.2, 0.4}, {3., 0.3, 0.9}}}

Import["c:\\tempmath\\test.xls", "Formulas"]


{{{"", "", ""}, {"", "", "A2:A4*B2:B4"}, {"", "", "A2:A4*B2:B4"}, {"", "", "A2:A4*B2:B4"}}}

Import["c:\\tempmath\\test.xls", {"Sheets", "SheetWithArrayFormula"}]


{{"X", "Y", "Formula"}, {1., 0.1, 0.1}, {2., 0.2, 0.4}, {3., 0.3, 0.9}}

generally when you want to look which elements are available into an external file, use the second Import argument "Elements"

Import["c:\\tempmath\\test.xlsx", "Elements"]


{"Data", "FormattedData", "Formulas", "Images", "Sheets"}

So, I guess the problem is not due to the Excel file contents (array formulas), rather to some wrong behavior of Import and XLSX.

• I wonder if there is a workaround using "Data"? Jul 19 '13 at 22:43
• Unfortunately, even adding "Data" it doesn't work. However, you can convert the file to xls format and it works. Jul 20 '13 at 4:56
• Yes, but that's going to be very unpopular with anyone hoping to use Mathematica for visualizing a lot of data stored in Excel. It is clearly a bug in Mathematica. Jul 20 '13 at 7:27
• @Verbeia that's right, I already sent a report to WRI technical support. Jul 20 '13 at 7:45
• I just received an answer from WRI technical support: The issue is known and they hope it will be fixed by a future release. Hope dies last as they say.
– gwr
Jun 30 '15 at 20:59