I recently had the opportunity to build a growth model at work, and it's been fun getting back to my roots in Excel/Google Sheets. Been a while since I've built a model from scratch so I of course referenced previous models my colleagues have built. Interesting to see the use of FILTER and INDIRECT in the models for referencing data, so I'm sharing how you can use these two functions when building a model (specifically, referencing raw data). The example Google Sheet uses an open data set for CO₂ emissions found on Kaggle.

The data set

The data set simply looks at all CO₂ emissions for every country by year (in some cases going back to the 1800s). Not quite sure how Our World In Data was able to get data going back this far, but it's there. The data set consists of four columns and is denormalized (just one long stats table):

About a year ago, National Geographic released a report card on how various countries are tracking towards emissions targets following the Paris Agreement a few years earlier. National Geographic put a few countries in the following buckets: "Top of the class," "Shows some promise," and "Barely trying":

Our raw data set has emission data for every country in the world, and our goal is to build a simply model that outputs the emissions for a few select countries for let's say the most recent 10 years of data. With this summary data set, we can then start looking at trends, growth patterns, and more. Basically, how do we get something that looks like this (and we can plug in whatever country we want in the first column)?

Modeling with named ranges vs. PivotTables

Since we have a denormalized data set, the easiest way to get the data into the "summary" table structure in the screenshot above is do a PivotTable:

You can play with the filters for Year and Country to just show the data you want. As much as I like PivotTables, it's not easy to manipulate when you want to change the filters on the fly, so instead we can create a separate summary table that references the raw data using formulas. This is where named ranges come into play.

Applying named ranges to columns in the data set

This is a new technique that I haven't done before in my models when I was more actively building models, but you'll see the flexibility once you see how named ranges can work with the Filter function. In the gif below, I've named the Country, Year, and Annual CO₂ emissions columns. The named range just represents the entire column. You can access named ranges by going to the "Data" menu in the toolbar.

Referencing named ranges in summary table

Back on the "Model" spreadsheet, I have my countries broken out into the three buckets listed in the National Geographic article. This is a screenshot of the summary table I need to fill out:

Since I have the country in column C and years from columns D onward, I can write a formula to pull in the data I need from the raw emissions data to fill out the table. Here's what that formula looks like: