Formula library

Look up a value in the first column of a range and return a value from another column in the same row.

The modern replacement for VLOOKUP and INDEX/MATCH — look up a value and return a match in any direction.

A flexible two-function lookup that can return values to the left of the lookup column.

Run SQL-like queries over a range in Google Sheets to filter, sort, and aggregate.

Apply a formula down an entire column at once in Google Sheets.

Return only the rows of a range that meet a condition — a live, formula-based filter.

Sort a range by a column without touching the original data.

Find the position of a value within a row or column.

Look up a value in the top row of a table and return a value from a row below it — the horizontal cousin of VLOOKUP.

Return a cell or range a set number of rows and columns away from a starting point — useful for dynamic ranges.

Turn a piece of text into a real cell or range reference — build references on the fly from other cells.

Return the value at a given position in a range. Combined with MATCH it becomes a lookup that can return values in any direction.

Search a sorted row or column for a value and return a result from a corresponding position in another range.

Return the relative position of a value in an array with support for exact, wildcard, and binary search modes.

Build a cell address string like $A$1 from a given row and column number, with optional absolute/relative and sheet name.

Return the row number of a cell or the first row of a range; useful for generating sequential numbers or building dynamic formulas.

Count the number of rows in a range or array, returning a single integer.

Return the column number of a cell, where A=1, B=2, etc.; handy for dynamic column offsets in formulas.

Count the total number of columns in a range or array and return it as an integer.

Flip a range so rows become columns and columns become rows, returning a transposed array.

Extract a specific value from a PivotTable using field names and item values as explicit identifiers.

Return the formula in a cell as a visible text string for documentation or auditing purposes.

Create a clickable hyperlink in a cell that navigates to a URL, file path, or cell range when clicked.

Select and return specific rows from an array by their position numbers, in any order.

Select and return specific columns from an array by their position numbers, in any order.

Return a specified number of rows or columns from the start or end of an array.

Remove a specified number of rows or columns from the start or end of an array and return the remainder.

Vertically stack multiple arrays or ranges on top of each other into one combined array.

Horizontally stack multiple arrays or ranges side by side into one combined wider array.

Convert a multi-column, multi-row range into a single horizontal row array.

Convert a multi-row, multi-column range into a single vertical column array.

Reshape a one-dimensional row or column array into a 2D table with a fixed number of columns per row.

Reshape a one-dimensional array into a 2D table with a fixed number of rows per column.

Expand an array to a larger size by padding extra rows and columns with a specified value or #N/A.

Returns the number of individual areas (contiguous ranges) inside a multi-area reference. Useful when validating or counting disjointed selections.

Add up the cells in a range that meet a single condition.

Sum values that meet multiple conditions at once.

Count the cells in a range that meet a condition.

Count cells that meet several conditions simultaneously.

Average the values in a range that meet a condition.

Return the distinct values from a range, removing duplicates.

Add up all the numbers in a range — the most common spreadsheet calculation.

Find the mean of a range of numbers, ignoring blanks and text.

Count cells that contain numbers (COUNT) or any value at all (COUNTA).

Return the largest (MAX) or smallest (MIN) number in a range.

Multiply ranges together row by row and sum the result — ideal for weighted totals.

Round a number to a set number of decimal places.

Average the values that meet several conditions at once across different columns.

Find the largest or smallest value among rows that meet one or more conditions.

Return the remainder after dividing one number by another — great for every-Nth-row logic.

Return the position of a number within a list — highest first by default, or lowest first.

Return the middle value of a range — a measure of center that ignores outliers, unlike the average.

Return the Nth largest (LARGE) or Nth smallest (SMALL) value in a range — e.g. the 2nd highest.

Generate a random whole number between a low and high bound — useful for samples and test data.

Calculate the periodic payment on a loan from the rate, number of periods, and principal.

Aggregate a range (sum, average, count…) while ignoring other SUBTOTALs and, optionally, hidden rows. Ideal under filters.

Always round a number up, away from zero, to a set number of digits. Useful for pricing and capacity planning.

Always round a number down, toward zero, to a set number of digits — the opposite of ROUNDUP.

Return the absolute (positive) value of a number, dropping any minus sign. Useful for differences and variances.

Raise a number to a power (exponent). POWER(base, exponent) is the function form of the ^ operator.

Return the square root of a number. Pair with POWER for other roots.

Multiply all the numbers in a range together. The multiplication counterpart of SUM.

Return the sum of the squares of all supplied numbers — useful for statistical and engineering calculations.

Apply one of 19 aggregate functions to a range while ignoring hidden rows, errors, or nested subtotals.

