Archives

PETE- People for the Ethical Treatment of Excel

Mario

“If you only have a hammer, you tend to see every problem as a nail.” – Abraham Maslow

Before you start posting your negative comments, let me just say that I think Excel is a wonderful tool for business intelligence. Excel’s position for data analysis will only continue to grow in future versions of SQL Server.

With that being said, many people use it as a poor man’s database. In nearly every company I’ve worked or consulted with, someone has a byzantine method for collecting and analyzing data with it. For example, rather than using Access or another database to keep historical data, users will continue to export to Excel and create ridiculously large spreadsheets with data going back years and then complain that the performance gets slower and slower over time. Well, what do you expect? You opened a 150mb file on your computer, and it’s hogging your system resources. The other problem I’ve seen is that users will create a new spreadsheet to contain data collected weekly. You’ll see this huge drill down into folders something like:

Z:\Operations\Order Administration\Reports\Weekly Order Admin Report\M2M BACKLOGS\2009\July\Week 1\ week 1 Product Class 01.xls

Incidentally, there were separate spreadsheets for each of the 20 product classes all exported from the Sales Order Backlog Report (RPBKLG). These spreadsheets are nearly impossible to analyze over time. The user’s typical solution is a monthly and yearly spreadsheet which summarizes their weekly exports. How exhausting! I’ve dubbed this behavior as “Excel Hell.” Needless to say: spreadsheets were never meant for this kind of abuse.

Another issue with using Excel in business intelligence is that although many people have a basic understanding of Excel use, they do not understand the structure of their database. Consequently, I regularly get complaints that the reports I write are “wrong.” Their reasoning is that my numbers do not match theirs and since the company has been using their numbers so long, mine must be wrong. However when I ask how their numbers are derived, I almost always hear, “Well, Bob created this spreadsheet years ago. It pulls from M2M in real time.” This is very similar to the situation I described here (link to Inigo Montoya post). Ugh. So, I have to spend time looking at Bob’s query, which he “wrote” with a drag and drop wizard, to find his error.

Did you know that if a user edits their query in Microsoft Query, it file locks the corresponding tables in the database? I have one executive who insists on doing this, tweaking the queries every so often, and within minutes I get a call from the purchasing department that they cannot add or edit purchase orders in M2M. What fun.

In the spirit of full disclosure, I have to admit that I was guilty of Excel abuse in the past. I created an entire e-commerce ordering solution for an employer using nothing but Excel and VBA. Yes, it worked and was a huge improvement in their ordering process, but it created data storage problems because each order took over 4mb to store. I created this system 10 years ago, and they are still using it to this very day. If I had the knowledge at the time to create and manipulate databases that I now have, I never would have used Excel.

I think it behooves us as IT Professionals to stop the insanity. I suggest forming a new organization PETE, People for the Ethical Treatment of Excel.

Who’s with me and what was the most egregious abuse of Excel that you have ever seen?

Related posts:

10 comments to PETE- People for the Ethical Treatment of Excel

  • David,

    I have been trying to educate users on the use of a database vs a spreadsheet for many years. I am at least wise enough now to start with “Well you really did a great job putting together that spreadsheet and I can see you spent a lot of time learning all the ins and outs, but …” However, I have perhaps been too quick to use a database in instances where a SS would be a better fit!

    Most agregious – a local pizza company with numerous locations and all financials on SS’s that are snail mailed to headquarters and than smooshed together.

  • I’ll support PETE.

    You can never get people away from Excel, but I’ll take 80gigs of Excel sheets over 20kb of Access any day of the week.

    My love for Excel goes deep, but my hatred for Access runs far deeper.

    I use Excel a lot, and in many cases probably more than I should. But, I use it that much because of how many other people around me insist on using it as well. It doesn’t matter what kind of report I build for them or how many different formats I can put it in, every single one of them will have it exported out to Excel. I can spend 20 minutes setting it up in Excel/VBA, or I can spend a few hours getting it set up some sort of reporting service. Since it all ends up in the same place I just save myself the time and effort.

    So rather than spending my time designing reports in SSRS or the like, I spend my time writing VBA code to mimic a report and its subsequent exportation out to Excel. I clean it up, put it in the right format, throw some charts and graphs in where needed, etc, etc.

    The most horrendous abuse I have ever seen in Excel is one that I still work on from time to time that I helped a guy from the ITToolBox.com forums set up. It’s a massive workbook that keeps track of payroll, attendance, vacation, clock-in/outs, and several other things related to HR functions.

    It has 16 forms tied into various parts of it that mimic data entry to save the user the effort of scrolling down the pages. There is a sheet for every employee in the company, each one listing every day of every week of every year that they have worked here.

    The thing is massive and it’s being done by a guy with very, very little programming knowledge. He does what he can, sends me an email with his problems, and then runs off with his answer using forums as best he can to fill in any blanks I may have left and fill in details that I don’t

  • Andrew

    I just read this. Funny stuff and so true. We have spreadsheets all over the place here. They track so much stuff outside of M2m because it’s hard to customize.

  • […] an aside, I have talked in the past about Excel Hell and often that hell is caused by spreadsheet exports from M2M or other ERP systems. For example, […]

  • […] in my company was tedious and time consuming. As is typical, the method used to track this was Excel Hell. Every week someone would run the Received Goods (RPREGO), Inventory On-Hand by Location (RPINOH), […]

  • […] requirement puts him into what I call “Excel Hell.” This is one of the reasons I formed PETE. They use ODBC spreadsheets to pull their data from M2M. As I watched, he went down his long list […]

  • […] back to the accounting folks. This kind of behavior is mind-blowing to me as much as Excel Hell. Users can be almost robotic in their actions. They don’t think about their actions nor question […]

  • I’ve seen these same user issues with Excel queries to M2M. I created a SQL Stored Procedure, determined the tables the users really needed, and created a subset of those tables (eliminating fields they usually do not use or understand) with an underscore prefix “_SOMAST”.
    I run a scheduled task on the hour which calls the stored procedure, and moves these files out of the production environment onto a different instance of SQL. Now they cannot affect production, and still can do all the runaway SQL queries they want;)
    Just a thought!

  • bob

    worst i have seen was a user rung me up to ask how many characters an excel cell could hold. He was typing a letter and had reach the limit. I was gobsmacked.

    Sharepoint FTW, lets replace excel except for statistical purposes. Not as a database tool

  • youssef

    This is an old thread, but I just discovered Dave recently 🙂
    I had a project where I converted an excel run process ( multi users) to MSAccess
    The most interesting thing I saw was that within a Tab, the headers for the data would not only repeat as you scroll down, but would also shift and change. Fun!

Leave a Reply to Jason Griffith

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>