The Future of Excel

I think an important aspect of what makes Excel formulas (relatively) useable to people who don't think they're programmers is their functional nature. You don't have to simulate the program's state in your mind because there is no state. You hit return and it shows you what the result is, and if your formula gets too long you can break it up into multiple cells. Giving users a procedural programming language is only going to help a small number of Excels users - the ones who like thinking in a procedural way.<p>For me, the real pain point in Excel's formula language is in how the path of least resistance leads you towards unmaintainable code. "SUM(A3:K754)" is not very meaningful. If only named ranges were more discoverable and useable, we might end up with more examples like "SUM(ProductSales2012)".<p>Shameless plug: I'm working on a visual, functional programming tool for querying data that's aimed at people who currently only use Excel: <a href="http://querytreeapp.com" rel="nofollow">http://querytreeapp.com</a>. I think the advance in browser technology is going to bring a number of tools like this to market to challenge Excel's dominance and hopefully improve to quality of "shadow IT" in business.

It strikes me that excel appeals to people for exactly the same reasons that lighttable appeals to developers. You put numbers and equations in and instantly see the results. If you need to do something complicated you can build it up, one step at a time and see how the numbers flow through the system. You can even try different scenarios by just tweaking the numbers.

You can already embed Python in Excel. There's a few different projects that do this; here's one: <a href="http://www.bnikolic.co.uk/expy/expy.html" rel="nofollow">http://www.bnikolic.co.uk/expy/expy.html</a><p>The article doesn't do any close human-factor analysis of why Excel is so much more accessible than programming languages; it's the lower abstraction bar. No need to name variables in functions when you can just click on the cell you want. No need to create loops when you can drag an already entered formula across a number of cells. Excel is a direct-manipulation programming environment, not a language, and a language can't compete here.<p>But I wish them luck; they diagnose the problem reasonably well, if not the solution.

How does adding Python to the spreadsheet paradigm improve the fragility problem at all? The major problem with spreadsheets is a dangerous mix of ad-hoc model definition and data. An author can build ad-hoc structure, but has no easy or default way to ensure that the structure is consistent or valid, and it's incredibly to accidentally change both data and model with bad keystrokes.<p>What really needs to replace spreadsheets is new ways to define models that are as easy-to-use as ad-hoc spreadsheets. For instance:<p><pre><code> * A single table should only contain one set of data adhering to the same schema. * Formulas, formatting and validation should be defined per-column, not per-cell. * Typing and pasting into ranges should be disabled by default. * Auto-import from other sources into a table needs to be much easier. </code></pre> Excel actually contains some of these features, but until it removes or disables the ad-hoc nature it'll still be dangerous. Maybe it should add a "safe mode" so that financial institutions can have an easy migration path from such high risk.<p>I've spent a lot of time migrating financial apps away from spreadsheets. Some of the errors I've seen could have caused many millions in unnecessary losses, maybe did before I got there. It's insane, but this doesn't seem to fix it in any meaningful way.

<i>I'm one of the (10) original developers of Excel.</i><p>I would say people love spreadsheets (Excel just happened to be a particularly well done implementation of a spreadsheet when the GUI became the most popular desktop platform) because:<p>a) Easy visual data entry - everyone understands a table of data.<p>b) Non-procedural programming model.<p>c) Simple analysis tools (batteries included): charts and pivot tables.<p>There just has not been much imagination applied to improving on this model - there are great gobs of ground for innovation for someone willing to take a stab.<p>1) Make spreadsheets "functional" - i.e., allow a whole spreadsheet model to be used as a function definition (I was amazed when I first learned spreadsheets and found they lacked this capability).<p>2) Make it easier to sling large data sets around - and especially share and work with dataset repositories.<p>3) Better hooks for developers to integrate spreadsheets with more advanced functionality and features. There is too much locked up in the spreadsheet as a monolithic application. I can publish my Python program easily - why can't I do the same for an application developer in "spreadsheet language"?<p>There has been relatively little thinking about how to organize and manage collections of spreadsheets and models. Most people still deal with one spreadsheet at a time; it's its own little island - largely cut off from the rest of the world. In the era of cloud infrastructure and hosted data, why aren't there better systems in place to manage data and models in the aggregate? What if you married the best of GitHub and Excel together, for example?

