Excel Formulas

The best Excel formulas include detailed examples and functions like XLOOKUP, FILTER, UNIQUE, SUMPRODUCT, and COUNTIFS.

Take a closer look at the formulas that are powered by user-defined functions. Microsoft Excel formulas help you to count, analyze, filter, convert, summarize, and transform data to boost productivity and speed up your work. You can use our free utility, DataFX, to perform advanced calculations. DataFX supports RegEx functions!

Jump to category:

For Excel newbies, we have good news; you can read our detailed article about the basics.

Let the adventure begin!

COUNT Formulas

FormulaRelated Functions
Count cells that contain numbersCOUNT, SUMPRODUCT, ISNUMBER
Count cells that contain textCOUNTX
Count cells that contain specific textCOUNTTEXT
Count rows that contain specific valuesROWCRT, MMULT, LAMBDA
Count cells that do not contain errorsCOUNTX
Count if cells less than a given numberCOUNTIF
Count cells that begin with specific textCOUNTBEGIN
Count cells over n charactersSUMPRODUCT, N, LEN
Count cells between two numbersCOUNTBETWEEN, COUNTIFS
Count cells not between two numbersNOTBETWEEN
Count numbers by nth digitCOUNTDIGITS, COUNTIF, SUMPRODUCT
Count unique text values in a rangeSUM, COUNTIF, UTEXT

SUM formulas

FormulaRelated Functions
Calculate the running total (cumulative SUM)RTOTAL
Sum if between two numbersSUMBETWEEN
Sum comma-separated numbers in a cellSUMCSV
Sum the last or first n columns in a rangeSUMCOLUMNS
Sum the top n values in a rangeSUMHIGH
Sum every n rowsSUMN_ROWS
Sum every nth columnSUMCOLUMNS
Sum every nth rowSUMN
Sum multiple rows or columnsSUM, XLOOKUP
Sum visible rows in a filtered listSUMVISIBLE
Sum cells that contain formulasSUMPRODUCT, ISFORMULA

TEXT and STRING manipulation formulas

FormulaRelated Functions
Abbreviate names or wordsABBREVIATE
Add leading zeros to a numberZFILL
Convert text to numberVALUE
Extract all numbers from a text stringGETNUMBERS
Extract text before or after a characterLEFT, RIGHT, FIND
Extract the nth word from a text stringTRIM, MID, LEN, SUBSTITUTE
Get cells that contain specific textISNUMBER, SEARCH
Get the first word from some textIFERROR, LEFT, FIND
Get the last name from a nameTEXTRIGHT, SUBSTRING
Get the last word from a text stringGETLASTWORD, TRIM, RIGHT
Get a middle name from a full nameTRIM, MID, LEN
Get the first name from a full nameLEFT, TEXTLEFT
Get the nth number from a text stringGETNUMBERS
Join first and last nameCONCAT
Remove characters from the rightLEFT, LEN, VALUE
Remove the file extension from the file nameLEFT, FIND
Remove the first characterREPLACE
Remove text by matchingSUBSTITUTE
Split dimensions into two partsSUBSTITUTE
Split text and numbersRIGHT, LEN, MID, FIND
Split text at specific charactersLEFT, FIND
Split text with a delimiterTRIM, MID, SUBSTITUTE
Trim text to n wordsLEFT, FIND, SUBSTITUTE

LOOKUP formulas

XLOOKUP changes everything. This section contains modern lookup formulas.

FormulaRelated Functions
Left lookup formulaXLOOKUP
Lookup and return multiple values in one cellMLOOKUP
Lookup across multiple WorksheetsXLOOKUP
Lookup and return the nth valueNVLOOKUP
Lookup row and columnXLOOKUP
Lookup with multiple criteriaXLOOKUP
Lookup and return match textXLOOKUP
Lookup and find the closest matchXLOOKUP, INDEX, MATCH
Lookup and find the last matchXLOOKUP
Lookup values between two numbersXLOOKUP
Lookup and find the first text value in a rangeHLOOKUP, XLOOKUP
Lookup the first or last positive value in a listXLOOKUP
Lookup the name of the nth largest valueLARGE, XLOOKUP
Lookup with logical criteriaXLOOKUP
Lookup formula with boolean logicXLOOKUP
Find the second match in a rangeXLOOKUP
Nested lookup using XLOOKUPXLOOKUP
XLOOKUP returns blank if blankXLOOKUP
Lookup and return all matchesXLOOKUP
Lookup multiple matches using VLOOKUPFILTER

Dynamic Array Formulas

FormulaRelated Functions
Count unique and distinct valuesUNIQUE
Extract common values from two listsCOMPARE, FILTER, COUNTIF
Extract unique values with criteriaUNIQUE, FILTER

IF Formulas

FormulaRelated Functions

DATE and TIME Formulas

FormulaRelated Functions
Add years to dateXDATE
Convert date to number or number to date
How to separate date and timeDATETIME

Formulas and Functions

There are two ways to perform calculations in Excel; using Formulas and Functions. But first, it’s time to clarify their differences.

What is a Formula? Excel Formulas are math statements that can perform calculations and manipulate the values of other cells. Also, it can test conditions and frequently uses logical operators.

Let’s see a simple example: =B1 + B2 + B3 + B4 finds the sum of the cell values between cell B1 and cell B4.

What is a function? Functions are built-in presets, so there are hard-coded Excel formulas. With its help, you can avoid manual data entry, and it has small, user-friendly names.

For example, the =SUM(B1:B4) formula uses the SUM function to sum all cell B1 to B4 values.

Best practices for using Excel Formulas

Toggle between relative and absolute reference. First, select the cell that contains the formula and use the F2 shortcut to enter the cell edit mode. Then, press F4 to switch between the reference types.

Apply autocomplete formula function. First, type an equal sign (=) and type the first character of the function. Excel shows a dynamic list of built-in functions. Next, select the function that you want to use.

Use Function ScreenTips. Type an equal sign and enter the function name. To add arguments, type the opening parenthesis. A screen tip will appear and show your formula’s corresponding argument.

Useful resources to learn Excel Formulas

Finally, if you want to learn more about Excel Formulas, we recommend the related articles below: