Dear Analyst

While this little Excel/Google Sheets trick is a pretty straightforward hack, it led me to think about how we use our tools, how we stretch the capabilities of our tools, and think outside the box to come to arrive at a solution. Seems like a lot for a formula trick on counting the number of words in a cell to bring to the table. Perhaps I'm looking into it too much. Perhaps it's just me rambling and opining on something meaningless. Or perhaps, it might cause you to stop, think, and reflect for even a minute about something as trivial as counting words in a cell. Come on this journey with me and learn how a stupid formula trick triggered my synapses to fire in a million directions. Link to the Google Sheet with the formula is here.

Video tutorial of the formula to count the number of words in a cell:

https://youtu.be/JRp5BSr5kuI

Why would you want to count words in a cell?

It's a great question. Maybe you need to see how many words are in a paragraph before you submit some online form that only allows you to submit an answer with 150 characters or 50 words or less. I've probably had to count the number of words in a cell a handful of times and it was probably for cleaning data purposes (more on this later). What may be more common is counting the number of characters in a cell to detect anomalies. In any event, the formula you use for counting the number of words in a cell is similar to how you might count the number of characters in a cell. If you want to skip straight to the answer (or doing a search on Google and maybe this will be highlighted in yellow):

=len(A2)-len(substitute(A2," ",""))+1

This formula should work in Excel and Google Sheets and A2 contains the cell with the words you are trying to count. Let's break this down a bit more, because when you break things down that's where the real learning takes place and it may spur other ideas you can incorporate into your spreadsheets.

Counting and substituting stuff

Composability gives formulas some pretty amazing capabilities. Greater than the sum of its parts kind of thing. One their own, the LEN() and SUBSTITUTE() functions do pretty standard things. The LEN function simply counts the number of characters (including spaces) in a cell:

SUBSTITUTE acts as you might expect. Turn all the "A"s in a cell into "X"s. Turn all the 5s into 9s. The first argument is the cell that contains the data, the second argument is what you are searching for in the value to replace, and the third argument is what everything in the second argument should be replaced with. In the example below, We are looking for all the spaces in cell A5 and replacing them with an empty string. Note the syntax here. A space (what we are looking for) is denoted by two double quotes with a space in between: " ". An empty string is two double quotes next to each other with no space in between them: "". The result, in this case, are sentences with no spaces in between them:

Composability is where the magic happens

What happens when you combine the two formulas together? You may be an Excel or Google Sheets guru and have built advanced nested formulas before. When I step back and see how these formulas--when combined--create interesting results that you wouldn't have expected. People say we're just number crunchers and just know when and how to use formulas correctly.

I'd say the composability of formulas is what inspires creativity and makes bui...

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 to combine the technical aspects of being a data analyst with other careers like science, art, and even wastewater treatment. Luke Barousse is a data analyst and YouTuber and we chatted about how he learned Excel, built a portfolio of his data work, and becoming a YouTuber.

From the U.S. Navy to Excel

Prior to joining the U.S. Navy, Luke took a C++ course as an undergrad and got a taste of coding. After joining the Navy and working in various roles, he didn't get a chance to utilize some of the coding skills he learned at university. Eventually he went to get his MBA and took an Excel course taught by Professor Elliot Bendoly (who also wrote the book Excel Basics to Blackbelt). Luke started seeing the potential of Excel as he dug into VBA and some of the coding capabilities in Excel.

In terms of content creation, Luke's day-to-day experience as a data analyst influences the videos he creates. The main reason he started getting into creating videos was because his colleagues wanted him to show them how to do things in Excel and other tools. Instead of teaching each of his colleagues one by one, he created videos to avoid the repetitive nature of teaching in person.

I realized that content creation is a way to automate teaching.

Using social media to share a Google Sheets template

For the class' capstone project, Luke built a meal prep Excel file. Even after the class was over, he took the meal prep Excel file tried to turn it into an application. He transferred it from Excel to Google Sheets and it started to gain some traction. He used Instagram and created content around meal prep and drew more attention to the Google Sheet template. Eventually he started using Python and Django to try and create an application since people were always messing up the formulas on the Google Sheet.

