Topics → Data Analysis in Google Sheets

Includes

54 Tutorials

Free for Members

These videos deal with formulas and functions you may encounter a lot. Or ones you may never have seen before.

You’re a gold rusher, heading for the hills. You got your pickaxe and bucket. With these fantastic functions you can make sure to get the right gold, at the right time.

Data Analysis in Google Sheets takes all shapes and sizes. What we'll focus on is getting you skilled up with formulas, formula combinations, and advanced statistical analysis that at times can feel like a slog.

We'll turn your counting, summing, averaging, into a single formula. You will feel like a powerful wizard.

You'll be able to calculate complex data in a matter of moments.
You'll be able to turn your data from a mess into a story.
And when you encounter errors, you'll be able to wiggle your way out of the situation with ease.

Overwhelming No More
Google Sheets is a powerful tool for data analysis, but it can be overwhelming for beginners. However, with the right formulas and functions, you can easily transform raw data into meaningful insights. Here are some of the key benefits of using formulas and functions to do your data analysis in Google Sheets:

Save time: By using formulas and functions, you can perform complex calculations and manipulations on large datasets in just a few clicks.

Accuracy: Manual data manipulation can lead to errors, but formulas and functions eliminate the risk of mistakes and ensure accuracy.

Visualization: By using formulas and functions to analyze your data, you can turn it into charts and graphs that make it easier to interpret and communicate your findings.

Check for Errors
While we might think some of us are superhuman, we are really all human. Errors happen. But when errors happen we don't want to fall apart. We want to make sure of the veracity of our data, and we want to make sure that errors don't stop us.

Understanding errors and handling errors in our data analysis makes our day to day jobs so much easier. You'll be flying through, under, and over errors while your coworkers are copy/pasting/retyping their errors into Google and asking ChatGPT "what does this mean?"

Better Sheets members are able to run circles around ChatGPT'ers.

Forget About Frustrations
Upskilling yourself in statistics and data analysis, along with being able to handle formulas and errors, will lead to much less frustration inside the sheets.

You might even bring about WORLD PEACE!
I kid, I kid...

Let's start with making sure we can make progress bars, then you can make progress bars out of emojis, and even add responsive graphs to your sheets.

Specialized Tools for Special Situations.
Don't forget about the SWITCH formula. this is a great extra special tool along with IF() and IFS()

Learn all about especially useful tools, when the situation calls for them.

54 Tutorials

Create a Summary Tab with Min and Max

Free tutorial for finding Minimum and Maximum data points.

Intermediate

MAX() INDEX() MIN()

How to Validate Email Addresses

I will show you how to validate email addresses using the Mailboxlayer API and Google Sheets Apps Script.

I will guide you through the process of checking MX records, SMTP checks, and other cool features.

By the end of the video, you will be able to validate up to a thousand email addresses per month.

get your free apikey and 1,000 requests at mailboxlayer.com

Intermediate

How to Replace Text with a Blank Cell in Google Sheets

Learn the Quick Fix! Replace Text with Blank Cell in Google Sheets Hassle-Free. Say Goodbye to Data Glitches. Super helpful when you need to find and replace in google sheets any text.

Beginner

You Should Know The Limitations of Data Validation

You should know the difference between Data Validation and Data Veracity (or truthfulness of data)
This video points out some incredibly simple concepts of Data Validation you may want to use in your Google Sheets to make your data analysis better.

Beginner Intermediate

Turn Bland Sheets Into Glam Sheets!

A few tips to make your Google Sheets look better.

Beginner

Easily Write 1,000 Headlines

Showing you how to use the new Spintax add-on to write 1,000 headlines from a spinned version of a headline.
Get Spintax here: https://spintax.bettersheets.co/

Intermediate

Equal, Equal, Not Equal, Not Equal - How to Tell if Two Cells are the Same or Not The Same