"Excel is like a user-friendly nuclear power plant. An amateur operator might be able to keep it running most of the time, and most of his mistakes won't be serious. But the wrong mistake can blow up your business."<p>Yeah, running huge Excel sheets is a risk. Running huge Excel sheets with VBA is a risk and a pain. Powering Excel with python is a step in definitely a step in the right direction and would save me personally a lot of pain and boost my productivity right now.<p>Unfortunately it is a step I can not take. Excel is supported by Microsoft and Excel Bloomberg plugin is supported by Bloomberg. DataNitro is supported by a company founded last year that doesn't have single person's name in their About Us or Contact Us pages. If I was to build a sheet depending on their technology, would a wrong misstep two years down the line blow up my business?

Many developers think they know the solution to a problem before understanding the problem. They jump to the solution, so to speak. This is exactly what HN has been doing about Excel the last few days... if you want to replace Excel, spend a year building models in it, then you'll see how it's harder to replace than teaching someone development.

Excel is a pretty amazing piece of software. Previous place i worked at -- a medium-size insurance company -- did all of their monthly expense calculations in uber-complicated spreadsheets and then exported the results to AS/400. Part of their issue was that training staff became an increasingly complex task because of the structure of the data in the sheets some of which had to be imported from a 3rd party source. I have never seen Excel used to this extent until i was brought in to convert these sheets to a web app. We ended up building a custom DSL based on mathematical expressions, an in-browser IDE for accountants to configure formulas in the system and a full-blown custom report builder. It was by far the most complex project I've ever designed and it had an immense impact on how i look at advanced Excel users today.

It's interesting to see this discussion of the future of Excel and complaints about its language. The fact of the matter is that the language is an evolved artifact with an incredible history. Spreadsheets started with VisiCalc. Then came Lotus 1-2-3, and Quattro. Finally, we have Excel and Numbers.<p>The language that Excel uses is odd because it was forged in the fire of competition between vendors. Each of them had to support their predecessors' idiosyncrasies to get adopters. No one was able to make a clean cut at spreadsheet languages and make it stick. I know there have been some attempts. Back in the days of the spreadsheet Cambrian explosion there were even some oddities like 3D and nD spreadsheets. None of them could ditch "the language" that we are left with today.<p>It's a shame. I think a good "ground up" redesign of the spreadsheet would be incredibly useful if it was done and people bothered to use it.<p>The thing that makes it hard to revisit the spreadsheet is that it is "the programming model" for non-programmers. Non-programmers just want to get their work done and they bias toward familiarity.

The beginning is silly: "Why do people keep using Excel to build giant, error-prone spreadsheets?"<p>This could just as easily be: "Why do people keep using programming languages to build giant, error-prone applications?"<p>I can't even read the rest of it. Excel is so successful because it lowers the bar for creating applications. They might not be pretty, and they may have expensive bugs, but its like everything else in the world.<p>I'd bet the amount of value Excel has created in the world far outweighs the consequences of any bugs in the worksheets.

There already is (and has been for some time) a "better Excell" that'a also a "better Ms Access" at the same time: Quantrix (<a href="http://www.quantrix.com/" rel="nofollow">http://www.quantrix.com/</a>), it can replace brittle spreadsheets with much easier to manage documents and do much more<p>...but there's a problem with it: it's just <i>too much</i> for 99% of people, both as <i>features</i> (It's probably easier to learn a general purpose programming language) and as <i>price</i> :)

