Google Sheets Solutions & Automation
Learn how to use Google Sheets for calculations, data management, reporting, automation and business workflows. From beginner fundamentals to SQL-level formulas and Apps Script triggers.
Hub Last Updated: October 30, 2026
Google Sheets Formula Assistant & Generator
Select your formula goal, customize your column ranges and cell references below, and instantly get verified Google Sheets formula syntax with syntax explanations.
Customize Parameters (XLOOKUP)
How it works: Returns the exact value from column D matching A2 without breaking if columns move.
Essential Google Sheets Formulas
The high-leverage formulas that power operational reporting, dynamic lookups, and automated data cleaning.
=QUERY(data, query, [headers])
The QUERY function is Google Sheets' most versatile formula, executing a SQL-like Google Visualization API Query Language string across spre...
=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
XLOOKUP is the modern, flexible replacement for VLOOKUP and HLOOKUP. It searches left, right, up, and down, defaults to exact match, handles...
=ARRAYFORMULA(array_formula)
ARRAYFORMULA enables single spreadsheet formulas to output an entire matrix or column of calculated results automatically. When new rows are...
=IMPORTRANGE(spreadsheet_url, range_string)
IMPORTRANGE imports a specified cell range from another external Google Spreadsheet workbook. It provides real-time multi-sheet linking with...
=REGEXEXTRACT(text, regular_expression)
REGEXEXTRACT extracts matching substrings from a text string according to Google RE2 regular expression patterns. Ideal for isolating email ...
=FILTER(range, condition1, [condition2, ...])
FILTER dynamically outputs only the rows or columns from a source range that satisfy one or more logical conditions. As source data updates,...
=INDEX(reference, [row], [column]) with MATCH(lookup_value, lookup_array, [match_type])
Combining INDEX with MATCH provides two-way horizontal and vertical matrix lookups. It looks left and right, supports dynamic column and row...
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
SUMIFS calculates the mathematical sum of values in a range that meet multiple criteria, such as date boundaries, status strings, customer t...
=COUNTIF(range, criterion)
COUNTIF counts the number of cells within a range that meet a specific condition, text string, numerical threshold, or wildcard pattern....
=LAMBDA([name1, ...], formula_expression)(arg1, ...)
LAMBDA allows users to author custom, reusable functions directly inside standard spreadsheet cells using native formulas, eliminating the n...
=LET(name1, value1, [name2, value2, ...], calculation_expression)
LET assigns names to calculation results inside a formula. This stops Google Sheets from calculating the same sub-expression multiple times,...
=MAP(array1, [array2, ...], LAMBDA(val1, [val2, ...], expression))
MAP applies a custom LAMBDA function to every element in one or more arrays, producing an array of transformed results. Unlike ARRAYFORMULA,...
=BYROW(array, LAMBDA(row, expression))
BYROW evaluates a 2D range row by row, passing each horizontal row array to a LAMBDA function and returning a single vertical column of calc...
=CHOOSEROWS(array, row_num1, [row_num2, ...])
CHOOSEROWS extracts specific rows from an array or range by their index numbers. Supports negative indexing to pull rows from the bottom of ...
=VSTACK(array1, [array2, ...])
VSTACK appends arrays and ranges vertically on top of each other. Replaces complex manual curly-bracket `{Sheet1!A:B; Sheet2!A:B}` syntax wi...
=HSTACK(array1, [array2, ...])
HSTACK appends arrays and ranges horizontally side-by-side in sequence, merging columns from separate sources into a single unified table....
=UNIQUE(range, [by_column], [exactly_once])
UNIQUE extracts distinct values from a column or table, stripping duplicates automatically. When source records change, unique list recalcul...
=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])
SORT reorders the rows of an array or range based on values in one or more specified columns without modifying the original raw data table....
=TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])
TEXTJOIN combines text from multiple strings or ranges, inserting a specified delimiter between each item while automatically skipping empty...
=INDIRECT(cell_reference_as_string)
INDIRECT converts a text string into an active cell or range reference. Enables dynamic switching between sheet tabs (e.g. querying Jan, Feb...
Beginner Guides
Master the core mechanics of Google Sheets without getting stuck.
How to Create & Name a Spreadsheet
Set up workbooks, manage tabs, and configure timezone and locale settings.
How to Format Data & Numbers
Format currency, percentages, dates, custom number strings, and headers.
How to Freeze Rows and Columns
Lock header rows and ID columns in place while scrolling through large sheets.
How to Filter Data with Filter Views
Sort and filter records without disrupting other active team members editing simultaneously.
How to Sort Data Multi-Level
Sort by primary column (Date) then secondary column (Customer) without scrambling formulas.
Advanced Workflows & BI Dashboards
Transform static tables into interactive business intelligence dashboards.
Executive Dashboards
Combine KPI scorecards, sparklines, and chart visualizations into printable dashboards.
Data Validation & Dropdowns
Create searchable in-cell dropdown lists, date pickers, and checkbox approval workflows.
Conditional Formatting Rules
Color-code deadlines, overdue invoices, and performance milestones automatically.
Pivot Tables & Calculated Fields
Summarize multidimensional transaction records with drill-down groupings.
Apps Script & Webhooks
Automate email dispatches, PDF exports, and database sync using JavaScript.
Looker Studio Integration
Stream Google Sheets data directly into interactive, shareable client dashboards.
Need Ready-Made Spreadsheets?
Download our free pre-built Google Sheets templates for business invoices, CRM tracking, budgets, and project planners.
Explore Free Google Templates →