Google Sheets Masterclass

What You Achieve by the End of Essentials Preview 03:20

Sheets Basics for a Smooth Start Preview 07:14

In this lecture we’ll cover how to open a blank Google Sheet, how to create a new spreadsheet, and how to hide and unhide a Google sheet. We’ll also look at how to save and move a spreadsheet, and how to find a Google sheet in Google Drive.

Cells, Rows & Columns Preview 08:34

In this lecture we’ll take a closer look at the difference between a cell and a range in Google Sheets. We’ll also learn about rows and columns, how to enter information, and how you can wrap text in a cell.

Productivity Tips & File Sharing (Pro Tip) Preview 07:25

In this lecture we’ll learn about some useful productivity tips for Google Sheets. We’ll create a shortcut to Sheets, we’ll make Sheets available offline, and we’ll share and email a spreadsheet.

Useful Menu & Toolbar Options Preview 09:47

Now it’s time to get more familiar with the menu and the toolbar in Google Sheets. We’ll look at merging cells, and how you can format the content in your cells. We’ll also learn how to insert images and checkboxes.

Language & Spreadsheet Settings (Important) Preview 04:58

In this lecture we’ll learn about important spreadsheet settings for your Google Sheet. We’ll define the locale of the spreadsheet and how this setting affects the appearance of numbers, currencies and dates in your spreadsheet. We’ll also learn how to change the display language of Google Sheets.

Version Recovery & Edit History Preview 03:34

Let’s learn about Version Recovery and Edit History in Google Sheets. With these amazing features you can go back in time and restore an older version of the same spreadsheet, and even assign a name to a certain version. We’ll also cover how to show the edit history of a certain cell.

Activity: 5 Minute Timer Preview 00:30

Quiz: Test Your Knowledge - Getting Started

Data Entry, Data Types & Autofill in Google Sheets Preview 09:27

Now it’s time to learn about data entry in Google Sheets. We’ll cover important data types and how you can use Autofill instead of manually entering data.

Common Formulas in Google Sheets Preview 09:44

Now we’ll cover how to write a formula in Google Sheets and common calculations you’ll need. We’ll also learn about Paste Special options like paste as values, paste format only, and paste transposed.

Important Functions in Google Sheets (Sum, Count, Counta, CountUnique) Preview 09:47

In this lecture we’ll get familiar with the important functions SUM, AVERAGE, COUNT(A), and COUNTUNIQUE.

Relative Vs. Absolute Referencing Preview 06:45

Now it’s time to learn about Relative versus Absolute referencing. We’ll cover why this is such an important concept when it comes to writing formulas in Google Sheets.

Challenge: Find the Mistake! Data Doesn't Add Up! Preview 03:09

In this challenge you need to find a mistake. There is something wrong with a calculation: The numbers do not add up.

Key Takeaways: The Basics Preview 00:29

Useful Features for Report Design Preview 11:57

Useful Shortcuts (Pro Tips) Preview 11:47

Now it’s time to learn some important and useful shortcuts to work faster in Google Sheets. We’ll also learn how to enable compatible spreadsheet shortcuts to be even more efficient.

Conditional Formatting (Steer Attention to What Matters) Preview 09:42

In this lecture we’ll learn how to apply conditional formatting in Google Sheets. This way we can dynamically highlight the important parts in the data.

Challenge: Conditional Formatting with Checkboxes and Emails Preview 04:03

Now it’s time for a little challenge in Google Sheets. You’ll have to conditionally format with a color scale, highlight incorrect email addresses in a dataset, and test if the formatting you applied is dynamic.

Data Validation & Drop-down Lists Preview 11:46

In this lecture we’ll learn about data validation and how you can easily create drop-down lists in Google Sheets. By using these techniques you’ll be able to reduce data input errors in your spreadsheets.

Working with Excel & CSV Files (& Importing Data) Preview 09:11

We’ll cover how you can work with data from Excel or CSV files in Google Sheets. We’ll also learn how you can convert these files to Google Sheets format. When importing from a CSV file we’ll learn about separator types and how you can even append data to an existing Google Sheet.

Getting Data from Other Google Sheets (ImportRange) Preview 08:21

Now it’s time to look at how we can import data from other Google Sheets files. We’ll learn how to use the IMPORTRANGE function to do it in a dynamic way.

Best Practice in Designing Functional Spreadsheets (Pro Tips) Preview 06:56

