Here we talk to leading spreadsheet and Microsoft Excel expert – Dr Chris Roast, who is based at Sheffield Hallam University in the UK.
Given that there over 1.2 billion Microsoft Office users globally, and that 89% of companies use Excel, it’s clear that making good use of the world’s most popular spreadsheet software is important for business productivity.
Following his PHD in Computer Science, which had a focus on Human-Computer Interaction, Chris has gone on to hold several simultaneous senior lecturing, research and leadership roles, but his expertise and influence reach far beyond academia.
He has worked on a number of commercial projects to help take spreadsheets from being home to large data sets that can quickly become unmanageable to being powerful tools for commercial strategy, planning and operations. One of the more recent projects is EQUS – a useful Excel plugin that creates formula visualizations to help users identify and avoid potential errors.
We asked Chris a few questions about his approach to using spreadsheets and data visualization to help students, peers and businesses to find new ideas and efficiencies, and improve business performance.
How has your study of Human Factors in Computer Science influenced your understanding of how people use spreadsheets?
From a Human Factors perspective, spreadsheets are responsive and follow a simple logic which means they are easily adopted. However, as most people will know, they can quickly become unmanagable, and once you are faced with a complex spreadsheet there are few easy routes to understanding it or improving it. I’d describe this weakness as an information infrastructure that is intrinsically easy to mismanage.
In order to address this there is strong evidence of good spreadsheet quality being maintained by specific good practices. This can range from organisational ‘rules’ such as “never nest functions” or “never use conditionals”, through to demanding specific templates are used and never altered.
What do you do with spreadsheets on a day to day basis?
I’m often working on organising spreadsheets more effectively. Creating templates and usage patterns to speed workflow.
A lot of organising action lists, as well as making project estimates and helping to ensure planning and decision making is as error-free as possible.
What also requires brain-work alongside making good use of Excel functions is stretching what seem like hopelessly unrealistic budgets to meet challenging project goals – ie fitting square pegs into round holes!
So which are your favourite Excel functions?
– VLOOKUP (allows you to look up and retrieve information from a table column, including approximate and exact matches)
– and HLOOKUP (similar to VLOOKUP but retrieves from a row).
It’s clear that certain roles, such as accounting, require high-quality human-computer interaction. Have you helped people in any more unusual areas to benefit from this kind of design?
My work has involved working with Fine Artists developing immersive interactive art installations, and English Literature Researchers bringing the details of early modern poetry to life and in doing so helping challenge traditional assumptions.
I’ve worked on the evaluation of robotic systems designed to support Firefighters. Such work always throws up interestingly different values and perspectives. While we often aim to make systems easy to use and operate, this can result in a loss of detailed control. We found that Firefighters prefer to have technical control and are not put off by difficult-to-learn technology – they actually want and relish the challenge. This in fact reflects part of their professional traditions – learning to master new technologies.
More traditional interdisciplinary work has involved working with Library Managers, Healthcare Professionals, Manufacturing Companies and Service Designers.
To learn more about Chris Roast’s work, including details of the EQUS project and advice and resources he shares on spreadsheets, data, analytics and visualization, visit the VeryViz website at www.veryvizsolutions.com