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.
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
| Formula | Related Functions |
|---|---|
| Count cells that contain numbers | COUNT, SUMPRODUCT, ISNUMBER |
| Count cells that contain text | COUNTX |
| Count cells that contain specific text | COUNTTEXT |
| Count rows that contain specific values | ROWCRT, MMULT, LAMBDA |
| Count cells that do not contain errors | COUNTX |
| Count if cells less than a given number | COUNTIF |
| Count cells that begin with specific text | COUNTBEGIN |
| Count cells over n characters | SUMPRODUCT, N, LEN |
| Count cells between two numbers | COUNTBETWEEN, COUNTIFS |
| Count cells not between two numbers | NOTBETWEEN |
| Count numbers by nth digit | COUNTDIGITS, COUNTIF, SUMPRODUCT |
| Count unique text values in a range | SUM, COUNTIF, UTEXT |
SUM formulas
| Formula | Related Functions |
|---|---|
| Calculate the running total (cumulative SUM) | RTOTAL |
| Sum if between two numbers | SUMBETWEEN |
| Sum comma-separated numbers in a cell | SUMCSV |
| Sum the last or first n columns in a range | SUMCOLUMNS |
| Sum the top n values in a range | SUMHIGH |
| Sum every n rows | SUMN_ROWS |
| Sum every nth column | SUMCOLUMNS |
| Sum every nth row | SUMN |
| Sum multiple rows or columns | SUM, XLOOKUP |
| Sum visible rows in a filtered list | SUMVISIBLE |
| Sum cells that contain formulas | SUMPRODUCT, ISFORMULA |
TEXT and STRING manipulation formulas
LOOKUP formulas
XLOOKUP changes everything. This section contains modern lookup formulas.
| Formula | Related Functions |
|---|---|
| Left lookup formula | XLOOKUP |
| Lookup and return multiple values in one cell | MLOOKUP |
| Lookup across multiple Worksheets | XLOOKUP |
| Lookup and return the nth value | NVLOOKUP |
| Lookup row and column | XLOOKUP |
| Lookup with multiple criteria | XLOOKUP |
| Lookup and return match text | XLOOKUP |
| Lookup and find the closest match | XLOOKUP, INDEX, MATCH |
| Lookup and find the last match | XLOOKUP |
| Lookup values between two numbers | XLOOKUP |
| Lookup and find the first text value in a range | HLOOKUP, XLOOKUP |
| Lookup the first or last positive value in a list | XLOOKUP |
| Lookup the name of the nth largest value | LARGE, XLOOKUP |
| Lookup with logical criteria | XLOOKUP |
| Lookup formula with boolean logic | XLOOKUP |
| Find the second match in a range | XLOOKUP |
| Nested lookup using XLOOKUP | XLOOKUP |
| XLOOKUP returns blank if blank | XLOOKUP |
| Lookup and return all matches | XLOOKUP |
| Lookup multiple matches using VLOOKUP | FILTER |
Dynamic Array Formulas
| Formula | Related Functions |
|---|---|
| Count unique and distinct values | UNIQUE |
| Extract common values from two lists | COMPARE, FILTER, COUNTIF |
| Extract unique values with criteria | UNIQUE, FILTER |
| Get column totals | TOTALCOL, SEQUENCE, MMULT |
| Get row totals | TOTALROW, SEQUENCE, MMULT |
IF Formulas
| Formula | Related Functions |
|---|---|
| MEDIAN IF Formula | MEDIANIF |
DATE and TIME Formulas
| Formula | Related Functions |
|---|---|
| Add years to date | XDATE |
| Convert date to number or number to date |
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: