Spreadsheets Done Right

Margriet de Wit's monthly KPI pack at Kestrel Energy starts life in a spreadsheet. Lucía Romero opens the 02:30 export of KBS (Kestrel Billing System), copies it into last month's workbook, adjusts the formulas and sends the result. It works, mostly, because Lucía is careful. This page rebuilds that workbook so that it no longer depends on being careful: the same numbers, laid out so that mistakes are hard to make and easy to find. The formulas themselves are on Spreadsheet Formulas.

What a spreadsheet is

A spreadsheet is a program, and the document it edits, that stores values and formulas in a grid of cells addressed by column letter and row number (B2, F14). When an input changes, every formula that depends on it is recalculated. Microsoft Excel, LibreOffice Calc and Google Sheets are the common ones. The file is a workbook: one document, such as an .xlsx or .ods file, that holds one or more sheets. Each worksheet (or simply sheet) is one grid of cells with its own name on a tab.

A spreadsheet is the most widely used analysis tool there is, and for good reasons: everyone has one, you see the data while you work, and a result is a few clicks away. The same freedom is the risk. Any cell can hold anything, a formula can point anywhere, and nothing forces a structure. The rules on this page add that structure by hand.

A million rows sounds like a lot until you compare it with Kestrel's smart meters, which deliver about 38.4 million readings a day. That limit, and the other reasons to move a process out of a spreadsheet, are on When a Spreadsheet Is Not Enough. The KPI pack itself uses daily totals per meter, which fit easily.

The KPI pack before

Lucía's current file is called KPI-pack-2026-09-final-v3.xlsx. It is a typical spreadsheet that grew over the years, and nothing in it is unusual:

• One sheet holds the pasted export, a summary block to its right and a chart, with blank rows between the parts.
• The header row is two rows high, with "September 2026" merged across four columns above "kWh", "amount", "status" and "due".
• Overdue invoices are colored orange by hand; the overdue total is added up from the orange cells.
• Prices are typed inside formulas (=C7*0.27), and the grand total row sits in the middle of the data, above a few rows added later.
• The steps exist in Lucía's head and in a sticky note on her screen.

Each of these is a reasonable shortcut on the day it was taken. Together they mean that only one person can produce the pack, and that an error can survive for months. The rest of this page goes through them one by one.

Tidy data

The most useful idea for laying out data in any tool comes from the statistician Hadley Wickham's paper "Tidy Data" (Journal of Statistical Software, 2014). Tidy data is data arranged so that its structure matches its meaning, by three rules:

1. Each variable forms a column.
2. Each observation forms a row.
3. Each type of observational unit forms a table.

Wickham notes that this is Codd's third normal form "framed in statistical language": the same idea as normalization in databases. The shorter version you will often see, "each value is a cell", comes from his later book R for Data Science, not from the paper. For a spreadsheet it means a sheet looks like a database table: a header row with one name per column, and below it one row per thing you measured.

Wide and long layouts

Meter readings are a good test. A layout that is easy to type, and that many exports use, puts each day in its own column:

It reads well, but "date" is a variable stored in the column names. A month needs 30 columns, a year 365, a new day means a new column and changed formulas, and there is no column to filter on. The tidy, long layout has one row per meter per day, so the date becomes an ordinary column. Totals per meter or per day are then the same formula with a different condition (SUMIFS is explained on Spreadsheet Formulas):

   | A        | B          | C          | D | E          | F
 1 | meter_id | date       | kwh_import |   | meter_id   | kwh_3_days
 2 | M-30002  | 2026-09-01 | 8.760      |   | M-30002    | =SUMIFS(C$2:C$10,A$2:A$10,E2)
 3 | M-30002  | 2026-09-02 | 7.860      |   | M-30004    | =SUMIFS(C$2:C$10,A$2:A$10,E3)
 4 | M-30002  | 2026-09-03 | 10.027     |   | M-30005    | =SUMIFS(C$2:C$10,A$2:A$10,E4)
 5 | M-30004  | 2026-09-01 | 9.680      |   |            |
 6 | M-30004  | 2026-09-02 | 8.860      |   | date       | kwh_3_meters
 7 | M-30004  | 2026-09-03 | 8.040      |   | 2026-09-02 | =SUMIFS(C$2:C$10,B$2:B$10,E7)
 8 | M-30005  | 2026-09-01 | 5.612
 9 | M-30005  | 2026-09-02 | 7.055
