The Complete Google Sheets Beginner To Advanced

  1. Home
  2. Google-sheets
  3. The Complete Google Sheets Beginner To Advanced

Tags: Google Sheets

A crash course in basic and advanced functions: Learn VLOOKUP, QUERY & MORE! Use many of these skills in Excel too!

Last updated 2022-01-10 | 4.6

- Create beautiful spreadsheets with amazing formatting tricks
- Learn to manipulate text in formulas
- Connect multiple Google Sheets together using a single formula

What you'll learn

Create beautiful spreadsheets with amazing formatting tricks
Learn to manipulate text in formulas
Connect multiple Google Sheets together using a single formula
Analyze data sets with ease using complex functions & formulas
Learn to build incredible data visualizations using charts
Feel completely comfortable in a spreadsheet environment
Learn formulas that work in both Google sheets & Excel!

* Requirements

* A computer
* a Google account
* and the ability to navigate the internet
* Know how to use a keyboard!

Description

In this course you will learn the fundamentals of Google Sheets (much of which translates to MS Excel!). You will not only learn the basics, like adding and subtracting. But you will also learn valuable advanced formulas like VLOOKUP, INDEX(MATCH()MATCH()), and IMPORTRANGE.

Never heard of those before? Don't worry! I start from the beginning - so those terms will become clear when the time is right.

Along the way, you will develop an amazing spreadsheet toolkit. Wondering what tools will be in that toolkit? Check out the list below for some highlights of the course:

  1. Learn the basics like how to create a Google Account and a Google Spreadsheet

  2. Arithmetic Functions like SUM, COUNT, and AVERAGE

  3. Shortcuts like filling formulas across THOUSANDS of cells

  4. Advanced charts & Beautiful Visualizations

  5. PIVOT TABLES - though I don't particularly like them...

  6. Advanced functions like INDEX MATCH MATCH and IMPORT RANGE

  7. QUERY, the function that DOES IT ALL!

Ultimately, the point of this course is to get some awesome skills for professional or personal use. And - above all - have fun doing it!

My course isn't like a lot of other online Excel or Google Sheet courses. Most of these courses force you to watch them build things and hope that you understand what they are doing. Instead of that old model, I've incorporated everything I've learned from my experience in the real professional world to make this the best online Google sheets course. The course includes:

  • Lectures

  • Activities

  • Projects

  • Exercises

  • Slides

  • Comprehensive Workbooks WITH Answer Keys

  • Extra Learning Resources

If you have any questions, please don't hesitate to contact me. Sign up today and see how fun, exciting, and rewarding web development can be!


Who this course is for:

  • Spreadsheet users of all levels, those who want to get better at analyzing data or building spreadsheets for personal use!
  • This course is not for you if you are an experienced data analyst or power spreadsheet user.
  • Students who want to start a career in management consulting or tech operations!

Course content

13 sections • 66 lectures

Spreadsheet Basics! What are they and how do they work? 8 lectures

Introduction: MANDATORY PLEASE READ Preview 00:24

Students will be able to create a Google account & spreadsheet.

Pre-Work: Creating a Google Drive Account Preview 01:20

Pre-Work: Creating a copy of my Spreadsheet Templates! Preview 02:50

Spreadsheet Basics! What are they and how do they work? Preview 06:22

Toolbar Walkthrough Preview 10:27

Data Types in a Spreadsheet Preview 06:34

Navigating Spreadsheets with Shortcuts! Preview 11:19

Simple Formatting Tricks - Beautify your Spreadsheets! Preview 05:55

Introduction to Functions! 4 lectures

Get Familiar with SUM, COUNT, AVERAGE, & MORE! Preview 13:38

More Simple Functions: COUNTA, MIN & MAX Preview 07:19

Intro to Activity: Your First Day at the Dumpling Stand Preview 02:58

Solution to Activity: Your First Day at the Dumpling Stand Preview 16:34

Conditional Functions 6 lectures

The IF Statement Preview 11:34

AND/OR Statements Preview 09:10

Nesting IF Statements & the IFS Statement Preview 11:08

Solution to Activity & Crash Course in Conditional Formatting! Preview 12:54

Conditional Arithmetic Functions (ie. SUM + IF) Preview 08:39

HW Problem Solution & Wrap Up Preview 11:19

Pivot Tables, Sorting, and Filtering! 10 lectures

