Blog

The Best Way to Structure Enterprise Spreadsheet Data for Reporting

Jamie Smith - 28 June, 2018

Many analysis and planning projects involve information ('x') that is to be presented as a series, 'over time.' No matter the information – whether it’s sales information such as order volume tracking, accounting information such as expense tracking, operational information like inventory balances, or financial information like profits or stock prices – your trend reports need to show daily, monthly, quarterly, or annual changes.

See one example of how you can improve your spreadsheet performance in our video here. 


Designing such a report can be straightforward if the data items in the report are mathematically simple (for example, a count of the number of orders received) and/or involves reporting only on a single entity (such as total volumes for an entire company). However, when reporting requirements are complex and involve many offices, facilities, or companies, and the key reporting metric becomes more convoluted, optimally structuring your data to facilitate trend reporting can become much more challenging.

Let’s use an example of a company with numerous sales offices:

  • Desired reporting metric: Average Gross Profit per Salesperson by month
  • Data items available:
    1. Gross Sales by office by month
    2. Cost of Goods Sold by office by month
    3. Average number of Salespersons in each office by month

At a minimum, each office will have at least four columns of data in your spreadsheet file for every month. One column for each of the three data inputs:

  1. Sales
  2. COGS
  3. Number of salespersons

As well as one column with the result of your reporting metric calculation:

  1. [Sales minus COGS] divided by the number of salespersons.

It might be tempting initially to put each of those four columns side-by-side in one worksheet, and for reporting on a single time period (or limited number of time periods), an appropriate report can indeed be produced in this manner. However, as the number of time periods increases, this format either requires creating a new worksheet tab in your spreadsheet file for every new month, or replicating another set of four columns in the same worksheet month after month. The first option requires a constant growth in the number of worksheets (one for each time period), while the second option produces a worksheet which rapidly becomes unwieldy – and neither can easily facilitate trend reporting.

Instead, follow this structure: Create a separate worksheet for each of the four data items – each sheet with one column of data for each time period. Each sheet would be structurally identical, like a group of matrices, and the metric calculation performed on the fourth worksheet (Average Gross Profit per Salesperson) would combine values from the same cell locations in the other three worksheets. For example, a hypothetical cell E9 on ‘Sheet4’ would have a formula like:

=(’Sheet1’!E9-’Sheet2’!E9)/’Sheet3’!E9

The number of worksheets remains static and each worksheet grows by only one column every month to accommodate the new month of data. Furthermore, the fourth sheet housing the reporting metric will already have the data perfectly formatted for producing a trend report that can easily be shared with others!

Of course there are a variety of database applications and/or pricey software packages in the market that could possibly be utilized in some fashion to make this same calculation, but why learn a new tool when you already know Excel? That's the idea behind xOverTime. Structure your data and make your spreadsheets robust business tools rather than get silo'd by expensive, special-purpose custom software applications.

Here's a great example. Need to easily look up a specific value in your enterprise spreadsheet? Our spreadsheet pros walk you through a working example in this video:

Improve business spreadsheet performance video

Do you have a tricky data management problem or a spreadsheet performance issue? Send your questions – we’d love to help.

Subscribe to the Enterprise Spreadsheet Performance Blog

Request a demo to get started today!

See how our Excel-enabled cloud database can help you better manage data to drive business decisions.