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.
Thanks Charlie - I'm sure there are loads around, Leila has an extensive range of online course so perhaps one of these covers PivotTables.
The Tech Faculty, via the Excel Community, has worked with a company called 'Filtered' to provide free access to members for a range of Excel courses: www.icaew.com/.../excel-online-training-registration (disclosure: I did help write some of their original courses many years ago and retain a very small shareholding).
Members also have access to a comprehensive set of recorded webinars with one of the most recent being: The Comprehensive Guide to PivotTables: www.icaew.com/.../the-comprehensive-guide-to-pivottables.
Circumstances have forced me to record one of my own live lectures all about PivotTables into a reccorded version. This is 3 hours long, but is divided into chapters to make it a bit more manageable, but if you are already a Tech Faculty or Excel Community member it would be well worth exploring the options included in your membership as set out above.
Thanks, Simon Hurst
Leila Gharani's course on Power Query at courses.xelplus.com/.../excel-power-query looks good and I'll sign up for it, but I'm also looking for good courses on pivot tables. Do you know of any that you can recommend?
Hi Mumtaz. Thanks for taking the time to add your comment and I'm really glad that you have been able to explore Power Query and see what it can achieve. Leila Gharani also delivered an excellent session on Power Query at the online conference I mentioned in the post. Good luck with your future use of Power Query.
There are a few other posts on Power Query here in Tech News and lots more over at the Excel Community but the Excel Community is only open to members of the community or members of the IT/Tech Faculty.
Simon, I read your blog, perhaps, a week before. I did not fully understand what you wrote. Currently, I am on public holidays due to Eid festival, but I have limited myself to home due to Covid-19. Therefore, in order to keep myself busy, I try to explore different things. Yesterday, I received a tweet from Leila Gharani who is an Excel expert and posts her videos on youtube. Well, I clicked her video with the name "Consolidate and Clean Multiple Excel Sheets in One Pivot Table". What I discovered was that her presentation was about power query. First, I grappled with the concept, but now I am amazed and thrilled. I reread your post. Now, it makes sense.