Discover the best Excel formulas, including detailed examples and functions like XLOOKUP, FILTER, UNIQUE, SUMPRODUCT, and COUNTIFS.
Excel formulas are the backbone of efficient data analysis. They allow users to perform calculations easily. This definitive guide provides hundreds of formula examples. Dive into categories like SUM, TEXT, LOOKUP, and conditional formatting to find the perfect formula for your needs.
Jump to category:
COUNT Formulas
This section covers various formulas that help you count cells with numbers, text, errors, or based on other conditions.
| Formula | Related Functions |
|---|---|
| Count cells that contain numbers | COUNT, SUMPRODUCT, ISNUMBER |
| Count cells greater than | COUNTIF, IF |
| Count cells that contain specific text | COUNTIF |
| Count cells that contain errors | ISERROR, SUMPRODUCT |
| Count cells that do not contain errors | SUMPRODUCT, ISERROR, NOT |
| Count cells that begin with specific text | COUNTIF |
| Count cells over n characters | SUMPRODUCT, N, LEN |
| Count cells between two numbers | COUNTBETWEEN, COUNTIFS |
| Count if cells less than a given number | COUNTIF |
| Count if two (or more) criteria match | COUNTIFS, SUMPRODUCT |
| Count matches between two columns | SUMPRODUCT, SUM |
| Count numbers by the nth digit | COUNTDIGITS, COUNTIF, SUMPRODUCT |
| Count rows that contain specific values | SUM, MMULT, TRANSPOSE, COLUMNS |
| Count unique text values in a range | SUM, COUNTIF, UTEXT |
SUM formulas
This chapter provides various formulas for summing cells based on specific conditions, such as the top or bottom values, every nth cell, or only visible cells in a filtered list.
| Formula | Related Functions |
|---|---|
| Calculate the running total (cumulative SUM) | RTOTAL |
| Sum if between two numbers | SUMIFS |
| Sum comma-separated numbers in a cell | SUM, FILTERXML |
| Sum bottom n values in a range | SUM, SMALL |
| Sum cells that contain formulas | SUMPRODUCT |
| Sum first n matching values | SUM, TAKE, FILTER |
| Sum the last or first n columns in a range | SUM, TAKE |
| Sum last n rows | SUM, TAKE |
| Sum last n columns | SUM, TAKE |
| Sum the top n values in a range | LARGE, SEQUENCE |
| Sum every n rows | SUM, OFFSET |
| Sum every nth column | SUM, MOD, FILTER, SEQUENCE |
| Sum every nth row | SUMN |
| Sum multiple rows or columns | SUM, XLOOKUP |
| Sum visible rows in a filtered list | SUBTOTAL |
TEXT and STRING manipulation formulas
Text manipulation is essential for handling and organizing data in Excel. This section provides formulas to help you modify text, extract specific parts, split strings, and format text to fit your needs.
LOOKUP formulas
Use XLOOKUP, VLOOKUP, and related functions to find specific matches, return multiple results, or handle complex searches, such as case-sensitive or last-match lookups.
Dynamic Array
Dynamic array formulas like FILTER, UNIQUE, and VSTACK make filtering data, counting unique values, combining ranges, and extracting specific information easily.
| Formula | Related Functions |
|---|---|
| Basic filter example | FILTER |
| Combine data in multiple worksheets | VSTACK, FILTER, CHOOSECOLS, LET |
| Combine ranges | VSTACK, HSTACK |
| Count unique values and distinct values | UNIQUE |
| Count unique values with criteria | SUM, LEN, UNIQUE, FILTER |
| Extract common values from two lists | FILTER, COUNTIF |
| Extract common values from text strings | FILTER, TEXTJOIN, TEXTSPLIT |
| Extract numbers from a text string | TEXTSPLIT, TOROW, DROP |
| FILTER case-sensitive | FILTER, EXACT |
| Filter data between dates | FILTER |
| Filter every nth row | FILTER, MOD, ROW, SEQUENCE |
| List the most frequently occurring numbers | LET, UNIQUE, SORT, HSTACK |
| Unique values from multiple ranges | UNIQUE, VSTACK |
Conditional Formatting
Conditional formatting formulas in Excel let you highlight cells based on specific conditions, making patterns, trends, and anomalies in your data more visible.
| Formula | Related Functions |
|---|---|
| Color ranking | RANK |
| Compare two columns (matches, differences) | IF, VLOOKUP, INDEX, MATCH, XLOOKUP |
| Create a heat map | IF |
| Find errors in a range | OR, ISBLANK |
| Find duplicates | COUNTIF |
| Highlight every other row | MOD, ROW |
| Multiple conditions | AND |
| Percentile rank formula | PERCENTILE |
| Show protected cells | CELL |
IF
IF formula examples using IF to check for specific text, calculate the median with conditions, and apply Boolean logic.
| Formula | Related Functions |
|---|---|
| IF cell contains specific text | IF, ISNUMBER, SEARCH |
| IF formula to calculate median | IF, MEDIAN |
| IF with boolean logic | IF, SUM, SUMIFS |
Date and Time
| Formula | Related Functions |
|---|---|
| Add years to date | DATE, YEAR, MONTH, DAY |
| Calculate hours between two times | IF, MOD |
| Convert Date to Number [or Number to Date] in Excel | DATE, LEFT, MID, RIGHT |
| Separate date and time in different columns | INT |
COLOR formulas
| Formula | Related Functions |
|---|---|
| Count cells based on color | COUNTBYCOLOR |
| Color ranking formula | RANK |
| IF statement based on cell color | IF, COUNTBYCOLOR |
| Fill cell with RGB color | MYRGB |
Miscellaneous
| Formula | Related Functions |
|---|---|
| Add leading zeros | RIGHT |
| Convert boolean TRUE and FALSE to 1 or 0 | N |
| Calculate the longest winning streak | LET, SCAN, LAMBDA, FILTER, FREQUENCY |
| Calculate probability | PROB |
| Remove special characters | TEXTCLEAN |
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 Formulas
Finally, if you want to learn more about Excel Formulas, we recommend the related articles below: