Two Ways to Gauge if Someone Really Uses Excel

I was on-site at a client for a couple of days last week and, during a break, got into a discussion with their resident CRM system expert. This fellow had managed some crazy stuff in Siebel for a major credit card company and was now working with Salesforce.com and a mishmash of other systems for a nonprofit. We got to talking about data, reporting, and analysis. Inevitably, Microsoft Excel came up — a tool of which both of us are huge fans.

Do they use pivot tables? It’s not just if they’ve ever seen a pivot table or struggled to create one in a pinch. It’s, when asked the question, “Do you use pivot tables?” their reaction involves some sort of significant change in facial expression along with a forceful response. Something along the lines of, “Oh my god, YES!!! I don’t know what I would do without them!”

Do they use VLOOKUP? Again, the key here is not whether they have heard of VLOOKUP. It’s whether they see it as a function that they simply could. Not. Live. Without!

What is interesting is that both of these features of Excel are database-like functionality. Pivot tables are, basically, a way to do semi-dynamic SQL GROUP BYs (or basic aggregation in MS Access). VLOOKUP is a poor man’s SQL join. But, both are fast (for pivot tables, I’ve learned that I can start the pivot table wizard and then, 99 times out of 100, simply click Finish without going through the intermediate wizard steps to get what I want) and, if data arrives for analysis in Excel, then both are native to the environment the data is already in.

One other note on these features is that, if someone is to the point where they automatically and naturally use pivot tables and VLOOKUP, then it is practically guaranteed that they use string manipulation functions (&, LEFT, RIGHT, MID, LEN, FIND) and conditional and comparison functions (IF, AND, OR, SUMIF). These are every bit as important as VLOOKUP, but, in my experience, they come before or with a VLOOKUP addiction; seldom do they come after.

There is a separate dimension of Excel usage that does not get covered by this test, and that is Excel’s data visualization capabilities. As a matter of fact, up through Excel 2003, pivot tables were particularly difficult to use for charting — charting pivoted data creates a pivot chart, which, while dynamic and manipulable, is pretty ugly. Excel can be used to generate very professional, impactful, clean visual representations of the data. Unfortunately, the tool’s defaults do not do this! But, that is a topic for a whole separate series of posts!