What Elements Are In a Strong Spreadsheet? (Contiguous Data)

QUESTION

What are the elements of a strong spreadsheet?

This question came from Judie Knoerle of Red Cup Presentations. Judy and I attend a networking group and I spoke highly of a friend’s spreadsheet. My friend had asked me to look at her spreadsheet and  “look at” often means “fix” but there wasn’t much to fix. Instead, there was opportunity to take the data into more advanced levels of Excel.

After the meeting, Judy asked me what was so right about the spreadsheet.

A conceptual answer is in this blogpost Don’t Work So Hard. Today, we’ll take a real-world perspective.

ANSWER (Long Answer)
There are a lot of elements that can make a spreadsheet exciting. But in this instance:

All of the source data was contiguous; i.e., all in one place; and all analysis was tied to the source.

A Contiguous Dataset Is The Sun In Your  Business Analysis Solar System My friend, was given data that was a solid block of rows and columns. And she worked her analysis from that source.

In situations like this, there is temptation to peel the data down into categories and put them on separate tabs in an Excel document. Initially, it makes sense to create separate tabs for categories like months or sales regions but it creates problems with analysis. It’s also among the Top 3 Spreadsheet Blunders.

Let’s look at some examples …

Actually, let’s not look at examples yet!

We have to set things up. This is lengthy but this is where our real work happens. If I jump into the spreadsheet how-to, then what are you left with? A bunch of random tricks that don’t amount to crap. So, let’s set his up.

THE CONTEXT FOR OUR SPREADSHEET & ANALYSIS

We have a hypothetical catering company and a spreadsheet of transactions for food supplies, office supplies and travel expenses. The fields being tracked are:

• Month
• Item
• Transaction Amount
• Vendor
• Authorized By

DETAILS & BUSINESS RULES

Possible Categories That Our Data Can Be Organized By

• Month
• Vendor
• Authorizing Manager
• Purchased Items
• Office

That’s the data. Now let’s start the analysis with the #1 question for any analysis:

WHAT DO WE WANT TO KNOW ABOUT OUR BUSINESS?

NOW! LET’S LOOK AT THE DATA

We have 3 tabs: Vendor A, Vendor B, Vendor C. Great.

No! Not great. Awful. And the crowd hates it.

What’s got the crowd in an uproar? 
First Problem: We want to add the office locations. We’re going to have to do it 3 separate times.
Next Problem: Juan-Carlos is also JC. We’ve got 3 places to make all entries either JC or Juan-Carlos.
3rd Problem: We have the Vendor data separated, now we’ve got the Monthly data to pull together via formulas across 3 ranges.
4th Problem: More formulas are more opportunities for errors.

We’ve got to stop this right now. So …

LET’S SEE THE BETTER EXAMPLE

Take notice that we have a Vendor Column and we’ve added columns for Office and Vendor Name.
WOAH! We have pivot tables available to us now! And here’s an interesting discovery in the pivot table below.
Who the heck is Randall? Why and How did he approve $422 worth of spending? He’s not one of the managers who can approve transactions.

The next Pivot Table (below) shows the manager approvals and breaks them down by office. Enid and Todd are in the West County office and together they approved $39,821 across the 3 vendors. And there’s creepy ol’ Randall at the bottom.

THE BIG FINALE: PIVOT CHARTS & SLICERS

With contiguous data we can create a pivot chart and add slicers, and make life so much easier.

The graphic above shows January through March transactions and the transaction amounts by manager and vendor.
–  The slicers are the green and gray boxes surrounding the bar graph. When we select one of the options, they filter for what we want.
–  The bar graph is our pivot chart, it adjusts depending on what we select in our slicers.

Notice: Randall’s name is grayed out. His mysterious approval happened in November. The graphic below shows that he approved purchase of computer monitors. The #N/A in Office is an Excel error because Randall is not a manager of anything.

[maxbutton id=”8″]

CONCLUSION

This long answer to Judy’s question is: My friend’s spreadsheet was so powerful because she left all of her data in one place, making that the sun in her galaxy, and all of her analysis came from that source. Let’s summarize.

What did we get from contiguous data?

• We had one place to correct inconsistent data
  • Juan-Carlos = JC
• When we have multiple categories, multiple spreadsheets create a mess. ONE sheet is divine.
  • We can’t have 12 sheets for Months, 3 more sheets for Vendors, and 4 more sheets for the 4 Offices. Let Excel filter that when we need it.
• We have an easier time of digging into weird stuff.
  • Who the heck is Randall?
  • What did Randall approve and when?
• Pivot Tables are possible with contiguous data.
• Pivot Charts and Slicers are possible.
• We limit our use of formulas and thereby limit opportunities for errors.

Judie, there’s your long answer as to why a good spreadsheet has contiguous data. I hope it helps. I purposefully didn’t get deep into the how-to because that’d be even longer. Most important is the understanding of what’s possible when we treat a central source of data as the sun in our data universe.

 SEE A DETAILED VIDEO HERE AT MY YOUTUBE CHANNEL:

Sun & Planets Image Credit: Idea go via FreeDigitalPhotos.net