Why We Use Spreadsheets
Spreadsheets are the backbone of business and companies usually have at least one mission critical spreadsheet in their organization. They are the tool that allows businesses to adapt quickly to new priorities and a low cost option for reporting and analysis. While they are indispensable, they also have drawbacks. They are manual, vulnerable to user error, not secure and can become unmanageable. So why do we use them and when should we consider replacing them with something else? Here I discuss two reasons we use them and when to consider a change.
The Starting Line
The first reason we use spreadsheets is as a starting place for tracking. Businesses have lots of informal processes but often they grow to point where they need to be tracked or measured. The logical place to put this information is a spreadsheet. It’s the first place people turn because they can do it themselves and they don’t need a programmer. This will start on one person’s desk and they will use just the first tab. Over time they add columns of data and more tabs. The bigger the sheet, the more likely they will share it with others for updating and viewing. They use the sheet because there isn’t anywhere else to track the process.
The Dashboard
The second reason for using spreadsheets is that businesses need a place to see their metrics combined in a single view. Companies often have their data stored in different systems and those systems don’t talk to one another. Furthermore, these systems are not able to report on the information the way the business needs it. The way a business handles it is to take downloads from the various systems, manipulate the data and combine it all into one sheet. It’s manual and you don’t need a programmer. You just need the grit and determination to copy, paste and pivot data. What’s great about Excel is that once you have the metrics you want to see you have some low cost options to present it. You can create charts and graphs. You can make the data look better by formatting the cells with shading and color.
Growing Pains
How do you know when it’s time to consider upgrading the spreadsheet into a database? Here are some signs.
Formulas Not Working: While editing the spreadsheets an important formula did not carry over properly or was hard coded. These can be hard to catch and the business can lose money because they made decisions on incorrect information.
Data is Lost: Since the spreadsheet is flexible, it’s easy to lose information because it was overwritten by accident or even deleted.
Using Excel as a Database: Excel has great tools for summarizing and searching data but it is not a database. If you’re using a lot of pivot tables or function like VLookup and Match, it will become increasingly difficult to get the numbers you want easily.
Too Manual: If it’s taking a long time to slice and dice the data the way you want, then it may be time to switch. This usually happens when you’re trying to combine data from different sources or even just manipulate a file from a 3rd party. At some point, it’s just too time consuming.
Complicated Macros: If you are an experienced user, you have probably created macros to manipulate data. The problem with macros is that it can be tricky to create iron clad processes that can handle the data properly. Even experienced programmers will tell you that they can be problematic and it often has to do with the nature of spreadsheets. Since they are so open, users can put any kind of data in there and also move things around. These actions cause macros to fail.
Upgrade Options
If you find that you are outgrowing a particular spreadsheet, it’s a good time to reevaluate what you are using it for. You’ll find some ideas in my last blog post likening Data to a Garden. After that, here are some options to consider for upgrading.
Migrate to Access: Access it Microsoft’s desktop database. It can be a great next step from Excel. You can control data entry and create summary information. It is also more secure.
Create a Database: If this is a mission critical spreadsheet, you could move the process into a custom database that you own. You will need a programmer but this solves many problems. The data will be secure and the data entry controlled. You will have much better options for reporting and backing up your data. It would be more robust than using Access.
Modify an Existing System: If you already have internal systems, you could have them modified to house this orphan data.
You can do a lot of this on your own! If you find that you need some assistance or just don’t want to do it yourself, maybe we can help. Reach out for a free consultation.