Excel Functions

Build a cell address as text from row and column numbers - "$C$5" - with control over $ style, R1C1, and sheet names. Pairs with INDIRECT.

Counts the separate rectangular blocks inside a reference - wrap multi-area unions in an extra pair of parentheses.

Pick a value from a list by position — index 2 returns the second value. One selector cell can switch entire ranges in and out of a model.

Return just the columns you want from a table — in any order. Negative numbers count from the end, so -1 is always the last column.

Return just the rows you want from a range by position. Negative numbers count from the bottom — -1 is always the newest row of a growing log.

Return the column number of a cell - A is 1, B is 2 - or of the formula's own cell. Automates VLOOKUP's col_index_num for drag-across lookups.

Count how many columns a range or array spans. Builds VLOOKUP column indexes that survive inserted columns.

Returns an array with rows or columns removed from its edges - drop headers with 1, totals rows with -1.

Grows an array to a fixed size, padding new cells with a value you choose - or with #N/A errors if you forget pad_with.

Return every row that meets your criteria — one formula, many results, updates automatically.

Returns another cell's formula as a text string - perfect for audit columns, documentation, and spotting pasted-over values.

Pulls a value out of a PivotTable by describing it with field/item pairs, so the formula survives pivot rearranging.

Builds a PivotTable-style grouped summary with one spilling formula that recalculates live.

VLOOKUP's horizontal twin: find a value in the top row of a table and return a value from a row below.

Places ranges or arrays side by side — left to right — into a single spilled result.

Creates a clickable link to a web page, email, file, or cell, displaying friendly text of your choosing.

Places a picture inside a cell from an https URL, so it sorts, filters, and moves with its row.

Return the value at a given row and column of a range. Half of the famous INDEX/MATCH combo.

Turns a text string like "North!B9" into a live cell reference, so references can be assembled from cell values.

Legacy approximate-match lookup across a vector. Still handy for tier tables and last-value tricks.

Find the position of a value in a row or column. The other half of INDEX/MATCH.

Returns a reference shifted a given number of rows and columns from a starting cell, optionally resized to a height-by-width range.

Builds a live cross-tab - row groups, column groups, aggregated values, and totals - from one spilling formula. GROUPBY's 2-D sibling.

Return the row number of a cell - or of the formula's own cell. Powers numbering that repairs itself when rows are deleted.

Count how many rows a range or array spans - full or empty. Great for measuring FILTER results and building running counts.

Receives live pushed data - market quotes, sensor feeds - from an installed real-time data server. A connector, not a data source: no add-in, no data.

Returns a sorted copy of a range that spills onto the sheet and re-sorts itself whenever the source data changes.

Sorts a range by one or more other ranges - the sort keys never have to appear in the spilled result.

Keeps only the first or last rows and columns of an array - negatives count from the end. The go-to wrapper for top-N lists.

Flattens any range or array into a single column, with control over blanks, errors, and reading order.

Flattens any range or array into a single row, with control over blanks, errors, and reading order.

Flip a range on its diagonal — rows become columns and columns become rows — with a live link to the source. Spills in 365, Ctrl+Shift+Enter before.

Strips blank outer rows and columns from a reference - makes whole-column references fast and safe. Shorthand: the A1:.E10 trim-ref operators.

Returns a live, spilled list of distinct values - or, with exactly_once, only the values that appear a single time.

The classic: find a value in the first column of a table and return a value from another column in the same row.

Stacks ranges or arrays vertically — one on top of another — into a single spilled result.

Folds a single row or column of values into a grid, filling column by column at the height you choose.

Folds a single row or column of values into a grid, filling row by row at the width you choose.

The modern lookup: searches any direction, exact match by default, built-in error handling. Replaces VLOOKUP and HLOOKUP.

The modern MATCH: find a value's position with exact match by default, search from the end, and no-sort next-smaller/larger modes.

Returns TRUE only when every condition is TRUE - the strict gatekeeper for multi-condition IF tests.

