5 common Excel problems (and what to do about them) Many businesses rely heavily, or even entirely on spreadsheets for their data storage and processing.5 common Excel problems (and what to do about them)
Many businesses rely heavily, or even entirely on spreadsheets for their data storage and processing. And yet spreadsheets can have serious problems associated with them.
Many users report a “love hate” relationship; their spreadsheets serve a purpose, but require a lot of investment in time and energy, often go wrong and sometimes don’t have the correct safeguards to prevent security breaches.
Here are some of the most common problems with spreadsheets and what you can do about them.
1. Time Wasting
We spend an awful lot of business time updating spreadsheets. According to research by the data analytics company Alteryx Inc., there are 5.5 million professional spreadsheet users in Europe, each spending on average 28 hours a week working in a spreadsheet. But if that isn’t bad enough, 9 of those 28 hours are spent duplicating work as data sources are updated. Overall, that’s 2 billion hours of duplicate work at the estimated cost of £48 billion a year in Europe alone! That’s the cost of having to manually update spreadsheets.
What’s the solution to this costly procedure? The structure of a database allows you to manipulate large chunks of information easily and quickly, cutting down on the time spent doing duplicate work.
It’s easy for human errors and mistakes to creep into a spreadsheet. 90% of spreadsheets contain errors. By default, Excel accepts whatever you type into a cell. You can set up validation constraints with lists of acceptable inputs, but this can become difficult to do, especially when you have a lot of data and multiple data sets are used.
One way of solving this problem is to use a database, as you can strictly control acceptable data inputs. Relational databases set rules for the relationships between data sets, and so pick up errors such as misspellings or incorrect numbers more easily.
3. Multiple Versions
Because only one user can work on a spreadsheet at any given time, when sharing a spreadsheet between multiple people you often run into the problem of people creating multiple versions of the one spreadsheet, which then have to be incorporated manually. This isn’t very time efficient, and can be quite laborious.
However, a database can be used by multiple people at the same time, provided that different people work on different parts of the database. Parts of the database can be “locked”, enabling multiple users to work on multiple areas at the same time.
While security can be added to Excel spreadsheets, it has significant holes. Namely, spreadsheet security is more concerned with maintaining the structure of the spreadsheet, rather than the data itself. The data in the spreadsheet can usually be changed, and spreadsheets can also be passed on to those who shouldn’t have access, or can be hacked.
On the other hand, databases have much tighter security. You can require all users to verify themselves, use passwords and you can set up multi-level access for different parts of the database.
Spreadsheets can be slow, particularly when they contain large amounts of data. Excel uses up a lot of memory space, and when a spreadsheet gets to a certain size this can not just slow down the spreadsheet but also any other applications that require memory space.
The solution is to use a data management system that doesn’t require large amounts of memory storage, and can find data simply so that it takes less time to find and manage data. This is another area in which databases, which use queries to find specific information, are superior to spreadsheets.
If you find yourself facing any or these problems with your spreadsheets, consider moving from Excel to a database. Wirebox have experience designing and delivering custom databases, as well as updating your existing data systems and managing the transition from spreadsheet to database. Our team have a wealth of experience to draw on, just contact us, we are happy to discuss your needs.