Spreadsheet engine (P33)

Cells is a spreadsheet with its own formula engine, written for the prototype in plain JavaScript. LibreOffice works out a corpus of workbooks, and Cells must give the same value in every cell.

The spreadsheet

The spreadsheet opens with a sample workbook for a bakery: a budget for a year that grows each month, a loan for its new oven with sixty payments, a price list the budget looks up, and a sheet of arrays.

The grid is drawn on a canvas, only the cells in view, so a sheet of any size scrolls at once. You select cells with the mouse or the arrow keys. Shift extends the selection, and Cmd or Ctrl with an arrow jumps to the edge of the data. Typing starts editing, and F2 edits in place.

A formula starts with =. While you type it, each reference has its own color in the cell, in the formula bar and on the grid. After an operator, a bracket or a comma, an arrow key or a click puts a reference into the formula. Function names are suggested as you type, and Tab takes the suggestion. A hint under the formula shows the function's arguments with the current one in bold.

The fill handle at the corner of the selection fills down or right: formulas move their relative references, and two or more numbers continue as a series. Copy and paste moves relative references, cut and paste keeps every reference pointing at the moved cells, and text copied from another program pastes into cells by tabs and lines. Rows and columns can be inserted and deleted, and every change can be undone.

The side panel shows a cell's precedents (the cells it reads) and dependents (the formulas that read it). It also shows how many cells the last change recalculated.

Formulas and values

The parser reads formulas as Excel writes them in XLSX files. The range, intersection and union operators come first, then the minus sign of a number, percent, powers, multiplication, addition, joining text and comparison. =-2^2 is therefore 4. A parse error says what is wrong and at which character.

Values work as LibreOffice works them for an XLSX file. TRUE and FALSE are the numbers 1 and 0, and two numbers that agree to about 15 digits are equal, so =0.1+0.2-0.3 gives 0. A date is a number of days from 30 December 1899.

Cells has 129 functions: sums and counts, statistics, rounding, logic, text, dates, lookups, finance, the criteria functions such as SUMIF and COUNTIFS, and the dynamic array functions SEQUENCE, FILTER, SORT and UNIQUE. A formula that gives an array spills it into the cells beside it, and shows #SPILL! when a cell is in the way.

Recalculation

Every formula registers the cells and ranges it reads. Ranges are kept in blocks of 64 rows by 16 columns, so a change in a big range finds its formulas quickly. A change recalculates only the cells it reaches, each after the cells it reads. OFFSET, INDIRECT and RAND are recalculated on every change. A circular reference shows #CIRC! in each of its cells.

The recalculation page builds a sheet of 100,000 cells in a Web Worker, with long chains down the sheet. Changing one input shows how many cells it reaches, how many were recalculated and the time, and compares that with recalculating every formula.

Against LibreOffice

The corpus has 14 workbooks in families: arithmetic, math, statistics, text, logic, dates, lookups, finance, criteria, errors, references across sheets, dynamic arrays, a budget and a loan schedule. A fuzzer writes 50 random formulas over a grid of values for each of 60 seeds.

Cells writes each workbook as an XLSX file with no cached values. LibreOffice 26.8 opens it, works out every formula and saves it. One value in each workbook is wrong on purpose, and LibreOffice must replace it. Cells reads LibreOffice's values back and compares every cell: numbers to 15 significant digits, texts and TRUE or FALSE exactly, and errors by type.

All 1,472 cells of the corpus and all 3,000 fuzz formulas match. The page against LibreOffice runs the comparison again in the browser.

Where Cells follows LibreOffice instead of Excel

• TRUE and FALSE count as numbers in SUM and COUNT, TRUE>1 is FALSE, and "a"&TRUE is a1.
• Dates before 1 March 1900 are one less than in Excel, because LibreOffice has no 29 February 1900.
• A text in arithmetic is read like typed input, so "1/2/2026"+0 is a date. A text typed into SUM or AVERAGE is #VALUE!.
• An illegal argument, such as SQRT(-1), is #VALUE! instead of #NUM!.
• A circular reference is written as #VALUE! in an XLSX file.
• IRR and RATE give #N/A when they find no answer.

Files

Cells opens an XLSX file and saves the workbook as one. It reads and writes shared strings, number formats, several sheets, defined names, and formulas with their values.

Previous: Bookkeeping (P32). Next: RISC-V computer (P34).