Test Your Knowledge - Useful Features

Key Takeaways: Best Practice & Useful Features Preview 00:37

Sort Feature & What You Shouldn't Do Preview 08:29

We’ll look at the Sort Feature in Google Sheets and how you can use it to sort your values based on a single column or based on multiple columns. I’ll also show you what you need to watch out for and what you shouldn’t do when you use Sort.

Filter & Filter View in Sheets Preview 08:44

In this lecture we’ll learn how to quickly find what you need with Google Sheets Filter feature. We’ll also cover how you can create Filter Views. This way you can filter without impacting the original sort order of your data.

Calculations on Filtered Results - SubTotal (Pro Tip) Preview 04:44

We’ll learn how to make calculations on filtered data with the Subtotal function in Google Sheets.

Challenge: Delete Empty Rows in Data Preview 05:12

In this challenge your task is to clean the data in a spreadsheet. You need to delete the empty rows and get a list of all incomplete records.

Split Text, Remove Duplicates and Trim Spaces Preview 05:21

In this lecture we’ll learn how to split text to multiple columns and how to remove duplicates in Google Sheets to get a distinct list of values. We’ll also cover how to trim whitespace.

Find & Replace Values (Pro Tip) Preview 09:45

We’ll cover how to find and replace values in Google Sheets. We’ll also learn about Regular Expressions (Regex) in Google Sheets to even find and replace based on a pattern in your data.

Challenge: Which Items Does Each Customer Order? Preview 04:29

In this challenge you need to figure out which unique items each customer orders based on an existing dataset. In the process you will also have to extract the color from the item description.

Key Takeaways: Data Cleaning & Management Tools Preview 00:44

Important: How to Properly Read & Understand Functions Preview 08:54

We’ll start out with the important concepts when it comes to functions in Google Sheets. We’ll cover the 3 types of functions, discuss the importance of the function signature and how you can properly mix text and cell references. In addition we’ll also cover how to select a suitable range for your dataset.

Unique & Sort Functions Preview 08:59

Instead of using the Sort and Remove duplicate features in Google Sheets we’ll now look at the UNIQUE and SORT functions. This way the results are dynamic and automatically update as soon as the source data changes.

SUM(IFS) by Criteria (also COUNT, AVERAGE, MAX, MIN) Preview 12:22

In this lecture we’ll learn how to summarize data by one or more criteria. We’ll use Google Sheets built-in functions SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS and MINIFS.

Challenge: Summarize by Criteria & COUNTUNIQUE(IFS) Preview 10:05

Now it’s time to solve the challenge from the previous lecture. We’ll use a more complex formula in Google Sheets with COUNTIFS and a cell reference. In addition, we’ll use COUNTUNIQUE and COUNTUNIQUEIFS to find distinct positions in our dataset.

Rounding Values (the Right Way) Preview 06:22

We’ll learn how to round values up and down in Google Sheets the right way.

Logical & Information Functions (TRUE & FALSE) Preview 08:40

In Google Sheets you can use logical and information functions on their own but they can also be embedded in other functions. We’ll learn about Boolean Values and how we can use them in formulas.

IF Function (IF, Then) Preview 10:27

Now it’s time to learn about the IF function in Google Sheets. It returns a value depending on a logical expression. We’ll also cover how we can combine the IF function with the logical AND & OR operators.

Nested IF & IFS Functions (Pro Tip) Preview 09:05

We’ll learn how we can account for multiple conditions. This is when you will need a Nested IF or the IFS function.

Error Types & How to Handle Errors Preview 07:26

In this lecture we will learn about the different error types that are common in Google Sheets and how to handle these errors.

Named Ranges (Pro Tip) Preview 06:29

In Google Sheets we can define named ranges to make formulas easier to read.

Challenge: Average Sales between Selected Dates Preview 07:49

In this Challenge you need to calculate the average quantity sold between selected periods. In addition, find out if the average quantity during the selected period was higher or lower than the average quantity sold over the entire period.

Key Takeaways: Essential Functions to Summarize Preview 00:50

Overview of Helpful Lookup Functions Preview 01:52

Quick introduction to helpful lookup functions in Google Sheets.

VLOOKUP and HLOOKUP Preview 09:02

In this lecture we will cover 2 very common lookup functions in Google Sheets: VLOOKUP and HLOOKUP. With these functions we can lookup values from another list. I’ll show you the main rules on how to apply these formulas.

