Stéphane-Robert Langer

You may remember the day you were introduced to Excel’s pivot tables. It might have happened through a co-worker, a book or an online tutorial but the effect was probably along the lines of: “Wow! How did I ever analyze data without them?”

Well, I sort of went through the same experience again several months ago reading about Dermot Balson’s Merge pattern (sample file here) – thanks to a post by Jim Johnson. I would love to elaborate some other time on Dermot’s idea of applying the concept of design pattern to spreadsheets but I will delve here specifically into his merge pattern, which makes use of Excel data tables.

According to Microsoft, data tables allow you to “test different input values for a formula without having to retype or copy the formula for each value” (Q282852). This is typically useful for sensitivity analysis, but it also works great for running complex calculations against multiple records in a data set.

I’ve argued before that most (structured) data benefits from being stored in a a flat file format. One of the downsides of doing so, however, is the amount of work involved in running a complex Excel model against all rows in a flat file. If you don’t intend to migrate your model to a relational or multidimensional database, then data tables as a wonderful way of achieving just that – free-form spreadsheet computations applied to structured data.

Additional links of interest

• An awesome powertool buried in Excel
• How to use Microsoft Excel data tables to analyze information in a database
• How to create and use two-input data tables in Microsoft Excel

Following up on a somewhat theoretical introductory post related to desktop data management, Here are 7 habits which which should prove useful when approaching any desktop data-related task. These will be refined based on the discussion you and I will have here, so let me know what you think.

I was actually shooting for 10 commandments, but 7 habits sounded good, too. Seven should be enough to start the discussion anyway, and leave room for expansion without making the number ridiculously large should we end up adding a few.

Now remember. This is supposed to apply to desktop work so let’s not wander off too far into foreign keys, surrogate keys or normal forms territory;-)

Here we go:

1. Any data processing task takes you through the steps of organizing data, processing it and presenting it. Acknowledge these steps in your workflow and organize around them.
2. Do the hard work first. Looking up and presenting information is both easier to perform and more valuable when it is base on a well-organized data layer.
3. Organize your data as flat files instead of free-form tables. The flexibility of spreadsheets makes them both a blessing and a curse, often leading you to design the processing and presentation layers first and the data organization layer last. Organizing your data as flat files may require some more work upstream but will pay off handsomely downstream. See #2.
4. Strive for one source of truth. An important piece of information should have to be changed in one place only and cascade from there.
5. As much as reasonably possible, always work with a full dataset that includes domains contiguous to the one you’re currently working on. You’ll be much better off keeping too much information and identifying records appropriately than discarding what you still think you won’t need – but will.
6. Don’t design for exceptions. You want a few standard tools that abide by the principles above while allowing you to handle exceptions, not multiple unmanageable tools that were each designed to handle a specific exception.
7. Use identifiers. Referred to as Unique IDs or keys in database jargon, the important idea to remember is that you want a way to lookup a particular item of data at any point in time and distinguish it unequivocally from its neighbors.

Each of these items deserves several posts and I will be following up on each – hopefully with a mix of opinions, examples, tool-agnostic tips, and tool-specific tricks.