99 Excel functions
99 of 99 functions
Type at least 2 characters
Logical
- ANDENDifficulty rating 2 of 5Returns TRUE only when every condition you give it is TRUE, and FALSE as soon as one of them is not.
- IFALSDifficulty rating 2 of 5Checks a condition and returns one value when it holds and another value when it does not.
- IFERRORALS.FOUTDifficulty rating 2 of 5Returns the result of a calculation, or a replacement value of your choice when that calculation ends in any error.
- IFNAALS.NBDifficulty rating 2 of 5Returns a fallback of your choice when a formula produces #N/A, and passes every other result through unchanged, including other errors.
- OROFDifficulty rating 2 of 5Returns TRUE when at least one of the conditions you give it is TRUE, and FALSE only when all of them are FALSE.
- SWITCHSCHAKELENDifficulty rating 2 of 5Under reviewCompares one value with a list of possible values and returns the result that belongs to the first exact match, with an optional fallback.
- IFSALS.VOORWAARDENDifficulty rating 3 of 5Tests conditions one after another and returns the value that belongs to the first condition that is TRUE.
- LETLETDifficulty rating 4 of 5Excel 2021 or laterGives a value or an intermediate result a name inside one formula, so you can reuse it by name and Excel calculates it only once.
Lookup & reference
- CHOOSEKIEZENDifficulty rating 2 of 5Under reviewPicks the value at a given position from a list of values you type or point to, counting from 1.
- TRANSPOSETRANSPONERENDifficulty rating 2 of 5Under reviewTurns the rows of a range into columns and the columns into rows, and stays linked to the source.
- HLOOKUPHORIZ.ZOEKENDifficulty rating 3 of 5Under reviewFinds a value in the top row of a table and returns the cell a given number of rows below it in the same column.
- INDEXINDEXDifficulty rating 3 of 5Returns the cell at a given row and column number inside a range, or a whole row or column of it when one number is 0.
- MATCHVERGELIJKENDifficulty rating 3 of 5Tells you where a value sits in a single row or column, as a position number counted from the start of that range.
- VLOOKUPVERT.ZOEKENDifficulty rating 3 of 5Finds a value in the first column of a table and returns the cell a given number of columns to its right in the same row.
- XLOOKUPX.ZOEKENDifficulty rating 3 of 5Excel 2021 or laterFind a value in one list and bring back the matching item from another.
- XMATCHX.VERGELIJKENDifficulty rating 3 of 5Excel 2021 or laterFinds a value in a row or column and returns its position as a number, with options for nearest matches, wildcards, regular expressions and searching from the end.
- INDIRECTINDIRECTDifficulty rating 4 of 5Turns text such as B5 or Sales!B5 into a live reference, so a formula can point wherever a cell tells it to.
- OFFSETVERSCHUIVINGDifficulty rating 4 of 5Returns a reference to the cell or block a set number of rows and columns from a start cell, optionally resized.
Math
- ABSABSDifficulty rating 1 of 5Returns a number without its sign, so a shortfall and an overshoot of the same size become the same distance from zero.
- INTINTEGERDifficulty rating 1 of 5Rounds a number down to the nearest whole number below it, which also strips the time from a date-time value.
- RANDASELECTDifficulty rating 1 of 5Returns a random decimal number from 0 up to, but not including, 1, and draws a new one every time the sheet recalculates.
- SUMSOMDifficulty rating 1 of 5Adds up numbers, cells and ranges, skipping anything in a range that is not a real number.
- TRUNCGEHEELDifficulty rating 1 of 5Cuts a number off after a chosen number of decimals, without rounding, so 7.98 becomes 7 and -7.98 becomes -7.
- MODRESTDifficulty rating 2 of 5Returns what is left over after dividing one number by another, which makes it the tool for anything that repeats in cycles.
- MROUNDAFRONDEN.N.VEELVOUDDifficulty rating 2 of 5Rounds a number to the nearest multiple of a step you choose, such as 0.05 for cash or 15 minutes for timesheets.
- RANDBETWEENASELECTTUSSENDifficulty rating 2 of 5Returns a random whole number between two bounds, both included, and picks a new one every time the sheet recalculates.
- ROUNDAFRONDENDifficulty rating 2 of 5Rounds a number to a chosen number of decimal places, or to tens and hundreds when the count is negative.
- ROUNDDOWNAFRONDEN.NAAR.BENEDENDifficulty rating 2 of 5Cuts a number off at a chosen digit position, toward zero, never adding anything: 7.9 becomes 7 and -7.9 becomes -7.
- ROUNDUPAFRONDEN.NAAR.BOVENDifficulty rating 2 of 5Rounds a number away from zero to a chosen number of digits, so any leftover fraction counts as a full extra unit.
- SUMIFSOM.ALSDifficulty rating 2 of 5Adds the numbers in a range for the rows where one other range meets a single condition.
- SUBTOTALSUBTOTAALDifficulty rating 3 of 5Calculates a total, count, average or other summary over the visible rows of a list, skipping filtered rows and other subtotals.
- SUMIFSSOMMEN.ALSDifficulty rating 3 of 5Adds the numbers in one range for the rows where every condition you set on other ranges is met.
- AGGREGATEAGGREGAATDifficulty rating 4 of 5Runs one of 19 calculations, such as SUM, LARGE or a quartile, while skipping error values, hidden rows or nested subtotals as you choose.
- SUMPRODUCTSOMPRODUCTDifficulty rating 4 of 5Multiplies the matching items of two or more ranges and adds up the products, which also makes it a tool for conditional sums and weighted averages.
Statistical
- AVERAGEGEMIDDELDEDifficulty rating 1 of 5Returns the arithmetic mean: the sum of the numbers divided by how many numbers there are, with blanks and text left out.
- COUNTBLANKAANTAL.LEGE.CELLENDifficulty rating 1 of 5Under reviewCounts the empty cells in one range, including cells that hold empty text from a formula, but not cells with a space or a 0.
- MAXMAXDifficulty rating 1 of 5Returns the largest number among its arguments, ignoring blanks and text in ranges.
- MINMINDifficulty rating 1 of 5Returns the smallest number among its arguments, ignoring blanks and text in ranges.
- COUNTAANTALDifficulty rating 2 of 5Counts how many cells in the arguments hold a number, including dates and zeros, and skips text, empty cells and errors.
- COUNTAAANTALARGDifficulty rating 2 of 5Counts the cells in one or more ranges that hold anything at all: a number, text, a date, TRUE or FALSE, or an error.
- COUNTIFAANTAL.ALSDifficulty rating 2 of 5Counts how many cells in a range meet one condition, such as equal to a value, above a number or starting with certain letters.
- LARGEGROOTSTEDifficulty rating 2 of 5Returns the k-th largest number in a range, so 1 gives the largest, 2 the second largest, and so on.
- COUNTIFSAANTALLEN.ALSDifficulty rating 3 of 5Counts the rows in which every condition you set is met, each condition tested on its own range.
- MAXIFSMAX.ALS.VOORWAARDENDifficulty rating 3 of 5Returns the largest number among the rows that meet one or more conditions, and 0 when no row qualifies.
- MINIFSMIN.ALS.VOORWAARDENDifficulty rating 3 of 5Returns the smallest number among the rows that meet one or more conditions, and 0 when no row qualifies.
Text
- CLEANWISSEN.CONTROLDifficulty rating 1 of 5Removes the 32 non-printing control characters (codes 0 to 31) such as tabs and line breaks from a text.
- EXACTGELIJKDifficulty rating 1 of 5Compares two texts character by character, including case, and returns TRUE only when they are identical.
- LEFTLINKSDifficulty rating 1 of 5Returns a chosen number of characters from the start of a text, counting from the first character.
- LENLENGTEDifficulty rating 1 of 5Counts the characters in a text, including spaces, punctuation and characters you cannot see on screen.
- LOWERKLEINE.LETTERSDifficulty rating 1 of 5Converts every letter in a text to lowercase and leaves digits, spaces and punctuation as they are.
- PROPERBEGINLETTERSDifficulty rating 1 of 5Capitalizes the first letter of a text and every letter that follows a non-letter, and lowercases all other letters.
- REPTHERHALINGDifficulty rating 1 of 5Repeats a text a given number of times and returns the result as one text.
- RIGHTRECHTSDifficulty rating 1 of 5Returns a chosen number of characters from the end of a text, counting back from the last character.
- TRIMSPATIES.WISSENDifficulty rating 1 of 5Removes spaces at the start and end of a text and collapses repeated spaces between words to one.
- UPPERHOOFDLETTERSDifficulty rating 1 of 5Converts every letter in a text to uppercase and leaves digits, spaces and punctuation as they are.
- CONCATTEKST.SAMENVDifficulty rating 2 of 5Joins texts, cells and whole ranges into one text, in the order you list them, with nothing in between.
- FINDVIND.ALLESDifficulty rating 2 of 5Returns the position at which one text first occurs inside another, matching upper and lower case exactly.
- MIDDEELDifficulty rating 2 of 5Returns a piece from inside a text: you give the position where it starts and how many characters it runs.
- REPLACEVERVANGENDifficulty rating 2 of 5Swaps a stretch of characters at a given position and length for new text, whatever those characters are.
- SUBSTITUTESUBSTITUERENDifficulty rating 2 of 5Replaces every occurrence of one piece of text with another, or only the occurrence you number, based on content rather than position.
- TEXTAFTERTEKST.NADifficulty rating 2 of 5Excel 2024 or laterCuts a text at a delimiter and returns everything that comes after it, optionally after the n-th delimiter or counted from the end.
- TEXTBEFORETEKST.VOORDifficulty rating 2 of 5Excel 2024 or laterReturns the part of a text that sits before a chosen delimiter, counting from the start or from the end, without LEFT and FIND.
- TEXTJOINTEKST.COMBINERENDifficulty rating 2 of 5Joins texts and whole ranges into one cell with a separator of your choice between them, optionally skipping empty cells.
- VALUEWAARDEDifficulty rating 2 of 5Turns text that looks like a number, a percentage, a date or a time into a real number Excel can calculate with.
- NUMBERVALUENUMERIEKE.WAARDEDifficulty rating 3 of 5Turns text into a number using the decimal and thousands separators you name, whatever the regional setting of the computer.
- SEARCHVIND.SPECDifficulty rating 3 of 5Returns the position at which one text first occurs inside another, ignoring upper and lower case and allowing the wildcards ? and *.
- TEXTTEKSTDifficulty rating 3 of 5Turns a number or date into text, formatted the way a format code describes, so it can appear inside labels, keys and sentences.
- TEXTSPLITTEKST.SPLITSENDifficulty rating 3 of 5Excel 2024 or laterCuts the text in one cell at every delimiter and spills the pieces into a row, a column or a two-dimensional grid.
- REGEXEXTRACTREGEXEXTRACTDifficulty rating 4 of 5Excel for Microsoft 365Pull the part of a text that fits a pattern, such as an invoice number inside a bank description.
- REGEXREPLACEREGEXREPLACEDifficulty rating 4 of 5Excel for Microsoft 365Replace the parts of a text that match a pattern, and reuse pieces of the match to reorder or reformat what stays.
- REGEXTESTREGEXTESTDifficulty rating 4 of 5Excel for Microsoft 365Check whether a text contains a pattern, or completely follows it, such as a reference scheme, and get TRUE or FALSE.
Date & time
- DAYDAGDifficulty rating 1 of 5Under reviewReturns the day of the month, a number from 1 to 31, from a date.
- DAYSDAGENDifficulty rating 1 of 5Counts the calendar days from a start date to an end date, returning a negative number when the end comes before the start.
- MONTHMAANDDifficulty rating 1 of 5Returns the month of a date as a number from 1 (January) to 12 (December).
- TODAYVANDAAGDifficulty rating 1 of 5Returns the current date, without a time, and updates it every time the workbook recalculates.
- YEARJAARDifficulty rating 1 of 5Returns the year of a date as a four-digit number, from 1900 to 9999.
- DATEDATUMDifficulty rating 2 of 5Builds a real Excel date from a year, a month and a day, rolling over any month or day that falls outside its normal range.
- DATEDIFDATUMVERSCHILDifficulty rating 2 of 5Under reviewCounts the complete years, months or days between two dates, for example the age of an asset or the months a receivable has been open.
- EDATEZELFDE.DAGDifficulty rating 2 of 5Returns the date that falls on the same day of the month a given number of months before or after a start date.
- EOMONTHLAATSTE.DAGDifficulty rating 2 of 5Returns the last day of the month that lies a given number of months before or after a date.
- NETWORKDAYSNETTO.WERKDAGENDifficulty rating 2 of 5Counts the working days, Monday to Friday, from one date to another, both included, minus any holidays you list.
- WEEKDAYWEEKDAGDifficulty rating 2 of 5Returns the day of the week of a date as a number, where return_type decides which day counts as 1.
- WORKDAYWERKDAGDifficulty rating 2 of 5Returns the date that lies a number of working days after, or before, a start date, skipping weekends and any holidays you list.
Dynamic arrays
- SORTSORTERENDifficulty rating 2 of 5Excel 2021 or laterReturns a sorted copy of a range, by any one of its columns, ascending or descending, as a spilled block that re-sorts itself when the data changes.
- CHOOSECOLSKIES.KOLOMMENDifficulty rating 3 of 5Excel 2024 or laterReturns the columns you name by number, in the order you name them, as a block that spills; negative numbers count from the right.
- DROPWEGLATENDifficulty rating 3 of 5Excel 2024 or laterCuts rows or columns off the start or end of a range and returns what is left, as a live spilled block.
- FILTERFILTERDifficulty rating 3 of 5Excel 2021 or laterReturns every row of a range that meets your condition, as a live list that spills into the cells below and updates when the data changes.
- HSTACKHOR.STAPELENDifficulty rating 3 of 5Excel 2024 or laterPlaces ranges or arrays next to each other, left to right, into one spilled block that updates when the sources change.
- RANDARRAYASELECT.MATRIXDifficulty rating 3 of 5Excel 2021 or laterFills a block of any size with random numbers, decimals or whole numbers between bounds you choose, and draws them again on every recalculation.
- SEQUENCEREEKSDifficulty rating 3 of 5Excel 2021 or laterBuilds a block of consecutive numbers with a start value and a step, which spills into as many rows and columns as you ask for.
- SORTBYSORTEREN.OPDifficulty rating 3 of 5Excel 2021 or laterSorts a range by the values of one or more other ranges or arrays, each with its own direction, and spills the sorted copy.
- TAKENEMENDifficulty rating 3 of 5Excel 2024 or laterKeeps only the first or last rows and columns of a range, spilled as a block; a negative count takes from the end.
- TOCOLNAAR.KOLOMDifficulty rating 3 of 5Excel 2024 or laterTurns a block of cells into one single column, optionally leaving out blanks and errors, so other functions can work on a flat list.
- TOROWNAAR.RIJDifficulty rating 3 of 5Excel 2024 or laterTurns a range or array of any shape into one row, reading it row by row or column by column and optionally skipping blanks and errors.
- UNIQUEUNIEKDifficulty rating 3 of 5Excel 2021 or laterReturns each value of a range once, in the order it first appears, as a list that spills and follows the source.
- VSTACKVERT.STAPELENDifficulty rating 3 of 5Excel 2024 or laterPuts ranges or arrays below each other into one spilled block, so separate exports become a single list you can sort, filter or deduplicate.
Financial
- PMTBETDifficulty rating 3 of 5Calculates the fixed payment per period on a loan or lease, given the interest rate per period, the number of periods and the amount borrowed.
Information
- ISBLANKISLEEGDifficulty rating 1 of 5Under reviewTells you whether a cell is truly empty: TRUE if nothing at all is in it, FALSE for anything else, including a space or empty text.
- ISNUMBERISGETALDifficulty rating 2 of 5Answers TRUE when a value is a real number and FALSE for text, errors, logical values and empty cells.
- Debtor age analysis table that ties to the control accountDifficulty rating 2 of 5Combination Total the open amount per age band with one SUMIFS over a band table, add the bands up with SUM and check that the total agrees with the control account.
- All invoices of one supplier in one cell with TEXTJOIN and FILTERDifficulty rating 3 of 5Combination Pick the invoice numbers of one supplier with FILTER and join them into a single line with TEXTJOIN, with a message when the supplier has no invoices.
- Find the customer from a bank descriptionDifficulty rating 3 of 5Combination Cut the invoice number out of a bank statement description with TEXTAFTER and TEXTBEFORE, then look up the customer in the receivables list with XLOOKUP.
- Invoices missing from the ledger with FILTER, ISNA and XMATCHDifficulty rating 3 of 5Combination List the invoices on the receivables specification that are not in the general ledger: XMATCH looks each one up, ISNA turns every miss into TRUE, and FILTER keeps those rows, with a message when nothing is missing.
- Split a pasted list into a clean columnDifficulty rating 3 of 5Combination Split a list of invoice numbers pasted into one cell on the commas, strip the stray spaces with TRIM and stack the pieces into one column with TOCOL, ready for matching.
- The five largest manual entries with TAKE, SORT and FILTERDifficulty rating 3 of 5Combination List the five largest manual (MEM) journal lines for journal entry testing: FILTER keeps the MEM lines, SORT puts the largest amount first and TAKE keeps the first five rows.
- Year-end cut-off test with FILTER and SUMDifficulty rating 3 of 5Combination Flag the sales invoices whose booking date and delivery date fall on different sides of year end and total them: two comparisons with the year-end date give TRUE or FALSE per date, FILTER keeps the invoices where the two differ, and SUM adds them up.
- Differences above the reporting threshold with LET, FILTER, HSTACK and ABSDifficulty rating 4 of 5Combination Reconcile the ledger to the client's specification per customer and list only the differences above the reporting threshold: LET names the differences once, HSTACK pairs them with the customer, FILTER keeps the rows where ABS exceeds the threshold.
- Duplicate invoice numbers across two exportsDifficulty rating 4 of 5Combination Stack two monthly purchase exports into one list and keep each invoice number that occurs more than once, using VSTACK, UNIQUE, XMATCH, ISNA and FILTER.
- Entries posted on weekends and public holidays with FILTER, WEEKDAY and COUNTIFDifficulty rating 4 of 5Combination List the journal entries dated on a Saturday, a Sunday or a Dutch public holiday: WEEKDAY flags the weekends, COUNTIF checks the date against a holiday list, and FILTER keeps the flagged rows.
- Find the customer with a regex lookupDifficulty rating 4 of 5Combination Pull the invoice number out of a bank description with REGEXEXTRACT, turn it into a pattern, and let XLOOKUP match that pattern against an invoice list that is typed in different ways.
- Gap test on invoice numbers with FILTER, SEQUENCE and XMATCHDifficulty rating 4 of 5Combination Find the missing numbers in a series of sales invoices: SEQUENCE builds every number from the lowest to the highest, XMATCH looks each one up in the ledger, ISNA marks the misses and FILTER lists them, with a message when there are no gaps.
- Journal entries where debit does not equal creditDifficulty rating 4 of 5Combination List the entries whose debit and credit totals differ: take the unique entry numbers, total debit and credit per entry with SUMIFS, and keep the unequal ones with FILTER over HSTACK, all named in one LET.
- Mini trial balance from journal lines with HSTACK, UNIQUE and SUMIFSDifficulty rating 4 of 5Combination Build a list of accounts with their net postings from raw journal lines: UNIQUE gives the accounts, SUMIFS totals each one, HSTACK puts both side by side and SORT orders them.
- Random sample of journal lines with TAKE, SORTBY and RANDARRAYDifficulty rating 4 of 5Combination Select n journal lines at random, never the same line twice: RANDARRAY gives every line a random key, SORTBY puts the lines in that order and TAKE keeps the first n.
- Receivables aging total per 30-day bandDifficulty rating 4 of 5Combination Turn every due date into a band with XLOOKUP and match_mode -1 on a band table, then let SUM add up the open amounts of one band.
- Revenue per month for twelve months in one formulaDifficulty rating 4 of 5Combination Total revenue for each of twelve months with one SUMIFS: SEQUENCE counts the months, EDATE turns each count into the first day of a month, and those dates become the criteria.
- Round-sum journal entries with FILTER, MOD, ROUND and ABSDifficulty rating 4 of 5Combination Flag ledger lines whose amount is an exact multiple of 1,000 (and above a minimum): ABS removes the sign, ROUND clears floating-point noise, MOD tests the multiple and FILTER lists the lines.
- Systematic sample of invoices with a fixed startDifficulty rating 4 of 5Combination Select every k-th invoice from a population: the interval comes from ROUNDDOWN and ROWS, SEQUENCE counts the picks from a fixed start typed in a cell, and INDEX lists the selected rows.
- Two-way lookup with one XLOOKUP and TOCOLDifficulty rating 4 of 5Combination Find the value where a row label and a column header meet, by turning both conditions into one grid of 0s and 1s and flattening it with TOCOL.
- Cell references: relative, absolute and mixedExplainer A cell reference is the address a formula reads. Dollar signs decide whether it moves when you copy the formula, and getting that right is what lets one formula fill a whole column or table.
- CriteriaExplainer A criterion is the condition a cell must meet to be counted, added up or averaged: how to write exact, comparison, date and blank criteria, and how to combine several.
- Dates and times as serial numbersExplainer Excel stores a date as a count of days and a time as a fraction of a day, so you can subtract, add and compare them. Learn how to count days, repair text dates and add up hours.
- Error valuesExplainer An error value is a result that starts with # and names what went wrong, such as #N/A or #DIV/0!. It spreads through formulas, and hiding it can hide a gap in your audit evidence.
- Named ranges and structured referencesExplainer A name or a table column stands in for a cell address, so formulas read in words and, with tables, stay complete when rows are added.
- OperatorsExplainer Operators are the symbols in a formula that calculate, compare, join text or combine references. Excel applies them in a fixed order, which explains many results that look wrong.
- Regular expressionsExplainer A regular expression is a compact text pattern that describes the shape of a text, such as an invoice number or an IBAN, so that Excel can test, extract or replace it.
- Spill and dynamic arraysExplainer A spill is what happens when one formula returns several values: they flow into the cells below and beside it. See which Excel does this, how to refer to the result with #, and what #SPILL! means.
- WildcardsExplainer Wildcards are characters that stand in for other characters, so a criterion or lookup value can match part of a text instead of the whole text.
No function matches that search.