Feeds each whole column of a range to a LAMBDA and spills one result per column - a totals row, monthly maximums, or per-column verdicts from one formula.

Feeds each whole row of a range to a LAMBDA and spills one result per row - row totals, row maximums, and per-row verdicts from a single formula.

Returns the logical value FALSE; kept for compatibility - the plain literal FALSE is equivalent. In math, FALSE coerces to 0.

Tests a condition and returns one value when it's TRUE and another when it's FALSE - the foundation of every spreadsheet decision.

Returns a formula's result when it works and your fallback when it raises any error - the universal (and sometimes too eager) safety net.

Replaces only the #N/A error with your fallback and lets every other error through - the safer wrapper for VLOOKUP, XLOOKUP, and MATCH.

Checks a flat list of conditions in order and returns the value for the first one that's TRUE - nested IF without the nesting.

Builds your own reusable custom function from formula logic - test it inline, then save it in Name Manager and call it like a native function.

Names values and expressions inside a formula, then reuses them in a final calculation - more readable, and each value computes only once.

Generates a brand-new array of any size, computing each cell from its row and column indexes - multiplication tables, checkerboards, and calendar grids from one formula.

Applies a LAMBDA to every element of an array and spills the transformed array back - pass several same-size arrays to zip them together.

Reverses a logical value: TRUE becomes FALSE and FALSE becomes TRUE. The cleanest way to express 'everything except' conditions.

Returns TRUE when at least one condition is TRUE - the lenient counterpart to AND for any-of-these IF tests.

Folds an array down to a single value by carrying an accumulator through every element - Excel's general-purpose loop.

Walks an array with an accumulator and a LAMBDA, returning every intermediate result - running totals and products in one spilled formula.

Compares one expression against a list of exact values and returns the matching result, with an optional default for everything else.

Returns the logical value TRUE; kept for compatibility - the plain literal TRUE is equivalent. In math, TRUE coerces to 1.

Exclusive or: TRUE when an odd number of arguments are TRUE. With two tests, that means one or the other - not both.

Converts a whole range or spilled array into one text string - concise comma list by default, or exact formula-style array syntax with format 1. The strict mode is Excel's best debugging lens.

Converts full-width (zenkaku) letters, digits, and katakana to half-width (hankaku) - the fix for Japanese imports where '123' is text that will not sum or match.

Converts a number to Thai text spelled out as baht currency - Excel's only built-in number-to-words function. No English version exists; use LAMBDA or VBA for that.

Returns the character for an ANSI code number (1-255). The classic move: CHAR(10) inserts a line break inside a formula.

Deletes non-printable control characters (ASCII 0-31) from text - the invisible junk that imports drag in. Pair with TRIM for a full scrub.

Returns the ANSI code (1-255) of the first character of a text string. The detective tool for unmasking invisible characters that break lookups.

Joins text from cells, strings, and whole ranges into one value - the modern replacement for CONCATENATE, minus a delimiter option.

Joins up to 255 individual text items into one string - the legacy function that works in every Excel version but cannot accept ranges.

Converts half-width (hankaku) characters to full-width (zenkaku) - the opposite of ASC, and the fix for cramped half-width katakana from legacy exports. Named JIS in Japanese Excel.

Converts a number to currency-formatted text like $1,234.57, with accounting parentheses for negatives - made for sentences, not for math.

Compares two text strings character for character, case included - the case-sensitive test that the = operator and every lookup function refuse to be.

Returns the position of text inside text, case-sensitively - the engine behind LEFT/MID/RIGHT extraction combos.

Case-sensitive search that returns the match's position in bytes - DBCS characters count as 2 on Japanese, Chinese, and Korean system locales. In English it works exactly like FIND.

Rounds a number to a set number of decimals and returns it as text with thousands commas - great for labels, but invisible to SUM.

Returns the first characters of a text string - the go-to for prefixes, initials, and (with FIND) the first word.

Returns the first num_bytes bytes of a string - DBCS characters (Japanese, Chinese, Korean) count as 2 bytes on those system locales. In English it works exactly like LEFT.

