The Workbook with Twenty-Three Formulas That Reported None

Why a file arrives without its results

Spreadsheet applications store the last computed value as a convenience so that a file opens instantly instead of recalculating thousands of cells. Anything that writes .xlsx without being a spreadsheet — a reporting job, an accounting export, a library in a server script — has no calculation engine, so it writes the formula it was asked for and leaves the result blank for whatever opens the file next. That is a legitimate file: the specification allows the value element to be absent or empty. It simply means the workbook has never been evaluated, and a reader has to decide whether that makes the cell interesting or invisible.

What the option costs on an ordinary file

Nothing worth noticing. Keeping stub cells adds entries for cells that exist but hold no value, which changes the shape of a raw row array — a row that came back as three items can come back as two, with the missing one implied. The fix is to pad every row to the width of the widest one before doing anything with it, which any table view needs anyway. On a normal workbook, where every formula carries its result, the rows read out character for character the same as before. The option only changes what happens to cells that would otherwise have disappeared silently, and silence is the part that made this bug expensive: the file opened, the table looked right, and the answer to a direct question was wrong.

Frequently asked questions

How do I tell whether my file has this problem?

Open it and look for a column that should be computed and is blank, while the columns it depends on are full. That is the signature. Opening the same file in a spreadsheet application hides the symptom rather than the cause, because the application recalculates on open and fills the values in — which is also the repair: open it, let it recalculate, save it, and the file now carries its results.

Does the viewer send my workbook anywhere?

No. The file is read in the browser, by code that has already been downloaded with the page, and nothing about it is uploaded. That matters for a spreadsheet more than for most files, because workbooks tend to hold payroll, client lists and prices — the sort of thing you would not paste into an unknown site to find out what is in it.

Which formats does it open?

The four you are likely to be sent: .xlsx, the older .xls, the OpenDocument .ods, and Apple's .numbers. An .ods written by some generators arrives without the style part that readers expect, and is repaired on the way in rather than refused — a file that a strict reader calls corrupt is usually just incomplete.