I've recently been looking at the updates to Excel since the original release of Excel 2016. There have certainly been many very useful and radical enhancements introduced in this period, but something else became apparent as I was looking at the detail of some of the changes: several of the most important changes seem to have been left unfinished or, at least, lacking some apparently minor additions that would make a significant difference to how useful they could be.
We'll start with a feature that is much older than Excel 2016: structured Tables. These were introduced with Excel 2007 and made a significant contribution to the automation and reliability of spreadsheets. Because a reference to a complete Table, or Table column, automatically expands to include new Table rows, the chances of references omitting adjacent rows is reduced and the need to manually change references to include new rows is eliminated. A key element of the way Tables work is the use of 'structured' references that use Table names and Table column headings instead of cell coordinates. However, the use of structured references has never been fully implemented. Neither Data Validation sources, nor Conditional Formatting formulae, are able to use structured references directly, leading to the need to allocate a Range Name to a Table column and then use the Range Name as the Data Validation source for example:
Excel troubleshooting – why does data validation work on my desktop computer but not on my notebook?
Perhaps even less forgivable is the failure to properly integrate Tables with the sheet protection model. There are many reasons not to use Excel for data entry. Tables might have made it a little more practical to use Excel for basic data entry except for one glaring drawback: rows can't be added to a Table on a protected sheet, whatever users are allowed to do when sheet protection is turned on. This means that one of the most important features of Tables, the ability for formulae to be automatically copied down to new rows as they are added, is severely compromised. It is impossible to abide by principle 20 of the ICAEW 'Twenty principles for good spreadsheet practice': "Protect parts of the workbook that are not supposed to be changed by users", if you need to add rows to a Table that contains a formula in any of its columns:
Twenty Principles for Good Spreadsheet Practice – principle 20
This deficiency has been the subject of a long-running campaign by the community, focusing on the Excel UserVoice idea:
How can we improve Excel for Windows (Desktop Application)?
This idea had attracted 1,497 votes and 220 comments at the time of writing this article, but the most recent response from Microsoft was nearly 3 years ago, acknowledging "this great suggestion" and promising to: "…prioritize this according to the number of votes".
Moving on to more recent enhancements, Excel 2016 saw the addition of 6 new chart types when it was first introduced, with two more following shortly afterwards. Useful as the new chart types are, they too suffer from some deficiencies. A relatively minor point relates to the chart title. In the pre-2016 charts, the chart title text can be linked to the contents of a cell. For the new types, the chart title has to be entered manually. More importantly, none of the new chart types work with PivotTables (although, in fairness, neither did the existing XY (Scatter) and Stock chart types):
This is a significant drawback. The new chart types would be ideal for use in Excel dashboards but the obvious way to create an interactive dashboard in Excel is to use PivotTables controlled by Slicers.
On the subject of Slicers, this is less of a criticism and more a suggestion of a missed opportunity but, had Slicers been given the capability to automatically 'play' through each item, it would have enabled Excel charts and dashboards to be animated in a similar way to the charts in the 3D Map and (now legacy) Power View features, and in the separate Power BI desktop application.
Coming right up to date, the new Dynamic Array feature is undoubtedly revolutionary allowing, for the first time, a single formula to 'spill' into multiple adjacent cells:
Excel Tip of the Week #327 - Introduction to dynamic array functions
Like the new chart types, the issues with Dynamic Arrays, probably fall into the categories of both deficiencies and missed opportunities. The inability of a Dynamic Array to be part of an Excel Table would appear to be a deficiency. One of the great advantages of Dynamic Arrays is the ability to automate processes that would previously have needed manual intervention. However, the inability to 'spill within a Table' makes it much more difficult to create a chart or a PivotTable that dynamically adjusts to take account of a change in the number of cells into which a dynamic array formula spills:
Multiple, dynamic Treemap charts - an Excel Challenge
For the missed opportunity, an APPEND() function is noticeable by its absence from the initial batch of specific dynamic array functions. This would allow the dynamic creation of a single table from multiple tables, something that is currently only easily possible using Power Query with its need to refresh.
I am sure there are many other examples of Excel features that come so close to being the perfect answer to a practical problem but fail for what seems like a relatively trivial reason. Please send us an email to let us know of any that you have come across.
Excel community
This article is brought to you by the Excel Community where you can find additional 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.
