Power Query makes cell-based Excel obsolete

Before starting, it's worth defining what I mean by 'legacy Excel'. In Excel 2010, Microsoft added a completely different method of working in Excel spreadsheets and skilfully hid it behind the name 'Power Query', as an optional Add-in. In Excel 2016 this alternative set of tools changed from being an Add-in to becoming an integral part of Excel. However, in an increasingly desperate attempt to keep it concealed, they renamed it as Get & Transform and disguised its true purpose by sneaking it into the Data Ribbon tab. In fact, Power Query has little to do with data and lots to do with radically changing the way in which we can all use Excel to work more automatically and more efficiently. When solving problems in Excel, we how have a choice between Power Query and a method of using Excel based on the ridiculously outdated concept of typing formulae into individual spreadsheet cells. It is this idea of formulae in cells that I now think of as 'legacy Excel'.

Why should we even think of abandoning legacy Excel? Let's consider an Excel Table of 1,000 sales invoices that has columns for unit price and quantity. We want to know the overall value of each invoice. In legacy Excel we have to enter 1,000 separate formulae, in 1,000 cells. Admittedly, as long as we know the absolute basics of Excel this involves typing in one formula and allowing Excel to copy it to 999 other cells. However, although setting up our 1,000 formulae might not be particularly onerous, we are left with 1,000 formulae, just sitting there waiting for something nasty to happen to them. Of course, we could ensure that our formula cells remain locked and then turn on sheet protection, but we wouldn't then be able to add any rows to our Table. When someone comes to check our spreadsheet they have 1,000 cells to check. In Power Query we read our Table into the Query Editor and add a column that is defined by a single formula. Because of the way that Power Query works, this also automatically separates out our original data from our calculation and our output Table and we have zero cell formulae. For more complicated processes, Power Query can replace hours of repetitive manual effort, leaving more time to concentrate on applying our skills to more productive areas.

Many times over the past few years I've wondered whether I have got all  this completely wrong and Power Query is really only such an obvious improvement in my own little parallel Excel universe, with the real world ploughing on happily with legacy Excel. Accordingly, it was with a growing sense of relief that I sat through a dozen or so sessions of the Excel Virtually Global conference this week. Over and over again, presenters started off addressing all sorts of different issues and within a few minutes, or even seconds, had said something along the lines of 'we could try and do this with thousands of complicated formulae but of course we now have a wonderful new way of dealing with this sort of thing – Power Query'.

Perhaps it could be argued that continuing to rely on legacy Excel in the Power Query era is akin to carrying on using a typewriter when you have access to word-processing.

So, emboldened by the conference experience, I think it is time to suggest that we all now start treating legacy Excel techniques as a last resort. When approaching any significant Excel problem, we should first try and solve it using Power Query and only revert to typing formulae into cells when we've proved to our own complete satisfaction that Power Query can't cope.

Cat, meet pigeons.