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)
❗Get the Most Out of This Course (Don't Skip)
Complete and Continue
Discussion
19
comments
Load more
Lesson Content Locked
Your session expired. Please sign in again to continue.
Login
19 comments