Approximate Match Lookup (Replacement for IFS - Pro Tip) Preview 05:41

I’ll show you 2 alternatives to lookup an approximate match in Google Sheets. We’ll use the IFS function and the much more efficient version with VLOOKUP.

FILTER Function to Return Multiple Match Results Preview 10:16

If you need to get all matches from a dataset in a dynamic way, then the FILTER function in Google Sheets is your best choice.

Lookup Pictures & Images (Pro Tip) Preview 09:27

In this lecture we’ll learn how easy it is in Google Sheets to lookup images.

SortN for Ranking (account for ties) Preview 08:07

With the SORTN function in Google Sheets you can restrict the number of results you want back after you sort your data based on a condition. We’ll also cover the different tie modes you can apply in the SORTN function.

Challenge: Find the Mistakes in the Formulas & Correct Them Preview 06:24

In this Challenge you’ll need to find the mistakes in several formulas that are not working or are returning errors.

Key Takeaways: Lookup & Filter Functions Preview 00:06

Useful & Easy Text Functions Preview 08:17

In this lecture we’ll cover some very useful text functions in Google Sheets. We’ll change the case of data to upper, lower and proper case. We’ll also use the Left, Mid and Right functions to extract text from a cell. We’ll also look at the Trim function to remove blank spaces from your data.

Search & Substitute Text (Pro Tip) Preview 10:08

Instead of using the Find and Replace feature you can use the Search and Substitute functions in Google Sheets. By using these functions, you can look and replace text in a dynamic way.

Split & Join Values to / from Multiple Columns Preview 05:46

With the Split function in Google Sheets we can split text from a single column to multiple columns by specifying a delimiter. The Join function allows us to concatenate the elements in multiple columns using a specified delimiter.

Challenge: Sort Multiple Words Inside a Cell Preview 03:49

Your task in this Challenge is to sort the names for team members inside a cell in ascending order.

Essential Date Functions Preview 10:26

In this lecture we’ll first look at how dates are stored in Google Sheets. Then we’ll learn about the essential date functions you can apply.

Time Calculations (Calculating Hours Worked) Preview 06:55

This lecture is about inputting and working with time properly and how you can calculate hours worked in Google Sheets.

Test Your Knowledge - Text & Date Functions

Key Takeaways: Useful Text Functions Preview 00:31

Detect Language and Translate Your Spreadsheets Preview 05:43

In this lecture we will learn how to use Google Translate functions to translate spreadsheets automatically into different languages. We’ll also cover how we can detect the language in a spreadsheet with a function.

Image Function (Pro Tip: Create QR Codes) Preview 06:40

With the Image function in Google Sheets you can bring images to your spreadsheet from any website. In addition, the Image function can be used to dynamically create scannable QR codes.

Import Tables From the Web (IMPORTHTML) Preview 05:23

The ImportHTML function in Google Sheets allows you to import tables and lists from websites.

Import Any Data from the Web (IMPORTXML) Preview 11:37

With the ImportXML function in Google Sheets you can import any data from any website. The data does not have to be formatted as a table or a list.

Stock & Currency Data with GOOGLEFINANCE Preview 09:09

With the GOOGLEFINANCE function in Google Sheets you get to import data about stocks, mutual funds, and currency exchange rates.

Challenge: Did You Invest in the Right Stock? Preview 06:15

In this challenge you need to calculate which stocks in a portfolio were a good choice and which ones resulted in a loss.

Key Takeaways: Google Functions & More Resources Preview 00:11

Sharing Files & Collaboration Tools Preview 04:27

In this video we’ll look at the different options you have in Google Sheets to share and collaborate on your spreadsheets.

Publishing Sheet or Chart (& Embedding in a Web Page) Preview 09:35

We’ll cover how you can publish the information in your Google Sheet to the web. We’ll learn how we can publish the spreadsheet as a link and how we can embed it as a part of a website.

Protection: Sheet & Cell Level Preview 08:47

Now let’s take a look at how you can protect your Google Sheets and how you can protect the information in certain ranges or cells.

Prepare for Print / PDF (& Page Breaks) Preview 09:21

In this lecture we’ll look at the different options to print your Google spreadsheet or to save it as a PDF document.

Challenge: Prepare Proper PDF Layout Preview 00:31

Use Google Forms to Collect Data (Pro Tip) Preview 10:38

With Google Forms you can collect data with online surveys and link them directly to your Google spreadsheet.