Return the integer portion of a division result, discarding any remainder — equivalent to integer division.

Rounds a number to the nearest multiple of a given factor, useful for rounding prices to the nearest 0.05 or times to 15 minutes.

Rounds a number up to the nearest multiple of significance, always rounding away from zero.

Rounds a number down to the nearest multiple of significance, always rounding toward zero.

Rounds a number down to the nearest integer, always toward negative infinity regardless of sign.

Removes the decimal portion of a number, truncating toward zero without rounding.

Returns e raised to the power of a given number, where e ≈ 2.71828. Used in exponential growth and decay models.

Returns the natural logarithm (base e) of a number. Used to invert EXP and model logarithmic growth.

Returns the logarithm of a number to a specified base (default base 10). Useful for scaling data across orders of magnitude.

Returns the base-10 logarithm of a number. Common in scientific notation, decibel calculations, and pH formulas.

Returns 1 for positive numbers, -1 for negative numbers, and 0 for zero. Handy for conditional logic based on sign.

Returns the greatest common divisor of two or more integers — the largest integer that divides all inputs without a remainder.

Returns the least common multiple of two or more integers — the smallest positive integer divisible by all inputs.

Returns the factorial of a non-negative integer (n! = n × (n−1) × … × 1). Used in permutations and combinations.

Returns the number of combinations (n choose k) — ways to choose k items from n without regard to order.

Returns the number of ordered arrangements (permutations) of k items chosen from n — order matters.

Returns a random decimal number between 0 (inclusive) and 1 (exclusive), recalculated every time the sheet recalculates.

Spills a grid of random numbers with specified rows, columns, min, max, and integer/decimal options. Excel 365 and Google Sheets only.

Converts an Arabic integer (1–3999) to a Roman numeral text string, with optional simplification mode.

Converts a Roman numeral text string to its Arabic integer equivalent. Accepts classical and simplified forms.

Converts a decimal integer to a text string in any number base from 2 to 36 (e.g. binary, hex). Excel only.

Converts a number represented in a given base to a decimal integer. The inverse of BASE. Excel only.

Returns the mathematical constant π (approximately 3.14159265358979) with 15 digits of precision.

Returns the sine of an angle given in radians. Convert degrees to radians with RADIANS() first.

Returns the cosine of an angle given in radians. Convert degrees to radians with RADIANS() first.

Returns the tangent of an angle given in radians. Undefined at 90° and 270°; convert degrees with RADIANS().

Converts an angle from degrees to radians. Required before passing degree values to SIN, COS, or TAN.

Converts an angle from radians to degrees. Useful after trigonometric functions that return radian values.

Rounds a number up to the nearest even integer. Handy for packaging or pairing calculations that need multiples of 2.

Rounds a number up to the nearest odd integer. Useful when calculations require odd-numbered groupings or counts.

Return one value when a condition is true and another when it is false.

Catch formula errors and show a friendly value instead.

Test several conditions in order and return the first match — cleaner than nested IF.

Compare one value against a list of options and return the matching result.

Combine multiple conditions — AND requires all true, OR requires any true.

Pick one value from a list by its position number — a clean alternative to nested IFs for fixed options.

Return TRUE only when every condition is true. The building block for multi-condition IF tests.

Return TRUE when at least one condition is true. Use it to flag rows that meet any of several criteria.

Test whether a cell contains a number, returning TRUE or FALSE. Great for validating imported data.

Returns a custom value when a formula produces an #N/A error, passing all other results through unchanged.

Reverses a logical value: returns FALSE if the argument is TRUE, and TRUE if the argument is FALSE.

Returns TRUE when an odd number of its arguments evaluate to TRUE (exclusive OR logic).

Returns the Boolean value TRUE — used to supply a literal TRUE in formulas or comparisons.

Returns the Boolean value FALSE — used to supply a literal FALSE in formulas or comparisons.

Assigns names to calculation results within a formula, improving readability and avoiding repeated computation.

Applies a LAMBDA function to each element of one or more arrays, returning an array of transformed results.

Accumulates an array into a single value by applying a LAMBDA function cumulatively across all elements.

Like REDUCE but returns the intermediate accumulator value at every step, producing an array of running results.

Applies a LAMBDA function to each row of an array and returns a single-column array of per-row results.

Applies a LAMBDA function to each column of an array and returns a single-row array of results.

Creates an array of specified dimensions by applying a LAMBDA function to generate each cell value.

Defines a reusable custom function using named parameters and a formula body, letting you call it like any built-in function — no VBA or scripts needed.

Join text from multiple cells with a separator, skipping blanks.

Extract part of a text string from the start, end, or middle.

