Some very sophisticated Excel solutions have been developed by businesses over time. They typically range from individual user, single purpose workbooks, to departmental or functional purpose workbooks. Oftentimes the workbooks have links to other workbooks across the network and many companies have operated their businesses for years using Excel in various ways. Like any software solution, it’s a difficult decision to abandon it and replace it with a new system. Knowing when it’s time and deciding before things become to painful is key.
The moment you need to share the workbook data and allow more than one person to edit data, things can get messy. Network and/or file level permissions can help, as can permissions that can be established down to the row, column, cell level within a workbook. Although these measures can help, it’s far too common that data can get deleted, formulas lost or altered and workbook links broken. Recovery from these risks can be time consuming and stressful.
Training new people to properly use existing workbooks can be difficult. Documentation helps but that takes discipline to keep current and is only as good as the author. It may not be immediately apparent how to use a particular workbook to accomplish some business goal without some ongoing support which consumes valuable business resources in the process.
As the volume of data grows over time, this can impact performance, especially where many formulas and validations are used or linked workbooks and multiple user scenarios might be involved.
Databases in general solve many issues.
● A large and growing amount of data
● Problems with unorganized and confusing data relationships
● Managing multiple people working with the same data at the same time
● Concerns over data integrity or data security
Microsoft Access is a good candidate for a variety of reasons, including the fact that the migration of Excel data to Access can be relatively simple as Microsoft does a good job of interactions between the various Microsoft Office products. Even after a migration to Microsoft Access, the reality is that Excel is still commonly part of the solution. Excel is still a popular ad-hoc “reporting” and analysis tool for certain things and the ability to seamlessly send data to Excel for that purpose can offer businesses the best of both worlds in solving the problems experienced in complex Excel solutions but still having Excel as a tool when it can be most useful.