Advanced Formulas in Google Sheets
An introduction to Advanced Topics like Absolute/Relative References, Named Ranges and Best Practices
Core Skills
Learn 30 Advanced Formulas in this
FREE course with Ben Collins
Nested IF formulas, IFs with logic, SUMIF and SUMIFS formulas.
IF function
All the counting functions! Count, Counta, Countblank, Countif, Countifs and Countunique
Counting Formulas
Create drop-down lists for user inputs
Data Validation
Starting Templates and Solution Files
Use these free templates and what you’ve learned to create spreadsheet masterpieces.
What’s Inside
Course Author
Google Sheets Developer & Instructor
Hi, I'm Ben!
I'm an experienced data analytics instructor and freelance spreadsheet developer, helping businesses better understand their data through analytical insights and visualizations, including dashboards.
I work primarily with Google Sheets, Apps Script and Data Studio, and also have extensive experience with Excel, SQL and Tableau. I've previously taught data courses and workshops for General Assembly in Washington, D.C.
@2018 Collins Analytics LLC
The Landing Page Builder For Marketers
The Collins School of Data
What You'll Get
-
Access to all 30 video lessons
-
Each lesson is 5 - 10 minutes
-
All the starter templates
-
All the solution files
-
Best of all, it's free!
How do you become a Google Sheets master?
Practice, practice, practice!
Learn 30 advanced formulas and techniques in 30 days, with these 30 bite-size tutorials.
Ever wanted to see what all the fuss about INDEX-MATCH is?
The best way to learn is to build stuff. You can read all the books in the world, but until you practice something for real yourself (and repeat, and repeat...) you won't get where you want to go.
Is this course really free?
Yes!
I wanted to give back to the community and say a big thank to all those who have purchased one of my premium courses.
I get a lot of emails and questions about the more advanced formulas in Google Sheets, so I saw a clear need for some structured training on this topic. So here it is. ;)
I've poured a lot of sweat and tears into this course, so if you enjoy it and get some value from these lessons, you can "pay me" by sharing this with a friend or colleague who you think might benefit.
Who is this for?
This course is intended for intermediate level users of Google Sheets, who want to become more comfortable with advanced formulas and unlock the full potential of their data.
You should be comfortable with basic formulas before taking this course, up to about the level of the VLOOKUP (although I do a very quick recap of that in lesson 1!).
Make your spreadsheets look beautiful!
Advanced Formatting
Learn how to use VLOOKUP's hidden superpower
VLOOKUP and TRUE
A must-learn formula for Advanced Sheets users!
INDEX
Like a dating service for spreadsheet cells
MATCH
The more laid back version of the VLOOKUP
HLOOKUP
The secrets you need to know to work with dates in Sheets
Date Formulas
Another lookup function?! The rare one
LOOKUP
One lookup formula to rule them all!
INDEX & MATCH
Returning values that pass the tests set by the function
FILTER
The most powerful formula in Google Sheets
QUERY Part I
Do the impossible with your formulas
Array Formulas
Your ticket to bring data from one Sheet to another
IMPORTRANGE
Powerful formulas to import data into Sheets
Import Formulas
The proper way to do searching, extracting and replacing in strings
Regular Expression
Create mini charts inside single cells to show trends
SPARKLINES
Two hard-working little functions worth knowing
INDIRECT & OFFSET
Count rows and columns, and create vectors for Array Formulas
ROWS & COLUMNS
Humble formulas that are useful tools to have in your arsenal
SMALL + LARGE
How to do the hard stuff with the powerful QUERY function
QUERY Part II
Running total calculations using Matrix Multiplication
MMULT
How to create datasets that behave "normally"
FREQUENCY & RANK
A useful but underused function, with some surprising features
SUBTOTAL
The correct way to reference Pivot Table data
Get Pivot Data
Create tables of contents or link to websites
HYPERLINK
Some esoteric formula tricks to finish with
Odds & Ends
Learn 30 Advanced Formulas in this FREE course with Ben Collins
ENROLL FOR FREEENROLL FOR FREEENROLL FOR FREE