Versioning an Operational Spreadsheet Like Software

Most operational spreadsheets change the way a shared document changes: someone edits it, and the old behaviour is gone. That is fine for a list. It is not fine for a workbook with formulas generating barcode SKUs and macros writing an audit log. When a number looks wrong, the first question is "what changed, and when?", and a spreadsheet has no answer unless someone kept one.

The BBTech Stock Tracker, which I worked on and maintained (project page: /work/itms), kept one: a Change History sheet with a version, the changes, a status and a date for every release. This article reads that sheet as a release log. It covers what the log made possible, and what a log inside the file itself can't do.

Eight versions in under three weeks before go-live is the shape of a prototype being fitted to a real process. Each version adds one capability the operation turned out to need: separate sheets per department, then identifiers, then prices, then scanning, then management views. Version 8 is interesting because it replaces version 7's approach to the same problem, time in stage, moving it from formulas to a macro. The log records a design decision, not just a feature.

The renumbering at go-live is the most software-like choice in the sheet. Versions 1 to 8 were development. Version 1.1 on 1 July 2025 is the first release people depended on, and everything after it is a change to a system in use. That line is useful later. A question about data from May 2025 is a question about a tool that did not exist yet, not a bug.

After go-live the entries change character. Two of the next five cite a user request. One is forced by growth: the serial scheme needed a new form once a brand passed 99 units, which is described in Generating Barcode and Group SKUs in Excel. One, version 1.6, is documentation, treated as a release like any other. That is exactly right, and Writing a User Guide Into the Spreadsheet Itself covers what the guide contained. The last, version 1.8, is a structural change aimed at preventing mistakes rather than adding features. That is usually a sign a tool has reached the limits of its form.

The sheet itself has a few rough edges, and each is a cheap lesson:

• Version numbers were typed as numbers. Excel stores version 1.1 as a floating-point value, 1.1000000000000001 in the file, and would show a version "1.10" as 1.1. Format the version column as text.
• Dates were typed as text, like "11th June 2025". They read well, but they can't be sorted, filtered or compared. Use real dates and a display format.
• The status column drifted. Most rows say Completed. One has its date typed into the status cell, and the last has no status at all. A status column is only useful if every row uses the same small set of values.

A Change History sheet records intent. It does not record the change. There is no diff, so a log line like "separating update into a different sheet" does not say which columns moved. That is the kind of detail that later matters to a macro with hard-coded column numbers, as described in Why My VBA Maps Columns by Header Name. There is no rollback either. The previous version exists only if someone kept a copy.

The log also travels inside the file it describes. The tracker was shared as a weekly submission, and each weekly copy carries its own copy of the history. The copy I read is named for a week in April 2026, and its last entry is from January. Whether anything changed between January and April, the log cannot say. It is only as current as the copy you happen to have open.

For a spreadsheet that has to stay a spreadsheet, the cheap improvements are to save each release as a dated file in one place and treat those files as the rollback, and to write the changed sheets and columns into each log entry. For a spreadsheet whose change log reads like the one above, the stronger conclusion is the one BBTech reached in 2026: move the workflow into an application with real version control. That application, the web ITMS, was built by another BBTech developer; I troubleshoot and maintain it with them.