When a Spreadsheet Makes Your Head Hurt

When a Spreadsheet Makes Your Head Hurt it's Time to do Something Different:
​
One of the biggest reasons spreadsheets get out of control, is because they are not being used as they were designed to be used. To be specific, Microsoft created Excel to analyze data. Very often folks use Excel to store data. This is not what spreadsheet applications are designed to do.

For data storage, the best tool is a database. The best selling desktop database application on the market in Microsoft Access. Access complements Microsoft Excel very nicely. It is quite easy to move data back and forth between MS Access and MS Excel.

​So when should you move your data to a database environment?

Think "one-to-many". Specifically, learn to view your data through a relationship lens. All data is related. When data becomes too complex for an Excel Spreadsheet application, it is usually because the data relationships have become too complex. A typical example of a "one-to-many" relationship within a dataset can be seen in the diagram above. Characteristics to look for in this diagram are:

1. ONE Inventory item per line
2. MANY weeks of data for each inventory item. This can be seen in the columns titled: "Wk 1 Qty", "Wk 1 Cost", "Wk 2 Qty", "Wk 2 Cost"
3. To complicate things even more, observe the individual tabs for each week of inventory data

Now just follow this data structure design to its final conclusion. By the end of the year there will be one tab for each week of the year, and two columns for each week of the year. In addition, when one starts a new year, it will be necessary to start with a whole new spreadsheet application. In order to compare numbers from one year to the next, folks find themselves searching multiple spreadsheet applications and building an entirely new spreadsheet, where they paste data from previous years so they can effectively analyze the data. When you find yourself doing this kind of work to maintain spreadsheets, it's time to move to the next level of data management.

The second level of data evolution is the local database level. At this level, data from various spreadsheets are often combined and streamlined into one core local database. Just as Microsoft Excel is the "go-to" tool for simple data management and data analysis, Microsoft Access is the "go-to" tool for internal database systems. Access is most often used in that nebulous area where the data is too complex for a spreadsheet application, but does not quite fit into the larger system-wide proprietary databases.

It is this second tier of data evolution that is most often under-utilized. Most offices employ people who are comfortable in Excel. They can build and manage extensive Excel applications. In addition, most businesses spend a lot of money on system wide database applications at the 3rd level of data evolution. But, the second level of data evolution is often left without a "guiding hand". The information being tracked doesn't quite "fit" into the system wide database, nor can it be easily tracked with an Excel spreadsheet application. If the second tier of data is not being properly managed, users find themselves dealing with an uncontrollable situation. This is where Microsoft Access can solve a lot of problems.

Microsoft designed Access as local database tool. MS Access is capable of handling the complex "one-to-many" data relationships in a way that Excel spreadsheets aren't. In addition, in comparison to other database products, MS Access can be learned by lay people. If someone is familiar with Excel, they are most likely able to learn the basics of Access. If professional development is needed, it is usually still more cost-effective than custom software development with other database tools. 

​Michelle Meyer - 01/27/2018

More Data Management Articles

• Yes – Microsoft Access works in a Multi-User Environment
• YES – Microsoft Access Can be Used Securely
• Deploying Microsoft Access in a Remote Environment
• Why You Should Care about RAD and How It Impacts Your Bottom Line ....
• Do you have questions about your own data management project? Contact Michelle.