Excel Formulas

Find the formula you need in our list of ready-to-use examples. Click a formula link for a full description. Related functions appear to the right and are also links.

Count

FormulaRelated Functions
Count cells between dates
Count cells between two numbers
Count cells equal to case sensitive
Count cells equal to either x or y
Count cells equal to one of many things
Count cells greater than
Count cells less than
Count cells not equal to
Count cells not equal to x or y
Count cells that are blank
Count cells that are not blank
Count cells that begin with
Count cells that contain either x or y
Count cells that contain errors
Count cells that contain five characters
Count cells that contain negative numbers
Count cells that contain numbers
Count cells that contain odd numbers
Count cells that contain positive numbers
Count cells that contain specific text
Count cells that contain text
Count cells that do not contain
Count cells that do not contain errors
Count cells that end with
Count if row meets internal criteria
Count if row meets multiple internal criteria
Count if two criteria match
Count matches between two columns
Count sold and remaining
Count total matches in two ranges

Sum

FormulaRelated Functions
3D SUMIF for multiple worksheets
Calculate running total
Subtotal by color
Subtotal invoices by age
Sum bottom n values
Sum by group
Sum by month
Sum columns based on adjacent criteria
Sum entire column
Sum every nth column
Sum if begins with
Sum if between
Sum if by year
Sum if cell contains text in another cell
Sum if cells are equal to
Sum if cells are not equal to
Sum if cells contain an asterisk
Sum if cells contain both x and y
Sum if cells contain either x or y
Sum if cells contain specific text
Sum if date is between
Sum if date is greater than
Sum if ends with
Sum if equal to either x or y
Sum if greater than
Sum if less than
Sum if multiple criteria
Sum if not blank
Sum top n values
Sum visible rows in a filtered list

Average

FormulaRelated Functions
Average numbers
Average numbers ignore zero
Average top 3 scores
Weighted average

Min and Max

FormulaRelated Functions
Maximum if multiple criteria
Maximum value
Maximum value if
Minimum if multiple criteria
Minimum value
Minimum value if
nth largest value
nth largest value with criteria
nth smallest value
nth smallest value with criteria

Conditional Formatting

FormulaRelated Functions
Highlight blank cells
Highlight cells that begin with
Highlight cells that contain
Highlight cells that end with
Highlight cells that equal
Highlight column differences
Highlight dates between
Highlight dates greater than
Highlight dates in same month and year
Highlight dates in the next N days
Highlight dates that are weekends
Highlight duplicate rows
Highlight duplicate values
Highlight entire rows
Highlight every other row
Highlight integers only
Highlight rows that contain
Highlight rows with dates between
Highlight unique values
Highlight values between
Highlight values greater than
Highlight values not between X and Y
Highlight values that don't exist

Lookup

FormulaRelated Functions
Approximate match with multiple criteria
Basic INDEX MATCH approximate
Basic INDEX MATCH exact
Calculate grades with VLOOKUP
Calculate shipping cost with VLOOKUP
Dynamic lookup table with INDIRECT
Exact match lookup with INDEX and MATCH
Exact match lookup with SUMPRODUCT
Extract multiple matches into separate columns
Faster VLOOKUP with 2 VLOOKUPS
Find missing values
Get address of lookup result
Get employee information with VLOOKUP
Get first match cell contains
Get first non-blank value in a list
Get first partial match in a range
Get first text value in a list
Get nth match with INDEX / MATCH
Get nth match with VLOOKUP
Group arbitrary text values
Group numbers with VLOOKUP
Lookup up cost for product or service
Map inputs to arbitrary values
Map text to numbers
Match first error
Max if criteria match
Partial match against numbers with wildcard
Partial match with VLOOKUP
Position of max value in list
Related info for max value in range

If

FormulaRelated Functions
If cell begins with x, y, or z
If cell contains
If cell equals
If cell is blank
If cell is greater than
If cell is not blank
If cell is this OR that
If cell is x or y and z
If else
If NOT this or that
If this AND that
Nested IF function example

