Google Sheets - The Comprehensive Masterclass

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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

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

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

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.

Quick introduction to helpful lookup functions in Google Sheets.

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.

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.

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.

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

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.

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

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.

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.

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.

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

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.

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

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.

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.

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

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.

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

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