Since the introduction of Office 365, the pace of change in Excel has accelerated. The last year alone has seen some really significant enhancements. However, is this anything more than a final flourish for a doomed application? For years, people have been predicting the imminent demise of the spreadsheet – albeit that those predictions have often come from those launching spreadsheet alternatives. Up to now, the spreadsheet has proved predictions of its death to be greatly exaggerated. It would be interesting to find out whether you think the changes discussed below might secure the future of the spreadsheet for many more years, or whether decline is inevitable and imminent.
Getting back to specific Excel changes, Liam Bastick continues to contribute a series of excellent articles to the Excel Community that provide advance notice of forthcoming Excel enhancements. This is a selection of the some of the most important such articles posted since late 2018. (Note that the Excel Community content is only available to Tech Faculty and Excel Community members. Details of how to join are included at the bottom of this article):
Modelling 101: Part 41 – Getting Arrays: Spilling the Beans on Seven New Functions
Modelling 101: Part 52 - New Excel Functions: XLOOKUP and XMATCH
Modelling 101: Part 53 - Dynamic Arrays One Year On
XLOOKUP Provides Moving Arguments
Office Scripts Coming to Excel
These posts cover three separate, and particularly important, enhancements. The two posts on arrays cover the introduction of Dynamic Arrays. Dynamic Arrays mark a revolution in Excel's calculation engine that allows a formula, for the first time, to 'spill' its results into adjacent cells. Adding further data to a Table column that the dynamic array formula refers to can result in a corresponding change in the range that the formula spills into. Perhaps the closest comparison in Excel is to a PivotTable. However, unlike a PivotTable, dynamic array formulae recalculate immediately with no need for a 'refresh' operation. Liam's article demonstrates many ways in which you will be able to use dynamic arrays to automate practical Excel operations.
The other topic that benefits from two posts is the death of VLOOKUP(). Understanding VLOOKUP() to prevent an international incident is one of dozens of Excel Community articles that cover the uses of VLOOKUP() and why the function can be as dangerous as an upturned plug on a bedroom floor during an unexpected power cut. The introduction of XLOOKUP() and XMATCH() not only condemns the existing Excel lookup and match functions to the eternal purgatory of 'legacy' status, but also brings significant new functionality and speed improvements.
Finally, the new decade starts with the promised introduction of a major new automation feature: Office Scripts. In addition to providing a whole new method of automating Excel operations by allowing steps to be recorded as a script (in a similar way to recording a macro in Visual Basic for Applications), Office Scripts will allow a series of Excel steps to be incorporated into a wider automation project involving other Office applications.
What these three improvements have in common is their ability to revolutionise the way in which all Excel users address practical Excel problems and to save significant amounts of time by automating previously manual processes. Although it is not particularly recent, there is another Excel feature that can also save career-changing amounts of time and effort: Power Query. This leads us on to our vote. Do these changes herald a new, more automated, dawn for Excel or are they just irrelevant tinkering with a doomed and outdated technology? Please add comments if you would like to explain the reasons for the way that you vote:
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.
Many thanks for the comments and votes so far. The more the merrier...
Spreadsheets are not doomed. For those of us in the real world, for as long as we have to manually mutate data from one system to the next, or even understand the data shifting between systems, we'll be using spreadsheets to do it.
Excel is the show in town for this job, because key functions that enable us to pre-control and pre-prove data before importing data into the next system are either missing from or dysfunctional relative to Excel's competitors. We use spreadsheets because we want to inject a review point in between systems, to reduce the risk of "garbage-in-garbage-out".
For example, =SUMIF() and =SUMIFS() work from within the same workbook in Excel, WPS Office, LibreOffice and PlanMaker. But only the former two support the functions' use between linked workbooks. Major issue that. But LibreOffice and PlanMaker appear to have zero plans to bring their offerings up to scratch.
In some cases, the use of Excel is the sole method of data transfer, e.g. between an authorisation workflow in Azure/G Suite/AWS WorkDocs or proprietary product (e.g. https://workflowfirst.com/) and a double-entry ledger system (e.g. Sage Line 50/100/200/300, Axapta/Great Plains, etc). Some proprietary products expect a spreadsheet to be available, for they export "authorised" data in XLSX format in a flat file whose structure is as compatible as they can get it with the next system to import the flat file.
Excel started life leaky and has just taken on more water, year after year. To analyse data you need to use a product that is dedicted to the cause rather than a one-stop shop product that tries to be all things to all (wo)men.