Structuring your data:
Tips to make working with your data much easier

↓
​

Have you ever exported data from a system in order to build a report, only to struggle for hours working with formulas to synthesize the data? After a while, you give up, manually summarize the data, and hardcode it into your report, only to have to do the same thing next week, month, or quarter?

This is all too common, and quite frankly the problem isn't you, it's how your data is laid out.

What do we mean by data structure?

There's what I like to call the 'pretty' vs the 'powerful' data structure. Pretty data structure is laid out in a matrix, or horizontal, view. It's how you typically will structure data in a pivot table using rows and columns with your values at the intersection of those.

It looks something like the following:

Good for: looking at.

Bad for: working with.

While this can be useful for presenting data, it does not make it easy to work with your data. This is where the vertical, or 'powerful', data structure comes in handy. It looks something like the following:

Good for: any kind of formula, pivot table, chart, or analysis you can dream of.

Bad for: a quick high-level glance.

How does this impact my use of formulas?

Let's go back to the horizontal table, if you were asked what were the total sales for region A was, your formula would be something along the lines of:

Now let's go back to the vertical table, your formula could be something along the lines of:

The best part, is you could add 50 more rows of data to the vertically laid out table, and never have to change your formula.

There are more advanced ways you can leverage this, by using an input sheet, and having the =sumif() formula dynamically reference the data.

Let's dig into that next time. For now, if you find this information valuable, please subscribe to our newsletter to get more tips and tricks directly to your inbox each week!

---

Subscribe to our weekly newsletter