Using a visual language to create better spreadsheets – Bas Jansen

Bas shows a real spreadsheet that he uses in his daily work as accountant.

Just 1 spreadsheet file, with 52 worksheets, but

  • references to 70 external worksheets!
  • 26.038 cells

The process is usually as follows: A company uses an ERP system, on which the out a data warehouse for reporting. But, people grab flat tables from the data warehouse and analyze them in… Excel, often combined with data from the outside, market data for example. Then, via PowerPoint, the numbers go to the boardroom.

This is what Bas wants to improve during his PhD project that he started in October. Because the formulas are hidden ‘behind’ the data cells, which is kind of problematic for 26.000+ cells. Existing solutions for analyzing spreadsheets, like tracing formula links are not supporting users enough.

20140702_111538

So, what exactly is the optimal structure for a spreadsheet? If bas asks software engineers, they tell him that they should separate input, calculations and output. This works for some models, but a problem is that users forget about the design and start mixing things after a while. Furthermore, for different goals, like an “what if” model, users what to have the input (the parameters) right next to the results, to be able to ‘turn the knobs’.

The solution

Bas wants to create visual language, that allows users to design their spreadsheet upfront, like this:

20140702_111929The question then, of course, is what the building blocks for the spreadsheet should be. When Bas analyzed the above spreadsheet (of 26.000 cells) it turned out to contain only 7 different functions (+, /, SUM, ROUND, FLOOR, CEILING, IF), so that seems feasible to make a language with a few blocks that can make real-life accountancy spreadsheets. He aims also to take inspiration from existing design patterns, of which a few suffice to make software. Stay tuned over the next few years to see if Bas succeeds!