Dear Analyst

This is probably the most click-baity title for a blog post/podcast about Excel, but we don’t get many of these in the Excel world, right? It all started in 2002 with the Dilemma music video by Nelly featuring Kelly Rowland, and Kelly decided to send a text using Excel. The most important text in the history of texts: “Where you at? Holla when you get this.” The Excel world has hollered back.

Kelly Rowland texting in Excel from the Dilemma music video

The world responds

A BuzzFeed article came out a few months ago about this 2-second scene from the video. Kelly was interviewed on a show called The Reel and was asked about this incident:

Kelly asked about her questionable text in Excel

This music video has been around for 15+ years and we’re still seeing memes about this momentous text in the interwebs. The fact that Kelly still doesn’t know what Excel is after all these years is mind boggling.

One-way data syncing with Coda

Now back onto regularly scheduled programming. This is the 3rd and final episode in this 3-part series about syncing data. In episode 12, I talked about using the IMPORTRANGE() function for syncing data between Google Sheets. In episode 13, I talked about syncing data using Google Apps Script. This episode is all about syncing data between Coda docs. In case you want to read the full blog post about syncing data, go here.

Here is the Gist with the full script for syncing data between your Coda docs. You’ll have to get the doc IDs and table IDs from the source and target Coda docs you want to sync. Those are set in rows 3-4 and rows 14-30 in the script; but it really depends on how many tables you want to sync.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

* The Knowledge Project: Episode 6 – Philip Tetlock: How to See the Future

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 full blog post on how to do one-way syncing, check out this blog post I wrote on the Coda blog.

Source: Google Apps Script

The script for syncing your Google Sheets

Check out this Gist to see the full script — it’s 17 lines long. Here’s the script:

var sourceSpreadsheetID = "TO UPDATE";

var sourceWorksheetName = "TO UPDATE";

var targetSpreadsheetID = "TO UPDATE";

var targetWorksheetName = "TO UPDATE";

function importData() {

var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);

var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);

var thisData = thisWorksheet.getDataRange();

//Uncomment line 11 below and comment out line 9 if you want to sync a named range. Replace "teamBugs" with your named range.

//var thisData = thisSpreadsheet.getRangeByName("teamBugs");

var toSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetID);

var toWorksheet = toSpreadsheet.getSheetByName(targetWorksheetName);

var toRange = toWorksheet.getRange(1, 1, thisData.getNumRows(), thisData.getNumColumns())

toRange.setValues(thisData.getValues());

}

You’ll notice that the only lines you need to edit are lines 1-4 in the script. As long as you have your Google Sheets IDs and sheet name from your source and target docs, you are good to go! I would recommend using Named Ranges in your Google Sheet to allow for situations where you data in your source Google Sheet is dynamic.

Being a maker, millennial burnout, and Google Sheets as a database

Normally I just link to the other podcasts and blog posts I comment on in the 2nd half of each episode, but I think it is worth writing a bit about the other podcasts and blog posts this time.

Adam Savage’s interview on The Tim Ferris Show embodies the spirit of being a maker. I think it’s easy to be a maker these days in the software/digital space, but there is a whole world out there of makers who are making with physical products. I would definitely give this episode a listen as well as checking out Adam’s book Every Tool’s A Hammer.

Source: Norman Chan

In the Product Hunt Radio episode with Danielle Morrill, I was reminded of this article about millennial burnout. I don’t fully agree with the character traits imbued upon millennials by the media. This article, however, did have some nuggets that I do agree with (such as the bit about finances).

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 Google Sheet for each of your clients might be the best way to limit the information shared with each client. Importing data from one Google Sheet to another is as simple as writing a formula.

A one-way data sync with IMPORTRANGE

In this episode, I talk about importing data from a Google Sheet. This episode is part of a broader series on importing data which I write about here. The main way you can import data from a source Google Sheet is by using the IMPORTRANGE() function. This function takes two arguments:

* The URL of the source Google Sheet* The cell references from the source Google Sheet you want to import data from in step 1

