Why the Way a Change Is Made Matters
A spreadsheet can contain a correct-looking result without showing how that result was produced. A cell may have been typed over, copied from another row, calculated by a formula, or created by a script. Those approaches can lead to the same values today, but they do not leave the same evidence for someone who needs to repeat or inspect the work.
This distinction matters whenever data are transformed before analysis. If a colleague asks how a category was recoded, which rows were retained, or how a new value was calculated, the answer should not depend on remembering a sequence of clicks. A recorded workflow makes the operations and their order visible enough for another person to follow them and, where appropriate, run them again.
Manual work is not automatically wrong, and formulas or scripts are not automatically correct. The important difference is what can be recovered from the record. A directly typed value usually does not say why it was chosen or how it would be obtained for another row. A formula or script can expose that rule, but only if the rule is clear, the relevant inputs are identifiable, and the steps are applied as intended.
What Makes a Workflow Repeatable?
Suppose \(X\) denotes the input data and \(T_1, T_2, \ldots, T_k\) are the transformations applied in order. The output is described as:
This notation emphasizes two practical points. First, the order of operations matters: changing the order can change the result. Second, repeating the workflow requires enough information to know what each \(T_i\) does and which input it acts on. A set of unexplained edits may leave \(Y\) behind without preserving the transformations that produced it.
A formula in a cell is a record of a rule, but not necessarily of the entire workflow. For example, if a formula is copied down only part of a column, some rows may still contain manually typed values. If someone later overwrites the formula with its displayed result, the calculation rule is no longer visible in that cell. Similarly, a script can be hard to follow if its purpose, inputs, or operation order are unclear. The format alone does not guarantee reproducibility.
Describe what is changing, such as converting a recorded code to a category label or calculating a duration.
Use a formula or script when it can express the transformation clearly, rather than relying on unexplained typed results.
Keep the original values available to the workflow so that a reviewer can see what each output depends on.
Note operations that a formula or script does not make explicit, including a decision about which records to retain.
The goal is not to eliminate every manual action. Sometimes a small one-time correction is necessary. The goal is to avoid treating an unrecorded action as if it were a repeatable rule. If a manual step remains, write down what was done, where it was applied, and the reason for it.
Worked Examples: Comparing Approaches
Worked Example: Replacing Short Category Codes
In a hypothetical training dataset, the response column contains the values shown below. The intended labels are “Yes” for Y and “No” for N. Compare typing replacements directly with using an explicit formula.
| Row | Original response |
|---|---|
| 1 | Y |
| 2 | N |
| 3 | Y |
| 4 | N |
Manual approach. A person can click each cell and type “Yes” or “No.” The four resulting labels might all be correct, but the cells alone do not show whether the conversion rule was Y to Yes and N to No, or whether the labels were selected individually for some other reason. If another row is added later, the same decision has to be remembered and applied again.
Formula approach. In a new column, enter a formula that explicitly maps each code. For example, in spreadsheet-style notation:
When applied to the four rows, the outputs are Yes, No, Yes, and No, respectively. The nested conditions state the rule: return Yes when the input is Y; otherwise return No when the input is N; otherwise leave the input unchanged. Keeping the original response in its own column makes the source value available for comparison.
The formula is more inspectable than four unexplained replacements because a reviewer can read the mapping. It still needs to be applied to the intended rows, and its conditions should match the planned interpretation. If a direct replacement is used instead, recording the exact mapping and the affected range would make the decision easier to repeat.
Worked Example: Calculating Elapsed Days
A hypothetical scheduling table has a start date and an end date for three tasks. The goal is to calculate elapsed days, with the end date minus the start date as the rule. Assume each end date is on or after its start date and that the spreadsheet stores both as dates in the same date system.
| Task | Start date | End date |
|---|---|---|
| A | April 2 | April 4 |
| B | April 6 | April 7 |
| C | April 10 | April 13 |
Manual approach. Someone could calculate each difference and type 2, 1, and 3 into a new column. Those results are correct, but a reader cannot tell from the typed numbers whether they came from date subtraction, a different counting convention, or another calculation. Repeating the operation for more rows requires performing and recording the calculation again.
Formula approach. If the start date is in column B and the end date is in column C, enter a formula such as =C2-B2 in the first result row and copy it down. For task A, the difference is April 4 minus April 2, which is 2 days. For task B, April 7 minus April 6 is 1 day. For task C, April 13 minus April 10 is 3 days. The resulting elapsed-day values are 2, 1, and 3.
The formula makes the calculation rule visible and uses the dates in each row as inputs. The assumption about how elapsed days are counted is also important: this rule measures the difference between the dates, not the number of calendar dates counted inclusively. A manually typed result can be retained when appropriate, but its meaning should be documented rather than inferred from the number alone.
Worked Example: Retaining Rows With a Stated Rule
A hypothetical workshop table has five records. The stated rule is to retain records whose status is “Complete” and exclude records whose status is “Practice.” Compare deleting rows by hand with representing the rule in a workflow.
| Record | Status |
|---|---|
| R1 | Complete |
| R2 | Practice |
| R3 | Complete |
| R4 | Practice |
| R5 | Complete |
Manual approach. A person might sort or filter the table, select R2 and R4, and delete them. The visible output would contain R1, R3, and R5. However, unless the action is recorded, the remaining rows do not reveal whether the rule was “retain Complete,” “remove Practice,” or simply “delete these two selected rows.” If row order changes before the selection, the same clicks might affect different records.
Recorded approach. State the rule as “retain rows where status equals Complete,” and express it in a script or a documented filter operation. Applied to the five listed records, the rule retains R1, R3, and R5 because each has status Complete; it excludes R2 and R4 because each has status Practice. The output has three records. The written criterion explains the result in a way that a list of deleted row positions does not.
A spreadsheet filter can support this process, but hiding rows is not the same as removing them. A later user may overlook the active filter and mistake a partial display for the full dataset. The workflow should make clear whether rows were merely hidden for viewing or whether the output was actually restricted according to the stated rule.
What Is Difficult to Reproduce or Audit?
Some actions leave little evidence of their intent or scope. Common examples include typing over existing values, pasting a block of values without retaining formulas, deleting selected rows, dragging a formula down an uncertain distance, and sorting only part of a table. A result can look plausible even when it is unclear which records were affected or which rule was applied.
Recorded operations are usually easier to inspect when they show both the rule and its inputs. A formula that refers to the value in the same row makes that dependency visible. A script that names the selection condition helps distinguish a rule-based selection from a one-time choice of row numbers. But a formula copied incorrectly, a script with an inappropriate condition, or a misunderstood date calculation can still produce incorrect output. Transparency supports review; it does not replace judgment.
There is also a difference between repeatability and auditability. A workflow is repeatable when its stated steps can be applied again to the intended input and produce the corresponding output. It is auditable when a reviewer can inspect what was done and assess whether the operations and decisions were appropriate. A script may be repeatable but hard to audit if it is opaque. A clear written record of a manual step may be auditable, though less convenient to repeat across many rows.
Common Mistake
Wrong reasoning: “The spreadsheet currently shows the right values, so the process is reproducible.”
Correction: Correct output values do not reveal how they were produced. A reviewer needs the operation, its scope, and the relevant inputs or decision rule. A typed value may be correct but unexplained; a formula may be visible but copied incorrectly. Reproducibility depends on a sufficiently clear record of the workflow, not only on the appearance of its output.
When deciding how to make a change, ask whether another person could apply the same rule to the same inputs without guessing. If not, make the rule more explicit or record the manual decision. This is especially important when a step affects many rows or when later results depend on it.
Check Your Understanding
Answer in terms of the operation, the record it leaves, and what a reviewer can infer.
- Why might four manually typed category labels be harder to reproduce than one formula expressing the same mapping?
- A spreadsheet contains elapsed times entered as numbers, but the date columns remain unchanged. What information would help a reviewer understand how the numbers were obtained?
- Explain why deleting selected rows by row number may be less repeatable than stating a condition for retaining records.
- Can a formula-based workflow still be difficult to audit? Give one reason.
- What is the difference between a repeatable workflow and an auditable workflow?