Tag Archives: google sheets

Dear Analyst #75: How to extract first, last, and multiple middle names into 3 separate columns

In episode 52, I talked about how to extract text from a cell. You might use this formula to extract a certain value from a cell when your database or CSV export contains a bunch of miscellaneous prefix data. This formula relies on finding a certain character in the cell and then using the MID […]

Dear Analyst #73: From a career in the U.S. Navy to data analytics YouTuber with Luke Barousse

The path to a career in data analytics can be full of twists and turns. Along the way, you pick up tools like Excel, Python, Tableau, and R. What about learning how to use YouTube and growing an audience of 50,000+ from publishing videos about data analytics? I’m always fascinated by people who are able […]

Dear Analyst #70: New advanced PivotTable class and a PivotTable calculated field trick for percentages

I’ve been planning a few advanced Excel classes with Skillshare and excited to launch my first one today called Advanced PivotTable Techniques for Analyzing and Presenting Data Faster. I use PivotTables on and off depending on the task at hand. In preparation for this class, I had the opportunity to research and learn some advanced […]

Dear Analyst #69: Import data from another Google Sheet and filter the results to show just what you need

You may be filtering and sorting a big dataset in a Google Sheet and want to see that dataset in another Google Sheet without having to copying and pasting the data each time the “source” data is updated. To solve this problem, you need to somehow import the data from the “source” worksheet to your […]

Dear Analyst #66: How to update and add new data to a PivotTable with ramen ratings data

PivotTables have been on my mind lately (you’ll see why in a couple weeks). An issue you may face with PivotTables is how to change the source data for a PivotTable you’ve meticulously set up. You have some new data being added to your source data, and you have to change the PivotTable source data […]

Dear Analyst #53: Making your Google Sheets do more for you with Google Apps Script and how to become more data-driven

When I worked in FP&A, I discovered that VBA could automate a lot of tedious tasks I was doing in Excel. From creating charts to formatting data, I realized that there possibilities with VBA were endless. As I started using Google Sheets more, I found that Google Apps Script offers similar functionality to extend what […]

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