How has Excel faired - a five year review

 

 One of the few blogs I have retained from my old collection was my wishlist for changes to Excel that I raised at a meeting with Microsoft back in 2015.  Which of my wishes has come true?

     First, let me say, Excel has added a whole lot of stuff which makes it a far better product than it was then and I wouldn't now be without.  Of the many changes, I rate the following as the star points:

• Power Pivot and M-language which allows a much greater ability to import all sorts of data and manipulate it without resorting to VBA
• Dynamic arrays
•  XLOOKUP() – well overdue although limited use in production spreadsheets until it has been rolled out to everybody and, indeed, the same with LET() and LAMBA()
•  Stocks and Currency Data types and the now in-test historical stock information (admittedly only replacing what we had once upon a time before the plug was pulled on it)
•  Slicers, Timelines and new chart types
•  Much more flexibility in building dashboards via BI
•  Better collaboration tools with notes and comments.

That said, almost all I wished for five years ago still isn’t there.  There remains a real lack of tools for structuring and documenting spreadsheets: no notes pages, no easy way of keeping track of linked spreadsheets or documenting ranges and no way of linking conditional formatting or data validation to styles.

My second set of wishes were to do with spreadsheet protection – particularly around ranges.  Indeed, the problem has been made worse by the introduction of Power Pivot and dynamic arrays.  Locking down the right parts of spreadsheets was nigh-on impossible and still is.

In my minor amendments, I came up with the following list:

• a better way of tracking circular errors – no change
• all functions should work if they refer to ranges in closed workbooks – no change
• Improvements to array formulae – generally can avoid the need for this now with dynamic arrays
• Ensure cells with spaces in them are treated consistently – no change; and we now need a better way to deal with null cells given that Power Pivot treats them differently to the Excel engine
• Introduction of RegEx for find and replace – no change
• Expand the use of the CELL() function – no change.

I could add to that the need to increase the number of events trapped by VBA to give the sort of functionality that is available in Access, again even more important now that we can have values returned by Power Pivot, but the list goes on and on.

The verdict with 2020 hindsight: we now have a version of Excel with more functionality than it ever possessed in 2015 but with some gaping holes – some easy to fix, others more difficult, but all still needed.

Microsoft asks for people to vote for the improvements they would like to see and you can add your voice at http://excel.uservoice.com and I would encourage you to do so.