5 Software mindsets we should have towards spreadsheets

Introduction

There are a few pros to choosing software over spreadsheets, but fortunately, many of them are down to how the spreadsheets are made rather than the fact that it is a spreadsheet. I will discuss five ways that people should view spreadsheets like software, when creating them or getting them created. By having a similar mindset to a software developer for certain elements, we can end up creating or expecting better spreadsheets, which will help close the gap between a spreadsheet and what we expect from software.

Layout and design

Good software usually has a clear layout and design. Clear indication of fields that need to be completed, tidy layouts, with no unnecessary ‘noise’ in design. It is usually clear as to where to find what you need, and it only shows you what you need to access. Then, in contrast, we see many spreadsheets that do the complete opposite. A mass of columns and rows, with zero indication of what goes where, and no colour (unless it’s over-run with colour). When making spreadsheets, think about the layout, and make it clear and professional. I try to make my spreadsheets look more like software (although there’s not always advantages in doing so) and less like a traditional spreadsheet. This way I still benefit with the advantages of a spreadsheet, but it doesn’t look confusing like most spreadsheets do.

Separate elements

People often separate stuff they shouldn’t in a spreadsheet, and then don’t separate stuff they should. For example, a tab per month for sales entries is not necessarily required. Entering all the data on one tab and then reporting per month can often be way more efficient and allow for more reporting. But then people dump all the columns together in a batch of data, whereas separating the editable data from the calculated data can make the spreadsheet much more efficient, as people are clear as to what is expected of them when completing the data they need to input. When software is made it takes longer to create than creating a spreadsheet, so the developer usually thinks about the end user, and makes the software clear for them (although some clearly don’t). You should so the same when making a spreadsheet. Separate things that need to be separated and combine things that need to be combined.

Hidden ‘back-end’

When you use 99% of software systems, you enter the data, and you see the results of that. You hardly ever see the workings. And if you did, you probably wouldn’t understand it anyway. So, why is it different with spreadsheets? Why do I need to see the 8 helper columns you used to get from A to B? Unless it is vital I see these stages, hide them. When you buy a spreadsheet from me, I estimate that you only see about 33% of what s going on. 67% is usually hidden away behind the scenes. Not because I’m hiding anything, but because it’s not relevant and will only confuse the issue. If there are elements that are useful to know, they will be displayed somewhere for you to see. Don’t confuse the end user by showing them everything.

Security and data monitoring

Let’s start with security. How often have you been using a software package and accidentally over-written some code? I doubt you ever have. OK, now, how often have you been using a spreadsheet and accidentally over-written a formula? All. The. Time. Why? This shouldn’t happen. Even the spreadsheets I make for myself are locked so that I don’t do this. Lock the spreadsheets. All of them. Unlock the data entry cells and lock the rest. This will protect your spreadsheets, even from yourself. As for data monitoring, have you ever used a software programme and you entered a date, and it says something like “That is not a valid date”? That is because it needs specific data to run, and the developer has set parameters to try and keep the data accurate. Very few do this with spreadsheets. So, when the staff enters the date as 29.04.24 instead of 29/04/24, it looks OK in the cell, but the spreadsheet might not include that in formulas. So, when you’re running the report for April 2024, this will not be included. The software would have picked this up, but your spreadsheet doesn’t. Unless you’ve told it to.

Design it to use it

Software is often created to be used a specific way. Then there are instructions on how to use it, and the ‘flow’ of the software should work in conjunction with that plan. This is seldom followed when making spreadsheets. Think about the user and design the spreadsheet accordingly. I make demo videos with all my standard spreadsheets and can do so for the custom ones too. Sometimes the processes are so easy, and the spreadsheet so intuitive, that I don’t even need a video to explain them. However, a mass of columns and rows with random data entered does not lend itself to this kind of flow. When designing spreadsheets, design them with the user in mind, not just what’s easier for you as the creator.

Conclusion

In conclusion, just because a spreadsheet is different to software, doesn’t mean that we shouldn’t consider it as software when making it. There are pros and cons to having software over a spreadsheet in some cases, and the other way around in others. What we want to do when making spreadsheets, is to eliminate some of the cons of having spreadsheets, while taking advantage of the pros. To do this, we need to think a bit more like a software developer when making a spreadsheet. The 5 aspects I spoke of will be a really good start to this new approach to making better spreadsheets. If you’d like me to make your spreadsheets for you, please get in touch.