Remove extra spaces from text — the fix for lookups that mysteriously fail.

Replace specific text inside a cell — remove or swap characters.

Format a number or date as text using a custom pattern.

Count the number of characters in a cell, including spaces.

Return the position of one piece of text inside another — FIND is case-sensitive, SEARCH is not.

Change the capitalisation of text — title case, all caps, or all lowercase.

Convert a number stored as text into a real number so it can be used in calculations.

Pull the first N characters from a text string — like grabbing a currency prefix or a code's leading letters.

Pull the last N characters from a text string — like the trailing digits of an order or SKU.

Extract characters from the middle of a string, starting at a position you choose for a length you choose.

Join text from several cells into one. The classic way to build a full name or address from separate columns.

Compare two text values for an exact, case-sensitive match. Returns TRUE only when they are identical.

Repeat a piece of text a set number of times — handy for in-cell bar charts or padding.

Joins multiple text strings or ranges into one string without a delimiter.

Joins a range of text values with a specified delimiter, optionally ignoring empty cells.

Removes all non-printable characters (ASCII 0-31) from a text string.

Converts all characters in a text string to uppercase.

Converts all characters in a text string to lowercase.

Capitalizes the first letter of each word in a text string and lowercases all other letters.

Replaces a specific number of characters within a text string starting at a given position.

Returns the starting position of one text string within another; case-sensitive.

Returns the position of one text string within another; case-insensitive and supports wildcards.

Converts a number or date to a formatted text string using a specified number format code.

Converts a text string representing a number to a numeric value using specified decimal and group separators.

Splits a text string into an array of substrings using column and/or row delimiters.

Extracts all text appearing before the first (or nth) occurrence of a specified delimiter.

Returns the text that appears after a given delimiter in a string, with options for instance and direction.

Splits text around a delimiter and places each piece into a separate cell across a row (Google Sheets only).

Concatenates elements of an array or range with a specified delimiter between each element (Google Sheets only).

Returns the character represented by a given ASCII/ANSI code number (1–255).

Returns the numeric ASCII/ANSI code of the first character in a text string.

Returns the Unicode character for a given code point number, supporting the full Unicode range beyond ASCII.

Returns the Unicode code point (decimal number) of the first character in a text string.

Converts a number to text formatted as currency with a dollar sign and specified decimal places.

Rounds a number to a specified number of decimals and returns it as text with optional thousands separators.

Returns the value if it is text, or an empty string if it is not — useful for type-safe text operations.

Extracts the first substring matching a regular expression pattern from a text string (Google Sheets only).

Returns TRUE if any part of a text string matches a regular expression pattern (Google Sheets only).

Replaces all substrings matching a regular expression with a new string (Google Sheets only).

Converts an array or range into a single text string, with concise or strict formatting options.

Converts any value to text, with concise or strict mode for use in formula-building or display.

Work with the current date and measure the gap between two dates.

Find the last day of the month, this month or a number of months away.

Count working days between two dates, excluding weekends.

Return the day of the week for a date as a number.

Returns today's date as a serial number, updating automatically each time the workbook recalculates.

Returns the current date and time as a serial number, updating each time the workbook recalculates.

Creates a date serial number from separate year, month, and day integer values.

Creates a time serial number from separate hour, minute, and second values (always between 0 and 1).

Calculates the difference between two dates in years, months, or days using a unit code string.

Converts a date stored as text into an Excel date serial number so it can be used in date calculations.

Converts a time stored as text into a decimal fraction between 0 and 1 representing the time of day.

Extracts the four-digit year from a date value, returning an integer from 1900 to 9999.

Returns the month number (1–12) from a date serial value, useful for grouping or filtering by month.

Extracts the day-of-month integer (1–31) from a date value for date arithmetic or display purposes.

Returns the hour component (0–23) from a time or date-time value for time-based analysis.

Extracts the minute component (0–59) from a time serial value for granular time-based calculations.

Returns the seconds component (0–59) from a time serial number for precise time decomposition.

Returns the week number of the year (1–54) for a date, using a configurable week-start convention.

Returns the ISO 8601 week number (1–53) for a date, where weeks always start on Monday.

Returns the date that is a given number of working days before or after a start date, skipping weekends.

Returns a working-day-offset date with custom weekend definitions, supporting non-standard work weeks globally.

Counts working days between two dates with configurable weekend definitions and optional holiday exclusions.

Returns the date exactly N calendar months before or after a start date, landing on the same day of the month.

Calculates the fraction of a year between two dates using a specified day-count basis (e.g. 30/360 or actual/365).

Returns the number of days between two dates as a simple integer, end minus start.

Calculates days between two dates using a 360-day year (12 months of 30 days), common in financial accounting.