10 | M-30005  | 2026-09-03 | 6.465

   | A        | B          | C          | E          | F
 1 | meter_id | date       | kwh_import | meter_id   | kwh_3_days
 2 | M-30002  | 2026-09-01 | 8.76       | M-30002    | 26.647
 3 | M-30002  | 2026-09-02 | 7.86       | M-30004    | 26.58
 4 | M-30002  | 2026-09-03 | 10.027     | M-30005    | 19.132
 5 | M-30004  | 2026-09-01 | 9.68       |            |
 6 | M-30004  | 2026-09-02 | 8.86       | date       | kwh_3_meters
 7 | M-30004  | 2026-09-03 | 8.04       | 2026-09-02 | 23.775
 8 | M-30005  | 2026-09-01 | 5.612      |            |
 9 | M-30005  | 2026-09-02 | 7.055      |            |
10 | M-30005  | 2026-09-03 | 6.465      |            |

The grids on these pages are real: each cell is typed into LibreOffice Calc 25.2 exactly as shown, and the output is what Calc displays, which is why 8.760 comes back as 8.76 (a number has no trailing zeros until you format it). The summary sits beside the data here only to keep the example small; in a real workbook it belongs on its own sheet, as described below. The long layout is also what databases, pandas and every BI tool expect, so a tidy sheet moves to those tools without being reshaped. When people need the wide view, make it from the long data with a pivot table (see Pivot Tables and Summaries) instead of storing it that way.

One table per sheet

The practical rules that follow from tidy data are short, and each one prevents a specific kind of error:

• One table per sheet, starting in A1. Sorting, filtering and "select the whole table" (Ctrl+A in Excel, Ctrl+* in Calc, where Ctrl+A selects the whole sheet) work on the block of cells around the cursor. A second table next to the first gets sorted along with it, or cut in half.
• Exactly one header row, with short, unique names and the unit in the name: kwh_import or "Import (kWh)", amount_eur. A two-row header means every tool has to be told which row is the real one.
• No blank rows or columns inside the table. A blank row ends the block: filters, sorts and tables stop there, and the rows below silently drop out.
• One type of value per column: only numbers, only dates or only text, and the same unit throughout.
• No totals, subtotals or notes inside the data. Put them on an output sheet.
• No meaning in formatting. Colors, bold and borders are for reading, never the only place where a fact is stored.

One type of value per column

A spreadsheet decides what a cell holds when you type or paste it. Text that looks like a number becomes a number, and a number that arrives as text (from an export with a stray apostrophe or space, or from a column formatted as Text) stays text, usually left-aligned with a small warning marker. Functions that add up ranges skip text, without an error:

  | A         | B
1 | meter_id  | kwh_september
2 | M-30002   | 296.708
3 | M-30004   | '271.212
4 | M-30012   | 231.104
5 | Total     | =SUM(B2:B4)
6 | Numbers   | =COUNT(B2:B4)
7 | Not empty | =COUNTA(B2:B4)

  | A         | B
1 | meter_id  | kwh_september
2 | M-30002   | 296.708
3 | M-30004   | 271.212
4 | M-30012   | 231.104
5 | Total     | 527.812
6 | Numbers   | 2
7 | Not empty | 3

The leading apostrophe in B3 is how you type "this is text" in Excel and Calc; it is not shown in the cell. The total is 271.212 short and nothing looks wrong. The two counts below it are a cheap check worth adding to any data sheet: COUNT counts numbers and COUNTA counts filled cells, so when they differ, some "numbers" are text. Converting them is covered under text and date functions.

The opposite problem hits codes. A customer number, postcode or meter code is a label made of digits, not a quantity, so it must be stored as text (see levels of measurement):

  | A                | B                   | C
1 | entry            | value               | check
2 | code as a number | 004417              | =LEN(B2)
3 | code as text     | '004417             | =LEN(B3)
4 | EAN as a number  | 871687400000000017  | =B4=871687400000000000
5 | EAN as text      | '871687400000000017 | =LEN(B5)

  | A                | B                  | C
1 | entry            | value              | check
2 | code as a number | 4417               | 4
3 | code as text     | 004417             | 6
4 | EAN as a number  | 8.716874E+017      | TRUE
5 | EAN as text      | 871687400000000017 | 18

