Share this content
Tags:

The future of spreadsheets – does anyone really know?

17th Oct 2016
Motion blur
istock_lkunl

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:

            =SUBSTITUTE(cell address,“£”,“”)

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

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.

Tags:

Replies (4)

Please login or register to join the discussion.

Routemaster image
By tom123
18th Oct 2016 08:23

The general rule is, the higher up an organisation the person is, the greater their belief that the all singing/dancing IT system purchased at great expense does everything.

The lower down (ie amongst the do-ers) everyone knows the whole thing hangs on spreadsheets.

Thanks (5)
Replying to tom123:
avatar
By Farkhem Hall
21st Oct 2016 18:39

Amen to that.

If only they knew....

Thanks (0)
avatar
By njpandya
19th Oct 2016 16:16

Excellent article & speaks the truth!

Thanks (0)
Chris Challis
By challisc
15th Nov 2016 19:50

Excel is readily available on business PCs, with nearly every user familiar at a basic level, plus sophisticated functions for more advanced users. Excel is therefore ideal for one-off situations and personal use.

Once a "system" develops for repetitive use, then it is worth considering replacement by task-specific software applications. Financial examples include forecasting, reporting and transaction processing. Horses for courses.

Flags suggesting replacement include:
- Multiple participants in forecasting, or
- Multiple users in recording or processing
- Multiple entities (such as departments and companies)
- Multiple dimensions (such as geography, product/service groups and time periods)
- Multiple currencies
- Multiple linked spreadsheets
- Need for audit trail

In many cases suitable packages are avainable, in the cloud or to run on-premise. In other cases a bespoke database-driven system would be preferable.

A question of establishing requirements and assessing cost-effective options.

Thanks (0)