Converts a date serial number to a text string in the locale-specific date format (Lotus 1-2-3 compatibility function).

Converts a Unix epoch timestamp (seconds since 1970-01-01) to a Google Sheets date-time value (Sheets only).

Count how many cells in a range are not empty — including text, numbers, and errors. Use it to count filled-in entries.

Count how many cells in a range are empty — handy for spotting missing data before you run a report.

Return the most frequently occurring number in a range — the value that appears most often.

Calculates the average of all values in a range, including text (as 0) and logical values. Use when you need non-numeric cells counted.

Counts cells that contain numbers in a range. Text, blanks, and errors are ignored. Essential for tallying numeric entries.

Returns the largest numeric value in a range or list of values. Ignores text and logical values. Perfect for finding peaks.

Returns the maximum value in a range that meets one or more criteria. Available in Excel 2019+ and Google Sheets.

Returns the largest value in a range including text (as 0) and logical values (TRUE=1). Use when non-numeric cells must be included.

Returns the smallest numeric value in a range or list. Ignores text and logical values. Ideal for finding the lowest score or cost.

Returns the minimum value in a range that meets one or more criteria. Available in Excel 2019+ and Google Sheets.

Returns the smallest value in a range including text (as 0) and logical values (TRUE=1). Use when non-numeric cells must be included.

Returns the k-th largest value in a dataset. Use to find the 2nd highest, 3rd highest, etc. without sorting.

Returns the k-th smallest value in a dataset. Complements LARGE for bottom-N analysis and reverse-rank lookups.

Returns the rank of a number in a list, assigning the same rank to ties (same as RANK). Essential for leaderboards.

Returns the rank of a number, averaging ranks for ties. Useful in statistical analysis where tied ranks should not cluster.

Returns the k-th percentile value from a dataset. Useful for performance benchmarking and threshold analysis.

Returns the rank of a value as a percentage of a dataset. Shows where a specific value falls relative to the whole.

Returns the quartile (Q0–Q4) of a dataset. Quickly divides data into four equal parts for distribution analysis.

Estimates standard deviation from a sample, ignoring text and logical values. The classic measure of data spread.

Calculates standard deviation for an entire population (n denominator). Use when you have all data, not just a sample.

Estimates standard deviation for a sample (n-1 denominator). Preferred over legacy STDEV for clarity in modern spreadsheets.

Estimates variance from a sample, ignoring text and logical values. Variance is the square of standard deviation.

Calculates variance for an entire population (n denominator). Use when your data is the complete dataset, not a sample.

Estimates sample variance (n-1 denominator). The modern, explicit replacement for the legacy VAR function.

Returns the Pearson correlation coefficient between two datasets. Measures linear relationship strength from -1 to 1.

Returns the population covariance between two datasets. Measures how two variables change together; foundation for correlation.

Predicts a future value along a linear trend based on existing x and y data points.

Returns predicted y-values along a linear trend for multiple new x-values using least-squares regression.

Returns the slope of the linear regression line through a set of x and y data points.

Returns the y-intercept of the linear regression line through a set of x and y data points.

Calculates the mean of a dataset after excluding a specified percentage of extreme high and low values.

Counts how many values fall into each bin of a specified range, returning a vertical array of frequencies.

Returns the geometric mean of a set of positive numbers, ideal for growth rates and ratios.

Returns the harmonic mean of positive numbers, best suited for rates and speeds.

Returns the skewness of a distribution — how asymmetric it is around the mean.

Returns the kurtosis of a dataset, measuring how heavy the tails are relative to a normal distribution.

Returns the margin of error for a population mean confidence interval using the normal distribution.

Returns the normal distribution probability (PDF or CDF) for a given value, mean, and standard deviation.

Returns the inverse of the normal cumulative distribution for a given probability, mean, and standard deviation.

Returns TRUE only when a cell is truly empty (no value, no formula, no space). Differs from =A2="" because ISBLANK treats formula-returned empty strings as not-blank.

Returns TRUE if a value is text, FALSE for numbers, dates, booleans, or errors.

