📊Spreadsheets & Automation Hub

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

⚡
Interactive Feature

Google Sheets Formula Assistant & Generator

Tested & Verified

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)

Generated Formula
=XLOOKUP(A2, Products!A:A, Products!D:D, "Item Not Found", 0)

How it works: Returns the exact value from column D matching A2 without breaking if columns move.

💡 Pro Tip: Use XLOOKUP instead of VLOOKUP because it searches both left and right and doesn't break when new columns are inserted.
Formula Cheat Sheet

Essential Google Sheets Formulas

The high-leverage formulas that power operational reporting, dynamic lookups, and automated data cleaning.

QUERYData Transformation
=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...

View Full Syntax, Examples & Errors →
XLOOKUPLookup & Reference
=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...

View Full Syntax, Examples & Errors →
ARRAYFORMULAData Transformation
=ARRAYFORMULA(array_formula)

ARRAYFORMULA enables single spreadsheet formulas to output an entire matrix or column of calculated results automatically. When new rows are...

View Full Syntax, Examples & Errors →
IMPORTRANGELookup & Reference
=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...

View Full Syntax, Examples & Errors →
REGEXEXTRACTText & Regex
=REGEXEXTRACT(text, regular_expression)

REGEXEXTRACT extracts matching substrings from a text string according to Google RE2 regular expression patterns. Ideal for isolating email ...

View Full Syntax, Examples & Errors →
FILTERData Transformation
=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,...

View Full Syntax, Examples & Errors →
INDEX / MATCHLookup & Reference
=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...

View Full Syntax, Examples & Errors →
SUMIFSMath & Aggregation
=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...

View Full Syntax, Examples & Errors →
COUNTIFMath & Aggregation
=COUNTIF(range, criterion)

COUNTIF counts the number of cells within a range that meet a specific condition, text string, numerical threshold, or wildcard pattern....

View Full Syntax, Examples & Errors →
LAMBDALogic & Modern Lambdas
=LAMBDA([name1, ...], formula_expression)(arg1, ...)

LAMBDA allows users to author custom, reusable functions directly inside standard spreadsheet cells using native formulas, eliminating the n...

View Full Syntax, Examples & Errors →
LETLogic & Modern Lambdas
=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,...

View Full Syntax, Examples & Errors →
MAPLogic & Modern Lambdas
=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,...

View Full Syntax, Examples & Errors →
BYROWLogic & Modern Lambdas
=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...

View Full Syntax, Examples & Errors →
CHOOSEROWSData Transformation
=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 ...

View Full Syntax, Examples & Errors →
VSTACKData Transformation
=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...

View Full Syntax, Examples & Errors →
HSTACKData Transformation
=HSTACK(array1, [array2, ...])

HSTACK appends arrays and ranges horizontally side-by-side in sequence, merging columns from separate sources into a single unified table....

View Full Syntax, Examples & Errors →
UNIQUEData Transformation
=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...

View Full Syntax, Examples & Errors →
SORTData Transformation
=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....

View Full Syntax, Examples & Errors →
TEXTJOINText & Regex
=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...

View Full Syntax, Examples & Errors →
INDIRECTLookup & Reference
=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...

View Full Syntax, Examples & Errors →
Foundations

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.

Scaling Up

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 →