Dear Analyst

This is one of my favorite VLOOKUP tips. Given that it's pride month, we'll be applying this tip to a list of all pride events in the United States. Here is the Google Sheet if you want to follow along with this example. Here's the scenario: you have a super large table in Excel or Google Sheets (by large I mean there are many columns) and you need to do a VLOOKUP on the 25th column. Instead of counting 25 columns from the left of your lookup column, you can use this column index trick to quickly get the column you're after.

Creating column indexes above your lookup table

In the screenshot above, you'll notice that each column has the column index above it. This is a simple formula of the previous column index added to 1:

This might feel a little strange because we're used to heaving the column headers in the first row of our table. By having this column index in above the column header, however, it'll make it easier to provide the col_index parameter you need to provide to your VLOOKUP formula. In this list of pride events, if I want to get the Start column pulled into my VLOOKUP formula, I simply reference the column index above the column header instead of writing out the number "5" (note that PrideEvents is a named range representing A2:E270 in my list of pride events):

Putting the column index above your new column headers

In this second example, I put the column index above the new table where I want to pull in data from my list of pride events. Notice that the order of columns I want to pull does not match the column order from my lookup table. The simple trick here is that I do a simple cell reference to the column index above the main table so that I know that the order of the columns I want to pull back in this case is 3, 5, 2:

One of the benefits of this trick is that you can move columns around in your lookup table and this VLOOKUP formula will still work only if you "reset" the column indexes above your lookup table column headers to be sequential (1, 2, 3, etc). This is kind of annoying because any time I switch columns around, I have to re-drag the formula of the previous cell plus 1 in row 1 where my column indexes are. Hopefully your columns aren't moving around too much and this solution works for you.

Using the MATCH() function to find the column index

This is a little more advanced, but another solution is to use the MATCH function to match the column name in your new table with the column names in your lookup table:

Instead of doing a simple reference to the column index in that first row in my new table, I have this MATCH function which tries to match Location, in this case, with the column headers in the lookup table ($A$2:$E$2 represents the column headers from my list of pride events). If it finds a "match," then the MATCH function returns back the column index. You could actually do this solution without having that column index above your new table columns by putting the MATCH function directly in your VLOOKUP formula, but it might make the formula more difficult to debug in the future.

Pride Easter egg in Google Sheets

