TL;DR
This paper introduces an automated, AST-based approach to refactor "nested-IF" formulas in spreadsheets—a notorious "code smell" that makes spreadsheets hard to read and maintain. By identifying logical redundancies and reassembling fragmented logic into high-level functions like IFS or VLOOKUP, the authors successfully simplified over 99% of formulas in a massive real-world dataset, significantly reducing complexity for end-users.
What Problem Does This Paper Address?
Spreadsheets are the world's most popular programming environment, but most users aren't trained software engineers. This leads to "smelly" code, specifically nested-IF expressions (e.g., IF(cond1, IF(cond2, A, B), C)). These structures are notoriously difficult to read, debug, and maintain.
In extreme cases, the authors found formulas with a nesting depth of 48! While professional developers might refactor such logic into cleaner structures, spreadsheet users often lack the knowledge of advanced functions (like INDEX/MATCH or IFS) to do so. Previous research offered only limited, manual, or low-coverage solutions. This paper seeks to provide a fully automated, high-coverage tool to clean up this "formula rot."
Methods
The authors developed a three-step automated refactoring framework:
- AST Generation: Formulas are parsed into Abstract Syntax Trees (ASTs). This structural representation makes it easy to calculate "if-depth" and navigate the logic branches.
- Redundancy Removal: The algorithm identifies and prunes "dead branches." For example, if a formula checks
X > 10inside a branch where it is already established thatX > 20, the inner check is redundant and can be removed. - Syntax Reassembling: This is the core innovation. The tool looks for specific patterns in the AST that correspond to higher-level Excel functions. They identified 9 key patterns mapping to:
- Logical aggregators:
AND,OR- Lookup/Selection:
CHOOSE,MATCH,VLOOKUP/HLOOKUP - Math-based selection:
MAX,MIN - Flat conditionals:
IFS(a newer Excel function that handles multiple conditions without nesting).
- Lookup/Selection:
The process is iterative, repeatedly simplifying the formula until no more patterns are matched.
Datasets
The study utilizes a massive, high-quality corpus:
- The IT Company Corpus: Over 68,000 real-world Excel files containing 149,170 worksheets, collected from a leading IT company.
- Scale: This dataset includes over 27 million nested-IF formulas, making it significantly larger and more diverse than standard academic benchmarks like the EUSES or Enron corpora.
- Unique Set: To avoid bias from "drag-and-fill" duplicates, they clustered formulas by R1C1 notation, resulting in a "Unique Set" of ~19.2 million formulas for analysis.
Key Insights and Ideas
- The "IFS" Silver Bullet: The
IFSfunction is the most powerful tool for refactoring, handling nearly 66% of all cases. It flattens hierarchical logic into a simple list of condition-value pairs. - Logic Redundancy is Common: About 6% of formulas contain logically redundant conditions. This suggests that as formulas grow, users lose track of the logic they've already written.
- The Knowledge Gap: A survey of 49 users revealed that while 92% preferred the refactored formulas, only 21% actually knew how to perform the refactoring manually. Users often simply don't know that functions like
CHOOSEorMATCHexist. - Readability vs. Specificity: In one interesting qualitative finding, a user preferred a nested-IF over a
MAXfunction because the nested-IF explicitly showed what happens when values are equal, whereasMAXhides that logic.
Key Results
- 99.8% Coverage: The automated approach was able to handle almost every nested-IF formula in the 27-million-formula dataset.
- Dramatic Simplification: For over 50% of the formulas, the nesting depth was reduced by more than half. Most were reduced to a depth of 0 or 1.
- 100% Correctness: Through both manual inspection and automated value comparison (using the
ClosedXMLlibrary), the authors verified that the refactored formulas remained functionally identical to the originals. - High User Approval: In the user study, 98.8% of participants agreed that such an automated tool is necessary and helpful.
Limitations and Future Work
- Complex Embedding: The tool struggles when IF functions are deeply wrapped inside other non-IF functions (e.g.,
SUM(IF(...), IF(...))), as the AST becomes too complex for the current pattern matcher. - Pattern Completeness: The current set of 9 patterns is not exhaustive. There are likely other specialized Excel functions that could further simplify formulas.
- Proactive Refactoring: The current tool is "reactive" (fixing existing smells). The authors suggest a "proactive" version—a plug-in that suggests better functions while the user is typing a complex nested-IF.
- Machine Learning: The authors propose using ML to infer the intent of a formula from its syntax to suggest even more radical simplifications.
Summary updated