Counts every character in a string - including the invisible spaces that break lookups - and powers RIGHT+LEN and LEN-SUBSTITUTE tricks.

Counts a string's length in bytes - DBCS characters (Japanese, Chinese, Korean) count as 2 on those system locales. In English it returns exactly what LEN does.

Converts every letter in a text string to lowercase - the standard fix for email addresses, usernames, and URLs typed with random capitalization.

Extracts characters from the middle of a string - any segment, anywhere, and (with FIND) pieces whose position varies row to row.

Extracts a substring by byte position and byte length - DBCS characters count as 2 bytes on Japanese, Chinese, and Korean system locales. In English it works exactly like MID.

Converts text to a number with separators you declare yourself - the fix for European-format imports like 1.234,56 on a US system.

Extracts the furigana reading stored with Japanese text typed via an IME - the key to sorting kanji name lists. Pasted or imported text has no reading, so it returns the text itself.

Capitalizes the first letter of every word and lowercases the rest - the quick fix for name lists, with one famous gotcha: McDonald becomes Mcdonald.

Overwrites a stretch of text by position - start here, replace this many characters. Set num_chars to 0 to insert instead.

Overwrites a byte-defined region of a string - DBCS characters span 2 byte positions on Japanese, Chinese, and Korean system locales. In English it works exactly like REPLACE.

Repeats text a set number of times - the engine behind in-cell bar charts, star ratings, leading zeros, and dot leaders.

Returns the last characters of a text string - last-four digits, file extensions, suffixes, and the leading-zeros padding trick.

Returns the last num_bytes bytes of a string - DBCS characters (Japanese, Chinese, Korean) count as 2 bytes on those system locales. In English it works exactly like RIGHT.

Finds the position of text inside text, ignoring case and supporting ? * wildcards - the everyday 'does it contain' tool.

Case-insensitive, wildcard-friendly search that returns the match position in bytes - DBCS characters count as 2 on Japanese, Chinese, and Korean system locales. In English it works exactly like SEARCH.

Swaps text by value - replace every occurrence of a string, or just the nth one. Case-sensitive, and the engine behind the count-occurrences trick.

Returns the value if it is text, empty text otherwise - a tiny legacy filter that still helps triage imported data.

Converts a number or date to text in any format you specify - currency, dates, leading zeros - perfect for labels and sentences, but the result is text, not a number.

Returns everything after a chosen delimiter - negative instance numbers grab text after the last occurrence.

Returns everything before a chosen delimiter - with instance numbers that can count from the end of the text.

Joins a range of values into one string with a delimiter between each piece, optionally skipping blank cells.

Splits one text value into spilled columns, rows, or a 2-D grid at every delimiter - a live, self-updating Text to Columns.

Strips leading and trailing spaces and collapses internal runs to a single space - the classic fix for lookups broken by invisible spaces.

Returns the character for any Unicode code point - bullets, check marks, arrows, trademark signs, and every script Unicode covers.

Returns the Unicode code point of a string's first character - the forensic tool for unmasking invisible characters that break lookups.

Converts every letter in a text string to capitals - the standard fix for inconsistently typed codes, IDs, and abbreviations.

Converts a number stored as text into a real number - the antidote to imported columns that SUM totals as zero.

Converts any single value to text. Strict mode (format 1) wraps text in quotes, instantly exposing text-numbers and stray spaces that break lookups and sums.

Read KPI value, goal, status, or trend from the model — scorecards without PivotTables.

Validate and display a Data Model member — the reusable building block CUBEVALUE reports are made of.

Read an attribute of a cube member (city, region, manager) from an Analysis Services cube.

Pull the nth item from a sorted cube set — self-updating top-10 leaderboards.

Define a sorted set of Data Model members — the foundation of self-updating top-N reports.

Count the items in a cube set — headline numbers like 'how many active customers' from the Data Model.

Pull an aggregated measure from the Data Model, filtered by any members — the engine behind formula-based pivot reports.