Home / Excel Tools
Excel Tools — Function Playground
28 Excel functions you can try live — including the ones most people never discover. Pick a function, edit the example, see the result, and copy the formula into your own spreadsheet.
XLOOKUP ⭐ Hidden gem
Find a value in one column and return a value from another — the modern replacement for VLOOKUP. No column counting, and a built-in message when nothing matches.
VLOOKUP
The classic lookup: find a value in the first column of a table and return a value from a column to its right. The 4th argument FALSE means exact match.
XMATCH ⭐ Hidden gem
Returns the position of a value in a list — the modern replacement for MATCH. It is what powers INDEX + MATCH lookups.
INDEX + MATCH
The flexible classic: MATCH finds the row, INDEX returns the value from any column — including columns to the left, which VLOOKUP cannot do.
SUMIF
Add up only the numbers that meet one condition.
SUMIFS
Like SUMIF, but every condition must match — category AND region here.
COUNTIF
Count how many numbers in a list meet a condition. Scores here: 45, 78, 92, 61, 88.
IF
One condition, two outcomes: pass at 50 or above, fail below it.
IFS
Several conditions in order — the first true one wins. No nested IFs needed for grade bands.
CONCAT
Joins text with no separator — the modern replacement for CONCATENATE. Note there is no space: for separators, use TEXTJOIN below.
TEXTJOIN
Joins a list with a separator of your choice, skipping empty cells (that is the TRUE argument).
TEXTSPLIT ⭐ Hidden gem
Splits one cell into many at a delimiter — no more Text to Columns wizard. One of the most useful functions added to Excel in years.
TEXTBEFORE ⭐ Hidden gem
Everything before the first delimiter — the clean way to grab a first name, a domain, or a code prefix.
TEXTAFTER ⭐ Hidden gem
Everything after the first delimiter — the partner of TEXTBEFORE.
UNIQUE ⭐ Hidden gem
Removes duplicates from a list in one step — the formula version of Remove Duplicates, and it stays live when the list changes.
FILTER ⭐ Hidden gem
Keeps only the rows that meet a condition — a live, formula-driven filter that updates itself. Numbers here: 12, 45, 8, 90, 33.
SORT ⭐ Hidden gem
Sorts a list with a formula, leaving the original untouched. The last 1 means ascending; use -1 for descending.
SEQUENCE ⭐ Hidden gem
Generates a grid of numbers from rows, columns, a start and a step — perfect for numbering, calendars and test data.
DROP ⭐ Hidden gem
Skips the first N items of a list — great for dropping headers or the first page of results.
TAKE ⭐ Hidden gem
Keeps only the first N items — the formula version of a top-N list.
CHOOSECOLS ⭐ Hidden gem
Picks columns out of a table by number, in any order — reorder or slim down a table without touching it.
CHOOSEROWS ⭐ Hidden gem
Picks rows out of a table by number — the row version of CHOOSECOLS.
TOCOL ⭐ Hidden gem
Flattens a grid into a single column, reading across each row. Rows are separated by semicolons here.
WRAPROWS ⭐ Hidden gem
Wraps a flat list into rows of N — the fastest way to turn a column into a table.
HSTACK ⭐ Hidden gem
Places two lists side by side, row by row.
VSTACK ⭐ Hidden gem
Stacks two lists on top of each other — the formula way to combine ranges.
LET ⭐ Hidden gem
Names values inside a formula so you write them once and reuse them — shorter formulas, fewer mistakes, and the calculation runs once instead of repeating.
LAMBDA ⭐ Hidden gem
Defines your own reusable function. In real Excel you save it in the Name Manager and call it by name anywhere — here is the idea, live: a function that doubles its input.
Which Excel do I need?
VLOOKUP, SUMIF, COUNTIF, IF and CONCAT work in almost every Excel version. TEXTJOIN and IFS need Excel 2019 or later. The ⭐ functions are newer: XLOOKUP, XMATCH, UNIQUE, FILTER, SORT, SEQUENCE and LET work in Microsoft 365 and Excel 2021, while TEXTSPLIT, TEXTBEFORE, TEXTAFTER, DROP, TAKE, CHOOSECOLS, CHOOSEROWS, TOCOL, WRAPROWS, HSTACK, VSTACK and LAMBDA are Microsoft 365 only. If a function shows #NAME? in your Excel, your version does not have it yet.
How to use a formula from here
- Pick a function and edit the example values until the result looks like your case.
- Press Copy formula, then paste it into your spreadsheet.
- Replace the example ranges (like A2:A4) with your own data ranges.