Automated refactoring of nested-IF formulae in spreadsheets

Jie Zhang, Shi Han, Dan Hao, Lu Zhang, Dongmei Zhang

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:

  1. 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.
  2. Redundancy Removal: The algorithm identifies and prunes "dead branches." For example, if a formula checks X > 10 inside a branch where it is already established that X > 20, the inner check is redundant and can be removed.
  3. 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:
  4. 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).

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 IFS function 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 CHOOSE or MATCH exist.
  • Readability vs. Specificity: In one interesting qualitative finding, a user preferred a nested-IF over a MAX function because the nested-IF explicitly showed what happens when values are equal, whereas MAX hides 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 ClosedXML library), 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

Abstract

Spreadsheets are the most popular end-user programming software, where formulae act like programs and also have smells. One well recognized smell is the use of nested-IF expressions, which have low readability and high cognitive cost for users, and are error-prone during reuse or maintenance. End users usually lack essential programming language knowledge to tackle or even realize this problem, yet no automatic approaches are currently available. This paper proposes the first exploration of the nest-if usage status against two large-scale spreadsheet corpora containing over 80,000 industry-level spreadsheets. It turns out the use of nested-IF expressions are surprisingly common among end users. We then present an approach to tackling this problem through automatic formula refactoring. The general idea of the automatic approach is two-fold. First, we detect and remove logic redundancy based on the AST of a formula. Second, we identify higher-level semantics that have been represented with fragmented and scattered syntax, and reassemble the syntax using concise built-in functions. A comprehensive evaluation with over 28 million nested-IF formulae reveals that the approach is able to relieve the smell of over 90% of nested-IF formulae.
BibTeX
@inproceedings{zhang2018automated,
  author = {Jie Zhang and Shi Han and Dan Hao and Lu Zhang and Dongmei Zhang},
  title = {{Automated refactoring of nested-IF formulae in spreadsheets}},
  booktitle = {ESEC/SIGSOFT FSE},
  year = {2018},
  doi = {10.1145/3236024.3275532},
}

Loading related content...

Figure