I find it interesting that Google Sheets template become the lowest common denominator when you need to create and share a simple tool, and are ok with it being rough around the edges. It may be too costly (and frankly overkill) to create a custom application with code with a Google Sheets template will suffice.

Realizing Excel is not the solution

Luke realized the Excel file Luke he created for his meal prep use case was going to be an issue because Excel is not the best medium to distribute his template (hence the move to Google Sheets). As Luke started using Excel more at work, he found his Excel files were constantly hitting row limits. His team worked with different suppliers and new data would get ingested every week into the Excel file. He also was building formula on top of formula and the result was an Excel file that took 2-3 minutes to load and was buggy with the cobbled together formulas. Luke details all this in this video below:

https://www.youtube.com/watch?v=3TBwY4VjLX8

Even after he left that group, his old teammates still asked him to update the Excel file since they didn't know how. In situations like this,

The impetus for this episode is a new Google Sheet (and Excel) tip I just shared on Instagram and TikTok (I never thought I'd join these platforms to start posting tips but alas, this is how people learn these days). After I learned how to be productive the PC version of Excel, I opened my Macbook and realized all my favorite shortcuts didn't carry over. Back in the day (whatever that means), you had a ThinkPad at work where you did your "serious work" and your personal Mac was for doing the "personal stuff." I found the PC equivalent shortcuts for the Mac and was able to be dangerous again in Excel and Google Sheets on the Mac. But there was one group of shortcuts I couldn't quite duplicate until I changed one little setting on my Mac.

Function keys on the Macbook

By default, the function keys on your Macbook do things on your Mac OS like increase brightness (F2), see/search Mac apps (F4), or decrease volume (F11).

If you're coming from using Excel on the PC, you know that these function keys are coveted tools in being faster in Excel (and Google Sheets). The most useless key is probably F1 because it brings up the help menu, and you might hit it by accident when you're debugging a formula and alternating between pressing F2 and ESC. Excel users will go as far as popping out the F1 key from their keyboard so they don't accidentally hit it and having to wait a few seconds for the help menu to open only to close it right away:

Change the default behavior of the functions keys for Mac Excel

To "unlock" the power of the function keys on your Mac so that they do what you expect them to do (like on a PC), follow these steps (this is for MacOS Big Sure v11.4):

1. Click the Apple icon in top-left and open system preferences

2. Click on "Keyboard"

3. Click on the "Use F1, F2, etc." checkbox

With this simple setting checked off, you can now use the function keys like you're used to using them on the PC. The downside is (of course everything has a tradeoff) is if you want to increase the brightness on your Mac, you need to press the FN key PLUS the F2 key. With these function keys enabled, some of these common operations become available to you (using Google Sheets as an example):

Enter "edit" mode in a cell formula (F2)

By tapping F2 you can go into the cell and start editing the formula. Once you're in the formula you can use the arrow keys to move around and when you're done editing the formula, just hit ESC. In the gif below, I'm just alternating between hitting F2 and ESC to get into the formulas in B2 and C4:

Alternate between locking and unlocking cell references (F4)

One of my favorite shortcuts when working on a big hairy formula is locking and unlocking the cell reference without having to manually type in the dollar sign in front of the column or row. The manual way of doing this is using your left and right arrows to move to the different cell references and typing in the dollar sign (or deleting them one by one):

With the F4 key, you can move your cursor to the cell reference and cycle between locking the row, the column, both the row & column, or nothing at all and keeping the reference as relative:

I tried to show the power of the F4 key in a pithy way in my first...

Learning the tips and tricks for doing data analysis in Excel is great and all, but stepping back to see the bigger picture leads to better questions (and answers) you can ask as an analyst. Benn Stancil is one of the founders at Mode Analytics, a data visualization platform you may have used before. Benn has done a ton of typical "analyst" work (check out his newsletter and previous Medium blog) but in this episode we talk about building Mode in the early days, asking good questions, and of course a little bit about Excel and the tools he uses.

Life before and founding story of Mode Analytics

Benn was a math and economics major in college. He worked in DC for a few years for a think tank doing economic policy research. It was an interesting time to do this type of analysis because this was right around the start of the financial crisis in 2008. Frustrated with his research not being able to make an impact given the glacial pace government adopts change, Benn ended up finding a job at Yammer (a social network for professionals, sound familiar?). Yammer was acquired in 2012 by Microsoft and Benn and some folks from the analytics team at Yammer left Microsoft to start Mode.

I would take a look at this blog post from Benn to get his reflections on the early days at Mode, but here is a quick summary. The founding team at Mode includes Derek Steer (CEO), Josh Ferguson (CTO), and Benn (the analytics guy). Before there was really a product, Derek was off talking to investors and Josh is talking to the engineers. Benn's expertise is in doing data-related stuff, but the problem is there wasn't a lot of data to analyze or explore and not many customers to get data from.

In the early days, Benn wrote blog posts that were generally about data but not about data products. He was basically doing content marketing for a small nice of data professionals. Today this is called data journalism and Benn was writing data stories related to sports (before 538 became ta thing). Once Mode had more customers, Benn's role changed often as he did tours of duties through marketing, customer support, sales, and a variety of other roles.

You have a "job title" and a "role" and those end up being two are very different things.

We talked a bit about a blog post Benn wrote in 2015 that had some traction in the data community on Facebook's "magic metric" of getting 7 friends in 10 days. The key takeaway is that Benn was doing the analysis in Excel, R, and a little python for web scraping. Benn had taught himself how to use some of these tools before. Now that he had a goal to work towards (creating these blog posts), this was the extra push for him to get over the hump to learn these tools more in-depth.

Source: Mode blog

Key skills for doing exploratory data analysis

I liked Benn's answer to this open-ended question because it doesn't involve mastering X tool or taking advanced statistics classes:

First and foremost you have to be curious. Be relentless in knowing there's a better answer out there.

Most analysts get a dataset and just look at the data to start generating questions as they do the analysis.

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 techniques that I personally didn't even know. I then pulled out the skills I think analysts would need the most (80/20 baby!) to be productive in their jobs and put them into this fast-paced 1-hour advanced PivotTable class. As a small teaser, I go through a calculated field technique for calculating percentages in your PivotTables below. To see some of my beginner Excel classes, take a look here. I'll be creating more bite-sized content on Instagram as well.

Click below to learn more and sign up for my advanced PivotTable class:

Credit card customer attrition data

This example is actually from the workbook used in the class project of my Advanced PivotTable class. This is the Google Sheet that shows the problem we're trying to solve. Let's take a quick look at the data:

It's a list of credit card customers and some demographic information about them. The most important column to note is the Attrition column because it indicates whether that specific customer churned or attrited (had to look up the past tense of attrition). This type of customer data would be great to summarize and analyze in a PivotTable like so:

You can get some summary stats about your customers, but what about the Attrition? If you throw that column into the PivotTable, you'll get something like this:

Not very helpful because our Attrition column consists of "Yes" and "No" as values. What I really care about is finding the Attrition % no matter how I set up my PivotTable. You could do something like this where you drag the Attrition column into the columns of the PivotTable. This would get you the Attrition % but it's a manual calculation and you can't see the Attrition % by different columns and properties in your PivotTable:

The minute you change up the PivotTable, column E will potentially get overwritten and you'll have to re-write the Attrition % for the cut of the data you care about. In order to get the Attrition % you may be thinking the calculated field is the way to go. That's partially right, so let's explore that option.

Adding a calculated field in Google Sheets for Attrition %

Adding a calculated field to your PivotTable in Google Sheets is similar to Excel. You have to go through the right sidebar instead of the ribbon:

A little known fact about PivotTables in Google Sheets or Excel (something I go over in my Advanced PivotTables class) is that you can add IF() statements to calculated fields. If we try to create a calculated field for Attrition %, however, we don't have the right data type to create this percentage. Additionally, the columns you put into the calculated field are summed. I tried experimenting with a few variations, but ultimately I couldn't find a formula to create a calculated field given the data we have:

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 "target" worksheet. When the source worksheet is updated with new sales or customers data, your target worksheet gets updated as well. On top of that, the data that shows up in your target worksheet should be filtered so you only see the data that you need and matters to you. The key to doing this is the IMPORTRANGE() function in conjunction with the FILTER() or QUERY() functions. I'll go over two methods for importing data from another Google Sheet and talk about the pros and cons of each. You can use this "source" Google Sheet as the raw data and see this target Google Sheet which contains the formulas.

Watch a video tutorial of this post/episode below:

https://youtu.be/7QLnAP0zHIM

Your Google Sheet is your database

No matter which team you work on, at one point or another your main "database" or "source of truth" was some random Google Sheet. This Google Sheet might have been created by someone in your operations or data engineering team. It may be a data dump from your company's internal database and whether you like it or not, it contains business-critical data and your team can't operate without it. The Google Sheet might contain customers data, marketing campaign data, or maybe bug report data that is exported from your team's Jira workspace.

The reasons why people default to using Google Sheets as their "database" is because anyone can access it in their browser, and more importantly, you can share that Sheet easily with people as long as you have their email address. This is probably your security team's worst nightmare, but at this point too many teams rely on this Google Sheet so it's hard to break away from it as a solution.

Credit card customer data

Before we get into the solution, let's take a look at our data set. Our "source" dataset is a bunch of credit card customer data (5,000 rows) with a customer's demographic and credit card spending data:

There are a ton of columns in this dataset I don't care about. I also only want to see the rows where the Education_Level is "Graduate" and the Income_Category is "$80K-$120K." Perhaps I'm doing an analysis on credit card customers who are high earners and have graduated some college. How do I get that filtered data of graduates earning $80K-$120K into this "target" Sheet:

Google Sheets is not the most ideal solution as a database, but you gotta live with it so let's see how we can get the data we need from our source Google Sheet over to the target. The money function is IMPORTRANGE() but there are multiple ways of using IMPORTRANGE() as I describe below.

Method 1: The long way with FILTER() and INDEX()

When you use the IMPORTRANGE() function on its own, you will just get all the data from your source Sheet into your target Sheet. In this formula below, I just get all the data from columns A:U in my source Shee...

If your dataset doesn't have a unique identifier (e.g. customer ID, location ID, etc.), sometimes you have to make one up. The reason you need this unique ID is to summarize your dataset into a nice report to be shared with a client or internal stakeholders. Usually your dataset will have some kind of unique identifier like customer ID or transaction ID because that row of data might be used with some other dataset. It's rare these days not to have one. Here are a few methods for creating your own unique identifiers using this list of customer transaction data (Google Sheets for this episode here).

https://youtu.be/fjkO0kHbbKw

Method 1: Create a sequential list of numbers as unique IDs

Each of these transactions is from a unique customer on a unique date for a unique product. We could do something as simple as creating a sequential list of numbers to "mark" each transaction. Maybe we can prefix this new transaction ID column with "tx-" so each unique ID will look something like this:

This method involves creating a dummy column (column I) of sequential numbers. Then in column A, you write "tx-" followed by the number you created in column I, and you have a unique ID. This unique ID is only relevant for this dataset, however. If there are other tables of data related to customers and transactions, those tables won't know about this new transaction ID you just created on the fly.

Method 2: Create random numbers as unique ID

This method will make your unique IDs feel a little more "unique" since the numbers are randomized:

Notice how we happen to take the result of the RAND() function and multiply it by 100,000 to get a random number with 5 digits. Our dataset is only 1,000 rows long so the chances of duplicate values is low, but there still exists that possibility.

This is probably the least preferred solution because of the fact that there could be duplicate values (there are formula hacks to get around it). Another reason this isn't a great solution is that you have to remember to copy and paste values from the random numbers into another column. The RAND() function is a volatile function (basically changes every time you reload the Sheet) so you would lose your unique ID every time the Sheet loads. This means you have to remember to paste just the values perhaps in the next column over before referencing that value as your unique ID.

Finally, if your dataset has timestamps like this, chances are the unique IDs are meant to be sequential (using Method 1). Assigning random unique IDs to each transaction might make reconciling the data in the future more difficult.

Method 3: Concatenate (add) columns together to create unique ID

This method involves concatenating (adding) together different columns to create a unique ID. The reason I like this method is because it makes creating reports a bit easier since you can write in the values in a cell for a lookup to reference. For instance, the unique IDs in our dataset is created by combining the Customer ID, SKU_Category, and SKU columns:

We put a dash "-" in between each of the cell references so it's a bit easier to see all the different characters in this "unique ID." The issue is this: what if there are multiple transactions with the same Customer ID, SKU_Category, and SKU? We insert a COUNTIF column in between columns B and C to count ...

This episode is actually a recap of a talk I gave at a meetup. After reflecting a bit about the subject matter, I wanted to discuss some other topics that are more important than writing VBA scripts or doing stuff in Excel. At the meetup, I discussed a VBA script and Google App Script I wrote for filling values down in a column. I actually published these scripts in a previous episode, but went in-depth during the meetup on how the scripts work. If I step back for a minute and ask myself: "why did I create these scripts in the first place?" To solve a simple problem that I'm sure many analysts come across. More importantly, it's a problem that doesn't have a clear solution which the our current software (Excel and Google Sheets) can fix easily.

Software that fixes your problems

For those of you who are:

* Using a recent version of Excel* On a PC* Have a Microsoft 365 subscription (depending on your package)

Congratulations! You are able to use Power Query to transform and clean "dirty" data and the problem described in this episode is easily solved with the software. All you have to do is click this option in Power Query to fill values down:

For the rest of us (Mac Excel or Google Sheets users), you're stuck doing this manually. Why does this feature have to be reserved to a small group (relatively speaking) when this problem is faced by thousands of people who may not have the same access as someone who works in the enterprise?

Cleaning data is part of any analyst's job and we should be able to do these tasks as quick as possible so that we can move onto more interesting projects. The fact that you need to have Power Query to fill values down like this is annoying to me. Do you ever go out of your way to prove a point; even if it's an extremely inefficient use of your time? Creating these VBA and Google App Scripts was just that for me. Instead of relying on the software to do the job for me, I created hacked up an inelegant but simple solution to hopefully give people more access to simple tools for cleaning up data.

Building for an audience of one

I might be over-estimating the number of people who have this fill values down problem. Maybe it's a few hundred people? Maybe less than 100? Who knows. The important thing is that I had the problem and needed to solve the problem for myself.

Perhaps you are in a position where you can't spend a few hours to learn how to write a script to automate one aspect of your job. That's understandable. You need to crank out reports and time spent away from cranking means you'll have to work after hours to get your job done.

I used to be on that hamster wheel, until I stepped back and saw the forest for the trees. Excel is just one tool in your vast array of tools to analyze and visualize data. There's a whole world of databases, data pipelines, machine learning, and more for you to explore. Just staying in the "Excel lane" is how one gets pigeonholed into a job, a career, a life.

Learning how to write scripts changed my perspective on more than just Excel. I realized I could build tools that help others save time because I knew it saved me time. By building for an audience of one, you are in fact building for an audience of many.

Meetup recap

This write-up definitely meandered a bit but I think that's ok. You can watch the recap of the meetup below and get lost in the details on how I loop through arrays to m...

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 to reference the additional rows that show up at the bottom of your source data. This may not be a big issue for you because maybe you're not getting new data added often so manually going into the PivotTable settings and changing the reference to the source data doesn't feel onerous. If you have new data coming in every day or every hour, you may want to automate this process.

Here are a few methods to accomplish this in both Excel and Google Sheet. My preferred method is to turn your source data into a table in Excel or reference the entire columns in Google Sheets. Download the Excel file or copy the Google Sheets with the dataset for this episode.

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

Ramen ratings from ramenphiles

I'm a big fan of these niche datasets like the one for this episode. It's a list of ramen products and their ratings created by a website called The Ramen Rater. The list consists of 2,500 ramen products along with that product's country of origin, the style (Pack or Bowl), and of course the rating. It appears the ratings are all done by one person. More importantly, the list contains the full name of the ramen product which means you can do some interesting text analysis to see what words are used most often in ramen products, how words might correlate with ratings, etc. For our purposes, this dataset is a great for creating a PivotTable with the rating being the main metric to analyze.

Method 1: Reference the entire column

Excel PivotTables

As shown in the first screenshot, the source data for the PivotTable in the Excel file comes from the "ramen-ratings" worksheet from cells $A$1:$G$2581. As you add more data to the source data, you'll have to change the source reference to reference a higher row number. If you add 10 more ramen ratings, you'll have to change the PivotTable reference to $A$1:$G$2591. We want to avoid having to change the reference every time we add new data, so we can just reference the entire columns in $A:$G:

The problem is the PivotTable we have in the "Ramen Pivot Table" worksheet now has this "(blank)" item in both the columns and rows fields of our PivotTable. Why? Because we're referencing a bunch of empty rows of empty countries and ramen styles:

This isn't a huge issue, because we can just remove the "(blank)" via the row and column filters:

Now when you add new rows of ramen ratings to the source data and then you refresh the PivotTable, the PivotTable will automatically pick up all the new rows of data since it's referencing the entire columns from column A to column G.

Google Sheets PivotTables

The same solution applies to Google Sheets:

I find the user interface much easier to use in Google Sheets for a variety of reasons:

* Less clicks - Right when you click on the PivotTable (as shown in the above gif), you can see and edit the source data in the top right of the PivotTable...

When you think of sports and data, you may think about all the data collect on player performance and game stats. There's another world of sports data that is usually overlooked: the fans. In this episode, I speak with Caiti Donovan, the VP of Data & Insights at Sports Innovation Lab, a sports market research firm. Caiti started her career in marketing and business development at Viacom and Spotify where she used data storytelling to work with advertisers and partners. More recently she learned how build the data systems she was once only a consumer of. We'll discuss how she made the transition to data, getting a data science certification at The Fu Foundation School of Engineering and Applied Science at Columbia University, and current projects she's working on at Sports Innovation Lab.

Working with data at ViacomCBS and Spotify

Caiti spent 15 years in marketing and sales roles where data was a core part of her day-to-day projects. She used a lot of proprietary data systems and even helped build some of these systems. Using the data available to her, she'd take different datasets and turn the data into a format useful for data storytelling. These stories would be used for partnership development or working with advertisers. Data storytelling is a common theme on this podcast. See episode 62 with Janie Ho, episode 56 with John Napolean-Kuofie, and episode 35 on the Shape of Dreams.

At ViacomCBS, Caiti would look at the data behind shows like Jersey Shore and SpongeBob to see what type of revenue opportunities her team could create based on the audience of these shows. The data could also be analyzed to help inform content development for these shows. The goal was to understand their younger fans and figure out what it meant to have conversations with the fans of these shows.

After a stint working with a few startups in a consulting capacity, Caiti eventually landed at Spotify. At the time, Spotify had a hard time turning all the data they were sitting on into narratives in a B2B and B2C context. She worked with clients like the NBA, Ford, and Nike. In terms of the data stories she was saying to her clients from a B2B perspective, she also had to make sure it carried over to the B2C side (Spotify subscribers).

From there, Caiti made a big hump from entertainment to sports. She realized her "purpose meets passion" moment is finding ways to use data to have impact on the world. She wanted to tackle challenges faced by women in sports and also find a way to better connect with the fans of women's sports. Caiti eventually co-founded the non-profit SheIS Sport to bring together every single professional women's sports league. Through this experience, Caiti learned a lot about the biases and inequities in data in the sports world. She realized she needed more technical expertise to have a direct impact on how data is collected and analyzed in this world, and went back to school for data science (more on this later).