Dear Analyst #45: Thinking long-term for structuring your dataset using U.S. public food assistance data

When you need to capture some data in a structured way, you’ll open up an Excel file or Google Sheet and just start throwing data into the spreadsheet. Not much thinking; just copy and paste. As that dataset grows, the original structure you had set up for that spreadsheet may not be ideal. Specifically, the […]

Dear Analyst #44: Referencing CO₂ emissions data with INDIRECT and FILTER to build a model

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 […]

Dear Analyst #41: How to do a VLOOKUP to the “left” without INDEX/MATCH with TikTok data

Since TikTok is in the news right now about who is going to buy them, I thought using some fake-ish TikTok acquisition data would be relevant for this episode. A classic Excel/Google Sheets challenge: how to do a VLOOKUP to the “left” e.g. your lookup column is not the first column in your lookup table. […]

Dear Analyst #40: A spreadsheet error from two Harvard professors leading to incorrect economic policies after 2008 recession

It’s 2010, and the world is coming out of recession. Two Harvard professors–one of whom is a former economist for the IMF and chess Grandmaster–publish a paper suggesting that a country with a high public debt-to-GDP ratio of over 90% is associated with low economic growth. Turns out the Excel model the professors use is […]

Dear Analyst #39: Generate a random list of names from a list of popular 90s TV characters

Let’s say you have a set list of names (in this case TV characters from popular 90s TV shows). You want Google Sheets/Excel to generate a random list of names from your list as if you were picking names out of a hat. How would you do this? It most likely would involve the RAND […]

Dear Analyst #38: Breaking down an Excel error that led to a $6.2B loss at JPMorgan Chase

You blink a few times at the screen and realize what you’re seeing is not a typo. $6.2B has left your bank due to some rogue trader making untimely bets on the market. That’s B as in billion. You call up the modeler who was supposed to make sure this never happens to your bank. […]

Dear Analyst #37: Text manipulation functions to extract domain names from email addresses

In Excel or Google Sheets, text manipulation is usually associated with data cleaning, data cleansing, and data transformation. Sometimes your data is “dirty” and needs to be categorized in a different way or you need to “extract” a piece of text from a another piece of text. In this example, we use a combination of […]

Dear Analyst #36: What The Economist’s model for the 2020 presidential election can teach us about forecasting

On a recent episode of The Intelligence, The data editor at The Economist spoke about a U.S. presidential election forecast their publication is working on. I looked more into their model and discuss some of the features and parameters of their model and what makes their forecast unique. Some of the techniques used in The […]

Join our community of 5,000+ Excel and data analysis enthusiasts!

  • Learn Excel techniques and shortcuts
  • Episode updates from the Dear Analyst podcast about Excel
  • Updates on online and offline data workshops

Enter your e-mail and hit the blue button!