Excel is excellent for simple formulas in a single spot. It sucks in two points: complex/ugly formulas (anything that requires a switch/case statement or nested IF's) and heavily repeated(copypasta) formulas.<p>Here's what I can imagine as a real improvement: Allow me to write a piece of code that does the job of a function, but allows to touch multiple cells - and integrate it into excel as deeply as 'normal' formulas.<p>This means the following things that current macros don't really do:<p>a) Show the "formula" in the formula bar when selecting a result cell (it will usually be only a couple or dozen lines long anyway), and allow editing it in the sheet, not in a separate editor;<p>b) When a result cell is selected, highlight the data source cells (as is done for 'normal' formulas), and the other result cells;<p>c) When I change a source cell, recalculate all result cells automagically as for normal formulas - this likely imposes a 'no-side-effect' restriction on the code language.<p>d) For the "stdlib" use the same function names as current excel formulas, so you don't have to learn a new set of them.<p>Simple, and very useful - could LibreOffice handle that?

Matrix functions and named ranges are the most underused functionality of Excel. Collectively they can replace a large amount of the conventional spagetti VBA code, emphasizing Excel's true strength.<p>Simple example: CDO pricer using Gaussian Copula and base correlation <a href="https://dl.dropbox.com/u/10755342/120626_Simple_CDO.xlsx" rel="nofollow">https://dl.dropbox.com/u/10755342/120626_Simple_CDO.xlsx</a>

Does anyone remember Resolver One? The proprietary Excel-with-python clone? I wish they'd release the source.

A couple of problems with Excel [edit: actually just spreadsheets in general], which I think fundamentally restrain it:<p>1. Difficult change control. It is very difficult to diff spreadsheets. So changes to a model are very hard to verify for correctness.<p>2. Massive code duplication by design (eg. the fill function). So it is easy to introduce an error by failing to update all relevant cells.

&#62; The only way to prevent people from running important systems with giant Excel spreadsheets is to present a better alternative.<p>I think the spreadsheet can still be improved. Macros can help a lot with this, but there are a myriad of other ways you could improve upon excel that gives a smooth transition from "casual user" to "power user". Read "A Small Matter of Programming"[0] for more!<p>[0] <a href="http://www.amazon.com/Small-Matter-Programming-Perspectives-Computing/dp/0262140535" rel="nofollow">http://www.amazon.com/Small-Matter-Programming-Perspectives-...</a>

This is one of the beauties of Matlab and similar languages. They look and act like excel but more explicit and powerful. (and harder to use) Matlab documentaiton is very similar to excel documentation and the ability to run m-files with the touch of a button gives a lot of 'reactivity' that other languages lack. Also a live, easily accessible and manipulable variable editor makes it much easier to work with data.<p>I would really like a program that wrapped something like Matlab around excel. Basically something that let you work in excel then let you hit a button to 'vomit' the variable space into Matlab code. I often find it much easier to manipulate data in Excel and often find myself working in Excel until it becomes too burdensome then swapping over and converting into Matlab or Python. I think seeing a language 'live coded' and being able to alter the live code would allow people to bridge the gap more easily. It would also fix the debugging issues as each spreadsheet would also contain a executable code that could be implanted into other spreadsheets easily, (it would also contain a number of assumptions made explicit like what variables it takes in and what it kicks out.) Of course how strings etc are handled is a messy question.<p>A similar tool exists for the open source scientific image altering toolbox-ImageJ that allows it to 'record' your actions and easily loop/automate your code by turning your calls into code as you perform them. I've found non-programmers can easily learn how to write 'code-like' actions into this macro language.

One of the problems is that Excel is used for tasks that it isn't designed for.<p>I'm currently plotting a lot of graphs in Excel (2007) and the experience is just dead awful, wanting to quickly change parameters and ranges and sheets to plot is a <i>nightmare</i>. So naturally, I went to find a good specialized tool for this but haven't come up with any. Is Excel cannibalizing the market for such tools despite it being so bad at it?

Excel really is programming. It is classified as cell-oriented dataflow programming. Functional Reactive Programming is a close cousin to such paradigm.

This sounds like Resolver One, which was a Python based SpreadSheet (using IronPython) from a few years ago. I wish these guys better luck.

I would love to see a good open source Excel clone. It does not need to have all the unnecessary features of the recent versions. IMHO Excel 97, the version that added VBA, was just about perfect.<p>Adding support for Linux, other "macro" languages and easy interfaces to popular open source projects, which MS will never do, could potentially make it the dominant spreadsheet.

There's a name for this product space! "Spreadsheet controls" describes the product space inhabited by various products that aim to help businesses manage the terrifying, complex Excel workbooks whose contents influence business decisions in subtle, interrelated ways.<p>Microsoft recognized that this was an important hole in the way enterprises used Excel, which is why they acquired a company in this space, Prodiance, in 2011. Office 2013 integrates some but not all of the company's products in its "enterprise risk management" / "spreadsheet controls" functionality; see some discussion at <a href="http://blogs.office.com/b/microsoft-excel/archive/2012/09/13/introducing-spreadsheet-controls-in-office-2013.aspx" rel="nofollow">http://blogs.office.com/b/microsoft-excel/archive/2012/09/13...</a> .<p>It was really interesting to read the prior blog post on this topic ( <a href="http://baselinescenario.com/2013/02/09/the-importance-of-excel/" rel="nofollow">http://baselinescenario.com/2013/02/09/the-importance-of-exc...</a> ) because it sounded so familiar! That blog post is spiritually identical to about the first third of the Prodiance sales pitch.<p>The "controls" space has a surprising amount of depth. Just a few problems that these folks think about:<p>(1) Excel spreadsheets are not a very good computation engine: business logic can be subtly altered by a typo that includes one too few lines in a range or a copy-paste error that accidentally includes a constant instead of a calculated cell. It turns out to be possible to programmatically inspect a workbook for "risky" or "possibly wrong" calculations and warn on their presence.<p>(2) Chained dependencies can be really hard to track: some businesses use one spreadsheet that refers to data in another spreadsheet that refers to data in another spreadsheet… and you have to hit "refresh" in all of them if you want your changes to propagate correctly.<p>(3) Data can be changed maliciously: by leaving some text white-on-white, by using Very Hidden sheets, or by deliberately changing a formula, you can put misleading information in a quarterly report to hide bad news!<p>(4) It's hard to find "one version of the truth": people constantly e-mail around things like "quarterly report - new calculations.xlsx" or "quarterly report - new calculations - REVISED USE THIS.xlsx" and we just have to hope that business processes use the right one. In some kinds of businesses, people <i>really</i> want to see a complete audited list of "important spreadsheets" and "all the versions of them" and "how they have changed and what specifically has changed".<p>These are all problems that can be solved at other layers — for example, this blog post advertises "move your business logic to Python with DataNitro/IronSpread" as a solution to problem (1).<p>If DataNitro's plugin is going to help provide "enterprise risk management", one really interesting thing to hear would be what their plan is for auditing. How can people see what ancillary Python code is attached to a workbook and how it's changed over time? That is, what assurances can someone get that the generateQuarterlyReport() method is working as designed, and can someone get an e-mail when it changes?

I love excel. Users love excel. Love it so much to the extent that we actively embed an excel sheet in our internal apps now instead of any third party "data grids".<p>Why?<p>They're easy to work with, copy/paste across apps, easy to create interface logic - even so much that the users can "code" up the sheet and we embed it and bind the dataset to a database or service layer.

I can't just install a random EXE file on my finance computer without knowing who you are. Even if I felt like this was a great product, there is no way I can risk my client's data to save some time.<p>I'm honestly not sure how you get over that hump, but you should think about it, hard.

The STEPS system provides a spreadsheet that is a simple extension of the system language: <a href="http://www.vpri.org/pdf/tr2011004_steps11.pdf" rel="nofollow">http://www.vpri.org/pdf/tr2011004_steps11.pdf</a>

Great idea but not sure who is going to use this. I can't see enterprises installing this on their desktops and as someone already pointed out the larger enterprises have been using <a href="http://www.clusterseven.com" rel="nofollow">http://www.clusterseven.com</a> or <a href="http://www.prodiance.com" rel="nofollow">http://www.prodiance.com</a> for years to control their spreadsheets

Yes, I agree that Excel is a programming language. This is one of the ways I learned programming also! Good way at looking at programming.<p>However, VBA is not the answer for most Excel users to move past because the expectation is set by Microsoft; it requires nothing more than introductory courses and common sense. This is where most people will use Excel.

As has been mentioned elsewhere, Excel users are programmers, whether they know it or not.<p>What they need is the discipline of software engineering, a completely separate discipline from programming. If Excel spreadsheets had better encapsulation, internal documentation, automated testing, and the like, the world would be a better place.

Slightly off topic, but are there any good books or other sources for experienced programmers who quickly needs to get up to speed on the more powerful features of excel/VBA? I've just been asked at work to take a look at a huge importan excel 'program' that's not quite working the way it should.

Excel VBA has libraries, they're just called references. It lacks an exception library, but it does have a similar style of attempt-fail-compensate (though you've got to put up with ugly labels). Personally I'd use IPython Notebook over it, but that's not always the reasonable choice.

Can someone explain why Excel disallows cycles (recursion) in formulas?

So, 6 months until the TDE (Test-Driven Excel) book comes out?

Why people don't use Microsoft Access when they need more complicated logic?

how does putting a "real" language on the backend of spreadsheets help fix what happened in London? seems like it's just a bigger gun with which to shoot yourself in the foot.

VBA should be replaced by VB.NET or (Iron)Python.

no for mac??? :(