Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

Are we spending too much time with Excel?

Despite my love of SQL Server and my respect for Oracle, and MySQL the worlds number one database is excel.  I know that many purists and DBA’s will shudder at me grouping a spreadsheet with a database, but look at this form the perspective of number of hours of users using excel and the volume of data and I am sure Excel would be in pole position.

The problem is that while our users love it IT Professionals constantly struggle to cope with the volume and complexity of data stored in it.  IMHO Excel services, Office Live groove etc. isn’t necessarily the answer, as all of these technologies simply put the spreadsheet in safer and more discoverable places, such as SharePoint, a server or the cloud.

The problem are about audit, accuracy and integration of the data:

Audit.who changed what and when?  in a database proper that can be tracked, in excel compliance is far harder problem to crack.

Accuracy. Do the numbers add up.  My wife is a forensic accountant and in her role she spends days checking and rechecking spreadsheets for accuracy for use in court.  This is a nightmare for her as her office don’t use any standards and each of her colleagues and external members of her team from the major accountancy firms all work differently.

Integration.  Getting data in and out of excel is very easy, so why is this a problem?  Well what part of a spreadsheet do you want to get out or put in?

So what’s a smarter way of doing things? Keep your data in a database, be it an accounts system a data warehouse , ERP etc. and query the data from excel, either natively or via the numerous add ins from Microsoft and other BI vendors.  Excel is then used for add hoc analysis but not for long term data storage.

So why isn’t this happening?  Users, notably accountants don’t want to give up what they see as their freedom to be flexible.  They haven’t been open to the arguments above until quite recently, but they are beginning to see the light.  If that’s not the case where you work, try waggling this article from Accountancy Age under their nose.   The site is also quite a good resource to see what your users are reading about.