To celebrate pride month, here's a fun Easter egg you'll find in Google Sheets if you type out "PRIDE" in separate columns (you'll also see this in the Google Sheets example for this blog post):

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 their dollars. While all these donations are great for all these organizations, I wanted to explore what happens once the news cycle dies down and whether donations will continue. I also make an argument for why you should consider donating on a monthly basis to organizations fighting racial injustice. This is the Google Sheet I discuss in the episode.

Source: Equal Justice Initiative

One-time donation model

As people rush to make donations to organizations fighting racial injustice, what will happen once the protests and police brutality finds their way out of the news cycle? Will donations dip? Here's an extremely oversimplified model of how an organization might see their donations over time:

Month 1 (today) we see a spike in donations, but then the total revenue from donations may decrease back to "normal" levels. What is the spike in month 7? This may be the organization's annual fundraising gala, when the grant money hits their bank account, or perhaps a large marketing campaign to drive new one-off donations.

The issue with this model is that it's--to give it a scientific term--lumpy. A lot of resources (human and monetary) is spent producing these fundraising events and applying to grants. To put the average donation size in perspective:

The average monthly online donation is $52 ($624 per year) compared to the average one-time gift of $128. According to Network for Good’s donation data, the average recurring donor will give 42% more in one year than those who give one-time gifts. Monthly donors also have a greater lifetime revenue per donor. Finally, 52% of Millennials are more likely to give monthly vs. a large one-time donation. Source: Donorbox

I used the same numbers from Network for Good in the Google Sheet. Let's see what happens with the monthly recurring donation model:

Monthly recurring donation model

This oversimplified model shows that total donation revenue (green bars) increases steadily over time. The reason the increase occurs is due to small increases in new donation revenue over the months but more importantly, there's a base of recurring donations every month.

Benefits of the monthly recurring donation model

As I discuss in the episode, the main benefits for the organization (and why you should consider donating monthly) for this recurring donation model include:

* Not relying on a large fundraising gala to raise funds for the year* Better predictability in terms of revenue which leads to better planning for costs and overhead* No time spent applying for grants

There are many other reasons for the organization, but to the individual donor there benefits as well:

* Lower dollar amount to "get started"* Less pain compared to a large one-time donation* More involvement with the community you are supporting

The list goes on and on. Another way to think about why donating monthly vs. a one-time donation is comparing your donation to p...

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 to follow along with the exercises I discuss in this episode, make a copy of this Google Sheet which contains the QUERY() functions I mention in the episode.

Basic query to find confirmed cases greater than 50,000

Our data set is from the COVID-19 Data Repository by the Center for Systems Science and Engineering (CSSE) at Johns Hopkins University. The data shows confirmed cases, deaths, and recovered cases by country (188 countries) on May 1st:

The first query simply pulls back the list of countries and confirmed cases where the number of confirmed cases is greater than 50,000. Notice how you reference the column letter name versus the actual name of the column in the header row:

The first parameter is covid_data which is a named range in Google Sheets. In this case, it references cells A1:E188 in our data set.

More SQL-like commands

You can do many database-like commands with the QUERY() function. The next example shows how you can use the ORDER BY command to find countries with deaths between 0 and 5 and the resulting list is sorted in descending order:

Check out Ben Collins' blog post about the QUERY() function to see some of the other SQL commands you can use.

Adding in new calculated columns

In the third query, we get a little more advanced and use the LABEL command to create a new "column" called Case Fatality Rate. This calculation is simply Confirmed / Deaths. Unlike SQL, you put the LABEL at the end of the command instead of in the beginning of the SELECT statement:

Coming from SQL, you'll need to account for the difference in the order of commands in the query in order for it to work correctly.

Inability to select column names

You'll notice that you don't put the actual names of the columns in your header row in the query. This can be a pro or con of the QUERY() function depending on how your underlying data set is structured.

Columns are changing a lot

If you underlying data is constantly "shuffling" where columns are moving around and the structure of the data is not set in stone, the QUERY() function will most likely break because you're referencing the column letter instead of the column name like in a traditional SQL query.

Columns are fixed

If your columns are not shuffling around a lot, this syntax of selecting the column letter may actually be easier for you. This is because you don't have to type out the long column name in the QUERY() function. If data is simply getting appended to the bottom of your data set, then the QUERY() function should work fine for you because the letters of the columns will always reference the correct columns of data.

PivotTables vs. the QUERY() function

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 ecosystem. The impetus for creating these two scripts was seeing a few people in the Coda community talk about syncing data between their Google Sheets and Coda. The big caveat is that these are only one-way syncs, but there are several use cases where doing this could be useful in business workflows and making your team more productive.

Writing a script in Google Apps Script

Some Google Apps scripts can be super simple to set up. See this pretty simple workflow below of sending email automatically when there is data in your Google Sheet:

https://www.youtube.com/watch?v=xxgQr-jSu9o

Most of the "work" with writing these scripts was transforming data so that the model in Google Sheets matches the model in Coda as per Coda's API. Once that data munging is done, the rest of the script was relatively easy in terms of giving users the ability to add, delete, and modify data. I would highly recommend taking a look at Google Apps Script especially if you use a lot of Google Sheets. You'll be able to connect your Google Sheet with other applications in G Suite and other 3rd-party apps you use for work.

Use cases for syncing data between Coda and Google Sheets

This comes straight from the blog post, but thought it was worth repeating again:

Data synced from your Google Sheet

* HR & recruiting - All your candidates are stored in a Google Sheet but you want to be able to move candidates through different stages in the interviewing pipeline and Google Sheets isn't sufficient for your needs. Having all your candidates in a table in Coda means you can use templates like this one to manage candidates more effectively.* E-commerce and ERP - Orders, customers, and POs may all be different tabs in a Google Sheet that gets updated through Shopify or some other e-commerce platform. In order to manage your e-commerce business, you may want to see charts, calendar of shipments, and reports that Google Sheets cannot provide easily. Syncing the data from Google Sheets to Coda means you can do ERP properly (see this template as an example).* Customer Feedback - You may have a ticketing system like Zendesk or Intercom and all feedback lands in a Google Sheet somewhere. You can do some basic analytics in the Google Sheet but to reply to the feedback means you have to go into Gmail and start replying to customers. If your customer feedback is all in a Coda doc, you can run analytics and send emails using the Gmail Pack (see this template).

Data synced to your Google Sheet

* 3rd-party vendor reporting - Your vendors may not be using Coda yet, but you have all your vendor data in Coda and need to send them the data in a format they prefer. While you could

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 online.

First of all, why are so many people trying to learn Excel? Maybe since all schools and universities have pushed to online learning, students may be questioning the value of their college degrees. Maybe I should start learning skills that will actually help me land a job...enter stage left: Excel and spreadsheets.

Spreadsheets most sought after skill

In episode 22, I brought up an episode of Freakonomics where they discussed different stats around subjects Freakonomics listeners wished they had learned in high school to better prepare them for their current jobs. The high-level numbers:

Skills currently used on their jobs

* Less than 5% - Percent of survey responders who said they still use calculus, trigonometry, or geometry in their current jobs* 70% - Those who use Excel or Google Sheets on a daily basis* 75% - Those who visualize data or present data to make an argument on a daily, weekly, or monthly basis

Skills people wished they had learned in high school

* 0% - Those who wished they had learned other traditional math subjects in high school beyond what they had already learned* 65% - Those who wished they had learned skills around analyzing and interpreting data to uncover insights* 60% - Those who wished they had learned how to visualize and present data

It's pretty clear that data-related skills are what's actually being used on the job, and during a pandemic where you may have been furloughed, laid off, graduating from university, or really any scenario where your future is unclear and you want to secure a job, learning Excel and data skills may bubble to the top on your to-do list while you're in quarantine at home. Hopefully these tips will help you gain the skills you need to learn Excel and spreadsheets to help land your next job.

1) Block out time on your calendar to take your class

