Automated Refactoring of Nested-IF Formulae in Spreadsheets

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

Abstract

Spreadsheets are the most popular end-user programming software, where formulae act like programs and also have smells. One well recognized common smell of spreadsheet formulae is nest-IF expressions, which have low readability and high cognitive cost for users, and are error-prone during reuse or maintenance. However, end users usually lack essential programming language knowledge and skills to tackle or even realize the problem. This paper proposes an AST-based automated approach to systematically refactoring nest-IF formulae. The general idea is two-fold. First, we detect and remove logic redundancy on the AST. Second, we identify higher-level semantics that have been fragmented and scattered, and reassemble the syntax using concise built-in functions. A comprehensive evaluation has been conducted against a real-world spreadsheet corpus of over 68,000 spreadsheets with 27 million nest-IF formulae. The results reveal that our approach is able to relieve the smell of over 99% of nest-IF formulae. Over 50% of the refactorings have reduced nesting levels of the nest-IFs by more than a half. In addition, a survey involving 49 participants indicates that for most cases the participants prefer the refactored formulae, and agree on that such automated refactoring approach is necessary and helpful.

Introduction

Spreadsheets are the world's most popular programming environment, yet most users are not trained software engineers. This often results in "smelly" code, particularly 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 identified formulae with nesting depths as high as 48. While professional developers might refactor such logic into cleaner structures, spreadsheet users often lack knowledge of advanced functions like INDEX/MATCH or IFS. This paper introduces a fully automated, high-coverage tool to address this "formula rot."

Methodology

The proposed automated refactoring framework consists of three primary steps:

1. AST Generation

Formulae are parsed into Abstract Syntax Trees (ASTs). This structural representation allows for the precise calculation of "if-depth" and navigation of logic branches.

2. Redundancy Removal

The algorithm identifies and prunes "dead branches." For instance, if a formula checks X > 10 within a branch where X > 20 is already established, the inner check is redundant. The authors found that approximately 6% of formulae in their dataset contained such logical redundancies, suggesting that as formulae grow, users lose track of established logic.

Figure 2: The redundancy removal process, showing how an AST is pruned and simplified.

3. Syntax Reassembling

This core innovation involves identifying patterns in the AST that correspond to higher-level Excel functions. The tool maps these patterns to nine key categories, including logical aggregators (AND, OR), lookup/selection functions (CHOOSE, MATCH, VLOOKUP/HLOOKUP), math-based selection (MAX, MIN), and flat conditionals (IFS).

Figure 3: AST patterns for various Excel functions used in syntax reassembling.

The process is iterative, repeatedly simplifying the formula until no further patterns can be matched.

Evaluation and Results

The study utilized a massive corpus from a leading IT company, comprising over 68,000 real-world Excel files and 27 million nested-IF formulae. To ensure data quality, formulae were clustered by R1C1 notation to create a "Unique Set" of ~19.2 million formulae for analysis.

Key Performance Metrics

  • 99.8% Coverage: The approach successfully handled nearly every nested-IF formula in the dataset.
  • Significant Simplification: For over 50% of the formulae, nesting depth was reduced by more than half, with most reaching a depth of 0 or 1.
  • 100% Correctness: Functional identity was verified through manual inspection and automated value comparison using the ClosedXML library.

User Insights

A survey of 49 participants highlighted a significant "knowledge gap." While 92% preferred the refactored formulae, only 21% knew how to perform such refactoring manually. Many users were unaware of functions like CHOOSE or MATCH. Interestingly, the IFS function emerged as a "silver bullet," handling nearly 66% of all cases by flattening hierarchical logic into simple condition-value pairs.

Conclusion and Future Work

The proposed AST-based approach effectively eliminates the "nested-IF" smell in spreadsheets with high accuracy and coverage. However, challenges remain when IF functions are deeply embedded within other complex functions. Future work will explore expanding the pattern set, developing "proactive" refactoring plugins that suggest improvements during formula entry, and utilizing machine learning to better infer user intent for radical simplification.

Summary updated

Abstract

Spreadsheets are the most popular end-user programming software, where formulae act like programs and also have smells. One well recognized common smell of spreadsheet formulae is nest-IF expressions, which have low readability and high cognitive cost for users, and are error-prone during reuse or maintenance. However, end users usually lack essential programming language knowledge and skills to tackle or even realize the problem. The previous research work has made very initial attempts in this aspect, while no effective and automated approach is currently available. This paper firstly proposes an AST-based automated approach to systematically refactoring nest-IF formulae. The general idea is two-fold. First, we detect and remove logic redundancy on the AST. Second, we identify higher-level semantics that have been fragmented and scattered, and reassemble the syntax using concise built-in functions. A comprehensive evaluation has been conducted against a real-world spreadsheet corpus, which is collected in a leading IT company for research purpose. The results with over 68,000 spreadsheets with 27 million nest-IF formulae reveal that our approach is able to relieve the smell of over 99\% of nest-IF formulae. Over 50% of the refactorings have reduced nesting levels of the nest-IFs by more than a half. In addition, a survey involving 49 participants indicates that for most cases the participants prefer the refactored formulae, and agree on that such automated refactoring approach is necessary and helpful.
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