We live in an era of rapid technological change. Some changes are incremental and others are more revolutionary. In some cases, the changes are so dramatic that they have the potential to completely replace what had gone before within a very short period of time. Perhaps we now have access to a technology that will make cell-based approaches to certain types of spreadsheet requirements seem ridiculously cumbersome.
Let's consider vacuum cleaners. If rechargeable vacuum cleaners and the mains-powered equivalent had both been invented at the same time, there's a good chance that the mains-powered version would have been rejected, or become a small niche product for specific applications. Dyson's latest advert (other brands of dust-removal via suction devices are available) announces that they are to stop developing corded vacuums. On the other hand, when E-readers were introduced, many predicted the end of printed books and high street bookshops. Instead, statistics from the Publishers Associations 2017 yearbook show that physical book sales continue to exceed the electronic equivalent and seem to be growing, while the sale of E-books declines. Perhaps even more surprising is the resurgence of vinyl. Almost killed off by CDs, it is now the existence of CDs that is threatened by downloads and streaming, with vinyl sales increasing dramatically (from an admittedly low base).
So, what has any of this to do with Excel? I've been working on a couple of Excel consultancy assignments recently and, coincidentally, both clients made similar comments about how they had been carrying on using Excel in the way that they knew, without necessarily being aware of alternative approaches. Returning to our, somewhat strained, vacuum analogy, if you didn't already have a vacuum cleaner and went out to buy one, the choice between corded and cordless might be an easy one. Rather than dragging a cylinder vacuum from room to room, plugging in and unplugging as you go, creating a potentially fatal trip hazard and gouging chunks out of your architrave, you could use something that weighs one third as much and is free from the restrictions of the power cord.
In one of my Excel assignments, an existing data entry sheet needed to cope with entering values by product, with each product subdivided into several possible categories, and into one of over 50 weekly periods set up as separate columns. In order to allow for the entry of new products, lots of blank sections were included and, for each row, most of the period cells remained empty. The task was then to extract the information from the data entry sheet in some sort of useable format.
A 'traditional' Excel approach could have involved entering approximately 500,000, quite complicated, SUMIFS() formulae into cells. The Power Query alternative would read the contents of a data entry sheet into the Power Query editor and apply 8 steps using the Power Query interface, eliminating the need to construct and enter any formulae. The result could then be loaded onto a sheet that used only about 1,600 cells rather than over half a million, halving the size of the file and dramatically improving efficiency and performance. However, there is a 'but'. The Excel formulae approach would recalculate automatically (albeit a little slowly) when any relevant data was added or changed, but Power Query requires the query to be refreshed in order to reflect changes in the data source. The cell-based approach was workable but very cumbersome and allowed 500,000 opportunities for introducing errors but, without knowledge of Power Query, there was probably not much alternative, without moving the entire system from a spreadsheet to a database. Looking at the two options, without the baggage of years of cell-based Excel, the Power Query approach seems pretty attractive.
Although this was a relatively extreme example, I am sure there are a lot of other spreadsheets in use that suffer from similar issues. Taking a completely fresh look at the structure of problem spreadsheets, with an understanding of the range of alternative methods now available, could dramatically improve the efficiency and reliability of a vast number of spreadsheets. Even better, unlike dumping your corded vacuum and spending money on a more technologically-advanced model, Power Query is completely free and built into Excel 2016.
So, will cell-based spreadsheets survive, with Power Query just being a product to deal with very specific data-acquisition and manipulation tasks, or will it herald a mass migration away from the use of cell-based solutions? Either way, can you afford not to explore its capabilities?
This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.
Thanks Malcolm, you’re probably right. However, I think a lot of spreadsheets will remain cell-based not because that is the best solution, but because people are much more used to that approach and perhaps reluctant to go for, what might seem, a more radical alternative.
It's a mixture, isn't it? Horses for courses, I've been modifying some complex spreadsheets full of lookups, SUMIFS and pivot tables to use Get & Transform. However the pricing in one of those models uses Black-Scholes and Monte Carlo simulations, these are done with a mixture of VBA and cell based calculations. In practice you could replace the cell based calculations entirely by VBA or vice versa, but I am not sure I would like to replace those calculations by an M formula!
Cell based calculations still seem to me to be generally easier to audit as well - there is nothing like the dependency arrows in Get & Transform. They are also easier to modify for one off changes.
My guess is that Power Query will find its uses in large scale data analysis and in solutions which need to be robust and unalterable, such as Board reports, but cell-based solutions will continue to play a large part in Excel life for a long time to come.