LOOKING FOR RATES AND SCHEDULES
​TO JOIN A PUBLIC CLASS?
CLICK HERE.

NEED A PROPOSAL FOR COMPANY?
EMAIL [email protected]

Advanced Google Sheets Training is designed for users who already know the basic spreadsheet environment and want to use Google Sheets for more structured reporting, formula building, data management, collaboration, and simple automation. The course focuses on practical workplace scenarios where participants need to clean data, build flexible formulas, manage controlled inputs, summarize records, and present insights using PivotTables and charts.

Unlike a basic spreadsheet course, this program goes beyond simple data entry and formatting. Participants will work with more advanced functions, conditional rules, validation tools, AI-assisted spreadsheet work, PivotTable analysis, and introductory automation concepts that can help them improve accuracy, reduce manual work, and create more reliable spreadsheet-based reports.

Main Objectives
By the end of the training, participants will be able to:

1. Apply basic to advanced Google Sheets functions for text handling, logical testing, mathematical summaries, arrays, and lookups.
2. Use data management tools such as conditional formatting, data validation, dropdown lists, and structured source data preparation.
3. Use AI tools to explain formulas, suggest solutions, summarize data, and identify possible trends or insights.
4. Create and enhance PivotTables for data analysis, filtering, grouping, and reporting.
5. Identify simple spreadsheet tasks that may be improved through macros or basic automation.

Who Should Attend

This training is ideal for employees, supervisors, analysts, administrative staff, reporting officers, trainers, business support teams, and Google Workspace users who regularly work with spreadsheet data. It is also suitable for Excel users who want to transition their formula, reporting, and data management skills into Google Sheets.

Participants should already know basic spreadsheet navigation, data entry, simple formulas, and basic formatting before attending this course.

Methodologies
Guided Demonstration and Formula Walkthroughs
The facilitator will demonstrate each function, tool, or feature using practical workplace examples. Participants will see how formulas are built step by step, including how to read, troubleshoot, and improve spreadsheet logic.
Hands-On Spreadsheet Exercises and Scenario-Based Activities
Participants will work on exercises involving data cleaning, formula writing, dropdown controls, conditional formatting, PivotTables, charting, AI-assisted analysis, and basic automation planning. Activities will be based on common workplace situations such as sales reports, attendance monitoring, inventory lists, trackers, and summary dashboards.

Outline 

Part 1: Basic to Advanced Google Sheets FunctionsText Functions

• Concatenating and combining texts using CONCATENATE and TEXTJOIN
• Extracting and changing text values using LEFT, RIGHT, MID, UPPER, LOWER, and PROPER
• Cleaning and converting text entries using TRIM, VALUE, SUBSTITUTE, and SPLIT

Logical Functions

• Creating basic IF statements for simple decisions
• Building nested and series IF statements for multiple conditions
• Combining conditions using AND and OR, with IFERROR for cleaner outputs

Math Functions

• Using core math functions such as SUM, AVERAGE, MAX, MIN, COUNT, and COUNTA
• Using named ranges for formula readability and control
• Applying conditional math functions such as COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS

Array-Based Formulas

• Understanding how arrays work in Google Sheets
• Shortening long formulas using ARRAYFORMULA
• Replacing repetitive row-by-row formulas with dynamic outputs

Lookup Functions

• Understanding VLOOKUP for basic data retrieval
• Applying advanced VLOOKUP techniques and recognizing its limitations
• Using XLOOKUP, INDEX, and MATCH for more flexible lookups

Google Sheets-Specific Functions

• Extracting and arranging records using FILTER, SORT, UNIQUE, and QUERY
• Parsing and cleaning text patterns using SPLIT, REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE
• Connecting and using Google-based functions such as IMPORTRANGE, GOOGLEFINANCE, and GOOGLETRANSLATE

Part 2: Managing Data Using Google Sheets ToolsConditional Formatting

• Applying basic conditional formatting for text, number, and date conditions
• Highlighting duplicates, blanks, deadlines, targets, and exceptions
• Creating formula-based conditional formatting rules for more specific scenarios

Data Validation

• Creating dropdown lists from typed options or cell ranges
• Restricting entries based on numbers, dates, text, and required formats
• Using validation help text, invalid input warnings, and checkboxes

Data Grouping and Summarizing

• Understanding subtotals vs. totals in summarized datasets
• Grouping related records for easier review and reporting
• Preparing clean source data for formulas, filters, and PivotTables

Part 3: Using AI Tools with Google SheetsAI Capabilities and Limitations

• Understanding what AI tools can and cannot do in spreadsheets
• Identifying spreadsheet tasks suitable for AI assistance
• Reviewing and validating AI-generated formulas, summaries, and insights

AI-Assisted Formula Work

• Using AI to explain existing formulas
• Asking AI to suggest formulas based on business questions
• Using AI to simplify long formulas and troubleshoot formula errors

AI-Assisted Data Review

• Using AI to summarize tables and structured datasets
• Asking AI to identify patterns, trends, and possible insights
• Creating report narratives from summarized spreadsheet data

Part 4: Analyzing and Presenting Data Using PivotTablesPivotTable Fundamentals

• Preparing source data for PivotTable analysis
• Creating and modifying PivotTables using rows, columns, values, and filters
• Refreshing PivotTables and changing source data when needed

Enhancing PivotTable Analysis

• Grouping dates, text, and numeric fields
• Filtering, sorting, and summarizing PivotTable results
• Showing values as percentages, comparisons, or calculated summaries

Presenting PivotTable Reports

• Formatting PivotTables for readability
• Using slicers for interactive filtering
• Creating Pivot Charts and simple dashboard-style reports

Related Courses

Basic Google Sheets

Advanced MS Excel