Key Takeaways: Collaborate, Protect & Collect Data Preview 00:39

Chart Basics (Column & Line Charts) Preview 12:26

In this lecture we’ll cover the chart basics: We’ll start out how you can create and customize column & line charts in Google Sheets.

Aggregating Data in Charts (Bar & Pie Charts) Preview 07:57

In this lecture we’ll learn how we can aggregate data automatically in any chart in Google Sheets. We’ll also cover how you can customize and format your chart area and add data labels.

Combo (Combination) Charts Preview 04:15

Sometimes you may want to have different chart types in Google Sheets for your data series in the same chart. That’s when you need a combo chart.

Scrolling Tables & Scorecards (Visualization Tools for Dashboards) Preview 06:36

In this lecture we’ll learn some important visualization features for Dashboards. We’ll cover how we can setup scrolling tables and scorecards in Google Sheets.

Gauge Chart (Visualization Tools for Dashboards) Preview 04:11

Now it’s time to cover how we can add Gauge Charts to a report in Google Sheets.

Add Slicers to Charts Preview 07:43

A slicer in Google Sheets allows you to filter chart data. In this lecture we’ll cover how we can create and use slicers.

Challenge: Better Dashboards (auto-sorted charts) Preview 11:50

In this Challenge you need to create a dashboard. It includes a sorted bar chart for the top 5 earners in a selected department. In addition, the dashboard should include a table showing all employees of the selected department sorted by salary in ascending order.

Sparklines to Impress Preview 11:30

In this lecture we‘ll learn how to create sparklines in Google Sheets.

Sparklines for Stock & Currency Trends (Pro Tip) Preview 09:38

Let’s see how we can use Sparklines in Google Sheets to visualize stock and currency trends.

Key Takeaways: Chart Tips Preview 00:30

Explore Feature in Google Sheets (Pro Tip) Preview 11:40

Let’s take a look at Google Sheets Explore Feature which helps you find answers to your questions by using artificial intelligence.

Pivot Table Basics in Google Sheets Preview 10:49

In this lecture we’ll learn how you can take full advantage of Pivot Tables to analyze your data in Google Sheets.

Filters & Slicers for Dynamic Pivot Tables Preview 08:20

Pro Tip: Grouping Items & Dates (by month, year) Preview 07:16

Custom Formulas in Pivot Tables Preview 08:39

Challenge: Master Pivot Tables in Google Sheets Preview 06:10

It’s time for a challenge again. If you master this, you master Pivot Tables.

Key Takeaways: Pivot Tables Preview 00:58

Congratulations! Get Ready for More Practice Preview 01:22

More Challenges & Practice Activities (You need this to pass the quiz) Preview 00:13

Google Sheets Essentials Quiz

Challenge Answers & Course Roadmap Preview 00:06

Introduction to Advanced Data Analysis Preview 02:10

INDEX MATCH for Complex Lookups Preview 12:18

Index and Match are two separate functions. But the real power of these functions shows when we use them in combination in Google Sheets. With these functions working together you can do lookups where the lookup column can be on the left OR on the right. We’ll also do matrix lookups where we have several conditions to meet.

Challenge: Find the Mistake! INDEX MATCH Problem Preview 06:57

In this challenge you need to find the mistakes again. You need to correct formulas that return incorrect values. In addition you need to use Index & Match to lookup values.

INDEX MATCH with Multiple Headers (Pro Tip) Preview 07:23

In this lecture we’ll cover how you can use Index & Match to do complex lookups for situations where you do not have unique column headers or unique row headers.

Array Formulas: ARRAYFORMULA Function Preview 06:28

Now we’ll have a look at what Arrays are in Google Sheets and why the ArrayFormula function can be really useful.

Create Own Arrays & Append Totals Preview 07:22

In this lecture we’ll learn about the importance of curly brackets to create an array in Google Sheets. We’ll also cover how this can be used to create a dynamic total row.

INDIRECT Function: Lookup from Different Sheets Preview 07:55

Generate Random Data & Text (RANDBETWEEN) Preview 10:39

In this lecture we’ll cover how you can easily generate random data and text in Google Sheets. We’ll learn about the feature to randomize a range. But we’ll also look at a dynamic solution with the RAND, RANDBETWEEN or the RANDARRAY function. We’ll also learn how we can randomly assign names from a list without repetitions.