If you're a fan of David Allen's Getting Things Done philosophy, you've probably head the phrase that if it doesn't gets scheduled, it doesn't get done. Blocking off time on your Google or Outlook calendar to actually take your Excel class versus taking the class when you feel like it will ensure you get through the material and get into a state of flow with the material.

2) Minimize distractions

While it's easy to stay connected with family and friends while at home, you really need to put away your phone and apps for doing all your meetings and virtual hangouts. Turning off notifications for Facetime, Facebook, Houseparty, Slack, etc. will ensure you can get some uninterrupted time to learn Excel. There are small nuances to writing Excel formulas that can be easy to overlook when you are distracted by your friends or social media.

3) Connect with the instructor and community

Many online Excel classes encourage you to ask the instructor questions and many platforms such as Skillshare encourage students to participate in the community of other students who are taking the class with you. For my Excel classes,

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 using these formulas which I discuss in this episode.

Source: Microsoft

Implicit intersection: what Excel does behind the scenes without you knowing

This is not meant to cause fear as in Excel is doing something "behind your back." Many Excel users don't know that Excel does some magic behind the scenes for formulas where the input may be a range of cells but the formula is not necessarily a formula that is meant to accept a range of cells. Excel does something called Implicit Intersection.

Source: Exceljet

With dynamic array functions turned on in your workbook, you may have to start using the "@" operator to tell Excel to keep implicit intersection "on." There are a lot of edge cases where you would need to use the "@" operator so I'd recommend reading this blog post if you would like to learn more.