There are a lot of nuances about how to make this formula extensible to account for different scenarios. One improvement is using the QUERY() function to limit the data that is returned from the IMPORTRANGE() function.

Importing data in Google Sheets using the IMPORTRANGE() function

Other podcasts

In the 2nd half of the episode, I talk about some episodes from other podcasts I found interesting:

* The Tim Ferriss Show: Episode 369: Kevin Systrom – Tactics, Books, and the Path to a Billion Users* Shop Talk Show: Episode 358: UX Design with Jessica Ivins

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 show how important this question is, my answer on Quora about this topic is my most viewed answer.

Falling out of love with Microsoft Excel

In this episode, I discuss why the answer is not so black and white, and really depends on your use case with data. I review an article written by Dan Kopf for Quartz called “I fell out of love with Microsoft Excel, because Google Sheets is better.” It’s not too often we see a mainstream article about Excel and Google Sheets, so give this article a read if you would like to see one person’s thought process behind ditching Excel.

Spoiler alert: the main reason Dan ditches Excel is due to a sorting bug he encountered in early 2018.

The battle between Microsoft Excel and Google Sheets.

Other podcasts

In the 2nd half of the episode, I talk about some episodes from other podcasts I found interesting:

* Making Sense with Sam Harris: Episode #153 – Possible Minds: Conversation with George Dyson, Alison Gopnik, and Stuart Russell* CodeNewbie: Season 8 Episode #4 – Coding without code with Joanna Smith

Given that this is the 10th episode of the Dear Analyst podcast, I thought it would be relevant to reflect on how I got started with KeyCuts and joined various Excel communities. I never thought I’d make it to 10 episodes, but here we are!

Excel communities

Like many online communities, I got started with the Excel community by connecting with a few bloggers who wrote about Excel back in the day. I then started attending meetups and events like Modeloff, and international financial modeling competition (read some of my previous blog posts about Modeloff here). Initially KeyCuts started as a company to sell a keyboard cover product, but it eventually transformed into freelance consulting gigs, creating blog posts and YouTube content, and more recently the Dear Analyst podcast.

Other podcasts

In the 2nd half of the episode, I talk about some episodes from other podcasts I found interesting:

* Acquired: Season 4 Episode 4 – The Lyft IPO* Software Engineering Daily: Bubbles with Haseeb Qureshi

If you want to count the number of cells with blank values in them, you may think it’s as simple as using the COUNTBLANK() formula. Usually you do this if you want to know how much data is missing in your data set. Say you have a list of 10 students, and if a student is missing from the set, you want to quickly count how many students are missing from your data range.

However, using COUNTBLANK in this manner only works if you have the exact cell references for your range of data (e.g. “A1:A20”). What if you don’t know the exact cell range? In this episode, I talk about a small trick I learned about how to count the number of blank cells when you give the COUNTBLANK()formula a dynamic column reference as opposed to a specific range of cells. I discovered the answer in a Mr. Excel forum thread about this exact issue.

Say you have the following list of students, and you want to count the number of cells with a value, you would simply use the COUNTA() function like so:

This gets you a count of 8 students. You will notice that there are two “blanks” in rows 4 and 7. How would you count the number of blanks in these 10 cells? You could use a COUNTBLANK() function like so:

Easy enough right? This formula gives you the answer of 2. However, sometimes your formula for counting the number of blanks may not be right below your actual range of data, and you may not know the exact cell references do your range. In the below case, the result of the COUNTBLANK() function leads to an answer of 1048568 which is a lot of empty cells!

What’s happening is Excel is counting all the blank cells below row 10 which is not really part of my data set. How would I tell Excel to only count the number of blank cells in my data range (A1:A10) when given an entire column reference?

The entire formula for the above scenario would be the following:

=COUNTBLANK(A1:INDEX(A:A,MATCH(REPT("Z",255),A:A)))

