Open XML Excel some formulas are not supported by shared formula

Excel doesn't support the shared formulas (created by the OpenXML SDK https://www.microsoft.com/en-us/download/details.aspx?id=30425) as expected:

formulas having a reference to a sheet and not using absolute references (eg. “=VLOOKUP(A1;Sheet2!A:B;2;0)”)
formulas using structured references such as [@[Sales Amount]] (https://support.office.com/en-us/article/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e)

provoke errors when the file is opened or saved, sometimes with the following error: "one or more formula ... are longer than the allowed limit of 8192 characters"

On the other hand, the following shared formulas work correctly:

“=VLOOKUP(A1;Sheet2!$A:$B;2;0)” (using absolute references with dollars).
"=A1" or "=$A1" (when extending the formula across a column)

The XML structure looks like this:

B2: <c r="B2"><f t="shared" ref="B2:B3" si="0">VLOOKUP(A2,Sheet2!A:B,2,0)</f><v></v></c>
B3: <c r="B3"><f t="shared" si="0"></f><v></v></c>

Replace the formula by the one with dollars, it will work. By a structured references, it won't, etc.

When using such formulas through the Excel UI, expanding the formula will either lead to a shared formula or not, depending on whether the formula is supported (I suppose). Trouble is that all formulas should be supported, or else it should be documented somewhere.

When a formula is a shared formula (t value is shared) then this value indicates the group to which this particular cell's formula belongs. The first formula in a group of shared formulas is saved in the f element. This is considered the 'master' formula cell. Subsequent cells sharing this formula need not have the formula written in their f element. Instead, the attribute si value for a particular cell is used to figure what the formula expression should be based on the cell's relative location to the master formula cell.

Reference: https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.cellformula.aspx

As anyone else experienced this?

After long talks with Microsoft support, their answer is:

First of all, even though we clearly understand it would be easier to have Shared Formulas, it is not how Excel works. Excel itself does not create the shared formulas that you are trying to create. If you enter the same formulas in Excel, fill down and save, you get the repetitive formulas stored and not the Shared Formulas.

(This is true, but Excel also creates shared formula when they work, my issue here is that not all formulas work.)

The error is clear that Excel will not work with it, which is consistent across all versions of Excel from 2007 and higher. In this case, as a workaround, you should mimic Excel and replicate the formulas down the sheet as Excel does which works without errors.

And how am I suppose to know beforehand if the formula will be supported by Excel? Microsoft doesn't even propose an official library to parse Excel formula. We could use https://github.com/spreadsheetlab/XLParser that looks pretty solid, but the whole point of using shared formula is not to parse the formulas, and let Excel do it.

The only way this behavior can be altered is to implement a design change into Excel’s behavior but by doing so the whole backwards compatibility will be broken all the way back to the 2007 version. This is not something our Product Group is interested in changing.

So Excel will keep crashing when saving the file with weird error messages...

As I was verifying internally to better answer your questions I now have a clear confirmation from my Escalation Team that the discussion about details in the file format handling, or in-memory handling in Excel of said shared formulas won’t change the outcome. Matter of fact, there are scenarios where one cannot use shared formulas for many different reasons, some of them might not be apparent and visible from outside Excel. The best rule of thumb is the one already outlined: don’t use shared formulas when Excel itself doesn’t use one. And this needs to take things into account like subtle differences in the formulas, whether there are absolute or relative references (as in the your example with A:B vs $A:$B), whether the reference refers to the same worksheet or another one, or in a different workbook, etc., then also what kind of formula it is used in (some functions like OFFSET or INDIRECT have dependencies on ranges that are subject to change during a calculation), and whether precedent or dependent ranges/formulas indicate that it might be safer to not use shared formulas. The file format documentation doesn’t clearly state under what conditions a shared formula can be used. It specifies that a file reader could encounter such formulas, and when this happens how to handle that, but that doesn’t mean that shared formulas are always possible or encouraged whenever a contiguous range of cells contains the same formula in the formula bar. For a file writer, it isn’t encouraged to use unless the scenario is restricted to exactly the same as when Excel writes a shared formula.

Still, LibreOffice handles it perfectly. Looks like "it's too complicated to fix" or "it's too risky to fix the bug" excuse.

Since they suggested that I create a User Voice entry, you can vote for it there:

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/37328965-full-support-for-shared-formula-matching-the-open

Recommended topics

Hot tags