The problem with spreadsheets...
05/02/2019 11:00 AM
The problem with spreadsheets is that they can store large amounts of data, have a great selection of functions to draw upon, and can automatically generate graphs that give us a tremendous insight into that data. Why is this a problem?
We can change the formatting of cells, have conditional colouring, change the height of rows and widths of columns to create the perfect visual report or dashboard for our managers or clients. And because the spreadsheets become an important part of our business and theirs, they grow and grow. We add columns, we add rows, we add more data sources. We discover macros and some weird programming language behind the scenes. We automate a few data entry steps or reporting steps. We copy and paste huge amounts of data into the spreadsheet. Everything is great. The business is doing great. Everyone is happy.
Then someone asks a question. The data doesn’t seem to be telling the us the same story they have seen in the rest of the business, something doesn’t add up. So you go away and spend time trying to find out what’s wrong. If you are lucky you might find a cell where a formula became a value a few versions ago, a formula has been tweaked by someone else but not propagated to the other cells where it is used, or someone has added a formula to a cell where you assumed it was always going to be a value or a column has been added which breaks a macro. When the spreadsheet is also being used in a multi-user environment the opportunities for rogue data and rogue formulae to penetrate your solution, multiply. Add users having their own copies of the spreadsheet and all of a sudden there’s an out-of-sync data and traceability problem to resolve.
The obvious solution of course is to protect cells and worksheets that we need to restrict access to, adding a password to prevent users changing these settings etc. Reporting deadlines sometimes mean shortcuts are taken and workarounds come into play.
But there is another solution. In the software development world there is “architectural pattern” called “model-view-controller’. In simple terms the idea is that you keep the data, the presentation or views of that data, and the program code that controls how data is presented, logically separate. This creates an environment where a change in any one part of the software application can be updated or changed in a controlled way, for example a function or formula can be changed in one place rather than in many places. What happens in many spreadsheets is that we don’t get that separation of data, presentation and logic. Data is formatted so we don’t actually see the raw numbers and formulae are hidden away on the same worksheet, displaying only the resultant values.
Getting the right solution means stepping back and thinking about what we are trying to achieve. What data do we need to capture, who needs to be involved, how do we process that data, manipulate it, and report on it. It doesn’t necessarily mean a spreadsheet is the wrong answer. However, it could mean identifying unused functionality in another software application we have available or lead to us to purchase a new software solution that meets those requirements.
In a later article we will touch on some of the analysis techniques used to discover and capture those requirements. In the meantime if you recognise the problem with spreadsheets in your own business, and would like to make some changes, then do get in contact.
Kevin Smith MRICS
LinkedIn Profile