Hermans Treats Excel as Functional Code, Demands Engineering Tools
GOTO Conferences
Because companies rely on Excel's functional engine to bypass slow IT departments, critical spreadsheet models require traditional software engineering practices such as automated refactoring and regression testing.
AI-assisted summary of Pure Functional Programming in Excel • Felienne Hermans • GOTO 2016 — original source. Check the original for full context.
Source published:
Key ideas
- Spreadsheets operate as Turing-complete, pure functional programs without side effects, providing instantaneous live programming feedback across millions of corporate business models. (0:11)
- Empirical corporate spreadsheet analysis reveals an average model lifecycle of 5 years with 12 distinct users, yet only one in three models includes any documentation. (0:11)
- Around 8% of real-world spreadsheets naturally contain informal assertions disguised as error-flagging formulas, paving the way for dedicated testing suites like Expector. (33:47)
- Spreadsheet formulas exhibit classic software engineering code smells such as long methods, feature envy across worksheets, and duplicate expressions that impair calculation caching. (22:50)
Detailed notes
## Background Published on YouTube by GOTO Conferences on August 9, 2016, this presentation features Assistant Professor Felienne Hermans speaking at GOTO Amsterdam 2016. According to the video description, the talk examines Excel as the world's most widely used programming language, illustrating how spreadsheets naturally execute functional programming concepts such as sorting, dynamic programming, and lambdas.
## TL;DR Felienne Hermans makes the case that spreadsheets are legitimate software code, noting that Excel pioneered pure functional, side-effect-free programming and live recalculation long before modern paradigms caught on. Demonstrating array formulas, monad-like error lifting, and a dynamic programming maze solver in native cells, she shows that spreadsheets solve the same complex computational problems as general-purpose languages. Because corporate workbooks suffer from the exact same maintenance issues as legacy codebases—including code smells and architectural coupling—she advocates applying classical software engineering to the medium. To bridge this gap, Hermans showcases tools developed at TU Delft: Bumblebee for automated formula refactoring and Expector for detecting existing assertions and mapping visual test coverage.
## Atmosphere & style The presentation is energetic, witty, and unapologetically contrarian, carried by Hermans's vibrant stage presence and crusading enthusiasm to defend spreadsheet users against developer elitism. She balances academic rigor with self-deprecating jokes, teasing the audience about testing skepticism, tossing t-shirts for correct answers, and illustrating high-level functional concepts with playful examples of money-filled mazes. The talk feels lively and vindicating, turning what many programmers dismiss as boring office software into a dynamic, visually screaming playground of computer science.
## Bulletin - [00:11](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=11s) Spreadsheets operate as Turing-complete, pure functional programs without side effects, providing instantaneous live programming feedback across millions of corporate business models. - [00:11](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=11s) Empirical corporate spreadsheet analysis reveals an average model lifecycle of 5 years with 12 distinct users, yet only one in three models includes any documentation. - [33:47](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=2027s) Around 8% of real-world spreadsheets naturally contain informal assertions disguised as error-flagging formulas, paving the way for dedicated testing suites like Expector. - [22:50](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=1370s) Spreadsheet formulas exhibit classic software engineering code smells such as long methods, feature envy across worksheets, and duplicate expressions that impair calculation caching.
## Key takeaways - [00:11](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=11s) Spreadsheets are genuine software code that implement standard domain calculations, investment advice, and complex business logic typically assigned to general-purpose programming languages. - [00:11](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=11s) Business units frequently favor end-user spreadsheet development over traditional IT vendor engagements due to lower costs, faster turnaround, and immediate adaptability. - [04:20](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=260s) Excel formulas constitute a pure functional programming environment where evaluations produce outputs without side effects or mutating external cell states. - [04:20](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=260s) Spreadsheets pioneered live programming decades before modern IDEs, recalculating downstream dependency graphs instantly upon each cell edit without explicit compilation. - [06:56](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=416s) Named ranges and implicit row intersections enable developers to write clear, uniform functional expressions without hardcoded relative cell coordinates. - [11:08](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=668s) Array formulas wrap list mapping, filtering, and reduction pipelines into single-cell evaluations without requiring cluttering helper columns. - [12:01](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=721s) Excel's automatic error propagation across arithmetic operators treats errors as algebraic data types similar to a Maybe or Either monad. - [13:54](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=834s) Dynamic programming models can be natively structured in Excel grids by tabling intermediate subproblem states to solve complex optimization problems like cash mazes. - [17:26](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=1046s) Formula-driven directional vector fields provide intuitive visual backtracking and route optimization directly inside the spreadsheet grid. - [21:07](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=1267s) Spreadsheet development mirrors historical software engineering crises, lacking standard IDE tooling to prevent architectural decay, bad smells, and orphaned code. - [22:50](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=1370s) Formula-level code smells include excessive nesting, unreadable parameter dispersion, and duplicate subexpressions that bypass Excel's cell-result calculation cache. - [26:02](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=1562s) Cross-sheet Feature Envy and cyclic worksheet dependencies cause expensive cognitive context switching and brittle inter-file architectures. - [31:38](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=1898s) The BumbleBee plugin introduces automated formula refactoring with domain-specific rewrite rules, interactive previews, and programming-by-example synthesis. - [33:47](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=2027s) Non-technical spreadsheet authors naturally author ad-hoc conditional tests (e.g., verifying sums equal 100%), which can be extracted into formalized test suites. - [36:12](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=2172s) The Expector plugin automatically detects existing assertions, generates visual dependency test coverage, and guides authors to test complex, high-risk calculation hubs. - [41:53](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=2513s) Critical enterprise sectors like banking and insurance rely fundamentally on complex Excel models, making rigorous spreadsheet software engineering tooling vital.
## Detailed walkthrough ### [00:11](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=11s) The Gospel: Spreadsheets Are Code
Felienne Hermans, Assistant Professor at Delft University of Technology, opens her presentation by challenging common developer perceptions of spreadsheets. Most software developers dismiss spreadsheets as merely "data," but Hermans insists that spreadsheets are genuine software code. In fact, she calls this belief the defining mission of her career: “Spreadsheets are code. This is the gospel of my life.” She jokingly tells the audience that if they want to leave, fall asleep, or answer emails right then, they can, because that single assertion is the core takeaway of the entire presentation.
To justify why spreadsheets are code, Hermans outlines three fundamental arguments:
1. **Spreadsheets Solve the Same Problems as General-Purpose Languages:** Hermans displays an example of an investment calculation where an end user supplies numbers and receives investment advice. This logic could easily be implemented in C, Objective-C for an iPad app, Java, or an Excel workbook. The business problem itself has nothing uniquely "spreadsheet-like" about it; it is standard business software logic. 2. **End-User Software Development Beats Traditional IT:** During her PhD research, Hermans worked alongside Dutch banks and investment institutions and asked business stakeholders why they built critical systems in Excel instead of commissioning proper corporate software from internal or external IT vendors. She candidly reveals the answer: “You know why? Because we are bad at making software. Seriously.” When business units hand over prototype spreadsheets to IT, vendors promise delivery in six months for €1,000,000. In reality, delivery drags out to nine months, costs €3,000,000, and delivers only half the requested features. Consequently, business users see themselves as faster, more effective developers than professional programmers because they can produce working prototypes on demand. 3. **Turing Completeness:** To definitively shut down claims that spreadsheets are not a real programming language, Hermans built a fully functional Turing machine using only native Excel formulas. In her implementation, each row represents an iteration step of the machine's tape, while conditional formatting highlights and tracks the machine's head moving across cells. This setup creates a clear visualization where the tape's entire execution history remains inspectable across rows rather than being overwritten in place. The project went viral, hitting the front pages of Hacker News and Reddit and crashing her personal blog for half a day.
```mermaid flowchart TD accTitle: Spreadsheets as Code Argument accDescr: Three reasons why spreadsheets qualify as genuine software code.
subgraph CoreArg["Core Proposition"] C["Spreadsheets Are Code"] end
subgraph Evidence["Supporting Arguments"] E1["Solves standard domain problems\n(finance, calculations, advice)"] E2["Empowers end users over\nslow, costly IT development"] E3["Turing complete\n(Turing machine in pure formulas)"] E4["Suffers classic software engineering woes\n(no docs, long lifespan, multi-user)"] end
E1 --> C E2 --> C E3 --> C E4 --> C ```
- An average corporate spreadsheet is used by 12 distinct individuals over a lifespan of 5 years. - Only one in three spreadsheets includes any form of user manual or documentation. - These maintenance and lifecycle issues closely mirror the exact software crisis that classical software engineering experienced 20 to 30 years prior, when software engineering degrees and standardized toolchains did not yet exist.
[04:20](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=260s) Why Excel Is the Next Language to Learn
Hermans argues that developers should not merely tolerate spreadsheets; they should actively learn Excel as their next language. She puts it bluntly: “Resistance is futile.” Spreadsheets inherently possess three characteristics that modern software movements revere:
- **True Live Programming:** Computer scientist Bret Victor popularized "live programming" by demonstrating an environment where editing source code on the right side of a split view instantly updates a rendered visual tree on the left without any manual compile phase. Hermans notes that the software industry has had this exact workflow since VisiCalc: the moment an end user edits a formula cell, every dependent computation across the workbook recalculates instantly without an explicit compilation step. - **Pure Functional Programming:** While the programming world treats functional programming as a recent resurgence, Excel has been pure functional programming from day one. A native Excel formula is inherently free of side effects; it accepts cell inputs, evaluates a calculation, and outputs a value into its host cell. It cannot mutate values in other cells or cause external side effects. - **Unrivaled Adoption:** Spreadsheets are ubiquitous across every facet of modern society, from a local neighbor organizing soccer club finances to certified accountants calculating tax returns. Developers are often the only demographic blind to this reality.
Emphasizing the sheer scale of the tool, Hermans observes: “I really like to say that spreadsheets are the most successful programming language in the history of mankind. It's such a good programming language that people don't even know they're programming.” By contrast, nobody ever accidentally writes Java; people inadvertently program in Excel every single day.
[06:56](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=416s) Functional Mapping and Named Ranges
Referring to Robert C. Martin's ("Uncle Bob") definition that functional programming is simply programming without assignment statements, Hermans demonstrates how basic mathematical pipelines map onto a spreadsheet. She begins with an example squaring a list of integers:
1. **Relative Cell Mapping:** Entering a formula such as `=A2 * A2` and dragging the fill handle (or double-clicking the corner) down the column automatically adjusts relative row references. This is functionally equivalent to a higher-order `map` operation applied across an input list. 2. **Eliminating Fragile Cell Addresses:** Professional developers often dislike the visual clutter of varying relative row references (such as `A2`, `A3`, `A7`). Hermans resolves this by selecting the input values and defining a named range called `input`. 3. **Implicit Intersection:** When a user enters `=input * input` beside the named column, Excel performs implicit intersection: the formula automatically resolves `input` to the specific cell value sharing that exact row. This makes the formula uniform across all cells in the column, mirroring clean source code without hard-coded row coordinates.
[08:44](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=524s) Conditional Sums and Array Formulas
Hermans then raises the stakes: calculating the sum of all squares that exceed 30.
A traditional spreadsheet approach splits the pipeline into multiple explicit intermediate columns: 1. Compute the squared values. 2. Add a boolean comparison column checking if the squared value is greater than 30 (evaluating to `TRUE` or `FALSE`). 3. Aggregate the values using `=SUMIF(...)`, which filters the range based on the boolean condition and sums the matching squares.
While this approach works, it wastes substantial grid real estate and requires extra helper columns for booleans and squares. To make the calculation compact and treat arrays as first-class citizens, Hermans demonstrates Excel array formulas.
Excel can treat boolean evaluations numerically (interpreting `FALSE` as `0` and `TRUE` as `1`), meaning values can be filtered through direct pairwise multiplication. Rather than relying on implicit row intersection via standard Enter, pressing `Ctrl` + `Shift` + `Enter` signals to Excel that the formula must be evaluated as an array formula, wrapping it in curly braces (`{...}`).
By applying an array formula to `=input * input`, the entire input range is multiplied by itself pairwise in memory. When placed into a single cell, it returns the first calculated element of the resulting array; if copied across a five-row span, it displays the first five elements of the resulting computed vector without needing intermediate helper cells.
[11:08](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=668s) Array Formulas and In-Cell Reduction
Hermans shows how an array formula produces a virtual second array without requiring helper cells across the spreadsheet. Instead of allocating columns of intermediate data, an author can reduce that entire array right inside a single expression. By wrapping the operation in a `SUM`, one can multiply an entire range pointwise with itself and sum the results simultaneously. Hermans adds a condition directly inside the calculation—multiplying the input array by whether each value exceeds thirty—effectively combining mapping, filtering, and reduction into one compact formula: “so in just one formula we don't need all the extra cells just in one formula I'm multiplying the range pointwise with itself and I'm summing everything”.
This concise pipeline means an analyst can delete clutter and spare worksheets entirely. The spreadsheet engine evaluates the vectors internally, demonstrating how array formulas replicate higher-order functional patterns without sacrificing processing efficiency.
[12:01](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=721s) Error Values, Lifted Operators, and the Accidental Monad
Turning to how spreadsheets handle edge cases, Hermans highlights that Excel's intuitive error system hides sophisticated type behavior under the hood. To illustrate, she populates an input column with random integers between zero and three and then attempts to divide by that range, humorously labeling the resulting calculation column the "danger zone" because zeros inevitably trigger division-by-zero errors. When `#DIV/0!` appears, Hermans asks the audience to consider the underlying data type of that column, prompting functional programmers in the room to recognize it as a `Maybe Int`.
```mermaid flowchart LR accTitle: Lifted Addition over Maybe Int accDescr: Visualizing how Excel automatically lifts arithmetic addition across integers and error values. subgraph S1["Inputs"] A["Integer (e.g. 5)"] B["Error (#DIV/0!)"] end subgraph S2["Lifted Operator (+)"] C["+ (Lifted)"] end subgraph S3["Outputs"] D["Integer Result"] E["Error (#DIV/0!)"] end A --> C --> D B --> C --> E ```
Rather than crashing or halting execution, downstream calculations seamlessly propagate these errors. An adjacent formula adding a constant to that column operates without an explicit null or exception check; the plus operator is automatically lifted to accept either an integer or an error value. Hermans points out the irony that hundreds of millions of people practice advanced functional patterns every single day without realizing it: “this is a 700 million Excel users they just use monads without any problems they don't even think about it because if we have another column that uses this maybe int as an input it just works”.
- Dividing by a column seeded with random values from `0` to `3` introduces sporadic `#DIV/0!` errors into the range. - Downstream arithmetic formulas treat cells not as bare `Int`, but as an algebraic data type equivalent to `Maybe Int` (or `Either Error Int`). - Excel automatically lifts built-in operators like `+` to work polymorphically across valid integers and error values.
[13:54](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=834s) Dynamic Programming in a Money-Filled Maze
Moving beyond basic array operations, Hermans tackles a classic algorithmic challenge: navigating a grid-based maze loaded with cash to collect the maximum possible reward. As she jokes, “I'm a poor academic so I need to get my money from programming examples that are set in a maze”. While one could write a recursive search that works backward from the destination by taking the maximum payoff of the two preceding steps, naive recursion re-evaluates the same overlapping paths repeatedly, making it computationally expensive.
```mermaid flowchart TD accTitle: Dynamic Programming Matrix Computation accDescr: How cell values in the Max sheet depend on the maximum of adjacent subproblems plus current cell cash. subgraph Grid["Max Worksheet Cell Evaluation"] Up["Cell Above (Subproblem Max)"] Left["Cell to Left (Subproblem Max)"] MaxOp{"MAX(Up, Left)"} Curr["Current Maze Value"] Result["Current Cell Max Value"]
Up --> MaxOp Left --> MaxOp MaxOp --> Result Curr --> Result end ```
To solve this efficiently, Hermans introduces dynamic programming by tabling subproblem solutions on a dedicated worksheet called `Max`. Because movement is constrained to rightward and downward steps, cells along the top and left borders have only a single arrival path. For every other interior cell, the formula simply takes the maximum of the two adjacent predecessor cells on the `Max` sheet and adds the monetary value present at the current maze square: “this is a very famous problem in computer science with the solution in C or even in Haskell it really looks very easy in a spreadsheet this is the only formula you need for the entire maze and you have your answer”.
[17:26](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=1046s) Vector Fields and Visual Backtracking
Knowing the maximum potential earnings is only half the battle; an optimal algorithm must also yield the exact route to take. Rather than manually retracing the scores or writing an external solver, Hermans crafts formulas that output arrow-like characters to build a directional vector field pointing toward whichever neighbor provided the optimal payoff. Border cells point unidirectionally up or left, while interior cells evaluate which neighbor holds the maximum value and point toward it.
Because a dense grid of arrows requires excessive mental overhead, Hermans refines the logic so that only cells belonging to the winning trajectory render an arrow, leaving non-path cells as empty strings. By inspecting whether a cell is actively pointed to from the bottom or from the right, the worksheet automatically illuminates the single optimal path across the board:
```text Formula Logic: =IF(OR(Below = "^", Right = "<"), Direction_To_Max, "") ```
Hermans emphasizes that this interactive feedback loop highlights the core strength of live programming. In typical text-based languages, inspecting intermediate stages requires print statements, debuggers, or pencil and paper. In a spreadsheet, the computation and the visual state are permanently unified: “the data is right there it's screaming at you you're like use me give me some formulas and I will tell you what I'm up to and that makes spreadsheet programming really very very easy and intuitive”. She passionately urges the audience to defend the medium's legitimacy: “if everyone say anyone close to you says it's just a spreadsheet hit them on the head with something really tell them it's untrue spreadsheets are real programming and it's super cool”.
[21:07](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=1267s) Bringing Software Engineering to Spreadsheet Code
Hermans transitions from spreadsheet demonstrations to the broader academic research she leads at Delft University of Technology, arguing that if spreadsheets are genuinely code, they deserve the discipline of software engineering. She draws a historical parallel to traditional programming twenty-five to thirty years earlier, when end-user programming was the norm and developers routinely rolled out bespoke compilers without formal maintenance practices. Over time, industry recognized the crisis of orphaned legacy systems and engineered modern development environments equipped with testing frameworks, debuggers, and code-metric analysis tools.
These modern IDE capabilities do not merely assist in typing out syntax; they structure disciplined engineering behavior: “those features are not helping me to build codes to write codes they're helping me to write responsible code to be a good developer”. In contrast, mainstream spreadsheet tools like Excel historically focused on presentation and rapid calculation while omitting the foundational scaffolding needed to safeguard, test, and analyze mission-critical business logic.
[22:50](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=1370s) Code Smells in Spreadsheet Formulas
Excel offers virtually no tool support or IDE-like assistance to guide developers toward good spreadsheet design. Users are free to write unreadable formulas like `A2 * A3` or give cells descriptive, maintainable names; either way, the interface remains completely agnostic and offers zero feedback or encouragement to structure things better. Because spreadsheets share core characteristics with traditional source code, ideas from software engineering can be brought directly to spreadsheets. During her PhD dissertation, Felienne Hermans explored adapting traditional code smells to spreadsheet formulas:
- **Long Formula (analogous to Long Method):** Length is evaluated by cognitive complexity rather than sheer character count. A formula that combines multiple operations—such as adding, multiplying, and dividing all in one go—exhibits this smell. “If a lot is going on in a formula, it's just as smelly and for the same reason as a long method is smelly,” Hermans argues. - **Many Parameters:** In traditional code, a method littered with knobs and parameters becomes brittle and unmaintainable. In Excel, a formula referencing half a dozen disparate cells scattered across a sheet causes identical readability headaches. - **Conditional Complexity:** Excel permits deeply nested `IF` statements. Early versions had an arbitrary limit of seven nesting levels, but that restriction was removed, allowing nesting depths of up to 64. When spreadsheet creators nest six to eight conditions, tracing and debugging becomes painful. - **Clones (Duplicate Code):** Just as duplicated logic hurts software maintainability when changes must be made in multiple places, subformulas repeated within the same cell or across cells cause problems. Beyond maintainability, spreadsheet clones damage execution speed. Hermans explains that Excel's internal caching model caches computed cell results, but does not cache intermediate subformulas. In a formula containing an identical expression like `EXP(0 - G36 * C36)` twice, the execution engine evaluates the exact same mathematical operation from scratch both times. Extracting duplicate expressions into separate intermediate cells cuts recalculation time in half.
[26:02](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=1562s) Worksheet-Level Architectures and Dependency Smells
```mermaid flowchart TD accTitle: Spreadsheet Architectural Smells accDescr: Three architectural patterns found in spreadsheet dependency graphs
subgraph Normal["Clean Model Flow"] direction LR I["Inputs"] --> C["Calculations"] --> R["Reports"] end
subgraph Cyclical["Tight Coupling Smell"] direction LR S1["Sheet 1"] <-->|"Bidirectional References"| S2["Sheet 2"] end
subgraph Fragile["External Folder Smell"] direction LR F1["File A.xlsx"] --> M["Master Model"] F2["File B.xlsx"] --> M end ```
Smells are not restricted to individual formulas; they also manifest at the architectural level across entire workbooks. Hermans built a dependency visualization tool that renders worksheets as blocks and inter-sheet formulas as directed arrows (e.g., an arrow connecting an `Inputs` tab to a `Calculation` tab containing `=Inputs!A1 * 12`). Visualizing real-world corporate workbooks exposed messy architectures:
- **Evolving Prototypes Left in Production:** One workbook showed quarters Q2, Q3, and Q4 cleanly structured across their monthly sheets, while Q1 (January, February, March) had a totally different dependency topology. When asked, the author confessed that Q1 had been a scratchpad prototype. Although they rebuilt the model cleanly starting in Q2, the model rolled into the next calendar year without anyone ever migrating Q1 to the new pattern. - **Cyclic Coupling (Feature Envy at Scale):** Visualizations between paired sheets revealed bidirectional arrows, where formulas on Sheet 1 pulled from Sheet 2, and formulas on Sheet 2 reached directly back into Sheet 1 across corresponding rows, indicating an unorganized data model crying out for consolidation. - **The "Wobbly" External Network:** One organization used a master model connected to external worksheets located across various disparate files. The business process consisted of colleagues emailing individual spreadsheets back and forth, saving them into a shared network folder, and opening the master workbook to pull the numbers. Hermans describes this system as “very wobbly to to say the least.”
[28:28](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=1708s) Feature Envy and the Infotron Spin-off
At the architectural level, Hermans draws a direct parallel to object-oriented class smells. Presenting two coupled classes to the audience, she asked what smell they exhibited; when an audience member called out "bad naming," she laughed, agreed it was true, and admitted she had been too lazy to rewrite the slides, before awarding a t-shirt to an attendee who correctly identified the problem as **Feature Envy**. In object-oriented programming, Feature Envy describes a method that constantly reaches into another class's fields rather than working on its own data.
In spreadsheets, Feature Envy occurs when a formula placed on one sheet consists almost entirely of references pointing to another worksheet. Hermans illustrates this with a formula on an `Income Statement` worksheet pulling building and depreciation metrics from a `Required Funds` sheet:
$$\text{Formula on Income Statement} = \frac{\text{Required Funds}!B9}{\text{Required Funds}!C9} + \dots$$
``` =Required Funds!B9 / Required Funds!C9 + Required Funds!B10 / Required Funds!C10... ```
Tracing this logic forces a user to jump back and forth between sheets to map cell coordinates to their actual labels. “We know from cognitive science that context switches are very expensive,” Hermans points out; “if you have to switch views all the time, that's super annoying.” Relocating the formula directly onto the `Required Funds` worksheet offers immediate ergonomic benefits: 1. Excel automatically highlights all local cell dependents with colored overlay boxes, making the formula visual and readable. 2. The sheet prefixes (`Required Funds!`) vanish entirely, shortening the formula and making the underlying mathematical pattern obvious.
An audience member noted that you could also refactor out common terms, such as factoring out a `/ 12` division. Hermans noted that this research led to the launch of **Infotron**, a university spin-off tool where corporate users upload workbooks to automatically detect code smells, including formulas that should be migrated to adjacent sheets.
[31:38](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=1898s) Automated Refactoring with Bumblebee
Identifying smells naturally demands a systematic way to repair them. Hermans and her team developed a spreadsheet refactoring plug-in named **Bumblebee**, bringing modern IDE refactoring capabilities directly into Excel:
- **Formula Transformations:** Bumblebee detects opportunities to streamline formulas, such as replacing a manual `=SUM(range) / COUNT(range)` with `=AVERAGE(range)`. While many users know the `AVERAGE` function, other transformations provide larger benefits, such as converting nested formulas into a `=SUMIF()`—a function many spreadsheet users either do not know or do not know how to construct properly. - **Interactive Previews:** Just like a code refactoring tool in an IDE, Bumblebee shows a before-and-after preview so users can inspect the transformed formula before applying it to the worksheet. - **Domain-Specific Transformation Language:** Bumblebee includes a mini-language allowing users to write custom rewrite rules, defining how pattern matches map to target syntax:
``` SUM(range) / COUNT(range) => AVERAGE(range) ```
Looking ahead, Hermans wants to test whether corporate power users—those capable of writing array formulas or writing VBA—can author their own custom Bumblebee refactorings. She also highlights ongoing research into **programming by example**, where the tool watches a user manually update several formulas, deduces the underlying refactoring pattern on the fly, and offers to automate the transformation across the rest of the spreadsheet.
[33:47](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=2027s) Refactoring Needs Testing: Discovering In-Sheet Assertions
Felienne Hermans notes that the BumbleBee refactoring tool can detect repetitive transformations across a spreadsheet and automate them, inviting attendees to try it or pass it to business users via `felienne.com/bumblebee`. Pausing as an audience member takes a photo of the slide, she segues naturally into the next logical software engineering concept: testing. As she puts it:
> “You're not going to refactor your spreadsheet without the security of some tests.”
When someone in the audience makes a skeptical noise, Hermans jokes about them not believing in testing, but admits that her own initial assumptions were flawed. She had suffered from “typical Ivory Tower thinking,” worrying about how to ever convince spreadsheet users or their management to test when it is already notoriously hard to persuade professional software developers and their bosses to write tests.
```mermaid flowchart TD accTitle: Evolution of Spreadsheet Testing accDescr: Flowchart contrasting initial academic assumptions with empirical reality found in spreadsheets.
subgraph Assumption["Ivory Tower Assumption"] A["Developers struggle to test"] --> B["Spreadsheet users won't test"] end
subgraph Reality["Empirical Finding"] C["Lack of formal framework"] --> D["Users write assertion formulas: IF(sum=100%, 100, 'ERROR')"] D --> E["Found in 8% of analyzed spreadsheets"] end
Assumption -.->|"Debunked by data"| Reality ```
However, examining empirical spreadsheet data revealed the opposite: end users were already testing instinctively. Lacking dedicated testing frameworks, users repurposed standard formulas as assertions. A classic pattern Hermans identified checked whether a set of percentage values summed to 1: ```excel =IF(SUM(A1:A5)=1, 100, "ERROR") ``` This formula encapsulates business domain knowledge: the items must add up to 100%, and if they don't, it displays an error. Analyzing an open dataset, Hermans found that roughly 8% of real-world spreadsheets already contained these ad-hoc test formulas. People were even returning graphical indicators like smiley faces or sad faces depending on formula results. Because users already had these rudimentary checks in place, Hermans realized researchers did not need to invent testing behaviors from scratch—they just needed to harness what users were already doing.
- BumbleBee is available for free download at `felienne.com/bumblebee`. - Approximately 8% of spreadsheets analyzed in the open corpus contained explicit test/assertion formulas. - Common assertion outputs in formula results included `"OK"`, `"ERROR"`, `"YES"`, `"NO"`, and even smiley or sad face emoticons.
---
[36:12](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=2172s) Expector: Test Extraction, Coverage Visualization, and Guided Assertions
To turn these informal practices into structured engineering workflows, Hermans and her team developed a plugin named Expector. The tool scans spreadsheets using heuristic pattern matching on formula logic and known keywords (such as `"error"`, `"yes"`, or smiling/frowning faces) to automatically discover existing test-like formulas. Expector then translates those formulas into human-readable, “natural-ish language” summaries (for instance, showing that a group of cells is expected to sum to 100%) so users can inspect them and add them to an official test suite with a single click.
```mermaid flowchart LR accTitle: Expector Testing Workflow accDescr: Workflow showing automated test discovery, coverage analysis, and targeted test generation in Expector.
subgraph Discovery["1. Discovery"] A["Scan Spreadsheet"] --> B["Detect Heuristics & Keywords"] B --> C["Parse into Natural Language Assertions"] end
subgraph Suite["2. Suite & Coverage"] C --> D["Add to Test Suite"] D --> E["Mark Dependents Yellow (Coverage)"] end
subgraph Growth["3. Guided Extension"] E --> F["Suggest 1 Target Formula"] F --> G["Add Bounds, Types, or Custom Formulas"] G --> H["Celebrate Increased Coverage%"] end ```
Expector also brings the concept of test coverage to spreadsheet grids. When users ask the tool to show what is covered, Expector highlights the assertion formulas and marks all of their precedent dependencies in yellow. While Hermans acknowledges this is not formal statement or branch coverage—and is not completely foolproof against indirect errors—it provides non-technical users with an immediate, visual intuition of which data paths are safeguarded.
To help users expand their suites without being overwhelmed, Expector offers targeted, bite-sized recommendations when someone has just a spare five minutes before a meeting:
1. **Complex formulas**: Intricate calculations that carry high cognitive load and risk. 2. **High-value formulas**: Cells calculating critical business metrics, large monetary amounts, or order volumes. 3. **High-precedent formulas**: Core calculation hubs referenced by many downstream dependent cells.
Users can attach assertions defining expected types (e.g., number), set upper and lower numerical bounds, or write arbitrary validation formulas in newer versions of the tool. Whenever a user adds a test, Expector encourages them with explicit positive reinforcement—congratulating them for boosting coverage from 6% to 8%—and maintains a dashboard of passing and failing tests. Expector is distributed free for research purposes at `felienne.com/expector`.
---
[39:50](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=2390s) The 30-Second Summary: Spreadsheets Are Real Code
Hermans wraps up the main presentation by offering a rapid, one-minute synthesis for anyone who arrived late or felt drowsy at the end of the conference day. She emphasizes the foundational thesis of her talk:
> “Spreadsheets are code: it's the most important message, remember and tell all your friends and tweet about it and put it on a t-shirt.”
Her core argument rests on three undeniable parallels between spreadsheets and traditional software: - **Domain equivalence**: Spreadsheets are deployed to solve the exact same classes of complex business logic as traditional programs. - **Computational equivalence**: Formula languages are Turing complete and support sophisticated functional programming concepts, from live evaluation to dynamic programming algorithms (such as solving mazes). - **Engineering problems**: Spreadsheets suffer from identical software engineering ailments—code smells, maintainability bottlenecks, lack of regression tests, and refactoring risks.
Far from being an inferior toy, Hermans argues that Excel embodies the very qualities contemporary language designers strive to achieve in 2016: it is purely functional, reactive, interactive/live, and accessible enough to be used by hundreds of millions of everyday workers worldwide. Because spreadsheets represent genuine source code, applying software engineering techniques—such as the commercial code smell detector *PerfectXL*, the *BumbleBee* refactoring tool, and the *Expector* testing suite—is essential. She directs attendees to `felienne.com`, her Delft research team at `przylab.org`, and invites further discussion during the evening conference party or the Q&A.
---
[41:53](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=2513s) Q&A: Enterprise Adoption, User Thresholds, and Microsoft Research
During the closing questions, an audience member asks about the actual reach of these tools and whether average office workers really interact with spreadsheets at the level of complexity Hermans showcased. Hermans clarifies that while plenty of people only make basic tables, critical industries rely entirely on heavy spreadsheet programming:
> “There are people that just use it for simple lists, but there are companies and organizations that really depend on Excel for their core business—so insurance companies, banks that really make models that the company depends on.”
Another participant asks how to determine the threshold where users should adopt dedicated developer tooling instead of treating Excel like a scratchpad. Hermans compares this dilemma to standard software development: deciding when an Excel user needs refactoring and testing plugins is fundamentally the same open question as deciding when a programmer should migrate from Notepad++ to a full-featured IDE. It ultimately hinges on the criticality of the model and what the organization is trying to achieve.
Finally, Hermans mentions that in two weeks she will be heading to Microsoft for a three-month research visit to work directly on improving Excel. She notes with a laugh that she is not certain whether they will grant her full access to proprietary telemetry data, but points out that Microsoft had been one of the original sponsors of her PhD research, confirming that the Excel engineering team is well aware of her research in spreadsheet software engineering.
## Notable quotes > [04:20](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=260s) I really like to say that spreadsheets are the most successful programming language in the history of mankind. It's such a good programming language that people don't even know they're programming. — Felienne Hermans
> [00:11](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=11s) Spreadsheets are code. This is the gospel of my life. — Felienne Hermans
> [22:50](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=1370s) If a lot is going on in a formula, it's just as smelly and for the same reason as a long method is smelly — Felienne Hermans
> [33:47](https://www.youtube.com/watch?v=0yKf8TrLUOw&t=2027s) You're not going to refactor your spreadsheet without the security of some tests. — Felienne Hermans
## Mentioned Felienne Hermans, Delft University of Technology, GOTO Conferences, Microsoft, Excel, Bret Victor, VisiCalc, Robert C. Martin, Haskell, Infotron, Bumblebee, Expector, PerfectXL
## Video description This presentation was recorded at GOTO Amsterdam 2016 http://gotoams.nl
Felienne Hermans - Assistant Professor at Delft University of Technology @felienne
ABSTRACT This talk is about the world's most popular programming language...Java? Python? COBOL? No... Excel!
Excel somehow hits the sweet spot between being powerful enough to run entire domains like finance and insurance, but also easy enough to attract a huge audience. Why is that?
In this talk we'll explore some of the mechanisms that make spreadsheets so powerful, while implementing a few interesting mathematical problems in spreadsheets.
While doing that, you might learn some (functional) programming concepts too, as we'll talk about sorting, dynamic programming and lambdas. [...]
Download slides and read the full abstract here: http://gotocon.com/amsterdam-2016/presentation/Pure%20Functional%20Programming%20in%20Excel
RECOMMENDED BOOKS Felienne Hermans • The Programmer's Brain • https://amzn.to/3mENbN0 Daniel Kahneman • Thinking, Fast and Slow • https://amzn.to/2XmJEtf
https://bsky.app/profile/gotocon.com https://twitter.com/GOTOcon https://www.linkedin.com/company/goto- https://www.instagram.com/goto_con https://www.facebook.com/GOTOConferences #Excel #FunctionalProgramming #ExcelProgramming #FP #Programming #ProgrammingExcel #SpreadsheetProgramming #FelienneHermans
CHANNEL MEMBERSHIP BONUS Join this channel to get early access to videos & other perks: https://www.youtube.com/channel/UCs_tLP3AiwYKwdUHpltJPuA/join
Looking for a unique learning experience? Attend the next GOTO conference near you! Get your ticket at https://gotopia.tech Sign up for updates and specials at https://gotopia.tech/newsletter
SUBSCRIBE TO OUR CHANNEL - new videos posted almost daily. https://www.youtube.com/user/GotoConferences/?sub_confirmation=1
[Source](https://www.youtube.com/watch?v=0yKf8TrLUOw)