Typed as a number, 004417 loses its zeros and no longer matches anything. The 18-digit EAN code that identifies a connection is worse: Calc stores it as the nearest floating-point number and Excel keeps 15 significant digits, and for this code both end up with 871687400000000000, as the TRUE in C4 shows. Excel displays it as 8.71687E+17, Calc as 8.716874E+017; either way the last digits are gone, and saving the file writes the damaged value back. Format a code column as Text before you type or paste into it, or import the CSV with that column set to text; the CSV page describes the import side and Excel's Automatic Data Conversion options.

Totals outside the data

A total or subtotal row inside the data is counted again by every formula that covers the whole column. Here the three dynamic meters are summed with a household subtotal in between:

  | A             | B         | C
1 | meter_id      | segment   | kwh_september
2 | M-30004       | household | 271.212
3 | M-30012       | household | 231.104
4 | Subtotal      | household | =SUM(C2:C3)
5 | M-30006       | business  | 1554.960
6 | Dynamic total |           | =SUM(C2:C5)

  | A             | B         | C
1 | meter_id      | segment   | kwh_september
2 | M-30004       | household | 271.212
3 | M-30012       | household | 231.104
4 | Subtotal      | household | 502.316
5 | M-30006       | business  | 1554.96
6 | Dynamic total |           | 2559.592

The right answer is 2,057.276 kWh, the figure DuckDB gives on What Is Data Analytics?; the households are counted twice. The subtotal row also breaks sorting, filtering and every pivot table built on the range, and a filter on segment = household now finds three "households". Keep the data sheet pure and compute subtotals on the output sheet.

Color is not data

"Orange means overdue" stores a fact where formulas cannot reach it: no ordinary worksheet function reads a cell's fill color, so Lucía's overdue total is added up by hand, and sorting or copying to another file can lose the color altogether. It also fails people who cannot tell orange from the other colors in use, the problem that WCAG success criterion 1.4.1 (Use of Color) addresses for web content (see color and contrast and color vision deficiency). Put the status in a column (status = paid, open, overdue). Conditional formatting can then color the rows from that column, so the color always agrees with the data and nothing is lost when it disappears.

Merged cells

A merged cell is a group of adjacent cells joined so that they display as one; only the top-left cell holds the value, and the others are empty. It looks tidy in a report header, but in data it breaks almost everything: sorting and filtering refuse to work or misalign rows, copying a column copies the empty cells, a lookup finds the value only in the first row of the group, and selecting a single column selects the whole merged block.

Lucía's "September 2026" header merged across four columns is the typical case. The month is a value, so in a tidy table it becomes a column (period) on every row. For a visual label across columns on an output sheet, use "Center Across Selection" (Excel: Format Cells, Alignment) or simply a title in one cell.

Formatted tables

Excel can turn a range into a table: a named, structured block of rows with a header row that the program manages as one object. You create one with Ctrl+T or Insert > Table ("My table has headers" checked). Excel names it Table1, Table2 and so on; rename it on the Table Design tab to something meaningful, such as Readings. A table gives you three things a plain range does not:

• It grows by itself. Rows pasted directly below become part of the table, and every formula, chart and pivot table that refers to it includes them. No more =SUM(C2:C359) that misses row 360.
• Structured references. Formulas refer to columns by name instead of by address.
• Calculated columns. A formula typed in one cell of a column is copied down the whole column, so the column cannot end up with a different formula halfway.

=SUM(Readings[kwh_import])                 total of the whole column, however long it gets
=SUMIFS(Readings[kwh_import], Readings[meter_id], "M-30002")
=[@kwh_import] * Tariffs!$B$2              inside the table: "this row's kwh_import"

Structured references started in Excel, but they are no longer Excel-only. Google Sheets has tables with table references (Format > Convert to table). LibreOffice Calc 25.2 has no table object like Excel's. Its closest equivalent is a database range (Data > Define Range): give it a name such as Readings, tick that it contains column labels, and Calc accepts =SUM(Readings[kwh_import]). It does not grow by itself or copy formulas down, though. If a workbook has to work in more than one program, test its formulas in each one, or use named ranges, which every spreadsheet program understands.

Named ranges

A named range is a name given to a cell or a block of cells, such as FixedPrice for the cell that holds €0.2700 per kWh, which formulas can use instead of the address. =C2*FixedPrice says what it does; =C2*Tariffs!$B$2 needs to be decoded, and =C2*0.27 hides a number that will change.

