Pros and Cons of Using Spreadsheets

If you’re thinking about using a spreadsheet for your business, there are a few things to consider before doing so. As much as I love making spreadsheets, and as versatile as they are, there are occasions when I advise against spreadsheets. I only want to create spreadsheets that are beneficial to the user, so it’s important to look at some of the pros and cons of using a spreadsheet. Let’s start with some of the cons.

Cons of Using a Spreadsheet

1. Large volumes of data.

This is possibly one of the biggest issues, the volume of data that can be handled. If for example, you have 100 lines of products, and you sell around 50 of each in a year. I would need to do some fairly large formulas against that data to produce the reports required. Now 100 lines of products, would produce about 5,000 lines of sales. This probably wouldn’t be an issue for Excel, depending on what analysis was needed. On the other hand, if you had 100,000 lines of products, and sold about 1,000 of each, that would need 100,000,000 lines of sales. That would crash Excel if you needed to analyse that in detail. There are possibly ways of using two spreadsheets, one to condense the original data into a more manageable layout for the main spreadsheet, but this would need to be advised on a case-by-case basis. The general rule of thumb is that if you have loads of data to analyse, don’t use spreadsheets. What I mean by loads would be determined by what you wish to do with it.

2. Drag and Drop or Editing Calculated Data.

There is a limitation with spreadsheets that don’t have macros. A macro is a form of programming (Visual Basic) done behind the scenes in a spreadsheet. There are pros and cons for using that, but assuming that there is no programming, another issue with spreadsheets will be the comparative limited functionality. Without going into too much detail, programmed software uses a front end and back end. The front end is what you as the user will see, the back end does all the heavy lifting, but you won’t see it. So, with software you can call up some data, edit it, and then close it. That then does what it needs to, and the necessary records are updated. With spreadsheets (with no macros) it is different. Each cell will need to be a data entry cell (unlocked) or a formulated cell (locked). This means that you may enter data into one tab, which then shows in a report on another tab. You can’t simply click on the report, open that data, and edit it. You will need to go back to the source of the data (the data entry cell) and change it there instead. This is not a massive issue, but it is worth understanding this and how it will affect your spreadsheet and process. Also, as the cells are formulated, you can’t just drag and drop info like you can on some software solutions. You will need to simply change the original data to make the changes which will then show in the relevant formulated cells.

​3. Integration

This is a tricky topic, because almost all software programmes say that they can export to Excel. Now, simply exporting to Excel is very different to integrating with Excel. If you have a ready-made spreadsheet that is set up to work in a certain way, getting it to communicate successfully with other programmes can be tricky. I normally prefer using CSV exports, to then use as raw data for the spreadsheet. This way you can see if things change and if the data is still usable. If you are looking for automated processes between a few existing software solutions, I would recommend looking for software that can do that. Even then there are potential risks if the platforms don’t help each other out, but it is probably still the safer route to use.

Pros of Using a Spreadsheet

​1. Flexibility

I see a blank spreadsheet as a blank canvas. I can do what I want with that and be creative to achieve what is needed. This is one of the massive advantages of spreadsheets, they are set up to create what is needed, with relative ease compared to programming. There are some boundaries, yes, but the main one is the ability of the user. I have made spreadsheets for data analysis, CRMs, project management tools, process trackers, dashboards, deadline alerts, and many more. Some of the best spreadsheets I have made have been born from a conversation that started with the words, “I don’t know if you can do this, but here’s what I need”. I would seriously suggest that whatever you want to do, a chat with me first would help. Spreadsheets are way more flexible than people thik, and a simple chat could determine what is possible. If it’s not a viable solution, then nothing is lost. If it is, there is plenty to gain.

2. Cost

The cost of software of any kind is largely based on the time it takes to create. There are some software developers that use components to build solutions, so they may be quicker, but true custom software could take years to develop. Most of my family are programmers, and they spend months or years on projects, whereas I spend weeks at the most. This then has a massive impact on cost. To give you an idea, I once did a project for someone which ended up costing about £1,200 once I had done some upgrades. He took that (which was part of the plan) to a developer to ask what it would cost to make that exact solution as a software programme. I don’t recall the exact figure he was quoted, but it was over £20,000. From what I could gather, that was using components. Now, you may be in a position where you can’t use a spreadsheet, so you have to use software. Having said that, many people assume a spreadsheet can’t do the job because they can’t do the job in a spreadsheet. If you want to know what is actually possible, I will be happy to advise you. Another thing is that I usually give a fixed price for the spreadsheet before making it (if possible) so you know what you’re in for. Most software developers charge a rate, and the time needed to complete the project could vary drastically, so you don’t really know what it will cost until it’s done. Even existing software solutions can prove to be expensive. Most are charged per month, which means it’s an ongoing cost as long as you use it. I charge once for a spreadsheet, and then you can use it for as long as you like.

3. Ease of Use and Visibility

Yes, there is a learning curve involved when someone has a new spreadsheet to work with, but I find that it is much quicker than a new software programme. Most people know how to fill in a spreadsheet, and how they work, so most adapt quickly. I usually do one final video at the end of a project, and the client goes away confident to use the spreadsheet. I also make spreadsheets with the client in mind, trying to keep the effort required as low as possible, while increasing the productivity of the spreadsheet. I’ve heard people rejecting software packages because they can’t seem to understand them. Most get how a spreadsheet works and are quite competent at using them. As for visibility, what I mean is what is happening with your data. As I mentioned before, you could add something in a software package in the front end, and it is whisked away to the back end. Yes, you could search for that data, but there is often a case where it just ‘disappears’ only to surface again in a report somewhere. With spreadsheets, the entered data is always there and visible. Yes, there are calculations going on in the background, but you can see where you entered data, and you can see what effect that has on the rest of the spreadsheet. It is often more understandable to see where the data goes in, and see what effect that has, than just simply feeding data into a bottomless pit. This also helps to see what data is included, what if any is missing, etc. Some software programmes make it so hard to spot mistakes, as the data is entered, it disappears, and then you have a month end report with no acknowledgement of what is included. Some see this as ‘automated’ whereas I see this as a potential issue.

So, there you have it. Some pros and cons of using spreadsheets for your business. There are others, depending on what you want to achieve, and some of these may not apply. If you really want to know if a spreadsheet is a suitable solution for your business, please get in touch. I am happy to listen to what you need, and then I can advise you accordingly. The right spreadsheet could make a massive difference to your business, and I’d hate for you to miss out on that because you were misinformed or didn’t have the right advice. If you’d like to know what can be done for you, please get in touch.