Introduction to Raw data and the new Section Format Preview 01:25

Crash Course in Pivot Tables Part I Preview 05:15

Crash Course in Pivot Tables Part II Preview 10:48

Intro to Activity: Building a Pivot Table for the Dumpling Business Preview 07:26

Sorting & Filtering Part I Preview 09:15

Sorting & Filtering Part II Preview 06:19

Sorting & Filtering Part III Preview 08:17

NOTE: There is a small error in the next lecture! Preview 00:14

Solution to Activity: Sorting & Filtering! Preview 13:43

Wrapping Up: Cool Applications of Sort & Filter Preview 03:15

Charts & Graphs: Visualizing Data in Spreadsheets! 4 lectures

Intro to Charts & Graphs Preview 05:12

Creating Charts in Google Sheets Preview 14:05

Intro to Activity: Creating AMAZING Visuals Preview 02:03

Solution to Activity: Creating AMAZING Visuals Preview 04:47

Data Validation & an Introduction to Dynamic Models 5 lectures

What is Data Validation? Preview 06:42

Data Validation: Hands on Activities Preview 06:09

Creating Basic Dynamic Models Preview 08:11

Intro to Activity: Beginner Data Validation & Intermediate Modeling Preview 04:47

Solution to Activity: Beginner Data Validation & Intermediate Modeling Preview 12:09

Errors, IFERROR, and creating Named Ranges 4 lectures

Every error you need to know, and how to fix 'em Preview 10:10

The IFERROR formula. A machine to keep your spreadsheets clean! Preview 04:24

Named Ranges - Prehabbing before you mess up your formulas! Preview 03:35

Activity with Solutions - Another Day at the Dumpling Stand! Preview 07:33

The Infamous VLOOKUP, Lookups, and INDEX(MATCH(),MATCH()) 4 lectures

Let's get into the VLOOKUP Function! Also HLOOKUP...I guess Preview 11:54

Activity: Housing Info with VLOOKUP & HLOOKUP Preview 03:45

The BEST Lookup Formula! (INDEX(MATCH()MATCH())) Preview 11:14

Activity: Finding Information with VLOOKUP & INDEX(MATCH,MATCH()) Preview 07:43

Working with Text Functions: Becoming a Wordsmith! 4 lectures

Introduction to Text Functions: Adding words together and changing their case! Preview 08:08

Using LEFT, RIGHT, LEN and more to extract characters from cells! Preview 11:50

Activity: Extracting First & Last Names from a cell! Preview 05:20

TRIM & Final Activity with Solutions! Preview 06:43

Date & Time Functions 4 lectures

Simple, but essential date & time functions: TODAY & NOW! Preview 05:28

Date Functions Continued! EDATE, EOMONTH, and MORE! Preview 07:19

Exercise Solution: The Countdown Clock! Preview 03:59

Synthesizing Sections: An Interactive Calendar Preview 02:20

Rounding & Randomizing: Keeping your spreadsheets SPICY! 4 lectures

Randomizing using RAND & RANDBETWEEN! Preview 07:29

Rounding Out the Numbers with ROUND, ROUNDUP, and ROUNDDOWN! Preview 06:41

Activity Intro: Build a Meal Randomizer! Preview 01:25

Activity Solutions: Build a Meal Randomizer! Preview 03:47

Stock Picking & Real-time Translators with GOOGLEFINANCE & GOOGLETRANSLATE! 3 lectures

Introduction to GOOGLEFINANCE: Get Stock Information INSTANTLY! Preview 04:42

Introduction to GOOGLETRANSLATE: Become a Polyglot! Preview 03:03

Activity: Creating the Stock Picker & Translator! Preview 11:04

The Final Quest: IMPORTRANGE & QUERY! 6 lectures

Pull Spreadsheets together from ALL OVER the Google sheets universe! Preview 06:35

CRASH Course in SQL! Introduction to SQL jargon and logic! Preview 07:36

Building QUERY() in your Spreadsheet! Preview 08:24

Mixing QUERY() with IMPORTRANGE(): Achieve Greatness. Preview 07:07

QUERY() with IMPORTRANGE() Activity: Apply EVERYTHING to Analyze some Data! Preview 18:45

Students should be able to apply almost all the concepts they've learned along the way to do some incredible analysis.

The Final Lecture: Create Cell References within QUERY()! Preview 05:17

Congrats on making it this far!!