Round

FormulaRelated Functions
Get decimal part of a number
Get integer part of a number
Round a number
Round a number down
Round a number down to nearest multiple
Round a number to n significant digits
Round a number to nearest multiple
Round a number up
Round a number up to nearest multiple
Round a number up to next half
Round a price to end in .99
Round time to nearest 15 minutes
Round to nearest 1000
Round to nearest 5

Date And Time

FormulaRelated Functions
Add business days to date
Add workdays to date custom weekends
Calculate elapsed work time
Calculate years between dates
Convert date string to date time
Convert date to Julian format
Convert date to text
Convert hours to time
Convert minutes to time
Convert seconds to time
Convert text to date
Convert time to time zone
Count day of week between dates
Custom weekday abbreviation
Date is same month
Date is same month and year
Date is workday
Days in month
Display the current date
Display the current date and time
Extract date from a date and time
Extract time from a date and time
Get age from birthday
Get day from date
Get day name from date
Get days before a date
Get days between dates
Get days between dates ignoring years
Get first day of month
Get first day of previous month

Date Series

FormulaRelated Functions
Series of dates by day
Series of dates by month
Series of dates by weekends
Series of dates by workdays
Series of dates by year

Text

FormulaRelated Functions
Add a line break with a formula
Add line break based on OS
Cell contains all of many things
Cell contains any numbers
Cell contains one of many things
Cell contains some words but not others
Cell contains specific text
Cell contains which things
Cell equals one of many things
Clean and reformat telephone numbers
Compare two strings
Count line breaks in cell
Count specific characters in a cell
Count specific characters in a range
Count specific words in a cell
Count specific words in a range
Count total characters in a cell
Count total characters in a range
Count total words in a cell
Count total words in a range
Double quotes inside a formula
Extract nth word from text string
Get first word
Get last word
Position of 2nd 3rd etc instance of character
Remove last n characters from text
Remove leading and trailing spaces from text
Remove text by matching
Remove text by position
Remove text by variable position

Finalcial

FormulaRelated Functions
Calculate compound interest
Calculate simple interest

Workbook

FormulaRelated Functions
Get full workbook name and path
Get sheet name only
Get workbook name and path without sheet
Get workbook name only
Get workbook path only

Internet

FormulaRelated Functions
Get domain from email address
Get domain name from URL
Get name from email address
Get top level domain (TLD)

Names

FormulaRelated Functions
Get first name from Last, First
Get first name from name
Get last name from Last, First
Get last name from name
Get middle name from full name
Join first and last name
Put names into proper case

Percentage

FormulaRelated Functions
Calculate percent variance
Decrease by percentage
Get amount with percentage
Get original price from percentage discount
Get percent change
Get percentage discount
Get percentage of total
Get profit margin percentage
Get total from percentage
Increase by percentage
Percent of goal
Percent of students absent

Range

FormulaRelated Functions
Address of first cell in range
Address of last cell in range
All cells in range are blank
First column number in range
First row number in range
Get relative column numbers in range
Get relative row numbers in range
Last column number in range
Last row number in range
Range contains a value not in another range
Range contains numbers
Total cells in a range
Total columns in range
Total rows in range

Miscellaneous

FormulaRelated Functions
Build hyperlink with VLOOKUP
Calculate a ratio from two numbers
Change negative numbers to positive
Extract unique items from a list
Flag first duplicate in a list
Flip table rows to columns
Get value of last non-empty cell
Increment a calculation with ROW or COLUMN
List contains duplicates
Longest winning streak
Mixed reference for multiplication table
Pad a number with zeros
Random date between two dates
Random number between two numbers
Random text values
Randomly assign data to groups
Range contains one of many substrings
Range contains one of many values
Range contains specific text
Range contains specific value
Reverse a list or range
Transpose table without zeros