What’s interesting about this formula is that it only requires you to know the “top” of your range which is the “A1” reference. The REPT() function is a new one I haven’t used before, and this function creates the value “ZZZ[…]” in whatever cell you choose (the “Z” letter repeated 255 times). The MATCH(REPT("Z",255),A:A) part of the formula is a common formula to find the row number of the last cell with a value in your data set. Similar to VLOOKUP(), the MATCH() function also has a lookup_type parameter when set to “1” or omitted, will do a fuzzy or approximate lookup on your data set. It tries to find the the largest value that is equal to or less than the value you are trying to match against.

In this case, since REPT("Z", 255) results in a value that you will most likely not have in your data set and is the “last” value you would find if used in a data set, the MATCH() function return the next largest value which is the last cell with text in your range (e.g. cell “A10” in our example above). Why is the “255” significant? Turns out that this was the character limit for cells in Excel ’95 and earlier, and is still the limit for how Excel stores “records” despite the limit being extended to 31,000 starting with Excel ’97.

In the 2nd half of the episode, I talk about some episodes from other podcasts I found interesting:

* The Changelog:

What are some best practices for data analyst working in the medical field? I review this blog post from the Tableau blog by Barry P. Chaiken, MD about the type of analytics used in the medical field, and the trust that must be garnered between the analyst and the physicians. An interesting aspect of doing data analysis for medical professionals is that there is so much on the line (patients’ health) if the data is not clean or the analysis is incorrect. Even if the results of your analysis are not life or death, applying that same level of rigor from the medical field will yield positive results for your team or company.

In the 2nd half of the episode, I talk about some episodes from other podcasts I found interesting:

* Inside Intercom Podcast: Scale #3 – What makes a world class CMO in 2019? And the #1 reason CMOs fail* The Changelog: Episode #339 – Why smart engineers write bad code

Should you use VLOOKUP or INDEX/MATCH in Excel? What are the pros and cons of each method? When you lookup data in Excel, you have the option to use either VLOOKUP or a combination of INDEX and MATCH functions. There is a huge debate over which method to use, and I break down the pros and cons of both methods. Long story short, I prefer using VLOOKUP due to its simplicity and the data sets I’m confronted with on a day-to-day basis.

In the 2nd half of the episode, I talk about some episodes from other podcasts I found interesting:

* My Side Project: Glide: David Siegel* The Tim Ferriss Show: Episode 364 – Safi Bahcall — On Thinking Big, Curing Cancer, and Transforming Industries* The Remote Show: Zack Onisko, CEO of Dribbble

This is my take on the a blog post written on the Mode blog: How to Develop the Five Soft Skills That Will Make You a Great Analyst. These soft skills are invaluable for all analysts and as Derek mentions in the blog post, helps you move up in your career. The most important soft skill, in my opinion, is being able to tell a story with the data. That skill, by far, has helped me the most with my career as an analyst.

In the 2nd half of the episode, I talk about some episodes from other podcasts I found interesting:

* Acquired: LP sneak peek: How to build a successful SaaS company* Syntax: Episode 126 – Bootcamps vs School vs Self-learning

Finally, there’s a YouTuber/comedian named KRAZAM who posted this amazing video about being an Excel “pro” and gives his take about recent Excel patches in the style of a video game Twitch streamer. He aptly goes by the name “MAKRO” in the video :).

What are the best ways to learn Excel? How do I get good at Excel? I’ve been asked this question many times in person or online, and even wrote some answers online. I discuss not only the best ways to learn Excel, but also what the core concepts you should learn to be the most effective at work when using Excel.

I am a big believer in the pareto principle, or as Tim Ferriss calls it, the Minimum Effective Dose to learn the least amount to get the most done. This concept can also be applied to learning Excel. Some strategies for learning Excel include enrolling in a full-time in-person class, taking an online class (like this Skillshare one :)), watching YouTube videos, or taking apart an existing Excel file (my preferred choice).

In the 2nd half of the episode, I talk about some episodes from other podcasts I found interesting:

* Planet Money: Episode 785 – The Starbury* Under the Skin with Russell Brand: Episode 70 – Heal Yourself with The Ice Shaman (Wim Hof)