Source-linked AI summary
Kale: A Transformation-Safe Spreadsheet System
Michael Coblenz, Jacob Yim, Ajinkya Bokade, Mounika Padala, Julia Epshtein, Priyanka Bhatia, Piyush Chauhan, Simran Gill, Aniket Gupta, Grishma Gurbani, Vaibhav Khetan, Arushi Munjal, Jeffery Tung, Joanna Yang
TL;DR
Spreadsheet structural changes can make formulas refer to unintended data, creating a significant risk of latent errors. The paper introduces Kale, a prototype that restricts reference forms and redefines reference semantics to preserve author intent, then evaluates it through user and corpus studies. The studies provide evidence that safer reference behavior can reduce spreadsheet errors, while Kale’s restrictions and unevaluated features leave practical usability questions open.
Problem
Structural changes can destabilize spreadsheet references and introduce bugs, yet traditional absolute-versus-relative semantics do not address this risk.
Method
Kale restricts references to individual cells, whole rows, and whole columns, and uses redesigned absolute and relative semantics to encode behavior under structural transformations.
Results
47% of responses were correct for questions about reference transformations on row insertion, while the paper reports that Kale users more often completed error-prone restructuring tasks successfully than Google Sheets users.
Takeaways & Limitations
Safer spreadsheet reference behavior may significantly reduce errors arising from structural changes.
Takeaways & Limitations
Kale’s restrictions may prevent solving some common spreadsheet problems, and queries and cross-table references were not evaluated.
Abstract
from arXiv · showhide
Spreadsheet formulas can refer to rectangular ranges of arbitrary size. When a user changes the structure of a referenced table, the spreadsheet system updates the references to refer to a new range. Unfortunately, this new range may differ from the user's expectations, introducing bugs in spreadsheets. We describe a user study showing that standard reference semantics are error-prone, resulting in significant risk to users. We introduce Kale, a prototype system that eliminates the risk of inserting these kinds of bugs by restricting the kinds of references that can be expressed. We show that Kale can be used effectively by users to complete tasks that are error-prone in traditional spreadsheet systems. Finally, we describe a corpus study that evaluates the extent to which the reference restrictions in Kale might have implications on users.
1 Introduction
Structural changes can destabilize spreadsheet references, introducing bugs when formulas no longer refer to the data their authors intended. Kale addresses this risk by restricting reference forms and encoding how references should behave under transformations.
- Kale: Kale restricts references to individual cells, whole rows, and whole columns rather than arbitrary rectangular ranges, avoiding reference instability.Its design re-envisions spreadsheet reference syntax and semantics to improve safety.
- Kale: Kale aims to preserve referenced data through inserted, removed, or permuted rows and columns, while moved formulas retain the same data or intended relative offset.This preservation property is stated as unavailable in traditional spreadsheets.
- Problem: Structural changes can introduce bugs by breaking assumptions about which data formulas refer to.Reference instability arises because formulas may follow cell data in some transformations but preserve cell geometry in others.
- Problem: Traditional spreadsheets expose several transformation risks, including ambiguous adjacent insertions, sorting that permutes referenced data, and moved cells that change referenced ranges.Moving a row can expand a range unexpectedly, while sorting can break formulas intended to follow a particular row.
- Problem: Traditional absolute and relative references address copy-paste and drag-fill behavior but do not resolve risks caused by restructuring referenced tables.Kale redefines these terms to capture intended behavior when tables are structurally modified.
- Evaluation: The paper evaluates Kale through a comparative user study and a corpus study of 60 randomly selected spreadsheets.The user study examines whether participants introduce, notice, and fix transformation-induced bugs; the corpus study examines representability under Kale’s restrictions.
2 The Kale Spreadsheet System
Kale improves spreadsheet safety by restricting references to cells, whole rows, and whole columns, while using absolute or relative semantics to preserve author intent through structural changes.
- Kale prevents reference-instability errors by disallowing arbitrary rectangular ranges and allowing references to individual cells, whole columns, or whole rows.This restriction is designed to preserve referenced data through structural transformations.
- Kale tables use a header row for column names, with row indices beginning at 0 below the header.The header row itself cannot be referenced.
- Single-cell references can be absolute or relative, while column and row references aggregate all non-header cells in a column or all cells in a row.Examples include SUM(Col) for a column and SUM([0]) for row 0.
- Absolute and relative references encode whether moved rows should be followed or resolved by relative position after insertions, deletions, sorting, or drag-and-drop.Absolute references continue to refer to the same moved rows; relative references use the specified offset.
- Kale supports arbitrary expressions for relative offsets, but not computed absolute indices, which must resolve to row IDs at parse time.For example, Col[+1+1] refers to the column two rows below the formula.
- Kale supports name-based references for individual cells, and named cells remain visible and move with their contents during cut/paste and sorting.This differs from Excel and Sheets, where names are visible only when the cell is selected.
3 Preliminary Study of User Expectations
A preliminary study found that participants generally understood absolute references but struggled to predict how traditional spreadsheet references change when rows are inserted.
- Five graduate students completed a survey predicting the results of several traditional spreadsheet operations.The study was IRB-approved and used multiple-choice scenarios.
- One participant answered all four absolute-reference questions correctly, three answered 3/4 correctly, and one answered 2/4 correctly.
- No participant answered all row-insertion questions correctly; two answered 2/3 correctly and three answered 1/3 correctly.
- Only 7 of 15 responses about reference transformations on row insertion were correct, a success rate of about 47%.
- The study suggests users may write formulas without considering structural updates and may lack a convenient way to assess whether table changes broke referencing formulas.
4 User Study
A 25-participant study compared Google Sheets and Kale on spreadsheet tasks designed to expose structural-reference risks, while measuring risk-related errors, correctness, and completion time. Kale reduced several risks without significant overall sacrifices in correctness or speed, though important exceptions remained.
- 4.1 Methods: 25 participants completed spreadsheet tasks in either Google Sheets or Kale, with 12 assigned to Sheets and 13 to Kale.The between-subjects study examined risk-related errors, overall correctness, and task completion speed.
- 4.4 Results: Kale users were significantly faster on Task 3 (p = .049), while timing differences were not significant for Tasks 1, 2, or 4; correctness and speed were not significantly sacrificed overall.The timing results were based on recorded task start and end times.
- 4.4 Results: R1 errors significantly decreased in the first and third instances, with χ2(1, N = 25) = 6.67, p = .0003 and χ2(1, N = 23) = 10.22, p = .0014, respectively.The second R1 instance showed no significant relationship: χ2(1, N = 25) = 0.01, p = .93.
- 4.4 Results: χ2(1, N = 21) = 10.69, p = .0011 for R2, and χ2(1, N = 22) = 17.99, p < .0001 for R4, indicating significantly fewer Kale errors.For R2, all but one Kale participant avoided the risk; for R4, all 13 Kale participants avoided it.
- 4.5 Discussion: 50-83% for R1, 56% for R2, 30% for R3, and 78% for R4 were the Sheets risk-related error rates, versus a prior average cell-error estimate of 3.9%.No participants encountered R5, so its danger or the task’s effectiveness in assessing it remained uncertain.
- 4.5 Discussion: Kale completely eliminated risk-related errors in four of seven risk instances where Sheets had rates over 50%, and significantly reduced R1, R2, and R4.The study’s risk comparison found a broad reduction across several structural-reference hazards.
- 4.5 Discussion: Kale did not eliminate one partial-range variant of R1, and its sorting behavior introduced new errors for two participants in a specific T3 situation.These issues partly explain failures to significantly prevent R3 in T3 and the first R1 instance in T2.
5 Corpus Study
The corpus study examined whether Kale’s restricted references could represent existing spreadsheets and found all 60 sampled spreadsheets translatable, while identifying scope limitations.
- Tasks requiring only part of a row or column may need multiple references or restructuring because Kale lacks arbitrary rectangular range references.
- 1,726 unique spreadsheets were categorized by formula types: 39% used range references, 3% cross-sheet references, and 58% only single-cell references.
- The study sampled 50 spreadsheets with range references and 10 with only single-cell references, then manually converted them to Kale.
- All 60 sampled spreadsheets were converted to Kale, requiring 4–44 minutes, with a mean of 18 minutes and median of 15 minutes.Spreadsheets with few range references were trivially easy to convert, while those with many range references were sometimes challenging.
- The conversion evaluation did not include documents using cross-table references, which comprised 3% of the corpus.The authors conclude that Kale’s reference restrictions are unlikely to present significant barriers to real-world success.
6 Related Work
Related work has studied spreadsheet errors, their causes, detection, prevention, and structural safeguards. Kale extends this line of work by addressing errors arising from spreadsheet reference behavior during structural changes.
- Prior studies commonly examined incorrect formulas, spreadsheet audits, human cognitive limitations, domain interpretation, algorithmic expression, and implementation mistakes.
- Field studies identified inaccurate data, inherited reuse errors, structural and omission errors, and incorrect function use among spreadsheet problems.
- Existing tools detect or reduce errors through dependency visualization, static analysis of rectangular layouts, spreadsheet refactoring, cell protection, and re-keying data.
- Researchers have proposed formal data structures and model-driven development to balance spreadsheet flexibility with stronger safety and correctness properties.
7 Limitations and Future Work
The paper identifies unresolved questions about Kale’s real-world use, including the frequency of structural-change risks, effects on authors and viewers, and support for queries and cross-table references.
- The study’s tasks were designed to produce structural-change risks, so future work must measure how often such situations occur during real-world spreadsheet authoring.
- The corpus study showed that existing spreadsheets can be replicated in Kale, but the effects of its structural constraints on authors and viewers remain unclear.
- The impact of splitting spreadsheets into separate, explicit tables on users is unclear and warrants further investigation.
- Kale’s immature prototype did not evaluate queries, which would allow formulas to reference subsets of ranges.
- Cross-table references were also not evaluated, although they may be important for representing spreadsheets with more diverse structures.
8 Conclusion
The paper presents evidence that structural spreadsheet changes may pose a common and serious risk, and introduces Kale as an approach to eliminate these risks through safer reference semantics.
- The paper reports evidence that structural changes in spreadsheets may present a common and serious risk to users.
- Kale restricts reference forms and redefines absolute and relative distinctions so users can specify how formulas update during structural changes.
- Study evidence suggests that safer spreadsheet reference behavior may significantly reduce the incidence of spreadsheet errors.
9 Declarations
The paper reports ethical approval, informed consent, contributor roles, and public availability of the user-study materials and Kale system.
- All studies received approval from the institution’s Institutional Review Board.
- Study participants provided informed consent for all collected data.
- Contributors divided responsibilities across system design, implementation, user-study design, paper writing, and corpus-study work.
- The user-study data, tasks, participant materials, and open-source Kale system are publicly available through replication-package and project repositories.