This month the IT faculty’s technical manager reports back from the future of spreadsheet event and reveals one of the most viewed spreadsheet tips of all time.
I mentioned in my last blog here that the ICAEW Excel Community was hosting a joint event with the Data Science Institute of Imperial College. Well, it’s been and gone now – and I’m here to tell you a bit about it.
The title of the afternoon was ‘The Future of Spreadsheets’, and we opened with a presentation from Simon Thorne, senior lecturer in computing and information systems at Cardiff Metropolitan University. Simon spoke around the topic of error rates in spreadsheets, highlighting how the lack of testing and peer review lead to high rates of errors, and how a culture of overconfidence in the outputs of spreadsheets can lead to damaging results. The future, he argued, was for spreadsheet use and analysis to be viewed as a proper discipline, rather than an ad hoc system.
I myself then had a chance to speak, introducing the ICAEW’s work in spreadsheets (particularly the Twenty Principles for Good Spreadsheet Practice and the Spreadsheet Competency Framework), and talked about how they had come to be and how the lessons of creating structure and guidance for spreadsheet practice would have to be applied in the future – not only for avoiding high-impact errors, but also shaving down the endless hours wasted in small inefficiencies.
A series of roundtable discussions went on from there, discussing for example the place of spreadsheets in the era of big data (perhaps even as the data itself!). But overall I was left with one lasting impression – some feel that spreadsheets will be replaced by task-specific software applications, and some felt that with over a billion unit sales, Excel was here to stay for good – but no one knows for sure. What do you think? Let me know in the comments.
Tip of the week highlight – accept no SUBSTITUTE
This one’s taken from July 2015, TOTW #88 – one of the most-viewed tips of all time.
SUBSTITUTE is a great little function – essentially a formula equivalent to the popular Find & Replace feature. The normal syntax is:
=SUBSTITUTE(cell address with text, old text to find, new text to replace)
This function will go through the targeted cell and strip out all instances of the ‘old text’ and replace them with the ‘new text’. It can be used to do things like changing names / references in text automatically with a formula.
You can also do more with this function – for example, by using “” (Excelese for ‘null text’) as the ‘new text’, you can simply remove all instances of the ‘old text’. So for example if some exported data has picked up some pound signs that aren’t needed, you could use:
This can also be used in combination with the LEN function, which tells you the length of a text string, to calculate how many times a certain character appears in a string. For example, this function will tell you how many spaces are in a cell:
=LEN(cell address)-LEN(SUBSTITUTE(cell address,“ ”,“”))
…which is effectively counting the number of words in the cell (actually one less than the word count, but close enough).
Principle of the month
This month’s selection from the 20 principles for good spreadsheet practice is #3:
Ensure that everyone involved in the creation or use of spreadsheets has an appropriate level of knowledge and competence.
This is all too often forgotten – but spreadsheets, no matter how well designed, are only as good as their users. Making a spreadsheet whose operation is obvious to an expert is no use if it has to be used by a novice. But more importantly, having the right skills is vital.
I’ve mentioned it a couple times before, but the spreadsheet competency framework is a free resource to help you identify and classify spreadsheet knowledge – and might be what you need to make sure that your training / hiring practices are up to scratch when it comes to spreadsheets.
See you next month,
David Lyford-Smith is a 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. Find out more atthis link.