ICAEW Excel tips: Looking back

Rearview mirror with modern building
istock_sinology
David Lyford-Smith
Share this content
Tags

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. 

Of course, each post includes excerpts from ICAEW’s two major publications in this area, namely the 20 Principles for Good Spreadsheet Practice and the Spreadsheet Competency Framework.

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.   

I’ve spoken about the nature of mastery and whether competitions like ModelOff really mean anything. And I’ve talked about both the necessity and the methods of pushing oneself to learn more.

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

This final Tip of the Week highlight is based on TOTW #234. If you are an ICAEW Excel Community member, don’t forget that you can see all the Tips of the Week here.

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:

Tip of the week

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:

Tip of the week

And finally, if you need the text to change according to the value shown, then build in one or more IF functions as well:

Tip of the week

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. 

About David Lyford-Smith

David Lyford Smith

David Lyford-Smith is the technical manager for ICAEW, where he works in the IT Faculty and manages and blogs for the Excel Community.

 

Replies

Please login or register to join the discussion.

avatar
By chatman
15th May 2018 08:07

In the tip, how do you get the 55% to show as "55%" and not "0.55"?

Thanks (0)

Related content