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.
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.
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…
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:
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 firstname.lastname@example.org!
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.
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.