Excel Tip of the Week #423 - Our Excel Christmas list

For many years now we’ve taken the Christmas period as a chance to post some more fun or different content – from a keyboard shortcuts advent calendar to a compendium of VBA games (the original file link is no longer working but the compendium can be accessed here). This year, we’re turning this around, and instead of offering you something festive, we’ve compiled some of our bloggers’ own Christmas wishes for the future of Excel.

John Tennent

First of all, charts and graphs author and frequent webinar host, John Tennent. John shared some non-existent functions that he would be happy to see added to the program. Here they are in his own words:

=NOTDATE(xx) if you have a part number 24-12-7005 Excel thinks it is 24 Dec 7005 which then has to be undone. In a recent project with a list of 2500 part numbers about 17 of them converted to dates. Can there be a function that allows ‘date like’ data not to treated as a date!

Excel’s automatic date formatting is notoriously overzealous, leading to errors where anything that could be conceived as a date being reformatting – and in turn to geneticists changing several gene names to avoid this problem. A formula (or other feature) to prevent this would certainly be welcome. And, if I can add my own note, why is it that Excel is happy to consider dates in the 8th millennium but not one in 1899?

=DATESWAP something that allows US dates to be treated as UK and vice versa. (ie MM DD YYYY v DD MM YYYY). Can use text to columns but a bit clumsy.

 

Going on with dates, another issue of dates is the different formats used in different parts of the world – particularly UK & European day/month/year and US month/day/year. This not only causes dates like 12/31/2021 to be treated as text, but worse causes dates like 4/6/2021 (April 6th in US format) to be read as the 4th June by a UK Excel installation, which can be subtly destructive. While Power Query does offer some slightly better date translation options, it’s still not perfect.

=PAYBACK – an easy work around with =IF(AND so cumulative cashflow is -ve one year and +the year after but why not have a function?

Here John is essentially asking for an easier way to find the point in a list of numbers where the cumulative total crosses 0 – useful not only for payback but several other things as well. A suitably flexible function could even allow nonzero thresholds.

=PAYBACKTIME – as above but converts decimal to a date i.e. payback of 2.9 years = 2 Years 11 months. Non-financial people take a while to translate – much easier to do it for them…

I think an easier way of getting this sort of formatting for dates – probably as an option in Excel’s custom formatting language that could be used in TEXT rather than as a whole new function – would be great. The current workarounds are annoyingly involved.

Simon Hurst

Here’s Simon’s entry:

I’d like to see the complete and utter obliteration of Power Query and all the tools, features and techniques associated with it. Prior to the introduction of Power Query, I used to have a perfectly viable Excel consultancy business. This has been decimated by Power Query. Over the past six to seven years, dozens of spreadsheet projects which would previously have taken days’ or weeks’ worth of high-powered Excel expertise have ended up being completed in a morning using Power Query. Even worse than this, instead of continuing work from implementation, support and troubleshooting, Power Query solutions seem to automate entire processes with little need for ongoing maintenance. As if all that wasn’t bad enough, several clients have found Power Query so easy to get to grips with that they’ve started to use it to implement their own solutions without any need for my help. Please see what you can do…

Canny users can perhaps peruse Simon’s extensive archive of PQ posts in our knowledge base and decide for themselves whether he is his own worst enemy…

David Lyford-Tilley

And what about me? Well, I have written a bit about Power Query myself, but my issue is more specific. If you ever have cause to write a formula in PQ, you need to use the M language editor:

After being used to the Excel formula editor and Intellisense, the M editor is just incredibly frustrating to use. For starters, it’s case-sensitive – so for example Table.AddIndexcolumn is not acceptable as a function name. Secondly, the tooltip is shaky at best – it doesn’t always appear, if for example if you move out of the function you’re writing and then go back into it. But for my mind the worst part is the atrocious autocomplete. Let’s say we want to write a Table.AddIndexColumn and we get to this point:

Ah-hah, there’s the function we want right at the top! So we press Shift to autocomplete and we get:

Argh! What utter nonsense. The “TableTable” start means this won’t work at all. So, all I want for Christmas is a working autocomplete in M. So how about it, Microsoft?

Share your own Christmas Excel wishes with us via excel@icaew.com!

1. Previous post - Better error catching formulas
2. TOTW index
3. Next time – Filters redux!