• Excel: select the cell, type the name in the Name Box left of the formula bar and press Enter; or use Formulas > Define Name. Formulas > Name Manager lists every name in the workbook.
• LibreOffice Calc: type the name in the Name Box, or use Sheet > Named Ranges and Expressions > Define.

A name cannot contain spaces and must not look like a cell address (KWH2026 is a valid cell reference in a sheet with 16,384 columns, so it cannot be a name). Names point to a fixed place, which is why they are ideal for single input cells such as a price or a reporting date. For a list that grows, refer to a table column instead, or make sure the name covers the whole list.

Data validation

Data validation is a rule attached to cells that limits what can be typed into them, such as a choice from a list, a whole number in a range or a date after a given day, with an optional input message and an error alert. It catches mistakes at the moment they are made, which is the cheapest moment there is.

• Excel: Data > Data Validation; choose List under Allow, and enter the values in Source (fixed,day_night,dynamic,variable) or, better, point Source at a range that holds the list.
• LibreOffice Calc: Data > Validity, with the same kinds of criteria, an input help and an error alert.

For the KPI workbook, a validation list on the tariff column of any sheet where people type, and a date rule on the reporting date, remove a whole class of typos. Keep the allowed values in a small list on the Tariffs sheet, so adding a tariff means adding one row.

Checking data that is already there

For pasted data, a check column with a formula is more reliable than validation, and it stays visible. One detail matters: spreadsheet comparisons ignore case. "den haag"="Den Haag" is TRUE, and COUNTIF counts both. The planted problem from Data Types and Data Quality disappears from view:

  | A                    | B        | C
1 | name                 | city     | exactly "Den Haag"?
2 | Anouk Brinkhorst     | Den Haag | =EXACT(B2,"Den Haag")
3 | Chidi Okonkwo-Visser | den haag | =EXACT(B3,"Den Haag")
4 | Ilse van Dorsten     | Den Haag | =EXACT(B4,"Den Haag")
5 | COUNTIF "Den Haag"   | =COUNTIF(B2:B4,"Den Haag")
6 | SUMPRODUCT EXACT     | =SUMPRODUCT(EXACT(B2:B4,"Den Haag")*1)

  | A                    | B        | C
1 | name                 | city     | exactly "Den Haag"?
2 | Anouk Brinkhorst     | Den Haag | TRUE
3 | Chidi Okonkwo-Visser | den haag | FALSE
4 | Ilse van Dorsten     | Den Haag | TRUE
5 | COUNTIF "Den Haag"   | 3        |
6 | SUMPRODUCT EXACT     | 2        |

EXACT compares two texts including case. COUNTIF says three customers live in "Den Haag", which is true for a person but not for a computer: DuckDB, pandas and most BI tools treat the two spellings as two cities, so a report built from the same data elsewhere shows "Den Haag 2, den haag 1". Fix the spelling in the source, and use exact checks when you test data that will leave the spreadsheet.

Separate inputs, calculations and outputs

The single most effective habit in spreadsheet work is to keep different kinds of cell on different sheets, and to let data flow in one direction. It is a common practice in financial modeling, and it is the spreadsheet version of what a data pipeline does: raw data in, transformations, a published result. Rebuilt this way, the KPI pack has five sheets:

The rules that make the layout work:

• Raw data stays raw. Corrections happen in Calc (or, better, at the source), never in Readings. That way the workbook can always be rebuilt from next month's export, and a difference can be traced to the row that caused it.
• No constants in formulas. Every number that can change lives once, on the input sheet, with a name.
• Never type over a result. A hand-typed total that "fixes" a formula cannot be reproduced next month and disagrees with every other report. If a figure is wrong, fix the formula or the data.
• Build in checks. A Calc cell that compares the sum of the readings with the sum of the per-tariff totals, or counts rows per meter, turns silent errors into a visible FALSE.
• Protect what should not change. Lock the Calc and KPI sheets (Review > Protect Sheet in Excel, Tools > Protect Sheet in Calc) so that a stray keystroke cannot replace a formula. Sheet protection prevents accidents; it is not a security control.

The KPI pack, before and after

None of this changes what the pack calculates, and it does not yet settle which contracts count as "active": that is a definition, and Femke Jansen's approved version gives 11 where the old pack counted 13 (see define the measure before you count). What the new layout does is make each number traceable to its inputs, so that such a difference can be found and explained. The next step is the formulas on the Calc sheet: Spreadsheet Formulas.