Archive / Podcast

RSS feed for this section

Dear Analyst #27: Splitting a cell diagonally to label y and x-axis and COVID-19 dashboard

This is an Excel trick that’s not super complicated but super useful for labelling a simple table in Excel. Let’s say you have one set of labels along the rows (e.g. “Region”) and then another set of labels along the columns (e.g. “Month”). Cell A1 is now empty because you don’t know which label to […]

Dear Analyst Episode 26: Data visualizations for infectious diseases/ideas during coronavirus (COVID-19)

Given the media attention placed on the coronavirus (COVID-19) in the media the last few weeks, I thought it was important to take a step back and look at the math behind infectious diseases and how diseases spread. I spend the entire episode taking a look at Going Critical, a blog post by Kevin Simler […]

Dear Analyst Episode 25: Structuring data challenge (denormalize data) with Get and Transform

This episode is based on a video and Tweet posted by Mr. Excel (Bill Jelen). Bill discusses an Excel challenge someone emailed him about regarding how to “transform” a badly structured table of data into a structure that makes it easy to do PivotTables and other downstream analysis. Interestingly, I received a ticket from a […]

Dear Analyst Episode 24: Finding and ranking percentiles

I discuss how to calculate percentiles in Excel or Google Sheets using the PERCENTILE function. With the PERCENTILE function, you can calculate the value that would represent nth percentile in your list of values. This is not exactly the calculation I was looking for. Instead, what if you wanted to know what the rank percentile […]

Revisiting Calculating Average Trends Across Time Periods in Spreadsheets

In this episode, I discuss how to calculate trends over time in Excel for the purposes for forecasting future values. I reference an old post about calculating trends where someone recently left a comment about the counter-intuitiveness on calculating averages of changes in your values. In order to follow along with this episode, I would […]

Dear Analyst Episode 22: Calculate win streaks for a pool of players in Google Sheets

If you are by your computer, you may want to open this Google Sheet to understand the example discussed in this episode. I walk through a rather long formula involving the FREQUENCY(), COLUMN(), MAX(), and the ARRAYFORMULA() functions in Google Sheets. Here’s the full formula below to calculate win streaks in the Google Sheet:

[…]

Dear Analyst Episode 21: Building No-Code Tools and Applications from Spreadsheets

This is my talk from Webflow’s No-Code Conference that took place on November 13th, 2019 in San Francisco. The title of my talk was called Building No-Code Tools and Applications from Spreadsheets. The slides from my presentation are on SlideShare here. Themes from the talk This was my first time talking about my experience with […]

What it’s like teaching a week-long online data analytics course

A few weeks ago I had the opportunity to teach a week-long data analytics course through General Assembly. The course was taught entirely online using Zoom. I discuss some of the topics the students learned in the class, and what the experience was like teaching an online class in real time. The topics we covered […]

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!