Parsing Excel formulas: A grammar and its application on 4 large datasets

Efthimia Aivaloglou, David Hoepelman, Felienne Hermans

Research output: Contribution to journalSpecial issueScientificpeer-review


Spreadsheets are popular end user programming tools, especially in the industrial world. This makes them interesting research targets. However, there does not exist a reliable grammar that is concise enough to facilitate formula parsing and analysis and to support research on spreadsheet codebases. This paper presents a grammar for spreadsheet formulas that can successfully parse 99.99% of more than 8 million unique formulas extracted from 4 spreadsheet datasets. Our grammar is compatible with the spreadsheet formula language, recognizes the spreadsheet formula elements that are required for supporting spreadsheets research, and produces parse trees aimed at further manipulation and analysis. Additionally, we use the grammar to analyze the characteristics of the formulas of the 4 datasets in 3 different dimensions: complexity, functionality, and data utilization. Our results show that (1) most Excel formulas are simple, however formulas with more than 50 functions or operations exist, (2) almost all formulas use data from other cells, which is often not local, and (3) a surprising number of referring mechanisms are used by less than 1% of the formulas.
Original languageEnglish
Pages (from-to)1-19
Number of pages19
JournalJournal of Software: Evolution and Process
Issue number12
Publication statusPublished - 1 Dec 2017
EventSCAM 2015, Bremen, Germany - Piscataway
Duration: 27 Sep 201528 Sep 2015

Bibliographical note

Special Issue on Source Code Analysis and Manipulation (SCAM 2015)


  • formula grammer
  • spreadsheets
  • syntax


Dive into the research topics of 'Parsing Excel formulas: A grammar and its application on 4 large datasets'. Together they form a unique fingerprint.

Cite this