Discover 4 easy ways to check if 2 cells in Excel are equal or not! Whether it's numbers, text, or dates, get the answer in seconds with simple tricks. Learn now in this video.

NE() EQ()

Weight Loss Tracker Template

I created a weight loss tracker template for myself and thought you, and all members would like it. So get it, copy it, and start your weight loss journey today. Comes with a nice chart already so all you have to do is add your numbers every day.

Beginner

REGEXMATCH Formula Generator for Google Sheets

Revolutionize the way you handle data with the BetterSheets REGEXMATCHER! Imagine having the ability to effortlessly validate text patterns, ensuring the integrity of your data. Whether you're searching for specific mentions, cleaning up unwanted content, or filtering out irrelevant information, our REGEXMATCHER is your go-to solution.

Error-Free Data:

Experience precise data validation ensuring your information is accurate and error-free.

Effortless Pattern Recognition:

Easily identify and eliminate unwanted data patterns, guaranteeing a clean and organized dataset.

Tailored Data Classification:

Classify your data based on specific patterns, streamlining your workflow and increasing efficiency.

Advanced

REGEXMATCH() IF()

Learn Google Sheet Formulas The Hard Way

One weird way to learn more about what Google Sheets can do. https://bettersheets.co/formulas

How to Merge Cells in Google Sheets

A master class on merging cells. How to merge two cells. How to merge multiple cells. How to merge horizontally, vertically, and why. Create groups. Create headers. And how to merge automatically. Members get the sheet and the apps script down below.

Beginner

JOIN() CONCATENATE()

Count Domains In a List of Emails - Part 2

Part two. Count domains of emails you get. List of emails and get which domains are there!

Advanced

3 Ways to Count in Google Sheets

The video explains three ways to count cells or information in Google Sheets: using the Explorer, using the COUNT function, and using the COUNTALL function. The Explorer is a tool located at the bottom right of the screen that allows you to quickly understand your data and find the count of items in a column. The COUNT function allows you to count the number of cells that contain numbers in a range of cells, while the COUNTALL function counts all the cells in a range, including those that contain formulas or are blank. The video also mentions other functions such as SUM and UNIQUE

Beginner

COUNTA() COUNTIF()

Twitter Sort Tool

I'll show you how I created a Twitter tool sort to organize my list of every tool I use to run better sheets.
Paste a list into Sheets and using a simple formula, I was able to determine the length of each item.

Then, I sorted the list in ascending order based on the length column. I even experimented with sorting it in descending order too!

Finally, I deleted the original list and pasted the sorted one using the "paste values" option. It's all done and ready to be shared. Check it out and let me know what you think! Don't forget to tweet about it if you find it helpful. Enjoy

Beginner

SORT() LEN()

How to Use AND(), OR() in IF() in Google Sheets For Multiple Criteria

One of the most simplest and useful formula combinations is using and and or inside of an if and what happens is we want to use this when we want an if formula to have multiple criteria.

Beginner

200 REGEXMATCH Examples in Google Sheets

Uncover the Magic of Regex Match! 🎩✨ Explore 200+ Examples at BetterSheets.co. Validate emails, detect hashtags, format data & more! Dive into the world of Regular Expressions. Start Your Regex Journey Now!

Use REGEXMATCHER to generate any REGEXTMATCH formula you want.

Conditional Formatting Data validation Advanced

REGEXMATCH() REGEXEXTRACT() IF()

Find Column with Header to Bullet Proof your Vlookup

Make your VLOOKUP formulas flexible and your data move-able with this trick.

Intermediate

MATCH() VLOOKUP() HLOOKUP()

What is this Ampersand Doing in Google Sheets?

What is "&" doing in this formula? MONTH(A1&1)
Do you know why this is so cool? It can replace an entire vlookup chart I've been making for years.

DATE() MONTH() CONCAT() VALUE()

Count How Many Subscribers In a Company by BetterSheets.co