Returns TRUE if a value is any error type (#N/A, #VALUE!, #REF!, #DIV/0!, etc.), FALSE otherwise.

Returns TRUE for any error except #N/A; useful when you want to handle #N/A separately.

Returns TRUE only when a value is the #N/A error, which typically means a lookup found no match.

Returns TRUE if a value is a logical (Boolean) value: TRUE or FALSE.

Returns TRUE if the argument is a valid cell or range reference, FALSE otherwise.

Returns TRUE if a number is even, FALSE if it is odd. Non-integers are truncated before checking.

Returns TRUE if a number is odd, FALSE if it is even. Non-integers are truncated before checking.

Returns TRUE if a cell contains a formula, FALSE if it holds a hard-coded value or is empty.

Returns the #N/A error value deliberately, typically to mark missing data or prevent partial chart plotting.

Returns a number representing the data type: 1=number, 2=text, 4=logical, 8=formula, 16=error, 64=array.

Returns a number identifying which type of error a cell contains; returns #N/A if there is no error.

Returns information about a cell's formatting, location, or contents based on an info_type string.

Returns information about the current operating environment such as OS, Excel version, or calculation mode.

Converts a value to a number; returns 0 for text, TRUE=1, FALSE=0, dates as serial numbers.

Returns the sheet number of a referenced sheet or the current sheet if no argument is given.

Returns the total number of sheets in a workbook or in a 3D reference range.

Returns the interest portion of a loan payment for a specific period in a fixed-rate amortisation schedule.

Returns the principal portion of a loan payment for a specific period in a fixed-rate amortisation schedule.

Calculates the future value of an investment given a constant interest rate, regular payments, and optional present value.

Returns the present value of an investment — the lump sum today equivalent to future cash flows at a given discount rate.

Calculates net present value of a series of future cash flows discounted at a given rate, assuming end-of-period timing.

Calculates net present value for cash flows with irregular (non-periodic) dates using exact date-based discounting.

Returns the internal rate of return for a series of periodic cash flows, assuming equal time intervals between each.

Returns the internal rate of return for cash flows with irregular dates, using exact date-based discounting.

Calculates the modified internal rate of return for cash flows with separate finance and reinvestment rates.

Calculates the periodic interest rate of an annuity given number of periods, payment, and present value.

Returns the number of periods required to pay off a loan or reach a savings goal at a fixed interest rate and payment.

Converts a nominal annual interest rate to an effective annual rate based on the number of compounding periods.

Converts an effective annual interest rate back to a nominal annual rate for a given number of compounding periods.

Calculates the straight-line depreciation of an asset for one period given cost, salvage value, and useful life.

Returns the fixed-declining-balance depreciation of an asset for a specified period using a fixed rate.

Computes double-declining-balance (or variable-rate declining) depreciation for an asset in a specific period.

Calculates sum-of-years'-digits depreciation for a specified period, front-loading expense more moderately than DDB.

Returns the cumulative interest paid on a loan between two specified periods at a constant interest rate.

Returns the cumulative principal paid on a loan between two periods at a fixed interest rate.

Returns the number of periods required for an investment to reach a specified future value at a constant rate.

Returns the equivalent interest rate per period for the growth of an investment from present to future value over n periods.

Sorts the rows of a range based on the values in one or more separate sort-by ranges, with optional ascending/descending control.

Generates a 2-D array of sequential numbers with configurable rows, columns, start value, and step size.

Google Sheets function that collapses multiple ranges or arrays into a single one-column list, ignoring structure.

Sums values in a database column that match criteria defined in a separate criteria range.

Averages values in a database field for records that match the specified criteria range.

Counts cells containing numbers in a database field for records matching the criteria range.

Counts non-empty cells in a database field for records matching the criteria, including text and numbers.

Extracts a single value from a database column where the record uniquely matches the criteria range.

Returns the maximum value in a database field for records that match the specified criteria range.

Returns the minimum value in a database field for records that match the specified criteria range.

Multiplies together all values in a database field for records that match the criteria range.

Converts a number from one unit of measurement to another using built-in unit codes for distance, weight, time, and more.

Converts a decimal (base-10) integer to its binary (base-2) text representation with optional zero-padding.

Converts a decimal integer to its hexadecimal (base-16) text representation, with optional padding.

Converts a binary (base-2) number string to its decimal integer equivalent.

Converts a hexadecimal (base-16) text string to its decimal integer equivalent.

Returns the bitwise AND of two non-negative integers, useful for masking and flag checking.

Returns the bitwise OR of two non-negative integers, useful for combining flags or permissions.

Imports a cell range from another Google Sheets spreadsheet using its URL and range string.

Imports data from a table or list on a webpage directly into Google Sheets.

Imports data from a publicly accessible CSV or TSV URL directly into Google Sheets.

Imports data from an XML or HTML document using an XPath query expression.

Fetches current or historical securities and currency data from Google Finance into a cell.

Inserts an image from a URL into a Google Sheets cell with optional sizing mode.

Renders a miniature chart inside a single Google Sheets cell from a range of data.

Automatically detects and returns the language code of text in a Google Sheets cell.

Translates text from one language to another using Google Translate inside Google Sheets.