Tuesday, July 19, 2011

About the export-to-Excel Anti-Pattern

Excel is a wonderful ad hoc data analysis tool for non-programmers, and is also often the first milestone in the software evolution. No wonder export to excel is the most common feature requested for business applications (some people even go as far as not developing a GUI at all, but just write Excel plugins).


The recent CRUD vs Task Based UI touches this issue, but focuses heavily on CRUD - and this problem is applicable to other types of applications (all applications have reports). The problem with the Excel export in any application is that important business activity happens outside the software. So when your users almost "beg" for a spreadsheet export, it's a strong clue that some aspect of the business process is not modelled, and our users likely perform some mundane, manual work - and IT is supposed to automate that!


Some examples I've run into (non exhaustive list!):


  • users were reconciling data from two sources, but it took three-four Whys to figure that out. And even after the discovery and the agreed automation solution I had to promise there will be an excel export feature (more on that later)!

  • users were manually looking for errors - turned out that adding a single filter to an existing report provided the same functionality.



Excel centric business processes indicate communication and trust problems - the business doesn't ask for features, and the developers don't know what their users do with the delivered software. There could be many reasons for that (organizational, bad experience, high turnover, etc.), but beware - and if you spot it, try to act to make the situation better.


I used to joke with users (now I believe it seriously) that my role as a developer is to force them to figure out and document (software is a form of executable documentation of a business process after all) their work processes, and enforce that for the future. Much better than a paper/Word based new hire getting started guide!

However, exporting to Excel is a feature business applications should have, possibly for every list/graph we have in the system. Some of the tasks don't have the ROI that would justify the development of a given new feature/report/etc., and giving users direct access to the data is more enabling (think innovation) than if they had to ask IT every single time they wanted to check something (the more hurdles we put up, the less likely they'll be to check, potentially damaging the business). We should monitor these exports, and look for patterns (potentially across screens), and when a pattern emerges, talk to our users about how we could make life easier for them. A few occasions don't warrant a development project, and we don't want to develop something too early (just like with refactoring - wait for the pattern to emerge before you prematurely refactor to a structure that hinders later development).