Dynamic Tables for Reporting:
Tips to make updating reports automatic

↓
​

Last week we discussed why structuring your data in a specific way is important based on the end goal. As a TL;dr, horizontally structured data is great to look at, but vertically structured data is great for working with.

Today, we'll be looking at how to build dynamic reporting tables off of vertically structured data.

Setting up your reporting table:

We're going to structure the table horizontally, but use formulas to fill in the data at the intersections.

Our blank table:

Since our data set is structured as column A containing Region, column B containing Month, and column C containing Sales, our formula needs to use the rows and the headers of the table as our 'criteria' in the SUMIFS formula:

And with that, here's the magic: after you've written that single formula in your first cell, you can grab the little blue square and drag it across and down to fill the entire table. In about three seconds, your whole report populates itself. This is the moment you go from manually working in a spreadsheet to making the spreadsheet work for you.

Breaking this down:

This formula allows us to use more than one set of criteria to summarize the data by. Since we are summarizing Region and Month, we need to match on both of the two criteria.

This is the column we want to sum, and contains the Sales ($M) figures. The '$' in the formula locks this cell reference in place, so as you copy the formula throughout your table the cell reference remains as column C. Try the formula without the '$'s and as you drag your formula left and right you'll notice the cell reference will change from C to B or D, and so on.

Column A contains our Regions and cell F3 contains the first Region we want to filter by, 'A'. Remember the '$'s locks the cell reference. In this case, it locks the column A, and it locks the column F. As you drag the formula down the rows the formula will dynamically refer to cell F3, F4, F5, but as you drag the formula to the right, it will remain locked on column F.

Similar to the above, Column B contains our Months and cell G2 contains the first Month we want to filter by, 'Jan'. In this case, the '$'s lock the row on G$2, so as you drag the formula left and right the reference will update to H$2, I$2, but will remain locked on row 2 as you drag the formula downwards.

What is the point when you can extract horizontally structured data?

You're right to ask this question. The outcome of this report will be similar to the horizontally structured data from last week, but the benefit here is that you can extract as many columns of data as you want and build any table off that data set. In fact, you can build many reporting tables off of the same data set.

Picture this as your data set:

You can easily build the Region/Month table we built above, but now you could also build a table based on the City, or by city for a specific Region, or other adaptations.

You can continue adding more and more columns to your data set, such that one data extract could power almost all of your reporting needs in a single workbook.

Taking this further, building forecasts essentially rely on aggregating historical data, applying assumptions on top of that data, and predicting future outcomes. All of this can by dynamically built on top of vertically structured data.

Next week we'll dive into building a dynamic forecasting model off of this data set. 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!