ICAEW Excel tips: Looking back
In his final blog, the ICAEW IT Faculty’s David Lyford-Smith reflects on the last two years and writes about the spreadsheet lessons he’s learned along the way.
After two years, it’s time for this blog to take a break. So I wanted to reflect on my last two years of posts and what I’ve learned.
Right at the beginning, my first post was all about risk (as it pertained to financial models). This has been probably the major theme of my thoughts on this blog – the causes, effect, and countermeasures to the risks that spreadsheets create.
Because of their ubiquity, flexibility, and power, spreadsheets are used across the business world, for tasks big and small. But as I’ve also written – spreadsheets aren’t right for every job. They have restrictions and aren’t the best tools for every job. And using spreadsheets where they’re not appropriate – or where due care and attention is lacking – can lead to disasters.
A second theme has been about my views on the journey of a spreadsheet expert, taking my own experiences as a guide. I’ve spoken about how getting out on the road and teaching has helped me keep sharp.
And one final theme would be about the future of Excel and spreadsheets – both comparing powerful tools like Power Query or exciting research such as at the data observatory, and talking about where the spreadsheet might be going in the future, including what Microsoft has to say about the subject.
In all, I think if I can take a stab at synthesising one lesson from this blog, it would be this:
Spreadsheets are here to stay. That means it’s our responsibility to improve ourselves and our systems to make the best use of them, and to minimise risk.
Thanks all, and see you again in the future.
Tip of the Week highlight – Inserting a calculated value into a sentence
Industry insightsView more
In general, if you need to put a formula (or even just a cell reference) into the middle of a sentence, you will want to make use of the “concatenation operator” & - which lets you glue together bits of text:
Note that the text inputs to this formula have to be enclosed with quotation marks “”.
If you need a percentage, date, or other format for your inserting / calculated value, then try the TEXT function:
And finally, if you need the text to change according to the value shown, then build in one or more IF functions as well:
Spreadsheet competency of the month
The Spreadsheet Competency Framework was created by ICAEW to provide a guide to how to talk about spreadsheet knowledge and ability, and what skills are needed for which roles.
The fourth and final level is Developer. Developers are the true masters of spreadsheets, with a grasp on the majority of the features of the package and ability to handle many complex tasks. They may be specialists such as modellers, VBA programmers, or statisticians.
Find out more about the definitions, and the list of skills needed for each, at the link above. And don’t forget about our other spreadsheet guide either, the Twenty Principles for Good Spreadsheet Practice.
Thanks and happy formulating!
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.