https://www.youtube.com/watch?v=1HF0UGMF070

Bringing array formulas to the masses

I argue that dynamic array functions and spill formulas are giving new Excel users a way to quickly calculate, filter, and sort their data sets without needing to go through a myriad of menus in the toolbar. Given that more jobs these days require working with large data sets and familiarity with various data models (SQL, NoSQL, GraphQL), knowing how to quickly manipulate data that's structured in one of these database models is becoming more important than ever.

I think that advanced Excel and SQL users will notice that Excel is getting closer to how PivotTables and SQL operate. With PivotTables, you have calculated fields which are similar to dynamic array functions in that you write the formula once and it applies to your entire PivotTable no matter how you slice and dice your data. In SQL, you are pretty much writing your own user-defined fields and aggregating data from other columns.

Other Podcasts & Blog Posts

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

* SheetsCon: Not a podcast, but a great virtual conference all about Google Sheets and all the replays are free to watch* devMode.fm #66: Interviewing for a Webdev Job in 2020

This spreadsheet tip is based on a question I get asked all the time when I teach (well taught) Excel at in-person classes: How do I fill a formula down to the last row of my data set without over-shooting the last row with keyboard shortcuts? This problem occurs with larger data sets where you have several hundred or thousands of rows and need to quickly apply a formula in a column for all these rows. This screenshot show the problem:

As the text on the screenshot shows, the Revenue per passenger formula needs to be applied to all rows in the data set, but we don't know where the data set ends. It could be row 100, 500, or 800,000. If you want to try this exercise for yourself, see this Google Sheet and make a copy for yourself.

Method 1: Double-click the bottom-right of the cell

This is how most people approach this problem, but the downside is that it requires you to use your mouse or trackpad. You basically hover your cursor over the bottom-right corner of the cell that contains the formula (in this case E2) and wait until your cursor turn into a black plus sign. Then you double-click and the formula for Revenue per passenger fills down to the last row in the data set (in this case row 281):

Method 2: Drag-and-drop the formula until it reaches the last row of data set

Even less ideal, you can drag the bottom right corner of the cell down and basically wait until the window scrolls to the last row of the data set. The downsides of this method:

* You're still using your mouse or trackpad* You might under-shoot or over-shoot the last row of your data set because the scroll depends on how far down you are holding down your mouse

Method 3: Press page down while having the first cell selected

This method uses keyboard shortcuts so definitely more ideal compared to methods 1 and 2. You keep the first cell with the formula selected by holding down SHIFT and then press PAGE DOWN a few times until you get close to the bottom of your data set. The downside is that you might overshoot your data set which means you have to keep SHIFT pressed while pressing UP ARROW a few times to get the select to "stop" right on the last row (row 281) in the Google Sheet. You can then press CMD+D on the Mac or CTRL+D on the PC to fill the formula down:

Some people ask me about using CMD+DOWN ARROW at this point to get to the bottom of the column (column E in this case) but the problem is that since all of column E is pretty much empty (rows 2 and below), you will simply go to the last row of the spreadsheet. You are over-shooting the last for of your data set by a lot in this scenario.

Method 4 (most ideal): Go to the bottom of the data set in the column to the left and then use the fill formula down shortcut

This method involves using only keyboard shortcuts and hence the most ideal. These are the steps:

* Move your cursor to the column to the left of your column that contains the formula you want to fill (in this case column D)* Press CMD+DOWN ARROW on the Mac or CTRL+DOWN ARROW on the PC and you'll most likely go to the last row of the data set (column D in this case) since the data should be contiguous.* Move your cursor to the right which puts you in the last row of your data set but also in the column that contains the formula you want to fill down (cell E281 in this data set)* Press CTRL+SHIFT+UP ARROW to select all the empty cells including the first cell that contains your formula above your ...

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 put in that cell. Do you put "Region" or "Month?" With this trick using the distributed indent horizontal alignment option, you can get something like this:

