Using Excel for Databases


Excel for Databases.  It is not at all uncommon for people to use Excel when working with databases.  They often just put the data in a column of cells.  This has its advantages and disadvantages.

Some Reasons Users Prefer Using Excel

  • More people are more familiar with Excel
  • Most people find it more user friendly than DBMS
  • Excel is automatically included in Office and Access needs to be added on using some extra money
  • Seems easier when there are needs to do a lot of calculating since cell formulas and a lot of functions are built in
  • People are more aware of the abilities to make grids, charts and do certain types of data analysis in Excel


Some Reasons Why Using a DBMS is Better

  • Generally configured better for handling larger amounts of data
  • Generally configured better to make sure data isn't inadvertently altered
  • Data doesn't need to be so close to fitting a flat file configuration
  • Easier to interrelate data in different sources - though requires more depth of knowledge
  • Some people find it easier to make use of email addresses in Access over Excel to send group emails
  • Generally easier and better for someone to write queries to retrieve only desired data
  • Generally easier to develop reports

Microsoft has made it fairly easy to move data between Access and Excel, particularly once one has learned about these features.

These lists will develop more over time.

SFd Forecaster.  Years ago, I started working on a database system that would also do demand forecasting.  I developed the system to track and rely purely on inventory data over time.  This data could then be used to forecast future demand.  While this is definitely not a forecasting course, we had the models look for several underlying factors in the forecast.

  • some underlying signal/directions
  • trends
  • cycles

So the predictions would generally do quite well.

I developed the prototype in Access for demos with the sense that we could often sell the prototype directly "off the shelf" to smaller businesses.

One of the very first larger businesses we interacted with was a well known Japanese auto manufacturer.  They were tracking hundreds of thousands of parts.  In addition, they needed to have replacement parts for models and parts that they had stopped selling years ago.  Even in this setting they had all of their data in Excel spreadsheets.  Doing the calculations required hours considering the huge number of parts.

There is much more I could say about this.