Advanced Formulas in Google Sheets

An introduction to Advanced Topics like Absolute/Relative References, Named Ranges and Best Practices

Core Skills

Lesson 1

Learn 30 Advanced Formulas in this
FREE course with Ben Collins

Nested IF formulas, IFs with logic, SUMIF and SUMIFS formulas.

IF function

Lesson 2

All the counting functions! Count, Counta, Countblank, Countif, Countifs and Countunique

Counting Formulas

Lesson 3

Create drop-down lists for user inputs

Data Validation

Lesson 4

Includes

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

Lesson 5

Learn how to use VLOOKUP's hidden superpower

VLOOKUP and TRUE

Lesson 6

$0

.00

A must-learn formula for Advanced Sheets users!

INDEX

Lesson 7

Like a dating service for spreadsheet cells

MATCH

Lesson 8

The more laid back version of the VLOOKUP

HLOOKUP

Lesson 9

The secrets you need to know to work with dates in Sheets

Date Formulas

Lesson 12

Another lookup function?! The rare one

LOOKUP

Lesson 11

One lookup formula to rule them all!

INDEX & MATCH

Lesson 10

Returning values that pass the tests set by the function

FILTER

Lesson 13

The most powerful formula in Google Sheets

QUERY Part I

Lesson 14

One for the connoisseur

SUMPRODUCT

Lesson 16

Do the impossible with your formulas

Array Formulas

Lesson 17

Your ticket to bring data from one Sheet to another

IMPORTRANGE

Lesson 18

Powerful formulas to import data into Sheets

Import Formulas

Lesson 19

The proper way to do searching, extracting and replacing in strings

Regular Expression

Lesson 20

Create mini charts inside single cells to show trends

SPARKLINES

Lesson 21

Two hard-working little functions worth knowing

INDIRECT & OFFSET

Lesson 22

Count rows and columns, and create vectors for Array Formulas

ROWS & COLUMNS

Lesson 23

Humble formulas that are useful tools to have in your arsenal

SMALL + LARGE

Lesson 24

How to do the hard stuff with the powerful QUERY function

QUERY Part II

Lesson 15

Running total calculations using Matrix Multiplication

MMULT

Lesson 25

How to create datasets that behave "normally"

FREQUENCY & RANK

Lesson 26

A useful but underused function, with some surprising features

SUBTOTAL

Lesson 27

The correct way to reference Pivot Table data

Get Pivot Data

Lesson 28

Create tables of contents or link to websites

HYPERLINK

Lesson 29

Some esoteric formula tricks to finish with

Odds & Ends

Lesson 30

Learn 30 Advanced Formulas in this FREE course with Ben Collins

Privacy Policy

Login (existing students)

Home

Login (existing students)