Tag Archives: google sheets

0-vlookup-multiple-conditions

How to do a VLOOKUP with multiple conditions or criteria (3 methods)

Once you learn the VLOOKUP formula, your world opens up in terms of being able to analyze and manipulate data. There are hundreds if not thousands of tutorials on how to use the VLOOKUP formula since it’s such a powerful formula for finding the data you need in a long list. Comparable formulas include the […]

0-google-sheets

Dear Analyst #95: Nobody ever got fired for choosing Google Sheets

The motivation for this post/episode is a selfish one (scroll to the very bottom or skip to the end of the podcast to see why). As I thought about the framing of this post during my normal “thinking” moments (commuting, on the toilet, during useless meetings), I realized I’m going to take a different approach […]

Dear Analyst #81: How to hack award flights on Avianca Airlines with Google Sheets

I love a good deal, especially when it comes to getting a good flight. Ever since I got into churning a few years ago (signing up for credit cards to get rewards), I started booking flights primarily with reward points from these credit cards. I spend more time searching for flights with good point redemptions […]

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