Dear Analyst #33: Comparing one-time vs. monthly recurring donations to support racial justice organizations

The Black Lives Matter movement has come to the forefront in the news media. People around the world are looking for ways to fight racial injustice. If you are in a position to donate to an organization fighting racial injustice, you are joining the ranks of individuals and companies who are supporting the movement with […]

Dear Analyst #32: How to use the QUERY function in Google Sheets on COVID-19 data

The QUERY() function in Google Sheets gives you the ability to quickly filter and sort your data similar to how you might get data from a database. If you write SQL queries, the QUERY() function feels easy and natural to use. There are a few caveats as I discuss in this episode. If you want […]

Dear Analyst #31: Writing Google Apps Scripts to sync data from Coda to Google Sheets

I worked on a “small” side project recently to sync data between Google Sheets and tables in Coda. The full blog post tutorial is here, and the GitHub repository is here. I started using Google Apps Script last year and it’s a super powerful way to connect different apps you use in the G Suite […]

Dear Analyst #30: How to learn Excel while staying at home during COVID-19

Now that you’re staying home and picking up new hobbies and taking classes online, here are a few tips on how to learn Excel and spreadsheets from an online class. I have seen viewership on my own Excel classes spike since COVID-19 hit which has led me to think about the best way to learn […]

Dear Analyst #29: Working with dynamic array functions and formulas that spill

Have you ever wondered what an “array-entered formula” is? It’s an intermediate/advanced concept in Excel but in late 2018, Microsoft released dynamic array functions and formulas that “spill” into the cells below your current cell with a function. This makes writing formulas easier and less prone to human error, but there are some tradeoffs to […]

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

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!