The Problem With Spreadsheet-Based Monitoring

Why this lesson matters

The spreadsheet is the second home of nearly every manual reading. It is free, universally understood and available immediately, which is exactly why it becomes the default operational record. The purpose of this lesson is not to argue that spreadsheets are bad software. It is to be precise about which properties an operational record needs and which of them a workbook structurally cannot provide.

What a monitoring record has to do

Before criticising the tool, define the job. A monitoring record must be able to answer, without interpretation:

• What is the full value series for asset ABC-01, in order, with units?
• When exactly was each value observed, and by whom?
• What evidence exists that the value came from that instrument?
• Which values fell outside the expected operating range at the time they were taken?
• Has any value been altered since capture, by whom, and what was the original?
• What is the consumption between two dates for a cumulative register, accounting for rollover and meter change?

Those are the six functional requirements. Now test the workbook against them.

Seven structural weaknesses

1. No fixed asset identity. "Gas Meter", "gas meter 1", "GM-01", "Main Gas" and "Gas Meter (new)" can all refer to the same physical instrument. A spreadsheet has no concept of a referential key, so it cannot prevent the same asset existing under five names, nor detect that it has. Every analysis begins with reconciliation work.

2. Position carries meaning. In a database, meaning is carried by the column definition. In a workbook, meaning is carried by where a value happens to sit. Insert a row, sort a range with one column unlocked, or paste into a merged cell, and the value silently changes meaning. There is no constraint to violate and therefore no error to raise.

3. No type or unit enforcement. 2.1, 2.1 bar, 2,1, ~2.1 and 2.1 (est) all live happily in the same column. Three of those are text. Averages and deltas calculated over that column are wrong, and they are wrong quietly. Units are usually recorded once in a header, or not at all, so a change from m3 to MJ or kL to L leaves no trace.

4. Cumulative and instantaneous values get mixed. A gas meter register is cumulative and must be differenced to yield consumption. A pressure gauge is instantaneous and must never be differenced. Spreadsheets do not know which is which. Rollover of a mechanical register (say a six digit index passing 999999) produces a large negative delta that a naive formula happily reports as negative consumption.

5. No capture time, only an entry date. The date in the sheet is usually the day someone typed it up, which may be days after the observation. Time of day is almost never recorded. For anything load related, a value without a time is close to useless.

6. No evidence and no audit trail. There is no photograph of the register, no capture location, no record of who entered the value and no history of amendment. Change tracking in shared workbooks is partial, easily disabled, and lost on export. When a figure is challenged in an audit you have the number and nothing behind it.

7. Uncontrolled copies. Monitoring_2026_v3_FINAL_JB.xlsx in three inboxes and two network folders is the normal end state. Without a single authoritative record, reconciliation becomes a recurring task rather than a one off.

The failure modes you should expect to find

If you audit a monitoring workbook that has been running for a year or more, you will usually find at least three of these:

• Carried forward values. The same figure repeating for weeks, generally because the instrument could not be read and the previous value was copied to keep the sheet complete.
• Transposition errors. 12,437 entered as 12,347. Undetectable without evidence or a plausibility check on the delta.
• Unit drift. A column silently changing from L to kL after a meter swap, producing a step change of three orders of magnitude in the trend.
• Broken formula ranges. A SUM or AVERAGE that stopped extending when rows were appended, so the summary tile has been wrong since the row it stopped at.
• Orphan rows. Readings for an asset that no longer exists, or for one that was renamed midway.
• Negative consumption. From register rollover, meter replacement without a reset entry, or a reading taken at the wrong index.

None of these are user error in the ordinary sense. They are the predictable output of storing event data in a grid with no constraints.

The honest counter argument

Spreadsheets are excellent at analysis. Once you have a clean, structured, exportable dataset with asset, timestamp, value, unit and quality flags, a workbook is a perfectly good place to build a pivot, a regression or a one off report. The problem is not analysis. The problem is using the workbook as the system of record for capture.

The rule worth adopting: capture in a structured system, analyse in whatever tool you like.

Migrating away without losing history

You do not need to abandon what you have. A practical sequence:

1. Fix identity first. Build an asset register with a permanent tag, a location, a measured quantity, a unit and whether the register is cumulative or instantaneous. Nothing else can be reliable until identity is.
2. Normalise units across the historical data and record the conversion you applied. Do not silently rescale.
3. Flag quality, do not delete. Mark estimated, carried forward, or suspect values rather than removing them. A gap you can see is far safer than a gap you cannot.
4. Import history as read only. Historical rows have no source evidence and should be labelled as such, so nobody later mistakes them for verified readings.
5. Switch capture at the instrument. From the changeover date, every new reading carries identity, value, unit, time, source and person.
6. Keep the workbook for one cycle as a parallel run, then retire it deliberately rather than by neglect.

Common mistakes at this stage

• Rebuilding the same structure in a database. Moving a flat grid into a table without asset keys, units and capture metadata reproduces every weakness with a slower interface.
• Discarding historical readings because they lack evidence. Imperfect history is still baseline. Label it, keep it.
• Assuming cumulative deltas are consumption without handling rollover, meter exchange and back dated corrections.
• Waiting for a perfect asset register before capturing anything. Start with the instruments on one round and extend.