Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Google Sheets - The Comprehensive Masterclass
Section 1: Welcome to Your Google Sheets Course
❗Get the Most Out of This Course (Don't Skip) (4:02)
⚙️How to Use the Platform for Best Learning Experience
🔻GET ALL COURSE FILES HERE
🎁 BONUS: Learning Path For Excel Users
🤔 Pre-Assessment Quiz (Check your current level) (3:54)
******* GOOGLE SHEETS ESSENTIALS *******
What You Achieve by the End of Essentials (3:20)
Section 3: Getting Started Right with Google Sheets
Sheets Basics for a Smooth Start (7:14)
Cells, Rows & Columns (8:34)
Productivity Tips & File Sharing (Pro Tip) (7:25)
Useful Menu & Toolbar Options (9:47)
Language & Spreadsheet Settings (Important) (4:58)
Version Recovery & Edit History (3:34)
Activity: 5 Minute Timer
🤔 Quiz: Test Your Knowledge - Getting Started
Section 4: Data Entry & Basic Formulas
Data Entry, Data Types & Autofill in Google Sheets (9:27)
Common Formulas in Google Sheets (9:44)
Important Functions in Google Sheets (Sum, Count, Counta, CountUnique) (9:47)
Relative Vs. Absolute Referencing (6:45)
💪 Challenge: Find the Mistake! Data Doesn't Add Up! (3:09)
📝 Key Takeaways: The Basics
Section 5: Best Practice & Useful Features
Useful Features for Report Design (11:56)
Useful Shortcuts (Pro Tips) (11:47)
Conditional Formatting (Steer Attention to What Matters) (9:42)
💪 Challenge: Conditional Formatting with Checkboxes and Emails (4:03)
Data Validation & Drop-down Lists (11:46)
Working with Excel & CSV Files (& Importing Data) (9:11)
Getting Data from Other Google Sheets (ImportRange) (8:21)
Best Practice in Designing Functional Spreadsheets (Pro Tips) (6:56)
🤔 Quiz: Test Your Knowledge - Useful Features
📝 Key Takeaways: Best Practice & Useful Features
Section 6: Sorting, Filtering and Cleaning Data
Sort Feature & What You Shouldn't Do (8:29)
Filter & Filter View in Sheets (8:44)
Calculations on Filtered Results - SubTotal (Pro Tip) (4:44)
Challenge: Delete Empty Rows in Data (5:12)
Split Text, Remove Duplicates and Trim Spaces (5:21)
Find & Replace Values (Pro Tip) (9:45)
💪 Challenge: Which Items Does Each Customer Order? (4:29)
📝 Key Takeaways: Data Cleaning & Management Tools
🆕 Enjoying the Course so far? Refer a friend and get 30% off your next XelPlus purchase!
Section 7: Essential Sheets Functions to Analyze & Summarize
Important: How to Properly Read & Understand Functions (8:54)
Unique & Sort Functions (8:59)
SUM(IFS) by Criteria (also COUNT, AVERAGE, MAX, MIN) (12:22)
Challenge: Summarize by Criteria & COUNTUNIQUE(IFS) (10:05)
Rounding Values (the Right Way) (6:21)
Logical & Information Functions (TRUE & FALSE) (8:40)
IF Function (IF, Then) (10:27)
Nested IF & IFS Functions (Pro Tip) (9:05)
Error Types & How to Handle Errors (7:26)
Named Ranges (Pro Tip) (6:29)
💪 Challenge: Average Sales between Selected Dates (7:49)
📝 Key Takeaways: Essential Functions to Summarize
Section 8: Lookup & Filter Functions
Overview of Helpful Lookup Functions (1:52)
VLOOKUP and HLOOKUP (9:02)
Approximate Match Lookup (Replacement for IFS - Pro Tip) (5:41)
🆕 XLOOKUP - the More Flexible Lookup Function
FILTER Function to Return Multiple Match Results (10:16)
Lookup Pictures & Images (Pro Tip) (9:27)
SortN for Ranking (account for ties) (8:06)
💪 Challenge: Find the Mistake in the Formulas & Correct Them (6:23)
📝 Key Takeaways: Lookup & Filter Functions
Section 9: Text, Date & Time Functions
Useful & Easy Text Functions (8:17)
Search & Substitute Text (Pro Tip) (10:08)
Split & Join Values to / from Multiple Columns (5:17)
Challenge: Sort Multiple Words Inside a Cell (3:49)
Essential Date Functions (10:26)
Time Calculations (Calculating Hours Worked) (6:55)
🤔 Quiz: Test Your Knowledge - Text & Date Functions
📝 Key Takeaways: Useful Text Functions
Section 10: Helpful Google Sheets Functions
Detect Language and Translate Your Spreadsheets (5:42)
Image Function (Pro Tip: Create QR Codes) (6:40)
Import Tables From the Web (IMPORTHTML) (5:23)
Import Any Data from the Web (IMPORTXML) (11:37)
Stock & Currency Data with GOOGLEFINANCE (9:09)
💪 Challenge: Did You Invest in the Right Stock? (6:15)
📝 Key Takeaways: Google Functions & More Resources
Section 11: Collaborate, Publish & Protect
Sharing Files & Collaboration Tools (4:27)
Publishing Sheet or Chart (& Embedding in a Web Page) (9:35)
Protection: Sheet & Cell Level (8:47)
Prepare for Print / PDF (& Page Breaks) (9:21)
💪 Challenge: Prepare Proper PDF Layout
Use Google Forms to Collect Data (Pro Tip) (10:38)
📝 Key Takeaways: Collaborate, Protect & Collect Data
Section 12: Charts to Impress
Chart Basics (Column & Line Charts) (12:26)
Aggregating Data in Charts (Bar & Pie Charts) (7:57)
Combo (Combination) Charts (4:15)
Scrolling Tables & Scorecards (Visualization Tools for Dashboards) (6:36)
Gauge Chart (Visualization Tools for Dashboards) (4:11)
Add Slicers to Charts (7:43)
Challenge: Better Dashboards (auto-sorted charts) (11:50)
Sparklines to Impress (11:27)
Sparklines for Stock & Currency Trends (Pro Tip) (9:38)
📝 Key Takeaways: Chart Tips
Section 13: Explore your Data with Pivot Tables in Google Sheets
Pivot Table Basics in Google Sheets (10:49)
Filters & Slicers for Dynamic Pivot Tables (8:19)
Pro Tip: Grouping Items & Dates (by month, year) (7:16)
Custom Formulas in Pivot Tables (8:39)
💪 Challenge: Master Pivot Tables in Google Sheets (6:10)
📝 Key Takeaways: Pivot Tables
Section 14: Test Your Sheets Essential Knowledge!
Congratulations! Get Ready for More Practice (1:22)
More Challenges & Practice Activities (You need this to pass the quiz)
🤔 Google Sheets Essentials Quiz
🛣️ Challenge Answers & Course Roadmap
******* ADVANCED DATA ANALYSIS WITH GOOGLE SHEETS *******
Introduction to Advanced Data Analysis (2:10)
Section 16: Advanced Functions & Formula Combinations
INDEX MATCH for Complex Lookups (12:18)
Challenge: Find the Mistake! INDEX MATCH Problem (6:57)
INDEX MATCH with Multiple Headers (Pro Tip) (7:23)
Array Formulas: ARRAYFORMULA Function (6:28)
Create Own Arrays & Append Totals (7:22)
INDIRECT Function: Lookup from Different Sheets (7:55)
Generate Random Data & Text (RANDBETWEEN) (10:39)
The Power of SEQUENCE Function (Dynamic Yearly Calendar) (11:28)
📝 Key Takeaways: Advanced Functions
🆕 Array Manipulation Functions (2023 update): VSTACK, TOCOL, CHOOSECOLS, WRAPROWS
Section 17: Query Function
QUERY Function Syntax & Important Guidelines (4:26)
QUERY Function Example (Select, Where, Order by) (6:54)
QUERY Function Aggregation & Calculated Columns (7:20)
Group by Multiple Columns & Pivot Columns (Query Function) (7:12)
QUERY Function with Drop-down List (Dynamic Cell References) (6:30)
QUERY Function with Dynamic Totals (Pro Tip) (7:06)
Working with Dates & Months in QUERY Function (8:50)
Combine Data from Multiple Sheets (External Files) with QUERY Function (6:30)
QUERY Function & IMPORTRANGE to Combine Data from an External File (4:21)
🤔 Quiz: Test Your Knowledge - QUERY Function Syntax
📝 Key Takeaways: Query Function
Section 18: Advanced & Hidden Features
Custom Number Formatting Explained (12:50)
Custom Formatting for Impressive Reports with Symbols (Pro Tip) (8:25)
Conditional Formatting with Formulas (The secret to getting it right) (7:45)
💪 Challenge: Find the Difference Between Two Lists (5:56)
Dependent Drop-down Lists in Google Sheets (5:29)
Dependent Drop-down for Every Row in Sheets (Pro Tip) (4:42)
💪 Challenge: Dependent drop-down Lists in Google Sheets (5:40)
📝 Key Takeaways: Advanced Features
Section 19: Introduction to Google Sheets Apps Script, Macros & Add-Ons
Google Sheets Macros: Record your First Macro (7:59)
Relative Vs. Absolute Macro Recording: Practical Example (6:22)
Combining Macros in Sheets (Mix Absolute & Relative Reference) (4:51)
💪 Challenge: Automate Chart Creation with Your Macro (3:52)
Reset Data Validation List with Apps Script in Sheets (6:54)
Find & Install Add-Ons (Goal Seek example) (9:12)
📝 Key Takeaways: Apps Script & Links to More Resources
🎁 BONUS: Your Complete Google Sheets Manual
🔻DOWNLOAD Your Complete Google Sheets Manual
🎁 BONUS Practice Activities
Extra Practice Activities - Can you solve these?
🎁 BONUS Project: Investment Analysis - Create a Model from Scratch
Project Overview
Case Study: Invest in a new Machine? (2:01)
Time Value of Money & NPV Function (10:17)
Setting up the Discounted Cash Flow Model (9:51)
Adding Calculations to the DCF Model (6:45)
Calculating the Payback Period (7:34)
Sensitivity Analysis (10:01)
Section 20: Final Words
🎁 Bonus: The Mechanics of the Quiz (8:22)
Final Words (1:35)
✍️ Leave a Review
My Favorite Resources
🏅 Share Your Digital Certificate (0:40)
Gauge Chart (Visualization Tools for Dashboards)
Now it’s time to cover how we can add Gauge Charts to a report in Google Sheets.
Complete and Continue
Discussion
6
comments
Load more
6 comments