Google Sheets: Advanced

You’ll dive into powerful functions like QUERY, INDEX/MATCH, and ARRAYFORMULA, mastering the tools that allow you to manage complex data sets, streamline tasks, and gain deeper insights with less effort. Learn how to structure your data more intelligently, write SQL-style queries, and create dynamic spreadsheets that do the heavy lifting for you.

Course Objectives

By the end of this course, you'll be able to:

• Work with advanced functions such as QUERY, INDEX/MATCH, and ARRAYFORMULA

• Analyse and manipulate large datasets with greater speed and accuracy

• Create powerful reports using SQL-style queries and dynamic filters

• Apply formulas that adapt to scale, conditions, and interactivity

• Confidently use Google Sheets to automate manual data work

Who Is This Course For?

This course is ideal for:

• Confident Google Sheets users who want to deepen their understanding of complex functions

• Those regularly working with large datasets, reports, or team-shared spreadsheets

• Delegates with a solid grasp of Sheets basics, including IFs, VLOOKUPs, and filters

Prerequisites: Previous experience using Google Sheets to a comfortable level or attended our Google Sheets Essentials course.

Course Outline

Getting Started with Advanced Sheets

• Recap of key features from Essentials

• Shortcuts and efficiency tips

• Structuring large datasets for analysis

Advanced Functions

• Linking worksheets with VLOOKUP, INDEX, MATCH

• Nested IF, AND, OR logic for decision-making

• Recap of IFERROR, COUNTIF(S), AVERAGEIF(S)

• Using ArrayFormula for multi-condition lookups

Managing Large Data Sets

• Advanced filters and the FILTER function

• Combining SUBTOTALS with filters

• Creating and managing named ranges

• Fuzzy lookups and data validation

The Query Function

• Introduction to SQL-style queries in Google Sheets

• SELECT, WHERE, ORDER, and LIMIT clauses

• GROUP BY and AGGREGATE functions

• Using LABEL for cleaner reporting

Pivot Tables and Charts

• Creating multi-level pivot tables

• Filtering and grouping within pivot tables

• Building pivot charts for interactive insight

• Choosing visuals based on the story you want to tell

Data Visualisation & Dashboards

• Conditional formatting for insights at a glance

• Advanced charts and sparklines

• Building interactive dashboards with slicers and filters

Reflections & Planning

• Recap of key advanced features

• How to apply these tools to real-world workflows

• Next steps: automation, integration, and connecting with other apps