I download it from MailChimp and then I try to figure out which companies are subscribed to me most. And I do, I have to figure out the domain and count the domain. And we're gonna do that all in Google Sheets.

Intermediate

COUNTIF() SORT() LOWER() SPLIT() UNIQUE() +1 more

Count Domains In a List of Emails - Part 1

I'm gonna talk about how to take a list of emails and figure out which domains count the domains. Few reasons we might do this is maybe we have a newsletter like I do, and we wanna find out which companies it's of like a business newsletter which companies subscribe to us more than others.

Intermediate

Member Asks: How do I calculate from a set of Form entries?

Use arrayformula to deal with Google form entries on a sheet. Even as they keep getting added.

ARRAYFORMULA()

Count Unique Attendees For Each Event

Find out how many people attended each event even if they are double counted in attendee lists.

Beginner

COUNTUNIQUEIFS() UNIQUE() COUNTUNIQUE() SUM() FILTER()

Allow Multiple Selections is in Google Sheets Yay!

Now you can select multiple options in a dropdown. Hip Hip Hooray!

Beginner

IFNA() SPLIT() JOIN() TRANSPOSE() FILTER() +1 more

User Filter a Google Sheet Database based on Dates, Checkboxes, Dropdown!

Search through and filter data to get what you want, when you want it. Tired of sifting through endless rows and columns? Learn how to filter your Google Sheet database with dates, checkboxes, and dropdowns. Simplify your workflow today!

Beginner

IF() FILTER()

Kristy Asks How to Get Percentages from Scores

How to figure out a percentage score when our reporting numbers are a formatted in a particular way. In this case we have a score and then a slash and then the total possible score in a single cell. We go through each option of solution and try to derive a solution that is both useful in the exact situation and possibly useful for other problems too.

We find that the Query formula and other solutions are inflexible or time intensive. We want a solution that does actually solve the problem at hand, in a quick way, and also is easy to edit and flexible to solve other problems.

Beginner

IF() ARRAYFORMULA() INDEX() ISBLANK() QUERY() +3 more

Figuring out the 80/20 Rule with a Running Sum Total

I figured out that 20% of my videos are getting 80% of the watch time on my YouTube channel, and I actually used a Google sheet to figure this out.

Beginner

SUM()

Create a Course Outline in Google Sheets

Wanted to share with members this course outline I am using for my own course making process. I outlined the course then added a bunch of cool little stats to help see my progress and keep me on a certain pace of video creation.

Beginner

REPT() IF() ISBLANK() CONCATENATE() COUNTA() +4 more

Figure Out Frequency

Fid out how to get the frequency of ProductIds, or Names, or Emails. or how many times they appear in a sheet/column/row.

Use Row() For Math You Need to Iterate

Create easy to use math iterators with the ROW() formula. It tells you the row your cell is on, but why do you need that? you can iterate math equations! It's neat!

Beginner

ROW()

Find The Most Common Word(s)

Find out which words in your sheet are the most common. Highest count of each individual word.

Intermediate

Highlight Row as You Move Your Cell Selection

Checking data? This script changes bg color of entire row as you move your cell selection.

Conditional Formatting Beginner

How Does Split Work?

We go deep into the syntax and usage of the SPLIT() formula.

Beginner

SPLIT()

Add Character Count and Character Limit

I'm gonna show you how to make a character counter and then account characters. And then I'm also gonna show you how to make a limit. It's really simple. It's one formula and some conditional formatting

Beginner

LEN()

How To Filter Dates (They Are Numbers Too!)

Dates are numbers so you can filter them with greater than and less than.

Beginner

FILTER() IFERROR()

