Sign up ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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. >>


A sample excel file can be found here:

share|improve this question
Please provide a small sample Excel file for testing. – Mr.Wizard Jul 19 '13 at 1:48
I've made an edit to contain a link with a sample excel file. – Jonie Jul 19 '13 at 1:58

1 Answer 1

up vote 9 down vote accepted

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"]


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

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


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

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


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


after converting the XLSX to 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.

share|improve this answer
I wonder if there is a workaround using "Data"? – Verbeia 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. – bobknight 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. – Verbeia Jul 20 '13 at 7:27
@Verbeia that's right, I already sent a report to WRI technical support. – bobknight 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 at 20:59

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.