Howdy folks. Jeff here, with yet another post on Pivot Tables.

Just kidding. :-)

My good internet pal Craig Hatmaker gives me a friendly ribbing every time I send him some code that does something in Excel that a database could implement much more efficiently. He points out that because he’s in IT, he doesn’t have anyone stopping him from doing things as he thinks they should be done. So he has no impediment to doing what seems ideal to him.

And he points out that you needn’t be from an IT background in order to replicate his type of approach: if you can master xl, you can master databases. (And he should know: check out his amazing Beyond Excel: VBA and Database Manipulation blog, where he starts off with looking at how to use simple things like Microsoft Query to get data, then progressively teaches you more and more every post until you’re using excel to add records to an access database using a table driven approach, so you don’t have to write SQL or update a single line of code. )

I agree 100%. If you can master xl, you can master databases. The problem is, most people don’t master the first. And out of those that do, many don’t choose to master the second. So the majority will continue to shoehorn large datasets into Excel crosstabs, and then do incredibly convoluted things in order to get the equivalent functionality of a PivotTable out of those crosstabs.

And so I’ll continue to build routines like my Unpivot Via SQL and Filter Pivots Based on External ranges to help them manage their lunacy. Even though I know I shouldn’t encourage them.

This reminds me of the conversation in the comments over at Chandoo’s blog in his post on Speedo Charts called Hack together a Gauge Chart in Excel without sweat, but which Jon Peltier would likely retitle Speedo Charts: The Little Chart That Shouldn’t:

• Jon Peltier: Speedo charts? Do you really want to encourage such poor presentation techniques
• Me: Despite the fact that we “shouldn’t really” use dials, I think that if a paying client or monster boss wants them and it’s a deal-breaker, then we best know how to whip one up.
• Chandoo: As long as we have bosses asking for gauges, we will have gauge charts.
• Anonymous Reader: Thank you for you posts Chandoo. Gauge/Speedometer chart is exactly what my superiors asked me to make for next EB meeting.
• Jon: Another happy reader led astray by the gauge chart instructions in this post.
• Me: Here’s a legitimate speedo chart that I’m sure even Stephen Few couldn’t argue with:
  

Explanation for non-Australian or non-New Zealand readers:
Speedos: Gentlemen’s bathing suit, typically in close form-fitting style.
Bonza: Slang for “remarkable or wonderful”
Crikey Dick: expression of surprise

Craig sums this up this tension between utopia and reality really succinctly: “That’s why we work in opposite directions. I do what seems ideal. You do what is necessary given restraints.”

*Sigh*. Afraid so. On the upside, I’ve formulated this into two helpful business rules:

Rule number 1: As long as clients keep shoehorning large amount of data badly in Excel, they will inevitably get into an urgent pickle.
Rule number 2: As long as Rule Number 1 holds true, I will continue to charge an ‘urgent pickle’ hourly rate.

On rule number 1, I’ve told several clients again and again that they need to migrate their 100MB+ spreadsheets to 0.05 MB database tables. I point out that these spreadsheets are inevitably just one in a chain of fat spreadsheets that contain data they use to make up monthly reports for clients. And I highlight that just getting those monthly BAU reports out the door inevitably has become a full-time exercise.
But they won’t migrate this data to a more sensible solution, because:

1. They don’t know whether the benefit of migration will outweigh the cost, and/or
2. They don’t want the hassle of working out how to migrate, and/or
3. They don’t want to master SQL, and/or
4. I’m really crap at communicating this stuff, and/or
5. This is the least pressing of their pressing business issues.

On that second point, I point out that their existing process is nothing but hassle, and that it will be a real big hassle when their spreadsheet finally dies, along with all their data. And on point 5, by the time this becomes the most pressing of their pressing business issues, they no longer have a business.

On that 3rd point, they generally answer “we are Economists/Accountants/Whatevers, not Data Analysts”. I say “Given your business is built around data, you are Data Analysts first, and Whatevers second”.
Cue blank stare.
*Sigh*