Formula Library — Every Formula, Validated
Browse ready-to-use formula templates for Excel, Google Sheets, SQL, and regex. Each formula includes a step-by-step breakdown, edge-case warnings, and real-world examples.
Browse hundreds of AI-generated formulas for Excel, Google Sheets, SQL, and regex. Every formula is validated against edge cases.
VLOOKUP With Multiple Criteria
Standard VLOOKUP only matches one column. Here's how to match on two or more criteria using INDEX-MATCH arrays.
INDEX MATCH Formula
INDEX-MATCH is the most powerful lookup formula in Excel. It's faster, more flexible, and more reliable than VLOOKUP.
XLOOKUP With Multiple Criteria
XLOOKUP simplifies multi-criteria lookups in Excel 365. No array formulas, no helper columns — just clean syntax.
VLOOKUP vs XLOOKUP
XLOOKUP is the modern replacement for VLOOKUP. Here's exactly when to use each — and why XLOOKUP wins in most cases.
SUMIFS With Multiple Conditions
SUMIFS lets you sum a range based on 2, 3, or more criteria. It's the workhorse formula for financial analysis and reporting.
COUNTIFS With Multiple Conditions
Count rows that match two or more criteria simultaneously. Essential for dashboards, KPI tracking, and data validation.
SUMPRODUCT for Conditional Calculations
SUMPRODUCT is the Swiss Army knife of Excel formulas. It handles weighted averages, complex conditions, and calculations SUMIFS can't.
Nested IF With Multiple Conditions
Nested IFs get messy fast. Here's how to write them cleanly — and when to use IFS or SWITCH instead.
IFERROR Formula
Stop ugly error messages from appearing in your spreadsheets. IFERROR catches any error and returns a clean fallback value.
Split Text With Formulas
Extract first names, last names, domains, or any delimited data using formulas instead of the Text to Columns wizard.
Dynamic Named Ranges With OFFSET
Stop updating range references manually. Dynamic named ranges grow and shrink automatically as your data changes.
Conditional Formatting With Formulas
Built-in conditional formatting rules are limited. Custom formulas let you highlight based on any logic you can write.
Calculate Date Differences
Find the exact number of days, months, or years between two dates. Including business days that skip weekends and holidays.
Percentage Change Formula
Calculate growth rates, month-over-month changes, and YoY comparisons. With proper handling for zero and negative baselines.
Remove Duplicates With Formulas
Extract a clean list of unique values without modifying your original data. UNIQUE for Excel 365, COUNTIF tricks for older versions.
Pivot Table Calculated Fields
Add custom calculations like profit margins, unit economics, and derived metrics directly inside your Pivot Table.
QUERY Function Basics
QUERY brings SQL-like power to Google Sheets. Filter, sort, group, and aggregate data with a single formula.
ARRAYFORMULA Basics
Write the formula once in the header row. It automatically applies to every row below. No dragging, no copying.
IMPORTRANGE With QUERY
Pull specific data from another Google Sheets file and filter it — all in one formula. The most powerful cross-sheet technique.
FILTER Function in Google Sheets
FILTER returns rows from a range that meet your conditions. Simpler than QUERY for straightforward filtering.
JOIN Three Tables
Most real queries need data from 3+ tables. Here's the clean pattern for multi-table JOINs that actually performs well.
SQL Window Functions
Window functions let you calculate across rows without grouping. Rankings, running totals, and row comparisons — all in one query.
GROUP BY With HAVING
WHERE filters rows before grouping. HAVING filters groups after aggregation. Know when to use each.
Extract Email Addresses
A battle-tested regex pattern for finding email addresses in unstructured text. Handles common formats and edge cases.
Extract Numbers From Text
Pull numbers out of messy text data — prices, measurements, IDs, or any numeric value embedded in strings.
Weighted Average Formula
Regular AVERAGE treats all values equally. Weighted average accounts for importance — grades, portfolio weights, survey responses.
Data Validation Dropdown Lists
Dropdown lists prevent bad data entry and make spreadsheets more user-friendly. Here's how to create them — static and dynamic.
Moving Average Formula
Smooth out noisy data with moving averages. Essential for sales trends, stock analysis, and KPI dashboards.
INDIRECT Function
INDIRECT converts text strings into cell references. Use it to dynamically reference sheets, create flexible formulas, and build cascading dropdowns.
Concatenate Text in Excel
Combine first name + last name, build addresses, or merge any text. Three methods compared: &, CONCAT, and TEXTJOIN.
LET Function
Stop repeating the same calculation 3 times in one formula. LET defines variables you can reuse — making formulas shorter, faster, and readable.
LAMBDA Function
LAMBDA lets you create your own reusable functions in Excel — without writing a single line of VBA. Define it once, use it everywhere.
FILTER Function (Excel)
FILTER extracts rows that match your criteria and spills the results automatically. No helper columns, no manual filtering.
SORT Function (Excel)
SORT and SORTBY create live-sorted copies of your data. Change the source, the sorted output updates automatically.
UNIQUE Function (Excel)
UNIQUE returns a dynamic list of distinct values from a range. No more Remove Duplicates button or helper columns.
SEQUENCE Function
SEQUENCE creates dynamic arrays of numbers. Build date ranges, row numbers, time slots, and matrix scaffolding without typing.
SUBSTITUTE Function
SUBSTITUTE replaces text within a cell using a formula. Clean messy data, standardize formats, and fix text issues automatically.
OFFSET Function
OFFSET creates references that move and resize based on parameters. Essential for auto-expanding ranges, rolling calculations, and dynamic charts.
CHOOSE Function
CHOOSE picks a value from a list based on an index number. It's a cleaner alternative to nested IF statements for fixed options.
GOOGLEFINANCE Function
Pull real-time stock prices, historical data, and financial metrics directly into your spreadsheet. Build portfolio trackers and watchlists.
CTE (Common Table Expression)
CTEs (WITH clause) break complex queries into named steps. Replace nested subqueries with clean, readable, maintainable SQL.
CASE WHEN Statement
CASE WHEN is SQL's IF-THEN-ELSE. Categorize data, create conditional aggregations, and build pivot-like outputs.
COALESCE Function
COALESCE returns the first non-NULL value from a list. Essential for default values, NULL-safe calculations, and clean query output.
SQL Subqueries
Subqueries let you use the result of one query inside another. Essential for filtering, comparisons, and derived tables.
IP Address Regex
Match and validate IPv4 addresses in text. From quick-and-dirty patterns to strict 0-255 range validation.
URL Extraction Regex
Find and extract URLs from unstructured text. Match full URLs, domains, or specific URL components with tested patterns.
Password Validation Regex
Enforce password strength rules with regex. Minimum length, character requirements, and common patterns for registration forms.
TRIM & CLEAN Functions
Data from imports, copies, and web scraping often has invisible junk characters and extra spaces. TRIM and CLEAN fix them.
TEXTJOIN Function
TEXTJOIN combines text from a range with any delimiter you choose. Replaces chained CONCATENATE or ampersand (&) formulas.
LEFT, RIGHT, MID Functions
Pull characters from the start, end, or middle of text. Essential for parsing product codes, IDs, and fixed-format data.
IF with AND/OR
Use AND and OR inside IF to test multiple conditions cleanly. Handle complex business rules without deeply nested formulas.
NPV and IRR Functions
NPV and IRR are the core functions for evaluating investments, comparing projects, and making capital budgeting decisions.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate it, explain it, and validate it — instantly.