This last month, the Excel Development Team (the team at Microsoft that make Excel) ran an “Ask Me Anything” (AMA) over on community news site Reddit. This is the third time that Microsoft have run an AMA, and they also monitor the Excel UserVoice site for suggestions for new features.
But if you could get the ear of Microsoft’s development team, what would you want to ask? Would you want to know about the future of Excel, or suggest an improvement to them, or just ask them why on earth a feature works the way it does?
I have one of each category of my own to share, and I encourage you to post your own! Maybe we can get some great questions for the next AMA.
The future of Excel
Later versions of Excel have seen significant resources poured into Excel’s data analysis tools, formerly known as Power Query, Power Pivot, and Power BI, and now increasingly folded into the standard operations of Excel. There are some amazing and powerful applications coming out of this area, but my question would be, what is happening to keep “classic” Excel functionality fresh and relevant? New functions do still come out sometimes, but slower. Will the more accessible functions suite get an overhaul at some stage? Perhaps a redesign of the way that functions are written and more powerful wizards.
My personal bugbear: MATCH and VLOOKUP both have an optional input that controls how to deal with inputs that don’t match the lookup range exactly – to either only find exact matches, or to use fuzzy matches in a sorted table. Despite the former being much more common and much safer, these inputs are optional, and if excluded the default is the inexact match. This leads to erroneous formulas and wasted time, and I’d want to see the default reversed. In the meantime, watch out and make sure to use 0 / FALSE for those functions!
How did this get in here?
The function BAHTTEXT translates a numeric amount to a written-out amount, in Thai. It’s the only numbers-to-words function in Excel. Why on earth is this the one chosen function of this type, and why not make more?
Tip of the Week highlight – CONVERT
This Tip is based on Excel Tip of the Week #206.
There are a massive number of units of measure in the world, not only the familiar metric and imperial units, but also many others for measuring distance, speed, temperature and more. Several of the most Googled queries of all time are unit conversions, and it’s a common problem from real life. Luckily, Excel’s CONVERT function can handle hundreds of different units with ease.
The syntax is as follows:
=CONVERT(number, from unit, to unit)
“Number” is typically a cell reference to the amount to be converted, or a formula that produces the desired number.
“From unit” and “to unit” are text values in quotation marks that indicate the units being converted. These can be selected from an extremely large range – the tooltip will show the full list of choices. Once a “from unit” is picked, only appropriate “to unit” options will be shown.
You can also use the standard prefixes for metric units, e.g. for miles into kilometres:
Principle of the month
This month’s selection from the Twenty Principles for Good Spreadsheet Practice is #11:
Be consistent in structure
Laying out your spreadsheets in a clear, consistent manner is another key element in helping the end user to understand and interpret your work. Keeping the same direction of work (say, adding new time series horizontally and new products vertically) also helps with formula building, as references and formulas can be simpler and easier to read. It’s also vital that all ranges have consistent formulas in them, rather than mixing exceptions. Not only does this aid review, it also protects against cases where a change is made and copied blindly over an exceptional formula that the user didn’t know was there.
This applies not only within one spreadsheet, but across multiple spreadsheets. The fewer variations on style and layout that you have in your work, the clearer and easier using it will be. Wherever possible, work with your colleagues to define standard layouts and templates, allowing easier review and use of one another’s work due to familiarity.
David Lyford-Smith is the technical manager in the ICAEW’s IT Faculty and blogs for the ICAEW Excel Community. The Excel Community is an Excel content hub that encompasses webinars, blogs, member Q&A, fortnightly bulletin updates, and more.