Tag Archives: google sheets

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 #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 #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 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: =ARRAYFORMULA(MAX(FREQUENCY(IF(B2:P2=”W”,COLUMN(B2:P2)),IF(B2:P2=”L”,COLUMN(B2:P2))))) […]

Dear Analyst Episode 16: The New York Times’ data bootcamp

A few months ago, The New York Times’ Digital Transition team headed by Lindsey Cook published a blog post about a data bootcamp they led for their reporters and editors. The New York Times frequently publishes amazing data visualizations, and it’s awesome to see this newsroom pushing their reporters into the world of “data journalism.” […]

How to sync data using Google Apps Script in Google Sheets

In episode 12, I talked about how you can use the IMPORTRANGE() function in Google Sheets to import data form a source Google Sheet to a target Google Sheet. In this episode, I get a little more technical and discuss how you can import data using Google Apps Script. If you want to read the […]

How to import data from one Google Sheet to another

Have you ever wanted to share data from one Google Sheet with another Google Sheet? Maybe you are a freelancer and have multiple clients, and keep all the payment data in one Google Sheet but need to send updates to your clients without sharing all your clients’ data. In this scenario, setting up one individual […]

Should you ditch Microsoft Excel for Google Sheets?

Google Sheets or Microsoft Excel? An age-old question since Google Sheets came onto the scene in 2006. Many saw Google Sheets just as a copycat of Excel that lacked most of Excel’s features. Over the years, Google Sheets has caught up with Excel’s many features including the number of formulas, PivotTables, and even macros. To […]

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!