A very common request, at pretty much every workplace I’ve ever been, is being able to export data into an Excel spreadsheet. This is usually quite difficult for a number of reasons:
- Excel is a wonderful tool for end users who make spreadsheets with formulas and charts and whatnot.
- When I am using it for financial purposes, I love it.
- But for programmers, it sucks. It has sucked since at least 1998 and perhaps all the way back to version 1.0.
- I’ve seen many ways of getting data into Excel:
- Using VBA (yuck!)
- using CSVs (bye-bye formatting and formulas)
- installing Excel on a web server and (trying to) make it communicate with an ASP/ASPX page (maintenance nightmare)
- doing plain-text search-and-replace in XLSX files (they’re just XML).
- designing for copy & paste (I like this one best).
Usually the problem is that:
- There’s a spreadsheet template with a complex set of charts and calculations.
- There’s a plainly-visible section of the spreadsheet into which the user needs to input data.
- There’s no easy solution for getting the data from the app into the spreadsheet.
- This leads users to spend countless hours re-keying data from an app into a spreadsheet.
Yesterday I heard that an account manager was spending the better part of a week preparing 49 spreadsheets for 49 clients. She was re-keying pieces of data from various parts of a web application into a spreadsheet. The spreadsheet contained charts, and it would be converted to a PDF to share with the client. In a good day, she could get 7 done.
My typical solution for this kind of problem is to:
- Request a copy of the spreadsheet template.
- Design a web page from which data can be copied from and pasted into the spreadsheet.
Because we use Ruby on Rails, it was simple (like frickin’ 90 minutes from concept to production deployment) to make a special one-off web page which incorporated all of the key figures (read: reused the code elsewhere in the app). Now the account manager doesn’t have to bounce all over the application grabbing numbers; they’re in one place and can be copied and pasted — literally: if you copy and paste <table> data from a web page, Excel usually interprets the <td> elements as cells.
One thing I found was that, in most browsers, you can’t highlight columns like you can in Excel. If you try to drag-highlight rows in a <table> on a web page, the browser will usually highlight all adjacent rows, which is usually more data than you want.
Solution: float 2 tables. view the solution here
Now the account manager can produce her client reports in very little time — simply by copying and pasting. Note: In reality, the data and charts are far more complex than what you see in the example below.
It’s not normal to go to this kind of effort for the sake of a user, especially an internal user within one’s company, for many reasons:
- This user is an employee of the company, and internal-user satisfaction isn’t widely emphasized and encouraged. (At least not at any company, save my current company, that I’ve ever worked for.)
- It’s hard to justify the effort required to do something like this, especially when users (account managers) have been using a certain process (albeit cumbersome) for years.
But after reading an excellent HBR article on internal customer satisfaction at Google, I completely changed my thinking, for several reasons:
- My account managers might not be paying customers like our end users, but they are users nonetheless and their needs are important
- If they can accomplish a week-long task in a day, then that saves the company money.
- If they can whiz through a repetitive task instead of slowly grinding through it, then that is employee satisfaction.
- I’m the IT Director. If I don’t suggest, allow, demonstrate, and encourage this kind of thinking…. who will?