Ian Asks: How to sort dates by year/month (can't do with filter)

In Excel you actually have these options of sorting by year, by month, by date. But let's do it in Google Sheets.

OpenSea Data Inside Sheets

Get floor prices into your spreadsheet with URLFETCHAPP. Accessing OpenSea's API directly from google sheets. Get any data you want. from Market Cap to One Hour Volume

Hardest Formula: SumIF | Addition Based on Dates / Values

Demystifying the SumIF Formula. Add up your values based on a condition, even if that condition is a date.

SUMIF() UNIQUE()

Countdown Blank Row

Great for counting writing sessions, or tweets you're writing.

Beginner

COUNTA() CONCATENATE()

Create an Auto-Update Sales Chart: Trailing 12 Months

how to create an auto-updating sales chart that displays the trailing 12 months of data in Google Sheets. The tutorial uses a sample dataset to demonstrate how to build a dynamic chart that updates automatically as new data is added.

Intermediate

SUM() IF() ISBLANK() INDEX() COUNTA()

3 More Ways to Count in Google Sheets

Learn how to use CountBlank() and more counting formulas.

Beginner

COUNTBLANK() COUNTIFS() COUNTUNIQUE()

How to Use AND() and OR() in IF() in Google Sheets For Multiple Criteria

One of the most simplest and useful formula combinations is using and and or inside of an if and what happens is we want to use this when we want an if formula to have multiple criteria.

Beginner

IF() AND() OR()

Capitalize Each Word

Every word can be capitalized with this formula.

UPPER() LOWER() PROPER()

Ben Asks: How Do I Add 1 Month?

Member Ben asks how to add 1 month to a date.

Intermediate

EDATE() TEXT() TODAY() CONCATENATE()

Use Min and Max for More Average Averages

What happens very often is we want to figure out an average averages, help us tell the story of a large set of data, but averages also can lie.

Beginner

MIN() MEDIAN() AVERAGE() MAX()

Switch Data

We're going through the switch formula because in the past if I've ever had to change, say a day of the week or a month on the calendar to a number, or I have to change that number to the month or text, it's really hard.

Intermediate

VLOOKUP() SWITCH()

Add Cool Responsive Graphs to Your Stock Tracking Sheets

Create little graphs with SPARKLINE() super easy!

INDEX() GOOGLEFINANCE() SPARKLINE() TODAY()

World Peace in Google Sheets

Combining GoogleTranslate() and DetectLanguage() to figure out what a language is without having to know the two letter codes. And adding a dropdown range to make it easier to find the language you want to translate to. What a great way to solve the frustrating world of International language!

Beginner

VLOOKUP() DETECTLANGUAGE() GOOGLETRANSLATE()

Add a Word Count to Google Sheets

Count your words inside a sheet. Great for ad writing, novel writing, or if you need to meet minimums.

IF() COUNTA() SPLIT() ISBLANK()

How Can I Compare Two Tabs for Differences?

So in this video we're going to compare two tabs and differences. Basically, someone asked actually on AppSumo, someone asked how do I compare two sheets to know what the differences are? This is the answer.

INDEX() IF() ROW() COLUMN() INDIRECT()

Most Common Errors and How To Handle Them in Google Sheets

Today we're gonna talk about errors in Google Sheets and how you can fix them. #N/A, #ERROR, #REF!, #NAME?, #DIV/0

Beginner

VLOOKUP() CONCATENATE()

Build a Domain Grader

Create a way to grade the quality of a domain based on many different votes and inputs and qualities. Like measuring the length and ranking the results.

COUNTIF() ARRAYFORMULA() COUNTA() LEN() SWITCH() +1 more

How To Track Crypto Prices

Get crypto prices into a Google Sheet.

Intermediate

GOOGLEFINANCE()

5 Ways to use IF() Formula to Create Magic in Your Sheets

We're going to create some magic here. Some if magic, sprinkle a little magic on your sheets, you can use these formulas in existing sheets.

Intermediate

AND() IF() SWITCH() ISBLANK() IFS() +1 more

Create a Progress Bar - Make Progress Bars with Sparkline: Part 1

Let's make a progress bar in Google Sheets.

SPARKLINE()