At the start of this week I went back to work full time. I’ve recently been on a 9-month half-sabbatical (day-job 2 days; own project 3 days) to develop a software idea of my own. I’ve been working on a new, better, spreadsheet application.

This is the story of how I got on.

So I’d better explain…

What I was actually trying to do.

I’m not a very heavy spreadsheet user in my day-to-day work, but I use Microsoft Excel at work from time to time. Anyway, it right royally annoys the tits off me every time I use it. Some specific frustrations:

• It’s difficult to keep visual styles consistent. When I copy and paste cells around, I find I have to fix up borders and background colours. In fact, it seems I spend half my time fixing up the formatting.
• Formulae are error-prone. When making changes to a formula, it’s easy to make a change in one place, and forget to apply it to all the other places the formula is used.
• Formulae are hard to understand and the ‘language’ they’re written in is horrible. For example, what does “F$3+E483*7” mean?

There are lots of minor annoyances too—why, for example, does Microsoft Excel disallow you from opening two files with the same name? It’s 2010 fer crying out loud!—but it’s the big annoyances that prompted me to start to think: In an ideal world, how should spreadsheet applications work?

I’d been chewing over some of these ideas, on and off, for the best part of a decade.

The problem

My fundamental problem with Excel (and those of its ilk) is that all it gives you to work with is a big, flat grid of cells. The user—you—is obliged to impose some kind of meaning upon these cells. The ‘meaning’ is implicit in how the cells are used and how they’re formatted. Therefore it is incumbent on the user to apply formatting to each cell individually, and to copy formulae around all over the place.

I remember reading a factoid once, along the lines of, 30% of all business spreadsheets contain errors. (Perhaps the percentage is higher.) The way spreadsheet applications work nowadays it’s easy to create spreadsheets which contains faults. And which look hideously ugly.

The spreadsheet application does not know the shape of your data; it does not distinguish headers from cell data; it doesn’t know that this cell’s value is litres, while this one is miles-per-gallon.

When you want to add a new column to your data table you have to copy all the formatting and cells yourself, because the spreadsheet application doesn’t know that you intended to shade every other column yellow—or data rows should have a white background, but total rows should be coloured blue. If you try to add x litres to y miles-per-gallon, it will happily let you, with nary a warning. If your sum row at the bottom of your table doesn’t include the first and last values in each column, the application cannot flag it up as a mistake… because it might not be a mistake.

The idea

So my idea was to take an entirely different approach to spreadsheets. Instead of presenting the user with an infinite grid of anonymous cells, onto which the user projects meaning, the application would instead allow the user to explicitly construct tables, naming rows and columns, and denoting certain parts of each table as ‘repeated elements’ (like a column for each week) and others as ‘distinct’.

New columns and rows in such tables would inherit the correct formatting automatically, and a formula which says ‘sum(all of the weeks)’ would automatically stay up to date, no matter how many new columns or rows are added.

No longer are there just ‘cells’: there are data cells, and column headers, and calculations—and the application knows which is which because it has been told.

Because the application knows the structure of your data,

1. you don’t need to repeat yourself, brainlessly applying the same borders to all the cells, and
2. the program can prevent certain kinds of mistake (mistakes such as omitting values from totals, or summing the wrong column or copy-and-pasting a formula wrongly).

In addition, because the program knows what is data, and what are labels, and what are totals, it becomes possible for it to apply visual styles automatically which look halfway decent. Spreadsheets which you create in a hurry need no longer look like a confusing jumble of numbers.

Sounds like a big project?

Yeah, turns out it is!

Naturally, in addition to figuring out how the thing fundamentally should work, I would have to consider how to make it easy to pick up and use. (Nobody reads instruction manuals any more.) Also, Microsoft pretty much control the market for spreadsheets, so persuading people to use a new, different one is going to be tricky (to say the least).

I decided to concentrate on the core ideas. I didn’t know how far I would get, really, but hoped to have something which was minimally useful by the end of my 9 months.

How did I get on?

To cut to the chase: 9 months on I have a kind of proof-of-concept, (though very much a proof-of-concept; nothing usable).

I didn’t get as far as I wished (a usable product), but I did develop some interesting ideas.

May–June: Worked on an initial idea of how tables might fit together, and how the user might drag table axes around to rearrange them. The idea was that each table would be composed of ‘slabs’ of data cells and labels and totals. I came up with a demo which let you drag axes around. It looked quite impressive, but unfortunately the underlying model just wasn’t practical. So I put that aside and…

June–August: Worked on a language for spreadsheet formulae. I learned a great deal about parsing and type inference, and all sorts of things that I’m sure you’re not terribly interested in. It was like going back to University. I realised by the end of August that this could easily eat up all of my time and I still wouldn’t have something ‘interesting’ to show to people, so…

September–Jan: Back to the table model again. This time I had a mechanism which seemed to work, so I worked on a demo which would allow the user to build up tables from rows and columns, group rows/cols together, repeat them, navigate with the keyboard and the mouse, and enter data. Still no calculation ability, but it does demonstrate some of the basic ideas.

If nothing else, the last 9 months has crystallised in my mind broadly how the damn thing should work. My brain is bursting with ideas of how to develop it further.

Where next

So I have created a rough proof-of-concept, and I’m back to full-time employment. Where should I take it from here?

My plan (for the time being at least) is to spend the next year working on it in my spare time. Even as a demo I think it could benefit from:

• Being able to do calculations. I mean, that’s pretty fundamental for a spreadsheet application, the calculation bit. There’s no getting round that, really. However, I’d keep it really simple at this stage, enough to do simple arithmetic, summations and averages, probably. The point is to show off how my scheme makes formulae easier to write, easier to understand and less error-prone.
• Making it look prettier. That sounds really trivial, but I think it’s important for 2 reasons:
  1. If a bit of computer software looks awkward, clumsy and ugly, people assume that it’s difficult to use. I want to see if people can genuinely use this, so I have to make it more approachable.
  2. I want people to look at it and see the potential, not concentrate on the fact that it looks like a really rough demo!

Then what? Maybe I’ll manage to get a bit more time off from my wonderful employers. Maybe the magical Open Source pixies will help me work on it. Maybe Google will give me 3 million dollars to finish it.

Currently my most practical plan for completing it rather hinges on my winning the lottery, which, given that I don’t play the lottery is quite a long shot.

Summary

I didn’t get as much done as I’d initially hoped, but then I didn’t know how long it would take.

I’ve explored the concept, and I want to work more on it. I think it has potential. From this angle alone, it’s been a worthwhile endeavour.

I’ve learned a lot about managing myself. Also, from working 3 days a week on my own project for 9 months, I’ve really grown as a programmer, as well as gained experience of a few interesting technologies (listed below if you really care about that sort of thing).

If anything more comes of it, or I have something to show on YouTube, or something you can actually download and play with, I will of course let you know.

Thanks for reading.

Thanks too…

…to everybody who’s asked about it and listened to me while I perfected my elevator pitch for the product.

…those of you who’ve sent me examples of spreadsheets. They’ve been really useful to see what people actually do with Excel. I’m still collecting spreadsheets, actually, if you have any you could send me.

…to my very flexible employers, Cygnet Solutions.

…to my fine project manager for the last 3 months, Miss KB.

Additionally, if you’re interested

I wrote it in a (relatively new) language called Scala, using the SWT windowing toolkit (both of which turned out to be fine choices). Started off using Netbeans as the IDE, then recently migrated to IntelliJ, once IntelliJ went Open Source. (And I much prefer IntelliJ—it’s fantastic.) Using git for version control, Trac for bug/feature tracking and Maven as the build tool. Lovely.