IIRF Online > Office Productivity > Google > Google Sheets > Google Sheets - The Comprehensive Masterclass
Google Sheets - The Comprehensive Masterclass by Udemy
Learn Google Sheets in 2024! Solve Complex Data Analysis Problems. Become an Expert: Learn Pivot Tables, QUERY & more
Course Highlights
- Solve real-life spreadsheet challenges with ease
- Create interactive & visually effective dashboards in Sheets
- Learn hacks and hidden Google Sheets features that even the Experts don't know
- Import & append data from other Google Spreadsheets
- Learn advanced functions like QUERY, IMPORTHTML, IMPORTXML and more
- Master Pivot tables, charts and sparklines to create impressive Google spreadsheets
- Secrets to properly use tricky features like Conditional formatting
- Automate your work with Macros & Google Apps Script
Skills you will learn!
Curriculum
3 Topics
Get the Most Out of This Course (Don't Skip)
GET ALL COURSE FILES HERE
Pre-assessment Quiz (Check your current knowledge)
1 Topic
What You Achieve by the End of Essentials
8 Topics
Sheets Basics for a Smooth Start
Cells Rows & Columns
Productivity Tips & File Sharing (Pro Tip)
Useful Menu & Toolbar Options
Language & Spreadsheet Settings (Important)
Version Recovery & Edit History
Activity: 5 Minute Timer
Quiz: Test Your Knowledge - Getting Started
6 Topics
Data Entry Data Types & Autofill in Google Sheets
Common Formulas in Google Sheets
Important Functions in Google Sheets (Sum Count Counta CountUnique)
Relative Vs. Absolute Referencing
Challenge: Find the Mistake! Data Doesn't Add Up!
Key Takeaways: The Basics
10 Topics
Useful Features for Report Design
Useful Shortcuts (Pro Tips)
Conditional Formatting (Steer Attention to What Matters)
Challenge: Conditional Formatting with Checkboxes and Emails
Data Validation & Drop-down Lists
Working with Excel & CSV Files (& Importing Data)
Getting Data from Other Google Sheets (ImportRange)
Best Practice in Designing Functional Spreadsheets (Pro Tips)
Test Your Knowledge - Useful Features
Key Takeaways: Best Practice & Useful Features
8 Topics
Sort Feature & What You Shouldn't Do
Filter & Filter View in Sheets
Calculations on Filtered Results - SubTotal (Pro Tip)
Challenge: Delete Empty Rows in Data
Split Text Remove Duplicates and Trim Spaces
Find & Replace Values (Pro Tip)
Challenge: Which Items Does Each Customer Order?
Key Takeaways: Data Cleaning & Management Tools
12 Topics
Important: How to Properly Read & Understand Functions
Unique & Sort Functions
SUM(IFS) by Criteria (also COUNT AVERAGE MAX MIN)
Challenge: Summarize by Criteria & COUNTUNIQUE(IFS)
Rounding Values (the Right Way)
Logical & Information Functions (TRUE & FALSE)
IF Function (IF Then)
Nested IF & IFS Functions (Pro Tip)
Error Types & How to Handle Errors
Named Ranges (Pro Tip)
Challenge: Average Sales between Selected Dates
Key Takeaways: Essential Functions to Summarize
9 Topics
Overview of Helpful Lookup Functions
VLOOKUP and HLOOKUP
Approximate Match Lookup (Replacement for IFS - Pro Tip)
FILTER Function to Return Multiple Match Results
Lookup Pictures & Images (Pro Tip)
SortN for Ranking (account for ties)
Challenge: Find the Mistakes in the Formulas & Correct Them
Key Takeaways: Lookup & Filter Functions
! XLOOKUP - the More Flexible Lookup Function
8 Topics
Useful & Easy Text Functions
Search & Substitute Text (Pro Tip)
Split & Join Values to / from Multiple Columns
Challenge: Sort Multiple Words Inside a Cell
Essential Date Functions
Time Calculations (Calculating Hours Worked)
Test Your Knowledge - Text & Date Functions
Key Takeaways: Useful Text Functions
7 Topics
Detect Language and Translate Your Spreadsheets
Image Function (Pro Tip: Create QR Codes)
Import Tables From the Web (IMPORTHTML)
Import Any Data from the Web (IMPORTXML)
Stock & Currency Data with GOOGLEFINANCE
Challenge: Did You Invest in the Right Stock?
Key Takeaways: Google Functions & More Resources
7 Topics
Sharing Files & Collaboration Tools
Publishing Sheet or Chart (& Embedding in a Web Page)
Protection: Sheet & Cell Level
Prepare for Print / PDF (& Page Breaks)
Challenge: Prepare Proper PDF Layout
Use Google Forms to Collect Data (Pro Tip)
Key Takeaways: Collaborate Protect & Collect Data
10 Topics
Chart Basics (Column & Line Charts)
Aggregating Data in Charts (Bar & Pie Charts)
Combo (Combination) Charts
Scrolling Tables & Scorecards (Visualization Tools for Dashboards)
Gauge Chart (Visualization Tools for Dashboards)
Add Slicers to Charts
Challenge: Better Dashboards (auto-sorted charts)
Sparklines to Impress
Sparklines for Stock & Currency Trends (Pro Tip)
Key Takeaways: Chart Tips
7 Topics
Explore Feature in Google Sheets (Pro Tip)
Pivot Table Basics in Google Sheets
Filters & Slicers for Dynamic Pivot Tables
Pro Tip: Grouping Items & Dates (by month year)
Custom Formulas in Pivot Tables
Challenge: Master Pivot Tables in Google Sheets
Key Takeaways: Pivot Tables
4 Topics
Congratulations! Get Ready for More Practice
More Challenges & Practice Activities (You need this to pass the quiz)
Google Sheets Essentials Quiz
Challenge Answers & Course Roadmap
1 Topic
Introduction to Advanced Data Analysis
10 Topics
INDEX MATCH for Complex Lookups
Challenge: Find the Mistake! INDEX MATCH Problem
INDEX MATCH with Multiple Headers (Pro Tip)
Array Formulas: ARRAYFORMULA Function
Create Own Arrays & Append Totals
INDIRECT Function: Lookup from Different Sheets
Generate Random Data & Text (RANDBETWEEN)
The Power of SEQUENCE Function (Dynamic Yearly Calendar)
Key Takeaways: Advanced Functions
! Array Manipulation Functions (2023 update): VSTACK TOCOL CHOOSECOLS WRAPRO
11 Topics
QUERY Function Syntax & Important Guidelines
QUERY Function Example (Select Where Order by)
QUERY Function Aggregation & Calculated Columns
Group by Multiple Columns & Pivot Columns (Query Function)
QUERY Function with Drop-down List (Dynamic Cell References)
QUERY Function with Dynamic Totals (Pro Tip)
Working with Dates & Months in QUERY Function
Combine Data from Multiple Sheets with QUERY Function
QUERY Function & IMPORTRANGE to Combine Data from an External File
Test Your Knowledge - QUERY Function Syntax
Key Takeaways: Query Function
8 Topics
Custom Number Formatting Explained
Custom Formatting for Impressive Reports with Symbols (Pro Tip)
Conditional Formatting with Formulas (The secret to getting it right)
Challenge: Find the Difference Between Two Lists
Dependent Drop-down Lists in Google Sheets
Dependent Drop-down for Every Row in Sheets (Pro Tip)
Challenge: Dependent drop-down Lists in Google Sheets
Key Takeaways: Advanced Features
7 Topics
Google Sheets Macros: Record your First Macro
Relative Vs. Absolute Macro Recording: Practical Example
Combining Macros in Sheets (Mix Absolute & Relative Reference)
Challenge: Automate Chart Creation with Your Macro
Reset Data Validation List with Apps Script in Sheets
Find & Install Add-Ons (Goal Seek example)
Key Takeaways: Apps Script & Links to More Resources
3 Topics
Extras: The Mechanics of the Quiz
Final Words
SPECIAL BONUS: Discover Exciting Offers!
Google Sheets - The Comprehensive Masterclass
Related Google Sheets Courses
Thank You!
Your review has been submitted successfully.