Separate Data From Reports for Best Results

Two Good Reasons to Separate Your Data From Your Reports

While it is convenient to have everything in one spreadsheet, and even on one worksheet, there are a number of good reasons to keep your data separate from your reports. This is especially true if you are sharing your information with others. Let me show you two such reasons by describing two commonly faced challenges and how to protect yourself from unnecessary grief.

Do you ever find yourself going back to a one-off report you did 3, 6, or 12 months ago, trying to figure out how you prepared a certain report because now the end user either wants to refresh the data or extend the analysis, even though they promised you at the time that it was a one-time request?

Do you need to share information with a group of people who work independently of one another? Have you ever had a boss, client, or co-worker who unfailingly broke any spreadsheet you prepared for them? How do you allow others to get the information they need without compromising the integrity or your data or without continually having to rebuild or troubleshoot your spreadsheet?

The answer for both scenarios is to separate your data from your final report. Why?

Scenario 1

First, it is good practice from a data analysis perspective that will help you when you inevitably have to go back to it some time later. A three-step process is often suggested for data analysis: first, get your data (which may involve importing and cleaning it), second, analyze your data, third, present your data.

If this is a simple, one-off analysis, it will probably suffice to do this in one spreadsheet with three or more worksheets (depending on the amount and complexity of the data you are working with and the number of people involved).

For example, your data might be on one worksheet.

Data separate from Report

Your analysis work might be on another.

Analysis is step 2

And the final presentation of your data might be on another worksheet.

Lastly, present your information

Such a separation of the steps of the process allows you to annotate your steps for future reference, play with your analysis as much as it takes to be sure that you understand your data, then finally decide what exactly you want to present to end-users of your analysis and how to most effectively display it to accomplish that purpose, perhaps even linking it to a slide presentation. It also makes it easier to quickly update your data when the end user comes back for more at a later time, as often happens.

There is, however, another compelling reason to keep your data and report separated, as highlighted in the second scenario mentioned at the outset, namely to protect the security of your data.

Scenario 2

How do you share information with an ever-increasing group of people while ensuring that all these fingers in the cookie jar don’t inadvertently corrupt your data? And since we have probably all worked with individuals that will break whatever you give them while trying to “enhance” it, how do you minimize the amount of time you spend fixing what you have given them?

The answer, again, is to separate your data into one spreadsheet (or database) and your reports that are built upon that data into another within a shared storage location. This allows you to use queries to read the information from the database (without any possibility of changing it) into other spreadsheets that act as reports. In effect, this is what Power BI and other business intelligence tools do on a larger scale. Once the first such report spreadsheet has been set up, with all the appropriate queries and data transformation built into it, it can then be copied as many times as needed for each end user. And if someone breaks their copy, you just give them a fresh copy.

Use queries to link reports to data

One client, for example, needed a way for senior management to daily be able to get a variety of reports on sales across multiple stores, but their accounting system didn’t easily allow what they needed in a timely fashion. By setting up an identical spreadsheet for each store and having the store manager be responsible to enter a half dozen key metrics (such as sales, number of customers, total employee hours) first thing each morning, by mid morning the next day each of the management team who wished could monitor the KPIs from the day before. Their spreadsheets were set up to refresh all data when the spreadsheet was opened, so if they waited until the agreed upon time they would be able to see yesterday’s results, allowing them to respond more quickly with needed changes. And when the inevitable happens and someone’s copy of the reporting spreadsheet becomes corrupted for any reason, a fresh copy can quickly be given to them. This also allowed each one to customize their copy to their heart’s content.


If you would like to see how you might similarly benefit from separating your data collection from your analysis and reporting within the business realities you face, please don’t hesitate to reach out to me through the Contact Us page.