BFTBY: Keep Your Data In One Place, Don’t Work So Hard!

BFTBY: Blogging From The Backyard

This weekend a friend asked me to take a look at her spreadsheet for a bit of manicure and polish. When I received the document, it was beautiful. So, the polish that I had was easy to apply. I can’t share the data with you because it has personal information, and I won’t generate dummy data to make a random example. I have a better idea.

Let’s go into the backyard, have a cigar, and sketch out a few spreadsheet basics that made my friend’s document so wonderful.

 

THE SPREADSHEET, THE BACKGROUND

The spreadsheet had survey responses about workplace attitudes, and details about the respondent’s role. My friend needed graphs and summaries of the various results. When I opened the document I saw:

• One worksheet was just straight data. Rows & columns.
• The other worksheets were her numerical summaries and graphs.

She really didn’t need much from me. Therefore, this was really a perfect opportunity to build on what she had, and not “fix” something. It was ready for polish, and didn’t need a full-on manicure.

 WHY WAS HER SPREADSHEET SO FUNCTIONAL AND EFFECTIVE?

The key to her spreadsheet was that she left the source data in one place and worked off of that; i.e. her source data was contiguous. When the source data is left in one place (or merged into one place) there is so much available. You can allow Excel to do your dirty work.

Here’s The Big Deal

It makes intuitive sense to separate things out and make worksheets for unique categories like:

• or whatever else you’re tracking that has multiple categories. But …

We open the door into data hell when we start making multiple tabs. Smell that brimstone as soon as you make tabs for January, February, March, April … It’s not funny and it doesn’t smell good. (It’s also a sure way of having Krampus come visit you for Christmas.)

There are 2 primary ways that people separate their datasets:

1. multiple tabs
2. blank rows/columns

I see this all the time, and it makes summaries incredibly hard if not terrifying.

THE BEAUTY & BENEFIT OF CONTIGUOUS DATASETS

 

 With a contiguous dataset the data can be sourced by Pivot Tables (click the link for a Pivot Table video tutorial); you can make tables and Data Models; you can use more complex functions like VLOOKUP.

 

 Instincts have us want to put in blank columns and rows. Maybe the blank row in the above sketch separates March from April. Maybe the column separates First & Last Names and Mailing Addresses. If a user is just reviewing the list of names, it makes sense to want to slide irrelevant information out of the way.

Again, it makes sense to separate things like this. So, if you’ve got blank rows in your spreadsheets, you’re not crazy. It’s just not a good way to develop spreadsheets and manage data.

Because of this disjointed set-up, if we want to create any summaries later, there are 4 separate regions that create problems for the Pivot Table wizard. Excel Tables don’t like gaps, either. In short, the things that work the hardest for us in Excel aren’t given a chance.

 The sketch above has 2 sections. The upper section shows the typical multiple tabs for each category. In this case, we have Vendor A, Vendor B, Vendor C. The lower section shows a solution to the need for multiple tabs: create a Vendor column.

And guess what? When the data is consolidated, we can work from the one source and have Excel do the heavy lifting in the creation of dashboards, and avail us of analytical tools like slicers and pivot charts. In this example, we could use SUMIFS or a pivot table to get at details about Vendor B, compare the 3 vendors, and generate reports.

CONCLUSION

When information is separated, in order to get summaries, comparisons, trends, counts, and other relevant analytics, the solutions are manual, detailed and error-prone. I also think this keeps pivot tables so mysterious: a person’s source data isn’t in the right condition and the pivot table can never happen.

Let’s look at one last thing to drive this point home.

Let’s say, we have an annual budget and want to generate a comparison of actual and projected savings. The data hell method is separated on monthly worksheets and we’d need:

January Savings + February Savings + March Savings  … + December Savings

There’s too much room to add a month twice, skip a month or, change something and have the whole formula error out. Now we’ve got data quality worries in addition to complicated formulas.

However, using contiguous data we’ve got multiple ways to extract the details we want–and trust the result! And that was the polish I added to my friend’s document. I just replaced her manual charts with Pivot Charts and Slicers (Sorry Mac users, you don’t have these), while working from the same beautiful source of contiguous data.

(By the way, did you see the rabbit in the first picture of the back yard? If you want to, click on the image for a larger view)

Now, let’s get out there and keep our data clean and tight!

Analytical Tools Available In Excel When We’ve Set Up Our Source Data Correctly

• Pivot Tables
• Data Model (available in Excel 2013)
• Slicers w/Pivot Tables
• Pivot Tables w/Excel Tables
• Slicers & Tables (available in Excel 2013)

manicure set image credit: m_bartisch  via FreeDigitalPhotos.net