English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 133 lectures (14h 26m) | 4.78 GB
Learn Google Sheets in 2021! Solve Complex Data Analysis Problems. Become an Expert: Learn Pivot Tables, QUERY & more
This is the only Google Sheets course you’ll ever going to need: Because it’s going to teach you everything you need to know. From data collection, automation, to proper data analysis and visualization.
What will happen by the end of the course:
- You’ll be a confident spreadsheet user
- You’ll be the one that brings solutions to the table instead of problems
- You can tackle complex data analysis with ease
- You’ll create interactive and compelling dashboards in Google Spreadsheets
- You’ll know the right way to approach a problem
What you’ll learn to get there:
- Properly input and collect data and navigate large Google spreadsheets
- Best practice and useful (& sometimes hidden) Sheets features
- Sort, Filter, create Filter views and clean data in Google spreadsheets
- Lookup functions in Google Sheets such as VLOOKUP, INDEX & MATCH, FILTER & SORTN.
- Essential Summarization Functions like SUM, COUNT, SUMIFS, COUNTIFS etc.
- Branching functions like Google Sheets IF and IFS functions.
- Combining data from other Google Sheets with IMPORTRANGE
- QUERY Function to create ANY analysis you want (You’ll learn the key to getting this right)
- Pivot tables, Charts & Slicers in Google Sheets to present your data
- How to automate your work with macros & Google Apps Script
How you will learn:
- Lots of challenges & exercises
- Practice activities where you need to find the mistakes
- Quizzes
- You get access to ALL the files I use. The START files so you can follow along with me and the SOLUTION files in case you get stuck.
- If you’re a current spreadsheet user take the pre-assessment quiz to discover your weak spots.
I promise, we’ll have fun!
Why Learn Google Sheets?
The way we’re using spreadsheets is changing. More of our work and collaboration is done through online channels, our spreadsheets are also moving online. Google Sheets is an online spreadsheet program that offers solutions many companies need right now.
In the past the main requirement companies had was for professionals to have solid knowledge in Microsoft Excel. Now they are looking for more.
As a business professional it’s important to master the important applications. You don’t need to know them all. Just the important ones.
And something else you may not know …
You can apply MOST of the knowledge you learn in this Google Sheets course to Microsoft Excel as well!
There are many helpful and time-saving Google Sheets formulas and features. We tend to forget what these are if we don’t use them. This course will give you the practice you need to be able to apply the best solution for the task at hand. This way you can do more in less time.
Why Take This Specific Google Sheets Course?
This Comprehensive Google Sheets Masterclass is simply like no other Google spreadsheet course out there. I don’t say that lightly.
This course will get you confident and comfortable designing simple to complex spreadsheets. You’ll go beyond Sheets basics. As you go through the course, you’ll be able to apply what you learnt immediately to your job.
You’ll master new formulas and learn new function so you can find better ways to setup your existing spreadsheets. Management loves efficiency.
In this course you’ll solve dozens of practical real-world examples. They’ll help you think outside the box so you can work smarter not harder.
What you’ll learn
- 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
Table of Contents
Welcome to Your Google Sheets Course
1 Get the Most Out of This Course (Don’t Skip)
2 GET ALL COURSE FILES HERE
3 Pre-assessment Quiz (Check your current knowledge)
GOOGLE SHEETS ESSENTIALS
4 What You Achieve by the End of Essentials
Getting Started Right with Google Sheets
5 Sheets Basics for a Smooth Start
6 Cells, Rows & Columns
7 Productivity Tips & File Sharing (Pro Tip)
8 Useful Menu & Toolbar Options
9 Language & Spreadsheet Settings (Important)
10 Version Recovery & Edit History
11 Activity 5 Minute Timer
Data Entry & Basic Formulas
12 Data Entry, Data Types & Autofill in Google Sheets
13 Common Formulas in Google Sheets
14 Important Functions in Google Sheets (Sum, Count, Counta, CountUnique)
15 Relative Vs. Absolute Referencing
16 Challenge Find the Mistake Data Doesn’t Add Up
17 Key Takeaways The Basics
Best Practice & Useful Features
18 Useful Features for Report Design
19 Useful Shortcuts (Pro Tips)
20 Conditional Formatting (Steer Attention to What Matters)
21 Challenge Conditional Formatting with Checkboxes and Emails
22 Data Validation & Drop-down Lists
23 Working with Excel & CSV Files (& Importing Data)
24 Getting Data from Other Google Sheets (ImportRange)
25 Best Practice in Designing Functional Spreadsheets (Pro Tips)
26 Key Takeaways Best Practice & Useful Features
Sorting, Filtering and Cleaning Data
27 Sort Feature & What You Shouldn’t Do
28 Filter & Filter View in Sheets
29 Calculations on Filtered Results – SubTotal (Pro Tip)
30 Challenge Delete Empty Rows in Data
31 Split Text, Remove Duplicates and Trim Spaces
32 Find & Replace Values (Pro Tip)
33 Challenge Which Items Does Each Customer Order
34 Key Takeaways Data Cleaning & Management Tools
Essential Sheets Functions to Analyze & Summarize
35 Important How to Properly Read & Understand Functions
36 Unique & Sort Functions
37 SUM(IFS) by Criteria (also COUNT, AVERAGE, MAX, MIN)
38 Challenge Summarize by Criteria & COUNTUNIQUE(IFS)
39 Rounding Values (the Right Way)
40 Logical & Information Functions (TRUE & FALSE)
41 IF Function (IF, Then)
42 Nested IF & IFS Functions (Pro Tip)
43 Error Types & How to Handle Errors
44 Named Ranges (Pro Tip)
45 Challenge Average Sales between Selected Dates
46 Key Takeaways Essential Functions to Summarize
Lookup & Filter Functions
47 Overview of Helpful Lookup Functions
48 VLOOKUP and HLOOKUP
49 Approximate Match Lookup (Replacement for IFS – Pro Tip)
50 FILTER Function to Return Multiple Match Results
51 Lookup Pictures & Images (Pro Tip)
52 SortN for Ranking (account for ties)
53 Challenge Find the Mistakes in the Formulas & Correct Them
54 Key Takeaways Lookup & Filter Functions
Text, Date & Time Functions
55 Useful & Easy Text Functions
56 Search & Substitute Text (Pro Tip)
57 Split & Join Values to from Multiple Columns
58 Challenge Sort Multiple Words Inside a Cell
59 Essential Date Functions
60 Time Calculations (Calculating Hours Worked)
61 Key Takeaways Useful Text Functions
Helpful Google Sheet Functions
62 Detect Language and Translate Your Spreadsheets
63 Image Function (Pro Tip Create QR Codes)
64 Import Tables From the Web (IMPORTHTML)
65 Import Any Data from the Web (IMPORTXML)
66 Stock & Currency Data with GOOGLEFINANCE
67 Challenge Did You Invest in the Right Stock
68 Key Takeaways Google Functions & More Resources
Collaborate, Publish & Protect
69 Sharing Files & Collaboration Tools
70 Publishing Sheet or Chart (& Embedding in a Web Page)
71 Protection Sheet & Cell Level
72 Prepare for Print PDF (& Page Breaks)
73 Challenge Prepare Proper PDF Layout
74 Use Google Forms to Collect Data (Pro Tip)
75 Key Takeaways Collaborate, Protect & Collect Data
Charts to Impress
76 Chart Basics (Column & Line Charts)
77 Aggregating Data in Charts (Bar & Pie Charts)
78 Combo (Combination) Charts
79 Scrolling Tables & Scorecards (Visualization Tools for Dashboards)
80 Gauge Chart (Visualization Tools for Dashboards)
81 Add Slicers to Charts
82 Challenge Better Dashboards (auto-sorted charts)
83 Sparklines to Impress
84 Sparklines for Stock & Currency Trends (Pro Tip)
85 Key Takeaways Chart Tips
Explore your Data with Pivot Tables in Google Sheets
86 Explore Feature in Google Sheets (Pro Tip)
87 Pivot Table Basics in Google Sheets
88 Filters & Slicers for Dynamic Pivot Tables
89 Pro Tip Grouping Items & Dates (by month, year)
90 Custom Formulas in Pivot Tables
91 Challenge Master Pivot Tables in Google Sheets
92 Key Takeaways Pivot Tables
Test Your Sheets Essential Knowledge
93 Congratulations Get Ready for More Practice
94 More Challenges & Practice Activities (You need this to pass the quiz)
95 Challenge Answers & Course Roadmap
ADVANCED DATA ANALYSIS WITH GOOGLE SHEETS
96 Introduction to Advanced Data Analysis
Advanced Functions & Formula Combinations
97 INDEX MATCH for Complex Lookups
98 Challenge Find the Mistake INDEX MATCH Problem
99 INDEX MATCH with Multiple Headers (Pro Tip)
100 Array Formulas ARRAYFORMULA Function
101 Create Own Arrays & Append Totals
102 INDIRECT Function Lookup from Different Sheets
103 Generate Random Data & Text (RANDBETWEEN)
104 The Power of SEQUENCE Function (Dynamic Yearly Calendar)
105 Key Takeaways Advanced Functions
The QUERY Function
106 QUERY Function Syntax & Important Guidelines
107 QUERY Function Example (Select, Where, Order by)
108 QUERY Function Aggregation & Calculated Columns
109 Group by Multiple Columns & Pivot Columns (Query Function)
110 QUERY Function with Drop-down List (Dynamic Cell References)
111 QUERY Function with Dynamic Totals (Pro Tip)
112 Working with Dates & Months in QUERY Function
113 Combine Data from Multiple Sheets with QUERY Function
114 QUERY Function & IMPORTRANGE to Combine Data from an External File
115 Key Takeaways Query Function
Advanced & Hidden Features
116 Custom Number Formatting Explained
117 Custom Formatting for Impressive Reports with Symbols (Pro Tip)
118 Conditional Formatting with Formulas (The secret to getting it right)
119 Challenge Find the Difference Between Two Lists
120 Dependent Drop-down Lists in Google Sheets
121 Dependent Drop-down for Every Row in Sheets (Pro Tip)
122 Challenge Dependent drop-down Lists in Google Sheets
123 Key Takeaways Advanced Features
Introduction to Google Sheets Apps Script, Macros & Add-Ons
124 Google Sheets Macros Record your First Macro
125 Relative Vs. Absolute Macro Recording Practical Example
126 Combining Macros in Sheets (Mix Absolute & Relative Reference)
127 Challenge Automate Chart Creation with Your Macro
128 Reset Data Validation List with Apps Script in Sheets
129 Find & Install Add-Ons (Goal Seek example)
130 Key Takeaways Apps Script & Links to More Resources
Final Words
131 Bonus The Mechanics of the Quiz
132 Final Words
133 BONUS More Resources
Resolve the captcha to access the links!