The Power of SEQUENCE Function (Dynamic Yearly Calendar) Preview 11:28

Key Takeaways: Advanced Functions Preview 00:28

QUERY Function Syntax & Important Guidelines Preview 04:26

It’s time to uncover one of the most flexible and powerful functions in Google Sheets: The Query function. We’ll discuss the main rules and syntax.

QUERY Function Example (Select, Where, Order by) Preview 06:54

In this lecture we’ll do an example to cover the Query function basics.

QUERY Function Aggregation & Calculated Columns Preview 07:20

We’ll cover how we can aggregate values and how to create calculated columns with the Query Function in Google Sheets

Group by Multiple Columns & Pivot Columns (Query Function) Preview 07:12

Let’s take a look how we can aggregate values for multiple columns with the Query function.

QUERY Function with Drop-down List (Dynamic Cell References) Preview 06:30

In this lecture I’ll show you how you can connect the Query function with a data validation drop-down list in Google Sheets.

QUERY Function with Dynamic Totals (Pro Tip) Preview 07:06

I’ll show you how you can append a dynamic total row to a report by using the Query Function and Array Syntax in Google Sheets.

Working with Dates & Months in QUERY Function Preview 08:50

I’ll show you how you can overcome the challenge to use dates in the select statement of the Query function in Google Sheets.

Combine Data from Multiple Sheets with QUERY Function Preview 06:30

Now we’ll check out how we can combine data from different sheets or from an external file and use that combination in Google Sheets Query function.

QUERY Function & IMPORTRANGE to Combine Data from an External File Preview 04:22

In this lecture we’ll learn how we can combine data from an external file. We’ll use the IMPORTRANGE function together with the Query function.

Test Your Knowledge - QUERY Function Syntax

Key Takeaways: Query Function Preview 00:33

Custom Number Formatting Explained Preview 12:50

In this lecture we’ll cover an underrated feature in spreadsheets: Custom Number Formatting. When you learn to use this properly you can create impressive spreadsheets.

Custom Formatting for Impressive Reports with Symbols (Pro Tip) Preview 08:25

In this lecture I’ll show you a really cool custom formatting trick. We’ll use emojis and symbols to visualize variances.

Conditional Formatting with Formulas (The secret to getting it right) Preview 07:46

Now it’s time to learn Advanced Conditional Formatting. We’ll cover how to use Custom Formulas with Conditional Formatting in Google Sheets.

Challenge: Find the Difference Between Two Lists Preview 05:56

In this challenge you have to solve a challenge for Conditional formatting in Google Sheets. You need to find the differences between two lists and highlight the cells that are different with Conditional Formatting.

Dependent Drop-down Lists in Google Sheets Preview 05:29

In this lecture we’ll cover how we can create Dependent Drop-Down lists in Google Sheets.

Dependent Drop-down for Every Row in Sheets (Pro Tip) Preview 04:42

Now it’s time to learn a common requirement for many reports. I’ll show you my trick how you can create dependent drop down lists in Google Sheets on every row.

Challenge: Dependent drop-down Lists in Google Sheets Preview 05:40

In this challenge you will create a dependent drop-down list. In addition, you need to calculate the total sales and total profit for the selected items.

Key Takeaways: Advanced Features Preview 00:26

Google Sheets Macros: Record your First Macro Preview 07:59

Now it’s time to learn how we can automate repetitive tasks using Apps Script in Google Sheets.

Relative Vs. Absolute Macro Recording: Practical Example Preview 06:22

In this lecture we’ll practice recording macros with absolute and relative referencing. We’ll also learn how we can combine different Macros together inside the Apps Script Code.

Combining Macros in Sheets (Mix Absolute & Relative Reference) Preview 04:51

Challenge: Automate Chart Creation with Your Macro Preview 03:52

In this challenge your task is to record a macro that automatically creates a pie chart in Google Sheets.

Reset Data Validation List with Apps Script in Sheets Preview 06:54

Now it’s time to take a look at how we can use Google Apps Script to reset the value of a dependent drop-down whenever we change the value of the first dropdown.

Find & Install Add-Ons (Goal Seek example) Preview 09:12

In this lecture we’ll cover what Add-Ons are in Google Sheet and how you can install and use them.

Key Takeaways: Apps Script & Links to More Resources Preview 00:40

Extras: The Mechanics of the Quiz Preview 08:22

Final Words Preview 01:35

BONUS! More Resources Preview 01:32