ICAEW.com works better with JavaScript enabled.

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

Author: David Lyford-Tilley

Published: 07 Dec 2021

Hello all and welcome back to the Excel Tip of the Week! This week, we have a Basic User post – of sorts – with a twist.

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:

Excel screenshot
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:
Excel screenshot
Ah-hah, there’s the function we want right at the top! So we press Shift to autocomplete and we get:
Excel screenshot

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!

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.

Excel polaroid
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.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250