"Month" is near the top right of cell A1 while "Region" is in the bottom left making it look like you have two separate labels even though it's all in the same cell. The diagonal line is simply a diagonal border you can add through the Format Cells menu. This is a tip I learned from this YouTube video from Godesignow:

https://www.youtube.com/watch?v=EVRm0QMCRBU

COVID-19 dashboard in a published Coda doc

I also discuss a COVID-19 dashboard I've been working on for a month or so that tracks data from Johns Hopkins University, Wikipedia, and departments of health from various states. There are some interesting visualizations once you triangulate these different data sources and start adding in country-level data like population and density. See the dashboard here.

One of the charts from the dashboard

CEO of Shopify discusses lessons learned from StarCraft

One one of my favorite episodes of the year so far from The Pylon Show: Tobi Lütke (CEO of Shopify) visits the show to talk about his experience playing StarCraft and how some of the lessons learned from the game can be applied to a growth mindset and even hiring at Shopify. I wrote a blog post about the lessons I've learned about life and startups inspired by Tobi talking publicly about his love for StarCraft. This is one of the best Tweets on how game recognizes game:

https://twitter.com/tobi/status/1183807041678299137

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 Pylon Show #79: CEO of Shopify joins TLO & Artosis to talk about StarCraft

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 on how diseases and ideas spread from one person to another. While the models Kevin created may not specifically translate into how COVID-10 (or any other infectious disease) spreads, it gives us a good look at how a disease could spread given transmission rates, immunity rates, and more.

The entire posts rests on this concept of nodes, edges, and active/infected nodes:

Source: Melting Asphalt

Kevin extends this concept to show how a disease or idea can spread to other nodes. I really like this visualization of how a disease could spread from the rural areas to the cities (and how fast that disease can spread once it infiltrates the outer rings of a city):

Other Podcasts & Blog Posts

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

* AcquiredFM: Season #5 Episode #10 - The Lean Startup and the Long-Term Exchange (with Eric Ries)* The Financial Modeling Podcast: Season 2 Finale - Around the World with Excel

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 user at work today about a very similar issue on denormalizing a table of data. Here is the Google Sheets file with the problem for you to follow along with.

The person is trying to convert this table of data (notice the quarters and employee sums across the columns):

The actual data set has many more quarters and employee columns along the top

Into a table like this where the employees are listed in a column with corresponding quarters of data:

Original Tweet and video below:

https://twitter.com/MrExcel/status/1226478550552924160

https://www.youtube.com/watch?v=S9ow-1Jq_rE&feature=youtu.be

Get and Transform (Power Query) in Excel

The solution Bill walks through involves the Get and Transform feature in Excel. I'm on a Mac and don't have Get and Transform since you need to be an Office 365 subscriber and my version of Excel is too old, but the method of transforming the data reminds me a lot of the transformations OpenRefine provides you. Major benefit of OpenRefine is that it's free. After you unpivot the columns, Bill's solution involves a bit of text manipulation to get your categories/dimensions to look the way you want.

Personally, I would've approached the problem like this user did with using the INDEX and MATCH formulas to pinpoint the row/column of data you need from the original "bad" dataset and getting the data to show up in your clean table:

INDEX(UGLY_RNG,MATCH(CAT),MATCH(EMPLOYEE#)+QTR#)

I didn't explore this solution entirely, but I prefer solving these data structuring problems with formulas vs. macros and "applied steps" in Get and Transform.

No-Code and keeping your identity small

In the second half of this episode, I mention an episode with Shawn Wang from Netlify on the Software Engineering Daily podcast, and highly recommend checking out a few blog posts related to the talkL:

* Keep Your Identity Small by Paul Graham's * No code is a lie by Shawn Wang

Other Podcasts & Blog Posts

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

* Software Engineering Daily: No Code with Shawn Wang