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.
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.
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 |
| Extract date from text string | LEFT, MID, FIND, TEXTSPLIT |
| Separate date and time in different columns | INT |
Financial
| Formula | Related Functions |
|---|---|
| Annual compound interest schedule | FV, SEQUENCE |
| Calculate cumulative loan interest | CUMIPMT |
| Calculate cumulative loan principal payments | CUMPRINC |
| Calculate interest rate for loan | RATE |
| Calculate original loan amount | PV |
| Calculate payment for a loan | PMT |
| Calculate payment periods for loan | NPER |
| Effective annual interest rate | EFFECT |
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: