Skip to main content
Login
Cart
Exceljet
Quick, clean, and to the point
Training
Videos
Functions
Formulas
Shortcuts
Blog
500 Excel Formula Examples
Detailed formula examples for key functions including VLOOKUP, INDEX, MATCH, RANK, SUMPRODUCT, AVERAGE, SMALL, LARGE, LOOKUP, ROUND, COUNTIFS, SUMIFS, CHOOSE, FIND, SEARCH, DATE, and many more.
Count
Count cells between dates
COUNTIFS
DATE
Count cells between two numbers
COUNTIFS
COUNTIF
Count cells equal to
COUNTIF
Count cells equal to case sensitive
SUMPRODUCT
EXACT
Count cells equal to either x or y
COUNTIF
Count cells equal to one of many things
COUNTIF
SUMPRODUCT
Count cells greater than
COUNTIF
Count cells less than
COUNTIF
Count cells not equal to
COUNTIF
Count cells not equal to many things
COUNTA
COUNTIF
SUMPRODUCT
Count cells not equal to x or y
COUNTIFS
SUMPRODUCT
Count cells over 100 characters
SUMPRODUCT
LEN
N
Count cells that are blank
COUNTBLANK
COUNTA
Count cells that are not blank
COUNTA
COUNTBLANK
Count cells that begin with
COUNTIF
Count cells that contain either x or y
COUNTIF
SUMPRODUCT
ISNUMBER
FIND
Count cells that contain errors
ISERR
SUMPRODUCT
ISERROR
Count cells that contain five characters
COUNTIF
Count cells that contain negative numbers
COUNTIF
Count cells that contain numbers
COUNT
Count cells that contain odd numbers
SUMPRODUCT
MOD
Count cells that contain positive numbers
COUNTIF
Count cells that contain specific text
COUNTIF
SUMPRODUCT
FIND
ISNUMBER
Count cells that contain text
COUNTIF
ISTEXT
SUMPRODUCT
COUNTIFS
Count cells that do not contain
COUNTIF
Count cells that do not contain errors
ISERROR
NOT
SUMPRODUCT
Count cells that end with
COUNTIF
Count dates by day of week
SUMPRODUCT
WEEKDAY
Count if row meets internal criteria
SUMPRODUCT
Count if row meets multiple internal criteria
SUMPRODUCT
Count if two criteria match
COUNTIFS
SUMPRODUCT
Count items in list
COUNTIFS
Count long numbers without COUNTIF
SUMPRODUCT
COUNTIF
Count matches between two columns
SUMPRODUCT
Count multiple criteria with NOT logic
SUMPRODUCT
ISNA
MATCH
Count numbers that begin with
SUMPRODUCT
LEFT
Count occurrences in entire workbook
COUNTIF
SUMPRODUCT
Count paired items in listed combinations
COUNTIFS
CONCAT
Count rows that contain specific values
MMULT
COLUMN
TRANSPOSE
Count rows with at least n matching values
MMULT
COLUMN
TRANSPOSE
Count rows with multiple OR criteria
SUMPRODUCT
Count sold and remaining
COUNTA
Count total matches in two ranges
SUMPRODUCT
COUNTIF
Count unique numeric values in a range
FREQUENCY
SUM
COUNTIF
Count unique numeric values with criteria
FREQUENCY
SUM
Count unique text values in a range
FREQUENCY
MATCH
ROW
SUMPRODUCT
Count unique text values with criteria
FREQUENCY
MATCH
ROW
SUM
Count unique values in a range with COUNTIF
SUMPRODUCT
COUNTIF
Count visible rows in a filtered list
SUBTOTAL
Count visible rows only with criteria
SUBTOTAL
OFFSET
COUNTIFS with multiple criteria and OR logic
COUNTIFS
Group numbers into ranges with COUNTIFS
COUNTIFS
Running count of occurrence in list
COUNTIF
Summary count by month with COUNTIFS
COUNTIFS
EDATE
Summary count of non-blank categories
COUNTIFS
Summary count with COUNTIF
COUNTIF
Summary count with percentage breakdown
COUNTIF
COUNTA
SUMPRODUCT count multiple OR criteria
SUMPRODUCT
Two-way summary count with COUNTIFS
COUNTIFS
Sum
3D sum multiple worksheets
SUM
3D SUMIF for multiple worksheets
SUMPRODUCT
SUMIF
INDIRECT
Calculate running total
SUM
Subtotal by color
SUMIF
Subtotal by invoice number
SUMIF
COUNTIF
Subtotal invoices by age
SUMIF
Sum 2d range with multiple criteria
SUMPRODUCT
Sum bottom n values
SUMPRODUCT
SMALL
SUM
ROW
INDIRECT
Sum bottom n values with criteria
SMALL
SUM
Sum by group
SUMIF
Sum by month
SUMIFS
EOMONTH
Sum by month ignore year
Sum by week
SUMIFS
Sum by week number
SUMIFS
WEEKNUM
Sum by weekday
SUMPRODUCT
WEEKDAY
Sum columns based on adjacent criteria
SUMPRODUCT
Sum entire column
SUM
Sum every n rows
SUM
OFFSET
Sum every nth column
SUMPRODUCT
COLUMN
MOD
Sum if begins with
SUMIF
SUMIFS
Sum if between
SUMIFS
Sum if by year
SUMIFS
DATE
Sum if cell contains text in another cell
SUMIF
SUMIFS
Sum if cells are equal to
SUMIF
SUMIFS
Sum if cells are not equal to
SUMIF
SUMIFS
Sum if cells contain an asterisk
SUMIF
SUMIFS
Sum if cells contain both x and y
SUMIFS
Sum if cells contain either x or y
SUMIFS
SUMPRODUCT
ISNUMBER
FIND
SEARCH
Sum if cells contain specific text
SUMIF
SUMIFS
Sum if date is between
SUMIFS
DATE
Sum if date is greater than
SUMIF
SUMIFS
DATE
Sum if ends with
SUMIF
SUMIFS
Sum if equal to either x or y
SUMIF
SUMPRODUCT
Sum if equal to one of many things
SUMIF
SUMPRODUCT
Sum if greater than
SUMIF
SUMIFS
Sum if less than
SUMIF
SUMIFS
Sum if multiple criteria
SUMIFS
Sum if not blank
SUMIF
SUMIFS
Sum if one criteria multiple columns
SUMPRODUCT
Sum last n columns
INDEX
COLUMN
SUM
Sum sales in last 30 days by ID
SUMIFS
TODAY
Sum top n values
SUMPRODUCT
LARGE
SUM
ROW
INDIRECT
Sum top n values with criteria
SUMPRODUCT
LARGE
Sum visible rows in a filtered list
SUBTOTAL
SUMIFS with multiple criteria and OR logic
SUMIFS
SUM
Average
Average by month
AVERAGEIFS
EOMONTH
Average last 5 values
OFFSET
COUNT
AVERAGE
Average last 5 values in columns
OFFSET
COUNT
AVERAGE
Average numbers
AVERAGE
Average numbers ignore zero
AVERAGEIF
Average response time per month
AVERAGEIFS
EOMONTH
Average the last 3 numeric values
LOOKUP
LARGE
ROW
AVERAGE
Average top 3 scores
LARGE
AVERAGE
Weighted average
SUMPRODUCT
SUM
Min and Max
First in, last out times
MINIFS
MAXIFS
MAX
IF
Large with criteria
LARGE
Larger of two values
MAX
Max of every nth column
MAX
COLUMN
MOD
Maximum if multiple criteria
MAX
IF
MAXIFS
Maximum value
MAX
MAXIFS
Maximum value if
MAX
IF
MAXIFS
Minimum if multiple criteria
MIN
IF
MINIFS
Minimum value
MIN
Minimum value if
MIN
IF
MINIFS
nth largest value
LARGE
nth largest value with criteria
LARGE
nth largest value with duplicates
MAX
IF
nth smallest value
SMALL
nth smallest value with criteria
SMALL
Smaller of two values
MIN
Lookup
Approximate match with multiple criteria
INDEX
MATCH
IF
Basic INDEX MATCH approximate
INDEX
MATCH
Basic INDEX MATCH exact
INDEX
MATCH
Basic Tax Rate calculation with VLOOKUP
VLOOKUP
Break ties with helper column and COUNTIF
SMALL
INDEX
MATCH
Calculate grades with VLOOKUP
VLOOKUP
Calculate shipping cost with VLOOKUP
VLOOKUP
Case sensitive match
MATCH
EXACT
Categorize text with keywords
INDEX
MATCH
ISNUMBER
SEARCH
Count missing values
COUNTIF
SUMPRODUCT
MATCH
ISNA
Dynamic lookup table with INDIRECT
INDIRECT
VLOOKUP
Exact match lookup with INDEX and MATCH
INDEX
MATCH
EXACT
Exact match lookup with SUMPRODUCT
SUMPRODUCT
EXACT
Extract data with helper column
INDEX
MATCH
AND
SUM
Extract multiple matches into separate columns
INDEX
SMALL
IFERROR
ROW
COLUMNS
Extract multiple matches into separate rows
INDEX
SMALL
IFERROR
ROW
ROWS
Faster VLOOKUP with 2 VLOOKUPS
VLOOKUP
NA
Find longest string in column
INDEX
MATCH
MAX
LEN
Find longest string with criteria
INDEX
MATCH
MAX
LEN
Find lowest n values
SMALL
INDEX
MATCH
Find missing values
COUNTIF
VLOOKUP
MATCH
First match in range with wildcard
INDEX
MATCH
Get address of lookup result
INDEX
MATCH
CELL
Get employee information with VLOOKUP
VLOOKUP
Get first match cell contains
INDEX
MATCH
ISNUMBER
SEARCH
Get first non-blank value in a list
INDEX
MATCH
ISBLANK
Get first text value in a list
VLOOKUP
Get first text value with HLOOKUP
HLOOKUP
Get information corresponding to max value
MAX
MATCH
INDEX
Get last match
INDEX
MATCH
MAX
ROW
Get last match cell contains
LOOKUP
SEARCH
Get location of value in 2D array
SUMPRODUCT
ROW
COLUMN
Get nth match
SMALL
IF
MIN
ROW
Get nth match with INDEX / MATCH
INDEX
MATCH
SMALL
Get nth match with VLOOKUP
VLOOKUP
COUNTIF
Group arbitrary text values
VLOOKUP
Group numbers with VLOOKUP
VLOOKUP
If cell contains one of many things
INDEX
MATCH
ISNUMBER
SEARCH
INDEX and MATCH descending order
INDEX
MATCH
INDEX and MATCH with multiple criteria
INDEX
MATCH
Join tables with INDEX and MATCH
INDEX
MATCH
Lookup and sum column
INDEX
MATCH
SUM
Lookup entire column
INDEX
MATCH
Lookup entire row
INDEX
MATCH
Lookup last file version
LOOKUP
ISNUMBER
FIND
Lookup latest price
LOOKUP
Lookup lowest value
INDEX
MATCH
MIN
Lookup up cost for product or service
VLOOKUP
Lookup with variable sheet name
VLOOKUP
INDIRECT
Map inputs to arbitrary values
VLOOKUP
CHOOSE
Map text to numbers
VLOOKUP
Match first error
MATCH
ISERROR
Match next highest value
INDEX
MATCH
Max if criteria match
MAX
IF
Merge tables with VLOOKUP
VLOOKUP
Multi-criteria lookup and transpose
INDEX
MATCH
Multiple chained VLOOKUPs
VLOOKUP
IFERROR
Multiple matches in comma separated list
TEXTJOIN
Next largest match with the MATCH function
INDEX
MATCH
Partial match against numbers with wildcard
MATCH
TEXT
Partial match with VLOOKUP
VLOOKUP
Position of first partial match
MATCH
INDEX
Position of max value in list
MAX
MATCH
Self-contained VLOOKUP
VLOOKUP
Sum lookup values using SUMIF
SUMIF
SUMPRODUCT
Sum range with INDEX
INDEX
SUM
Two-way lookup with INDEX and MATCH
INDEX
MATCH
Two-way lookup with VLOOKUP
VLOOKUP
MATCH
VLOOKUP by date
VLOOKUP
VLOOKUP from another sheet
VLOOKUP
VLOOKUP from another workbook
VLOOKUP
VLOOKUP with 2 lookup tables
VLOOKUP
VLOOKUP with multiple critiera
VLOOKUP
VLOOKUP with numbers and text
VLOOKUP
VLOOKUP with two client rates
VLOOKUP
VLOOKUP without #N/A error
VLOOKUP
IFERROR
IFNA
If
Force negative numbers to zero
MAX
If cell begins with x, y, or z
SUM
COUNTIF
If cell contains
IF
if cell contains this or that
COUNTIF
SUM
If cell equals
IF
If cell is blank
IF
ISBLANK
If cell is greater than
IF
If cell is not blank
IF
ISBLANK
NOT
If cell is this OR that
IF
OR
If cell is x or y and z
IF
OR
AND
If else
IF
If NOT this or that
IF
OR
NOT
If this AND that
IF
AND
If this AND that OR that
IF
AND
OR
Invoice status with nested if
IF
TODAY
Nested IF function example
IF
Tax rate calculation with fixed base
IF
Win loss points calculation
VLOOKUP
IF
Conditional formatting
Highlight 3 smallest values with criteria
SMALL
AND
Conditional formatting dates overlap
SUMPRODUCT
Conditional formatting gantt chart
AND
Conditional formatting gantt chart weekends
WEEKDAY
Conditional formatting highlight target percentage
Find duplicate values in two columns
COUNTIF
AND
Highlight approximate match lookup conditional formatting
LOOKUP
OR
AND
Highlight blank cells
ISBLANK
LEN
Highlight bottom values
SMALL
Highlight cells that begin with
COUNTIF
FIND
IFERROR
Highlight cells that contain
SEARCH
FIND
ISNUMBER
Highlight cells that contain one of many
SEARCH
FIND
ISNUMBER
SUMPRODUCT
Highlight cells that end with
COUNTIF
RIGHT
LEN
EXACT
Highlight cells that equal
EXACT
Highlight column differences
EXACT
NOT
Highlight data by quartile
QUARTILE
Highlight dates between
DATE
AND
Highlight dates greater than
DATE
Highlight dates in same month and year
DATE
TEXT
Highlight dates in the next N days
TODAY
AND
Highlight dates that are weekends
WEEKDAY
OR
Highlight duplicate columns
SUMPRODUCT
COUNTIF
Highlight duplicate rows
COUNTIFS
COUNTIF
SUMPRODUCT
Highlight duplicate values
COUNTIF
Highlight entire rows
Highlight every other row
ISEVEN
ISODD
ROW
MOD
Highlight integers only
MOD
Highlight missing values
COUNTIF
Highlight multiples of specific value
MOD
Highlight numbers that include symbols
ISNUMBER
MID
Highlight row and column intersection exact match
OR
AND
Highlight rows that contain
SEARCH
FIND
Highlight rows with blank cells
COUNTBLANK
Highlight rows with dates between
DATE
AND
Highlight top values
LARGE
Highlight unique values
COUNTIF
Highlight unprotected cells
CELL
Highlight values between
AND
Highlight values greater than
Highlight values not between X and Y
AND
NOT
Shade alternating groups of n rows
ROW
CEILING
ISEVEN
ISODD
Data validation
Data validation allow numbers only
ISNUMBER
Data validation allow text only
ISTEXT
Data validation allow uppercase only
UPPER
EXACT
AND
Data validation allow weekday only
YEAR
TODAY
Data validation contains specific text
FIND
ISNUMBER
Data validation date in next 30 days
AND
TODAY
Data validation date in specific year
YEAR
TODAY
Data validation don't exceed total
SUM
Data validation exists in list
COUNTIF
Data validation must begin with
EXACT
LEFT
COUNTIF
Data validation must not contain
ISNUMBER
SEARCH
SUMPRODUCT
Data validation must not exist in list
COUNTIF
Data validation only dates between
AND
DATE
Data validation require unique number
AND
ISNUMBER
COUNTIF
Data validation unique values only
COUNTIF
Data validation whole percentage only
TRUNC
AND
Data validation with conditional list
IF
Rank
Rank function example
RANK
Rank race results
RANK
Round
Get decimal part of a number
TRUNC
Get integer part of a number
TRUNC
INT
Get number at place value
MOD
Round a number
ROUND
Round a number down
ROUNDDOWN
Round a number down to nearest multiple
FLOOR
Round a number to n significant digits
ROUND
LOG10
INT
ABS
Round a number to nearest multiple
MROUND
Round a number up
ROUNDUP
Round a number up to nearest multiple
CEILING
Round a number up to next half
CEILING
Round a price to end in .99
ROUND
Round by bundle size
CEILING
Round time to nearest 15 minutes
MROUND
Round to nearest 1000
ROUND
Round to nearest 5
MROUND
CEILING
FLOOR
Date and Time
Add business days to date
WORKDAY
NETWORKDAYS
Add days exclude certain days of week
WORKDAY.INTL
Add days to date
DAY
Add decimal hours to time
TIME
MOD
Add decimal minutes to time
TIME
MOD
Add months to date
EDATE
Add workdays no weekends
WORKDAY.INTL
NETWORKDAYS
Add workdays to date custom weekends
WORKDAY.INTL
NETWORKDAYS
Add years to date
DATE
YEAR
MONTH
DAY
Assign points based on late time
IF
VALUE
Basic overtime calculation formula
MIN
Calculate date overlap in days
MAX
MIN
Calculate days remaining
TODAY
Calculate expiration date
EOMONTH
EDATE
Calculate number of hours between two times
IF
MOD
Calculate retirement date
EDATE
EOMONTH
Calculate years between dates
YEARFRAC
INT
Convert date string to date time
DATEVALUE
LEFT
MID
TIMEVALUE
Convert date to Julian format
DATE
YEAR
TEXT
Convert date to month and year
TEXT
Convert date to text
TEXT
Convert decimal hours to Excel time
Convert decimal minutes to Excel time
Convert decimal seconds to Excel time
Convert text date dd/mm/yy to mm/dd/yy
DATE
LEFT
MID
RIGHT
TRIM
Convert text to date
DATE
LEFT
MID
RIGHT
Convert time to time zone
MOD
Count birthdays by month
SUMPRODUCT
MONTH
Count dates in current month
COUNTIFS
EOMONTH
SUMPRODUCT
N
Count day of week between dates
WEEKDAY
ROW
INDIRECT
SUMPRODUCT
Count holidays between two dates
SUMPRODUCT
Count times in a specific range
COUNTIFS
TIME
Create date range from two dates
TEXT
IF
Custom weekday abbreviation
WEEKDAY
CHOOSE
Date is same month
MONTH
Date is same month and year
MONTH
YEAR
Date is workday
WORKDAY
WORKDAY.INTL
Days in month
DAY
EOMONTH
Days until expiration date
Display the current date
TODAY
Display the current date and time
NOW
Extract date from a date and time
INT
TRUNC
Extract time from a date and time
MOD
Get age from birthday
INT
TODAY
YEARFRAC
Get date from day number
DATE
RIGHT
LEFT
Get day from date
DAY
Get day name from date
WEEKDAY
CHOOSE
Get days before a date
TODAY
Get days between dates
TODAY
DAYS
Get days between dates ignoring years
DATEDIF
Get days, hours, and minutes between dates
INT
TEXT
Get days, months, and years between dates
DATEDIF
Get first day of month
DAY
EOMONTH
Get first day of previous month
EOMONTH
Get first Monday before any date
WEEKDAY
Get fiscal quarter from date
MONTH
CHOOSE
Get fiscal year from date
MONTH
YEAR
Get last day of month
EOMONTH
DATE
Get last weekday in month
EOMONTH
WEEKDAY
Get last working day in month
WEEKDAY
EOMONTH
Get month from date
MONTH
DATE
Get month name from date
MONTH
CHOOSE
TEXT
Get months between dates
DATEDIF
Get most recent day of week
MOD
Get next day of week
WEEKDAY
Get nth day of week in month
DAY
WEEKDAY
Get nth day of year
DATE
YEAR
Get percent of year complete
YEARFRAC
YEAR
DATE
Get quarter from date
MONTH
ROUNDUP
Get same date next month
EDATE
Get same date next year
EDATE
Get start date based on target date
WORKDAY
WORKDAY.INTL
Get week number from date
WEEKNUM
ISOWEEKNUM
Get work hours between dates
NETWORKDAYS
NETWORKDAYS.INTL
Get work hours between dates custom schedule
CHOOSE
ROW
INDIRECT
WEEKDAY
SUM
Get workdays between dates
NETWORKDAYS
NETWORKDAYS.INTL
Get year from date
YEAR
Group times into 3 hour buckets
FLOOR
Group times into unequal buckets
VLOOKUP
If Monday, roll back to Friday
WEEKDAY
IF
Join date and text
TEXT
Last updated date stamp
TEXT
List holidays between two dates
TEXTJOIN
IF
Next anniversary date
EDATE
DATEDIF
Next biweekly payday from date
CEILING
Next business day 6 months in future
WORKDAY
WORKDAY.INTL
Next working day
WORKDAY
WORKDAY.INTL
Pad week numbers with zeros
TEXT
WEEKNUM
Random times at specific intervals
RAND
Sum race time splits
SUM
Time difference in hours as decimal value
MOD
Total hours that fall between two times
MIN
MAX
Workdays per month
NETWORKDAYS
NETWORKDAYS.INTL
EOMONTH
Working days left in month
NETWORKDAYS
NETWORKDAYS.INTL
TODAY
Year is a leap year
DATE
YEAR
MONTH
Date series
Series of dates by day
Series of dates by month
DAY
MONTH
YEAR
DATE
EOMONTH
Series of dates by weekends
IF
WEEKDAY
Series of dates by workdays
WORKDAY
NETWORKDAYS.INTL
IF
WEEKDAY
Series of dates by year
DAY
MONTH
YEAR
DATE
Text
Abbreviate names or words
TEXTJOIN
MID
ROW
CODE
LEN
INDIRECT
Add a line break with a formula
CHAR
Add line break based on OS
INFO
CHAR
Cell contains all of many things
SUMPRODUCT
ISNUMBER
SEARCH
COUNTA
Cell contains number
FIND
COUNT
Cell contains one of many things
SUMPRODUCT
ISNUMBER
SEARCH
Cell contains one of many with exclusions
SUMPRODUCT
ISNUMBER
SEARCH
Cell contains some words but not others
COUNT
SEARCH
AND
Cell contains specific text
SEARCH
ISNUMBER
FIND
Cell contains which things
SEARCH
ISNUMBER
FIND
Cell equals one of many things
SUMPRODUCT
Clean and reformat telephone numbers
SUBSTITUTE
Compare two strings
EXACT
Convert numbers to text
TEXT
Convert string to array
MID
ROW
LEN
INDIRECT
Convert text to numbers
VALUE
LEFT
RIGHT
Count line breaks in cell
LEN
SUBSTITUTE
CHAR
ISBLANK
Count specific characters in a cell
LEN
SUBSTITUTE
UPPER
Count specific characters in a range
LEN
SUBSTITUTE
UPPER
SUMPRODUCT
Count specific words in a cell
LEN
SUBSTITUTE
UPPER
Count specific words in a range
LEN
SUBSTITUTE
UPPER
SUMPRODUCT
Count total characters in a cell
LEN
Count total characters in a range
LEN
SUMPRODUCT
Count total words in a cell
LEN
SUBSTITUTE
TRIM
ISBLANK
Count total words in a range
SUMPRODUCT
LEN
SUBSTITUTE
TRIM
Double quotes inside a formula
CHAR
Extract last two words from cell
MID
LEN
SUBSTITUTE
FIND
Extract multiple lines from a cell
TRIM
MID
SUBSTITUTE
REPT
LEN
Extract nth word from text string
TRIM
MID
SUBSTITUTE
REPT
LEN
Extract text between parentheses
MID
SEARCH
Extract word containing specific text
MID
MAX
FIND
SUBSTITUTE
REPT
TRIM
Extract word that begins with specific character
MID
LEN
FIND
SUBSTITUTE
REPT
TRIM
Find nth occurrence of character
SUBSTITUTE
FIND
CHAR
Get first word
LEFT
FIND
ISERROR
Get last line in cell
TRIM
SUBSTITUTE
RIGHT
REPT
CHAR
Get last word
TRIM
SUBSTITUTE
RIGHT
REPT
Join cells with comma
TRIM
SUBSTITUTE
TEXTJOIN
Most frequently occurring text
INDEX
MATCH
MODE
Normalize text
SUBSTITUTE
TRIM
LOWER
Position of 2nd 3rd etc instance of character
SUBSTITUTE
FIND
Remove characters from left
RIGHT
LEN
Remove characters from right
LEFT
LEN
VALUE
Remove file extension from filename
LEFT
FIND
Remove leading and trailing spaces from text
CLEAN
TRIM
SUBSTITUTE
Remove text by matching
SUBSTITUTE
Remove text by position
REPLACE
Remove text by variable position
REPLACE
FIND
Remove unwanted characters
SUBSTITUTE
CHAR
CODE
LEFT
Replace one character with another
SUBSTITUTE
Split dimensions into three parts
LEFT
RIGHT
MID
LEN
SUBSTITUTE
Split dimensions into two parts
SUBSTITUTE
LEFT
RIGHT
FIND
Split numbers from units of measure
MAX
LEFT
RIGHT
ISNUMBER
VALUE
MID
Split text and numbers
FIND
MIN
RIGHT
LEFT
Split text string at specific character
LEFT
RIGHT
LEN
FIND
Split text with delimiter
TRIM
MID
SUBSTITUTE
REPT
LEN
Strip html from text or numbers
MID
LEN
Strip non-numeric characters
TEXTJOIN
MID
ROW
INDIRECT
Strip numeric characters from cell
TEXTJOIN
MID
ROW
INDIRECT
Financial
Annual compound interest schedule
FV
Calculate compound interest
FV
Calculate cumulative loan interest
CUMIPMT
Calculate cumulative loan principal payments
CUMPRINC
Calculate interest for given period
PPMT
Calculate interest rate for loan
RATE
Calculate original loan amount
PV
Calculate payment for a loan
PMT
Calculate payment periods for loan
NPER
Calculate principal for given period
PPMT
Calculate simple interest
Workbook
Dynamic workbook reference
INDIRECT
Dynamic worksheet reference
INDIRECT
Get full workbook name and path
CELL
Get sheet name only
CELL
MID
FIND
Get workbook name and path without sheet
CELL
LEFT
FIND
SUBSTITUTE
Get workbook name only
CELL
MID
FIND
Get workbook path only
CELL
LEFT
FIND
Indirect named range different sheet
INDIRECT
List sheet index numbers
SHEET
Worksheet name exists
ISREF
INDIRECT
Internet
Create email address from name
LEFT
LOWER
Create email address with name and domain
LEFT
LOWER
Get domain from email address
RIGHT
LEN
FIND
Get domain name from URL
LEFT
FIND
Get name from email address
LEFT
FIND
Get top level domain (TLD)
RIGHT
LEN
SUBSTITUTE
FIND
Strip protocol and trailing slash from URL
MID
RIGHT
LEN
FIND
Names
Get first name from name
LEFT
FIND
Get first name from name with comma
RIGHT
LEN
FIND
Get last name from name
RIGHT
LEN
SUBSTITUTE
FIND
Get last name from name with comma
LEN
LEFT
Get middle name from full name
MID
LEN
TRIM
Join first and last name
CONCATENATE
Put names into proper case
PROPER
TRIM
Percentage
Calculate percent variance
ABS
Decrease by percentage
Get amount with percentage
Get original number from percent change
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
Percent sold
Project complete percentage
COUNTA
Range
Address of first cell in range
ADDRESS
ROW
COLUMN
CELL
Address of last cell in range
ADDRESS
ROW
COLUMN
ROWS
COLUMNS
All cells in range are blank
SUMPRODUCT
Dynamic named range with INDEX
INDEX
COUNTA
Dynamic named range with OFFSET
OFFSET
COUNTA
First column number in range
ROWS
MIN
First match between two ranges
INDEX
MATCH
COUNTIF
First row number in range
ROW
MIN
Get address of named range
ADDRESS
ROW
COLUMN
ROWS
COLUMNS
Get relative column numbers in range
COLUMN
Get relative row numbers in range
ROW
Last column number in range
COLUMN
COLUMNS
MIN
Last row in mixed data with blanks
MATCH
Last row in mixed data with no blanks
COUNTA
Last row in numeric data
MATCH
Last row in text data
MATCH
REPT
Last row number in range
ROW
ROWS
MIN
Multiple cells are equal
AND
EXACT
Multiple cells have same value
COUNTIF
Multiple cells have same value case sensitive
EXACT
SUMPRODUCT
COUNTA
Range contains a value not in another range
SUMPRODUCT
MATCH
ISNA
Range contains numbers
SUMPRODUCT
ISNUMBER
Total cells in a range
ROWS
COLUMNS
Total columns in range
COLUMNS
Total rows in range
ROWS
Miscellaneous
Abbreviate state names
VLOOKUP
INDEX
MATCH
Basic attendance tracking formula
COUNTIF
Basic error trapping example
IFERROR
Basic in cell histogram
REPT
CHAR
Build hyperlink with VLOOKUP
HYPERLINK
VLOOKUP
Calculate a ratio from two numbers
GCD
Cap percentage at specific amount
MIN
Cash denomination calculator
SUMPRODUCT
INT
FLOOR
Change negative numbers to positive
ABS
Check register balance
IF
AND
ISBLANK
Convert column letter to number
COLUMN
INDIRECT
Convert column number to letter
ADDRESS
SUBSTITUTE
Copy value from every nth column
OFFSET
COLUMN
Copy value from every nth row
OFFSET
ROW
Count consecutive monthly orders
FREQUENCY
MAX
IF
Count with repeating values
ROUNDUP
COLUMN
ROW
Course completion status summary
COUNTIFS
Course completion summary with criteria
COUNTIFS
Create array of numbers
INDIRECT
ROW
Cube root of number
POWER
Easy bundle pricing with SUMPRODUCT
SUMPRODUCT
Extract unique items from a list
INDEX
MATCH
COUNTIF
LOOKUP
Filter values in array formula
ISNUMBER
MATCH
Fixed value every N months
MOD
COLUMN
Flag first duplicate in a list
COUNTIF
Flip table rows to columns
TRANSPOSE
Formula with locked reference
INDIRECT
Get date associated with last entry
LOOKUP
Get first entry by month and year
INDEX
MATCH
TEXT
Get last entry by month and year
LOOKUP
TEXT
Get pivot table grand total
GETPIVOTDATA
Get pivot table subtotal
GETPIVOTDATA
Get pivot table subtotal grouped date
GETPIVOTDATA
Get value of last non-empty cell
LOOKUP
Hyperlink to first blank cell
HYPERLINK
CELL
INDEX
MATCH
Hyperlink to first match
HYPERLINK
CELL
INDEX
MATCH
Increment a calculation with ROW or COLUMN
ROW
COLUMN
Increment a number in a text string
RIGHT
TEXT
Issue response time calculation
VLOOKUP
Leave a comment in a formula
N
List contains duplicates
SUMPRODUCT
COUNTIF
List most frequently occuring numbers
MODE
ISNUMBER
MATCH
Longest winning streak
FREQUENCY
MAX
IF
Lookup last file revision
SEARCH
ISERROR
MAX
INDEX
IF
ROW
Most frequently occurring number
MODE
Multiplication table formula
Normalize size units to Gigabytes
MATCH
LEFT
RIGHT
nth root of number
POWER
Pad a number with zeros
TEXT
REPT
Random date between two dates
RANDBETWEEN
WORKDAY
Random number between two numbers
RANDBETWEEN
Random number from fixed set of options
CHOOSE
RANDBETWEEN
Random text values
CHOOSE
RANDBETWEEN
Random value from list or table
INDEX
RANDBETWEEN
ROWS
Randomly assign data to groups
RANDBETWEEN
CHOOSE
Randomly assign people to groups
RAND
ROUNDUP
CEILING
Range contains one of many substrings
SUMPRODUCT
COUNTIF
Range contains one of many values
SUMPRODUCT
ISNUMBER
SEARCH
Range contains specific text
COUNTIF
Rank with ordinal suffix
CHOOSE
ABS
MOD
Reverse a list or range
INDEX
COUNTA
ROW
Search entire worksheet for value
COUNTIF
Search multiple worksheets for value
COUNTIF
INDIRECT
Simple currency conversion
VLOOKUP
INDEX
MATCH
Split payment across months
AND
Square root of number
SQRT
POWER
Student class enrollment with table
IF
COUNTIF
Sum every 3 cells
OFFSET
COLUMN
Transpose table without zeros
TRANSPOSE
IF
Validate input with check mark
IF
COUNTIF
Value exists in a range
COUNTIF
MATCH
Value is between two numbers
AND
MAX
MIN
Value is within tolerance
IF
ABS
Volunteer hours requirement calculation
AND
COUNTIF
SUM
Download 200+ Excel Shortcuts
Get over
200 Excel shortcuts for Windows and Mac
in one handy PDF.
Your website is excellent and really helped me accomplish my goal with Excel. -Adam