Count between dates by age rangeCOUNTIFS FIND LEFT RIGHT SUMPRODUCT TE

XTBEFORE

Count birthdays by year SUMPRODUCT YEAR COUNTIFS BYROW UNIQU

E SORT LET
Count cells between dates COUNTIFS SUMPRODUCT
Count cells between two numbers COUNTIFS SUMPRODUCT
Count cells equal to COUNTIF SUMPRODUCT
Count cells equal to case sensitive SUMPRODUCT EXACT

Count cells equal to one of many things

COUNTIF SUMPRODUCT ISNUMBER MATCH

Count cells equal to this or that COUNTIF SUM SUMPRODUCT

Count cells greater than COUNTIF COUNTIFS
Count cells less than COUNTIF COUNTIFS
Count cells not between two numbers
COUNTIF SUMPRODUCT
Count cells not equal to COUNTIF COUNTIFS
Count cells not equal to many things
MATCH ISNA SUMPRODUCT COUNTA COUNTIF
Count cells not equal to x or y COUNTIFS SUMPRODUCT
Count cells over n characters SUMPRODUCT LEN N
Count cells that are blank COUNTBLANK COUNTA COUNTIF SUMPRODUC
T
Count cells that are not blank COUNTA COUNTBLANK COUNTIFS SUMPRODU
CT
Count cells that begin with COUNTIF SUMPRODUCT LEFT LEN EXACT

Count cells that contain case sensitive

SUMPRODUCT ISNUMBER FIND

Count cells that contain either x orSUMPRODUCT

y ISNUMBER FIND COUNTIF

Count cells that contain errors SUMPRODUCT ISERROR ISERR ERROR.TYPE IF

ERROR

Count cells that contain n characters

COUNTIF SUMPRODUCT LEN REPT

Count cells that contain negative numbers

COUNTIF SUMPRODUCT

Count cells that contain numbers COUNT SUMPRODUCT ISNUMBER

Count cells that contain odd numbers

SUMPRODUCT ISODD MOD ISEVEN

Count cells that contain positive numbers

COUNTIF SUMPRODUCT
 COUNTIF SUMPRODUCT SEARCH FIND ISNUMB
Count cells that contain specific text
ER

Count cells that contain text COUNTIF COUNTIFS SUMPRODUCT ISTEXT NOT

Count cells that do not contain COUNTIF SUMPRODUCT SEARCH FIND NOT

Count cells that do not contain errors

ISERROR NOT SUMPRODUCT ISERR COUNTIF

ISNUMBER
Count cells that do not contain many strings SEARCH MMULT TRANSPOSE REDU
CE
Count cells that end with COUNTIF
Count columns that contain specific values COLUMN TRANSPOSE BYCOL LAMBDA
MMULT

Count dates by day of week SUMPRODUCT WEEKDAY MATCH

Count dates in given year YEAR SUMPRODUCT
Count if row meets internal criteriaSUMPRODUCT

Count if row meets multiple criteriaSUMPRODUCT

Count if two criteria match COUNTIFS SUMPRODUCT
Count items in list COUNTIFS
Count long numbers SUMPRODUCT COUNTIF
Count matches between two columns
SUMPRODUCT

Count matching values in matchingSUMPRODUCT

columns ISNUMBER MATCH SEARCH

Count missing values COUNTIF SUM SUMPRODUCT MATCH ISNA

Count non-blank cells by category COUNTIFS

Count not equal to multiple criteriaSUMPRODUCT ISNA MATCH COUNTIFS

Count numbers by nth digit SUMPRODUCT MID
Count numbers by range COUNTIFS FREQUENCY DROP

Count numbers that begin with SUMPRODUCT LEFT LEN COUNTIF

Count numbers with leading zeros SUMPRODUCT COUNTIF SUMIF

Count occurrences in entire workbook

COUNTIF SUMPRODUCT VSTACK
Count or sum variance SUMPRODUCT SUM ABS
Count or sum whole numbers onlyMOD SUMPRODUCT
Count paired items in listed combinations
COUNTIFS CONCAT

Count rows that contain specific values

MMULT COLUMN TRANSPOSE BYROW LAMBDA

Count rows with at least n matching values COLUMN TRANSPOSE

MMULT

Count rows with multiple OR criteria

SUMPRODUCT
Count rows with OR logic SUMPRODUCT
Count sold and remaining COUNTA COUNTIF

Count total matches in two rangesSUMPRODUCT COUNTIF MATCH ISNUMBER CO

UNT

Count unique dates UNIQUE COUNT COUNTIF SUMPRODUCT

Count unique numeric values in a range

FREQUENCY SUM COUNTIF

Count unique numeric values with FREQUENCY

criteria SUM

Count unique text values in a rangeFREQUENCY 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 with criteria SUBTOTAL OFFSET SUMPRODUCT INDEX

COUNTIF with non-contiguous range

COUNTIF INDIRECT VSTACK

COUNTIFS with multiple criteria and

COUNTIFS
OR logic
Histogram with FREQUENCY FREQUENCY
Running count of occurrence in listCOUNTIF IF

Summary count by month with COUNTIFS

COUNTIFS EDATE
Summary count with COUNTIF COUNTIF
Summary count with percentage breakdown
COUNTIF COUNTA

SUMPRODUCT count multiple OR criteria

SUMPRODUCT

Two-way summary count COUNTIFS LET UNIQUE HSTACK VSTACK

Calculate running total SUM SCAN
Count cells that contain formulas SUMPRODUCT ISFORMULA
NOT
Subtotal by color SUMIF COUNTIF
Subtotal by invoice number SUMIF COUNTIF
Subtotal invoices by age SUMIF COUNTIFS
Sum across multiple worksheets SUM

SUMPRODUCT SUMIF INDI

Sum across multiple worksheets with criteria
RECT
Sum and ignore errors SUM SUMIF AGGREGATE IF
ERROR

Sum bottom n values SUMPRODUCT SMALL SUM

ROW INDIRECT

Sum bottom n values with criteria SMALL SUM FILTER SEQUE

NCE
Sum by group SUMIF SUMIFS IF
Sum by month SUMIFS EDATE SUMPRODU
CT TEXT
Sum by month ignore year SUMPRODUCT MONTH
Sum by month in columns SUMIFS EOMONTH
SUMIFS ROUNDUP MONTH
Sum by quarter SUMPRODUCT LET LAMBD
A BYROW

SUMIFS LET LAMBDA UNIQ

Sum by week UE WEEKDAY BYROW HST
ACK VSTACK

SUMIFS WEEKNUM LAMBD

Sum by week number A LET BYROW UNIQUE VST
ACK HSTACK

Sum by weekday SUMPRODUCT TEXT WEEK

DAY

SUMPRODUCT YEAR SUMIF

Sum by year S DATE LET BYROW UNIQU
E VSTACK HSTACK

Sum columns based on adjacent criteria

SUMPRODUCT
Sum entire column SUM
Sum entire row SUM
Sum every n rows SUM OFFSET

Sum every nth column FILTER SEQUENCE SUMPR

ODUCT COLUMN MOD
Sum every nth row FILTER SEQUENCE MOD SU
M SUMPRODUCT ROW
Sum first n matching values FILTER TAKE SUM
Sum first n rows TAKE SUM OFFSET
Sum formulas only SUMPRODUCT ISFORMULA
NOT
Sum if begins with SUMIF SUMIFS
Sum if between SUMIFS
Sum if case-sensitive SUMPRODUCT EXACT

Sum if cell contains text in another cellSUMIF SUMIFS SUMPRODU

CT FIND ISNUMBER
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 FIND FILTE
R
Sum if cells contain both x and y SUMIFS

Sum if cells contain either x or y SUMIFS SUMPRODUCT ISN

UMBER FIND SEARCH

Sum if cells contain specific text SUMIF SUMIFS SUMPRODU

CT FIND ISNUMBER
Sum if date is between SUMIFS DATE
Sum if date is greater than SUMIFS SUMIF DATE
Sum if ends with SUMIF SUMIFS
Sum if greater than SUMIF SUMIFS
Sum if less than SUMIF SUMIFS
Sum if multiple columns SUMPRODUCT SUMIFS
Sum if multiple criteria SUMIFS
Sum if not blank SUMIFS SUMPRODUCT FIL
TER SUM

Sum if one of many things SUMIFS SUMPRODUCT ISN

UMBER MATCH FILTER

Sum if x or y SUMIF SUMPRODUCT ISNU

MBER MATCH
Sum last 30 days SUMIFS TODAY SUMPRODU
CT FILTER
Sum last n columns TAKE OFFSET COLUMNS IN
DEX SUM
Sum last n rows TAKE SUM OFFSET INDEX

Sum matching columns SUMPRODUCT LEFT FILTE

R SUM
Sum matching columns and rows SUMPRODUCT FILTER SUM

Sum numbers in single cell TEXTSPLIT SUM FILTERXM

L SUBSTITUTE
SUM LARGE SEQUENCE SU
Sum top n values MPRODUCT ROW INDIREC
T

Sum top n values with criteria SUM LARGE FILTER SEQUE

NCE IF
Sum visible rows in a filtered list SUBTOTAL AGGREGATE
SUMIFS with horizontal range SUMIFS
SUMIFS with multiple criteria and OR logic
SUMIFS SUM
SUMPRODUCT with IF SUMPRODUCT IF
Average and ignore errors AVERAGEIF AGGREGATE AVERAGE IFE
RROR FILTER

Average by group UNIQUE AVERAGEIFS

Average by month AVERAGEIFS EDATE FILTER AVERAGE

Average call time per month AVERAGEIFS EDATE FILTER AVERAGE

TEXT

Average hourly pay per day SUMPRODUCT SUMIFS

Average if not blank AVERAGEIFS FILTER AVERAGE

Average if with filter AVERAGE FILTER SUMPRODUCT IF AV

ERAGEIFS

Average last 3 numeric values AVERAGE FILTER TAKE LOOKUP LARG

E ROW
Average last n columns TAKE AVERAGE OFFSET COUNT INDEX
COLUMNS

Average last n rows TAKE AVERAGE OFFSET COUNT

Average numbers ignore zero AVERAGEIF AVERAGEIFS AVERAGE FIL

TER

Average salary by department UNIQUE AVERAGEIFS

Average top 3 scores LARGE AVERAGE SEQUENCE

Average with multiple criteria AVERAGEIFS

Basic average example AVERAGE AVERAGEIFS AGGREGATE

Moving average formula OFFSET AVERAGE MIN

Must pass 4 out of 6 subjects IF COUNTIF AND

Weighted average SUMPRODUCT SUM AVERAGE TRANSPO

SE
Basic filter example FILTER
Biggest gainers and losers FILTER LARGE SMALL SORT

VSTACK FILTER LET CHOOSE

Combine data in multiple worksheets
COLS

Combine ranges INDEX SEQUENCE ROWS COL

UMNS LET

Count unique dates ignore time INT UNIQUE COUNT LET LAM
BDA SCAN
Count unique values UNIQUE COUNTA

Count unique values with criteria UNIQUE FILTER LEN SUM CO

UNTA

Detailed LET function example LET VLOOKUP IF XLOOKUP

Distinct values UNIQUE

Dynamic summary count UNIQUE COUNTIF LET SORT S

CAN LAMBDA

Dynamic two-way average UNIQUE TRANSPOSE AVERAG

EIFS

Dynamic two-way count UNIQUE TRANSPOSE COUNTI

FS LET HSTACK VSTACK

Dynamic two-way sum UNIQUE TRANSPOSE AVERAG

EIFS

FILTER XMATCH ISNUMBER L

Extract common values from text strings
ET SORT

FILTER XMATCH ISNUMBER

Extract common values from two lists
UNIQUE SORT COUNTIF

Filter and sort without errors FILTER SORT

Filter and transpose horizontal to vertical
FILTER TRANSPOSE
Filter by column, sort by row FILTER SORT
Filter by date FILTER MONTH
FILTER case-sensitive FILTER SEARCH ISNUMBER

Filter contains one of many FILTER ISNUMBER MATCH

Filter data between dates FILTER DATE
Filter every nth row FILTER SEQUENCE MOD ROW
Filter exclude blank values FILTER
Filter horizontal data FILTER TRANSPOSE

FILTER last n valid entries FILTER SEQUENCE INDEX

FILTER on first or last n values FILTER SEQUENCE INDEX

FILTER on top n values FILTER LARGE SORT

FILTER on top n values with criteriaFILTER LARGE SORT

Filter text contains FILTER SEARCH ISNUMBER

Filter this or that FILTER
Filter to extract matching values FILTER COUNTIF COUNTIFS

FILTER to remove columns FILTER MATCH ISNUMBER

FILTER to show duplicate values UNIQUE FILTER COUNTIF

Filter values within tolerance FILTER ABS
FILTER with complex multiple criteria
FILTER LEFT MONTH NOT
Filter with multiple criteria FILTER
FILTER with multiple OR criteria FILTER ISNUMBER MATCH

FILTER with partial match FILTER SEARCH ISNUMBER

Generate random text strings SORTBY RANDARRAY COUNT

A SEQUENCE CHAR

Get column totals BYCOL LAMBDA MMULT RO

W TRANSPOSE SEQUENCE

Get row totals BYROW LAMBDA MMULT CO

LUMN TRANSPOSE SEQUENCE

LAMBDA append range LAMBDA LET INDEX SEQUEN

CE
LAMBDA append range horizontal LAMBDA LET INDEX SEQUEN
CE
LAMBDA contains one of many LAMBDA SUMPRODUCT SUM I
SNUMBER SEARCH

LAMBDA contains which things LAMBDA LET ISNUMBER SEA

RCH SORTBY

LAMBDA count words LAMBDA SUBSTITUTE TRIM L

EN
LAMBDA replace characters recursive
LAMBDA LEFT MID LEN

LAMBDA split text to array LAMBDA FILTERXML TRANSP

OSE

LAMBDA strip characters LAMBDA LET MID MATCH ISN

UMBER

LAMBDA strip trailing characters recursive

LAMBDA MID LEN

List upcoming birthdays SORTBY LET TEXT INDEX SEQ

UENCE XMATCH
MAP with AND and OR logic MAP AND OR
Minimum value if unique UNIQUE MIN COUNTIF IF

Random list of names SORTBY RANDARRAY COUNT

A SEQUENCE

SEQUENCE SORTBY RANDAR

Random numbers without duplicates
RAY INDEX

Random sort SORTBY RANDARRAY ROWS

Remove blank rows FILTER BYROW
Sort birthdays by month and day SORTBY TEXT
Sort by custom list SORTBY MATCH
Sort by one column SORT

Sort by substring SORTBY TEXTBEFORE TEXTA

FTER
Sort by two columns SORTBY
Sort text by length SORTBY LEN
Sort values by columns SORT SORTBY

Sum numbers with text TEXTBEFORE TEXTAFTER UN

IQUE LET LAMBDA BYROW

TEXTSPLIT get numeric values TEXTSPLIT VALUE IFERROR

LET
Unique rows UNIQUE SORT
Unique values UNIQUE
Unique values by count UNIQUE FILTER COUNTIF

Unique values case-sensitive REDUCE LAMBDA EXACT VST

ACK

Unique values from multiple rangesUNIQUE VSTACK

Unique values ignore blanks UNIQUE FILTER
Unique values with criteria UNIQUE FILTER
Unique values with multiple criteriaUNIQUE FILTER

UNIQUE with non-adjacent columnsFILTER UNIQUE SORT

XLOOKUP match any column MMULT SEQUENCE COLUMNS

Cap percentage between 0 and 100 MIN MAX MEDIAN IF

Find lowest n values SMALL INDEX MATCH

First in last out times MINIFS MAXIFS MIN MAX IF FI

LTER

Large with criteria LARGE FILTER IF

Larger of two values MAX IF

Max by month MAXIFS MAX TEXT

MAX FILTER SEQUENCE COLU

Max of every nth column MNS CHOOSECOLS COLUMN
MOD

Max value ignore all errors AGGREGATE MAXIFS

Max value on given weekday FILTER TEXT MAX IF AGGREG

ATE

Max value with variable column INDEX MATCH MAX FILTER C

OUNTIF

Maximum change MAX INDEX MATCH XLOOKUP

LET HSTACK SORT VSTACK
Maximum if multiple criteria MAX IF MAXIFS FILTER

Maximum value MAX MAXIFS LARGE

Maximum value if MAXIFS MAX IF FILTER BYRO

W VSTACK HSTACK UNIQUE

Minimum if multiple criteria MIN IF MINIFS

Minimum value MIN MINIFS SMALL

Minimum value if MINIFS MIN FILTER IF BYROW

VSTACK HSTACK UNIQUE

Name of nth largest value LARGE INDEX MATCH XLOOK

UP

Name of nth largest value with criteria LARGE INDEX MATCH IF XLO
OKUP

nth largest value LARGE

nth largest value with criteria LARGE FILTER IF

nth largest without duplicates MAX UNIQUE IF

nth smallest value SMALL
nth smallest value with criteria SMALL
Smaller of two values MIN IF
Case sensitive lookup EXACT INDEX MATCH XLOOKUP

Find closest match XLOOKUP INDEX MATCH ABS MIN

Find longest string XLOOKUP FILTER INDEX MATCH MAX LEN

Find longest string with criteria

XLOOKUP INDEX MATCH MAX LEN

Find missing values COUNTIF IF MATCH ISNUMBER

Get address of lookup result

CELL XLOOKUP INDEX MATCH

Get all matches cell contains

SEARCH ISNUMBER FILTER TEXTJOIN

Get cell content at given ADDRESS

row and column
INDIRECT INDEX

VLOOKUP
Get employee information XLOOKUP CHOOSECOLS TAKE
with VLOOKUP
DROP
 XLOOKUP ISNUMBER SEARCH INDEX MAT
Get first match cell contains
CH AGGREGATE

Get first non-blank valueXLOOKUP

in a list NOT ISBLANK INDEX MATCH

Get first numeric value inXLOOKUP

a range ISNUMBER INDEX MATCH

XLOOKUP VLOOKUP INDEX MATCH ISTEX

Get first text value in a range
T

Get first text value in a row

HLOOKUP XLOOKUP ISTEXT

Get information about max valueSORT MAX MATCH INDEX

TAKE

Get last match XLOOKUP XMATCH FILTER TAKE LOOKUP

INDEX
 XLOOKUP LOOKUP SEARCH INDEX MATC
Get last match cell contains
H ISNUMBER AGGREGATE

Get location of value in 2D array

SUMPRODUCT ROW COLUMN

Get nth match FILTER INDEX CHOOSEROWS SMALL IF MI

N ROW

Get nth match with INDEX / MATCH

INDEX SMALL IF

Get nth match with VLOOKUP

VLOOKUP COUNTIF

Get value of last non-empty cell

LOOKUP XLOOKUP

INDEX and MATCH advanced example

INDEX MATCH LEFT

INDEX and MATCH all matches

INDEX MATCH AND SUM

INDEX and MATCH all partial

INDEX matches
AGGREGATE ISNUMBER SEARCH

INDEX and MATCH approximate

INDEXmatch
MATCH

INDEX and MATCH approximate

INDEXmatch withIF
MATCH multiple criteria
INDEX and MATCH case-sensitive
INDEX MATCH EXACT XLOOKUP

INDEX and MATCH descending

INDEX order
MATCH

INDEX and MATCH exactINDEX

match MATCH TRANSPOSE

Index and match on multiple

MMULTcolumns
COLUMN TRANSPOSE INDEX

INDEX and MATCH two-column

INDEXlookup
XMATCH XLOOKUP CHOOSECOLS

INDEX and MATCH with multiple

INDEX criteria
MATCH

INDEX and MATCH with variable

INDEX columns
XMATCH XLOOKUP CHOOSECOLS

INDEX with variable arrayINDEX MATCH CHOOSE

Join tables with INDEX and MATCH

INDEX MATCH

Left lookup with INDEX and MATCH

INDEX MATCH

Left lookup with VLOOKUP

VLOOKUP CHOOSE

List missing values FILTER COUNTIF NOT

Look up entire column XLOOKUP INDEX MATCH

Look up entire row XLOOKUP INDEX MATCH

Lookup and sum column INDEX MATCH SUM

Lookup first negative value

XLOOKUP FILTER TAKE INDEX MATCH

Lookup last file version LOOKUP ISNUMBER FIND

Lookup latest price LOOKUP

Lookup lowest Monday tide

INDEX MATCH IF MIN XLOOKUP

Lookup lowest value INDEX MATCH MIN

Lookup number plus or minus N

XLOOKUP ABS INDEX MATCH

Lookup up cost for product

VLOOKUP
or service

Lookup value between two

LOOKUP
numbers

Lookup with variable sheet name

VLOOKUP INDIRECT

Match first does not begin with MATCH LEFT

INDEX

Match first error MATCH ISERROR

Match first occurrence does not contain

INDEX MATCH ISNUMBER SEARCH
Match long text MATCH LEFT MID EXACT

Match next highest valueINDEX MATCH

Max if criteria match MAX IF MAXIFS

Merge tables with VLOOKUP

VLOOKUP

Multi-criteria lookup andINDEX

transpose
MATCH

Multiple chained VLOOKUPs

VLOOKUP IFERROR

Multiple matches in comma

TEXTJOIN
separated list

FILTER
Multiple matches into separate UNIQUE SORT SEQUENCE INDEX S
columns
MALL IFERROR COLUMN COLUMNS

FILTER
Multiple matches into separate UNIQUE SORT TRANSPOSE INDEX S
rows
MALL IFERROR ROW ROWS

Nearest location with XMATCH

INDEX XMATCH

Next largest match with the MATCH

INDEX function
MATCH

Partial match with numbers and wildcard

MATCH TEXT

Partial match with VLOOKUP

VLOOKUP
Position of first partial match
MATCH INDEX

Position of max value in list

MAX MATCH

Quantity based discount XLOOKUP INDEX MATCH IFNA

Rank and score with INDEX and MATCH

RANK INDEX MATCH

VLOOKUP CHOOSE INDEX MATCH XLOOK

Reverse VLOOKUP example
UP

Self-contained VLOOKUPVLOOKUP

Sum lookup values using SUMIF

SUMIF SUMPRODUCT

Sum range with INDEX INDEX SUM

SUMIFS multiple criteria SUMIFS

lookup in table

SUMPRODUCT case-sensitive lookup

SUMPRODUCT EXACT

Two-way approximate match

INDEXmultiple
MATCH criteria
IF

Two-way lookup with INDEX and MATCH

INDEX MATCH
VLOOKUP by date VLOOKUP
VLOOKUP calculate grades
VLOOKUP

VLOOKUP calculate shipping cost

VLOOKUP MAX

VLOOKUP case-sensitive VLOOKUP EXACT CHOOSE

VLOOKUP faster VLOOKUP

VLOOKUP NA

VLOOKUP from another sheet

VLOOKUP
VLOOKUP from another workbook
VLOOKUP

VLOOKUP if blank return VLOOKUP

blank IF

VLOOKUP override output

VLOOKUP

VLOOKUP tax rate calculation

VLOOKUP

VLOOKUP two-way lookup

VLOOKUP MATCH

VLOOKUP variable commission

VLOOKUPsplit

VLOOKUP with 2 lookup tables

VLOOKUP

VLOOKUP with multiple criteria

VLOOKUP

VLOOKUP with multiple criteria

VLOOKUP advanced
CHOOSE

VLOOKUP with numbers VLOOKUP

and text ISTEXT IFERROR

VLOOKUP with two clientVLOOKUP

rates

VLOOKUP with variable table array VLOOKUP IF

INDIRECT

VLOOKUP without #N/A error

VLOOKUP IFERROR IFNA ISNA

XLOOKUP approximate match with multiple

XLOOKUP criteria
IF FILTER

XLOOKUP basic approximate

XLOOKUP
match

XLOOKUP basic exact match

XLOOKUP

XLOOKUP binary search XLOOKUP INDEX XMATCH

XLOOKUP case-sensitive XLOOKUP EXACT

XLOOKUP date of max value
XLOOKUP INDEX MATCH

XLOOKUP horizontal lookup

XLOOKUP
XLOOKUP last match XLOOKUP
XLOOKUP latest by date XLOOKUP MAX
XLOOKUP lookup left XLOOKUP
XLOOKUP lookup row or XLOOKUP
column

XLOOKUP match text contains

XLOOKUP SEARCH FIND ISNUMBER

XLOOKUP rearrange columns

XLOOKUP

XLOOKUP return blank if XLOOKUP

blank IF LET

XLOOKUP two-way exactXLOOKUP

match INDEX MATCH

XLOOKUP wildcard contains substring VLOOKUP TRANSPOSE

XLOOKUP

XLOOKUP wildcard matchXLOOKUP

example TRANSPOSE

XLOOKUP with Boolean OR logic

XLOOKUP INDEX MATCH

XLOOKUP with complex multiple

FILTERcriteria
LEFT MONTH NOT

XLOOKUP with logical criteria

XLOOKUP

XLOOKUP with multiple criteria

XLOOKUP INDEX MATCH
XLOOKUP without #N/A error
XLOOKUP IFERROR IFNA

XMATCH reverse search XMATCH INDEX

XMATCH with multiple criteria

XMATCH XLOOKUP INDEX MATCH

Zodiac sign lookup INDEX MATCH TEXT DATEVALUE IFNA YE

AR
Calculate sales commission with if
IF IFS

OR LEF
If cell begins with x, y, orTz IF CO
UNTIF
IF SEAR
If cell contains CH ISNU
MBER
SEARCH
ISNUM
if cell contains this or that
BER OR
COUNTI
F SUM
If cell equals IF
If cell is blank IF ISBL
ANK
If cell is greater than IF
IF ISBL
If cell is not blank ANK NO
T
If cell is this OR that IF OR
If cell is x or y and z IF OR A
ND
IF UNIC
If complete show checkmark
HAR CH
AR

If date is between two dates

IF AND

IF IFS V
If else LOOKU
P
IF ISBL
If not blank multiple cellsANK NO
T XLOO
KUP

If NOT this or that IF OR N

OT
If this AND that IF AND
If this AND that OR that IF AND
OR
IF with boolean logic IF
IF with other calculationsIF AND

IF COUN
IF with wildcards TIF SEA
RCH ISN
UMBER
Invoice age and status IF TODA
Y
Nested IF function example
IF IFS

IF AND
Nested IF with multiple AND
OR
IF COUN
T ISBLA
NK COU
Only calculate if not blank
NTBLAN
K AND
OR

IF ISBL
Return blank if ANK CO
UNTBLA
NK
Categorize text with keywords INDEX MATCH ISNUMBER SEARCH
XLOOKUP
Group arbitrary text values VLOOKUP
Group numbers at uneven intervals LOOKUP
Group numbers with VLOOKUP VLOOKUP
Group times into 3 hour buckets FLOOR
Group times into unequal buckets VLOOKUP
If cell contains one of many things INDEX MATCH ISNUMBER SEARCH
Map inputs to arbitrary values VLOOKUP CHOOSE
Map text to numbers VLOOKUP
Running count group by n size COUNTA CEILING
Highlight 3 smallest values with
SMALL ANDcriteria

Conditional formatting based on another cell

Conditional formatting based on another column

Conditional formatting
OR ANDcolumn is blank
NOT

Conditional formatting
TODAY date past due

Conditional formatting
SUMPRODUCT
dates overlap

Conditional formatting highlight target percentage

Conditional formatting last n rows

Find duplicate values in two columns

COUNTIF AND

Gantt chart AND

Gantt chart by week
AND

Gantt chart time schedule

AND OR

Gantt chart with weekends

WEEKDAY

Highlight approximate match OR

LOOKUP lookup
ANDconditional formatting

Highlight blank cells

ISBLANK LEN

Highlight bottom values

SMALL

Highlight cells thatCOUNTIF

begin with FIND
Highlight cells thatSEARCH
contain FIND ISNUM
BER

Highlight cells thatSEARCH FIND

contain one ISNUM
of many
BER SUMPRODUCT

Highlight cells thatCOUNTIF

end with RIGHT LEN
EXACT
Highlight cells thatEXACT
equal

Highlight column differences

EXACT NOT

Highlight data by quartile

QUARTILE

Highlight dates between

DATE AND

Highlight dates greater

DATEthan

Highlight dates in same

DATE month
TEXT and year

Highlight dates in the next NAND

TODAY days

Highlight dates that are weekends

WEEKDAY OR

Highlight duplicateSUMPRODUCT
columns COUN
TIF

Highlight duplicateCOUNTIFS
rows COUNTIF S
UMPRODUCT
Highlight duplicateCOUNTIF
values

Highlight entire rows

ISEVEN
Highlight every other row ISODD ROW
MOD
Highlight integers only
MOD

Highlight many matching

COUNTIFvalues

Highlight missing values

COUNTIF

Highlight multiplesMOD
of specific value
Highlight numbersISNUMBER
that include symbols
MID

Highlight row and OR

column
ANDintersection exact match

Highlight rows thatSEARCH

contain FIND

Highlight rows withCOUNTBLANK

blank cells

Highlight rows withDATE

dates AND
between

Highlight specific day

TEXT
of week

Highlight top values

LARGE

Highlight unique values

COUNTIF

Highlight unprotected
CELLcells

Highlight values between

AND

Highlight values greater than

Highlight values not

ANDbetween
NOT X and Y

ROW CEILING
Shade alternating groups of n rows ISEVE
N ISODD
Data validation
ISNUMBER
allow numbers only

Data validation
ISTEXT
allow text only

UPPER
Data validation
EXACTallow uppercase only
AND

YEAR
Data validation T weekday only
allow
ODAY

ANDdate
Data validation TO in next 30 days
DAY

YEAR
Data validation T in specific year
date
ODAY

Data validation
SUM don't exceed total

Data validation
COUNTIF
exists in list

EXACT
Data validation
LEFT must
C begin with
OUNTIF
 FIND IS
Data validation
NUMBEmust contain specific text
R

ISNUMB
ER SEA
Data validation
RCHmust
SU not contain
MPROD
UCT

Data validation
COUNTIF
must not exist in list

FINDnoCO
Data validation punctuation
UNT

ANDonly
Data validation DA dates between
TE

Data validation
MODrequire specific multiple

AND ISN
UMBER
Data validation require unique number
COUNTI
F

MATCH
COUNT
Data validation
LENspecific
MI characters only
D INDIR
ECT

Data validation
COUNTIF
unique values only
 TRUNC
Data validation whole percentage only
AND

Data validation
IF with conditional list
 SMALL
Break ties with helper INDEX
column and COUNTIF
MATCH

Rank function example

RANK

Rank if formula COUNTIFS RA

NK

Rank race results

RANK

LARGE INDEX
Rank values by month
MATCH TEXT I
F

CHOOSE
Rank with ordinal suffix ABS
MOD

RANK COUNTI
Rank without ties
F
Get decimalTRUNC
part of a number

Get integerTRUNC
part of aInumber
NT

Get number
MOD
at place value

Round a number
ROUND

Round a number
ROUNDDOWN
down

Round a number
FLOORdown to nearest multiple

ROUND
Round a number
LOG10toIn significant digits
NT ABS

Round a number
MROUND
to nearest multiple

Round a number
ROUNDUP
up

Round a number
CEILING
up to nearest multiple

Round a number
CEILING
up to next half

ROUND
Round a price to end in .99
MROUN
D
Round by bundle
CEILINGsize

Round price
CEILING
to end in .45 or .95

MROUN
Round timeDtoCEILI
nearest 15 minutes
NG FLO
OR

Round to nearest
ROUND1000

MROUN
D CEILI
Round to nearest 5
NG FLO
OR
Add business days toWORKDAY
date NETWORK
DAYS

Add days exclude certain

WORKDAY.INTL
days of week

Add days to date DAY

Add decimal hours toTIME

time MOD

Add decimal minutesTIME

to timeMOD

Add months to date EDATE

WORKDAY.INTL NETW
Add workdays no weekends
ORKDAYS

WORKDAY.INTL
Add workdays to date custom weekends NETW
ORKDAYS

Add years to date DATE YEAR MONTH D

AY EDATE

Assign points based on

IF late time
VALUE

Basic timesheet formula

MODwith breaks
Calculate date overlap in days
MAX MIN

Calculate days open TODAY IF ISBLANK

Calculate days remaining

TODAY

Calculate expiration date

EOMONTH EDATE

Calculate number of IF
hours
MODbetween two times

Calculate retirementEDATE
date YEARFRAC SIG
N

Calculate years between dates

YEARFRAC INT

DATEVALUE
Convert date string to date time LEFT MID
TIMEVALUE

Convert date to Julian format

DATE YEAR TEXT

Convert date to month

TEXT
and year

Convert date to text TEXT

Convert decimal hours to Excel time

Convert decimal minutes to Excel time

Convert decimal seconds to Excel time

Convert Excel time to decimal hours

Convert Excel time to decimal minutes

Convert Excel time to decimal seconds

Convert Excel time toDATE

Unix time

DATE LEFT
Convert text date dd/mm/yy MID RIGHT
to mm/dd/yy
TRIM

Convert text timestamp intoMID

TIME time

Convert text to date DATE LEFT MID RIGHT

Convert time to money

Convert time to timeMOD
zone

Convert Unix time stamp

DATEto Excel date

Count birthdays by month

SUMPRODUCT MONTH

Count calls at specificCOUNTIFS

times

COUNTIFS
Count dates in current month EOMONTH
SUMPRODUCT N

WEEKDAY ROW INDIR

Count day of week between dates
ECT SUMPRODUCT SE
QUENCE

Count holidays between

SUMPRODUCT
two dates

Count times in a specific range TIME

COUNTIFS

Create date range from twoIF

TEXT dates

Custom weekday abbreviation

WEEKDAY CHOOSE
Date is same month MONTH

Date is same month and year YEAR

MONTH

Date is workday WORKDAY WORKDAY.

INTL

Days in month DAY EOMONTH

Days until expiration date

Display the current date

TODAY

Display the current date

NOW and time

Due date by categoryVLOOKUP WORKDAY

XLOOKUP

WEEKDAY CHOOSE TO
Dynamic calendar formula
DAY SEQUENCE DATE

WEEKDAY CHOOSE TO
Dynamic calendar grid
DAY

Dynamic date list TODAY ROWS

Extract date from a date

INTand time
TRUNC
Extract time from a date
MODand time

FILTER
Filter on dates expiring soon SORT AND TOD
AY

Future time intervals

DATEDIF TODAY DATE

Get age from birthday
YEARFRAC INT

Get date from day number

DATE RIGHT LEFT

Get day from date DAY

TEXT WEEKDAY CHO

Get day name from date
OSE

Get days before a date

TODAY

Get days between dates

TODAY DAYS

Get days between dates

DATEDIF
ignoring years

Get days, hours, and INT

minutes
TEXTbetween dates
Get days, months, and years between
DATEDIF LET dates

MINIFS
Get earliest and latest project MAXIFS
dates MIN M
AX IF

Get first day of month

DAY EOMONTH

Get first day of previous

EOMONTH
month

Get fiscal quarter from date CHOOSE

MONTH

Get fiscal year from date

MONTH YEAR

Get last day of monthEOMONTH DATE

Get last weekday in month

EOMONTH WEEKDAY

Get last working day WEEKDAY

in month EOMONTH

Get Monday of the week

WEEKDAY

Get month from dateMONTH DATE

Get month name from date CHOOSE TEXT

MONTH
Get months betweenDATEDIF
dates YEARFRAC M
ONTH YEAR

Get most recent day MOD

of week

Get next day of weekMATCH TEXT WEEKD

AY

MIN IF MINIFS INDEX

Get next scheduled event
MATCH

Get nth day of week DAY

in month
WEEKDAY

Get nth day of year DATE YEAR

YEARFRAC YEAR DAT

Get percent of year complete
E

Get previous SundayWEEKDAY CHOOSE

Get project end dateWORKDAY WORKDAY.

INTL

Get project midpointWORKDAY WORKDAY.

INTL

Get project start dateWORKDAY WORKDAY.

INTL

Get quarter from date

MONTH ROUNDUP
Get same date next month
EDATE

Get same date next year

EDATE

WEEKNUM
Get week number from date ISOWEEKN
UM

NETWORKDAYS
Get work hours between dates NETW
ORKDAYS.INTL

NETWORKDAYS
Get work hours between dates and timesNETW
ORKDAYS.INTL

MID ROW INDIRECT W

Get work hours between dates custom
EEKDAY schedule
SUMPRODUC
T

NETWORKDAYS
Get workdays between dates NETW
ORKDAYS.INTL

Get year from date YEAR

TEXT TODAY IF AND M

Happy birthday message
ONTH DAY

If Monday, roll back to Friday

WEEKDAY IF

Join date and text TEXT

Last n days AND TODAY

Last n months AND TODAY EOMONT
H

Last n weeks AND TODAY WEEKDAY

Last updated date stamp

TEXT TODAY

List holidays betweenTEXTJOIN

two dates IF

Month number fromMONTH

name DATEVALUE

Next anniversary date

EDATE DATEDIF

Next biweekly payday

CEILING
from date

WORKDAY
Next business day 6 months WORKDAY.
in future
INTL

Next working day WORKDAY WORKDAY.

INTL

Pad week numbers with

TEXTzeros
WEEKNUM

Parse time string to time

TIME RIGHT LEFT MID

Previous working dayWORKDAY WORKDAY.

INTL
Remove time from timestamp
INT TRUNC

Sum by fiscal year MONTH YEAR SUMPR

ODUCT SUMIF

Sum race time splits SUM

Sum time SUM SUMIF

Sum time by week and

SUMIFS
project

SUMPRODUCT TIME S
Sum time over 30 minutes
UMIFS COUNTIFS

Sum time with SUMIFS

SUMIFS

Time difference in hours

MODas decimal value

Time duration with days

TIME

Time since start in day

IFSranges
IF

Timesheet overtime MIN

calculation formula
Total hours that fall between
MIN MAX two times

NETWORKDAYS NETW
Workdays per monthORKDAYS.INTL EOMO
NTH

NETWORKDAYS NETW
Working days in yearORKDAYS.INTL DATE
TODAY

NETWORKDAYS NETW
Working days left in month
ORKDAYS.INTL TODAY

Year is a leap year DATE YEAR MONTH

 SEQUEN
Biweekly pay
CEschedule
WOR
KDAY

LET SE
QUENC
E FILTE
List nth weekdays
R EOMO of the month
NTH TE
XT EDA
TE

LET SE
QUENC
E FILTE
List workdays between dates
R WEEK
DAY WO
RKDAY.
INTL

FILTER
SEQUEN
CE LET
Semimonthly
DAY payEO
schedule
MONTH
WORKD
AY

WORKD
AY.INTL
Sequence of custom days
SEQUE
NCE

Sequence of
SEQUENCE
days

SEQUEN
CE
Sequence of EDA
months
TE EOM
ONTH
SEQUEN
Sequence of
CE times
TIM
E
WORKD
AY.INTL
Sequence ofWEEKD
weekends
AY SEQ
UENCE

SEQUEN
CE WOR
Sequence of workdays
KDAY.I
NTL RO
W
 SEQUEN
CE DAT
Sequence of
E years
YEAR
MONTH
DAY
 UNIQUE
COUNT
10 most common text values
IF HSTA
CK SOR
T TAKE

TEXTSP
LIT TEX
AbbreviateTJOIN F words
names or
ILTER
MID CO
DE LEN

Add a line break

CHAR with a formula

INFO
Add line break C on OS
based
HAR

REPLAC
E UPPE
Capitalize first letter in a text string
R LEFT
MID LE
N
LEFT E
Cell beginsXACT
with C
OUNTIF

SUMPR
ODUCT
ISNUMB
Cell contains
ERallSEA
of many things
RCH CO
UNTA C
OUNT

FIND CO
UNT SE
Cell contains number
QUENC
E MID L
EN

SUMPR
ODUCT
Cell contains one of many things
ISNUMB
ER SEA
RCH
 SUMPR
ODUCT
Cell contains one of many with exclusions
ISNUMB
ER SEA
RCH

COUNT
Cell contains some words but not others
SEARCH
AND

SEARCH
ISNUM
Cell contains specific text
BER FIN
D

TEXTSP
LIT XM
ATCH
Cell contains C words
specific
OUNT C
OUNTA
LET

RIGHT
EXACT
Cell ends with
COUNTI
F

SUMPR
Cell equalsODUCT
one of many things
TRIM

TEXTSP
LIT TEX
Clean and reformat
TJOIN Stelephone numbers
UBSTIT
UTE

Compare two
EXACT
strings

ConditionalREPT IF with REPT function

message
AND
Convert numbers
TEXT to text

VALUE
Convert text to numbers
LEFT RI
GHT
SUMPR
ODUCT
MATCH
Count keywords cell contains
ISNUMB
ER SEA
RCH

LEN SU
BSTITU
Count line TE
breaks
CHA in cell
R ISBLA
NK

COUNT
SEQUEN
CE MID
Count numbers in text string
LEN IND
IRECT R
OW

LEN SU
BSTITU
TE characters
Count specific UPPE in a range
R SUMP
RODUC
T

LEN SU
BSTITU
Count specific characters in text string
TE LOW
ER

LEN SU
BSTITU
Count specific words in a cell
TE UPPE
R

LEN SU
BSTITU
TE words
Count specific UPPE in a range
R SUMP
RODUC
T

Count totalLEN
characters in a cell
 LEN SU
Count totalMPROD
characters in a range
UCT

LEN SU
Count totalBSTITU
words in a cell
TE TRI
M

SUMPR
ODUCT
Count totalLEN SUin a range
words
BSTITU
TE TRI
M

Double quotes
CHARinside a formula

MID LE
Extract lastNtwo
SUBST
words from cell
ITUTE F
IND
TRIM M
ID SUBS
Extract multiple
TITUTE lines from a cell
REPT L
EN

TEXTSP
LIT IND
EX TRI
Extract nthMword
MID from
S text string
UBSTIT
UTE RE
PT LEN

Extract substring
MID

Extract textMID SE parentheses

between
ARCH

MID MA
X FIND
SUBSTI
Extract word containing specific text
TUTE R
EPT TRI
M
 MID LE
N FIND
SUBSTI
Extract word that begins with specific character
TUTE R
EPT TRI
M

SUBSTI
Find and replace
TUTEmultiple
IN values
DEX

SUBSTI
TUTE FIof character
Find nth occurrence
ND CHA
R
LEFT FI
Get first word
ND ISER
ROR
TRIM S
UBSTIT
Get last lineUTE RI
in cell
GHT RE
PT CHA
R
TRIM S
UBSTIT
Get last wordUTE RI
GHT RE
PT
TRIM S
UBSTIT
Join cells with comma
UTE TE
XTJOIN
TEXTJO
IN format
MAC address SEQU
ENCE M
ID
IF IFNA
Make words plural
VLOOK
UP
INDEX
MATCH
Most frequent text with criteria
MODE I
F

INDEX
Most frequently
MATCH occurring text
MODE
 SUBSTI
TUTE T
Normalize text
RIM LO
WER

Pad text toREPT L

equal length
EN

SUBSTI
Position of TUTE
2nd 3rdFIetc instance of character
ND

LEFT LE
Remove characters
N VALUfrom right
E

Remove fileLEFT FI from filename

extension
ND

REPLAC
E RIGH
Remove first character
T LEN L
EFT
MID FIN
D word
Remove last SUBST
ITUTE L
EN

CLEAN
TRIMand
Remove leading S trailing spaces from text
UBSTIT
UTE

SUBSTI
Remove line breaks
TUTE C
LEAN

Remove text
SUBSTITUTE
by matching

Remove text
REPLACE
by position
 REPLAC
Remove text by variable position
E FIND

SUBSTI
TUTE C
HAR COcharacters
Remove unwanted
DE LEF
T CLEA
N

Replace one
SUBSTITUTE
character with another

TEXTSP
LIT TEX
TJOIN
Replace one S with another
delimiter
UBSTIT
UTE CH
AR

TEXTJO
IN MID I
Reverse text string
NDIREC
T SEQU
ENCE

TEXTSP
LIT SOR
T separated
Sort comma TEXTJ values
OIN TRI
M FILT
ERXML

TEXTSP
LIT LEF
T RIGH
Split dimensions into three parts
T MID L
EN SUBS
TITUTE

SUBSTI
TUTEinto
Split dimensions L two parts
EFT RIG
HT FIND

MAX LE
FT RIG
HTfrom
Split numbers ISNUunits of measure
MBER V
ALUE M
ID
 FIND MI
Split text and numbers
N RIGH
T LEFT

TEXTBE
FORE T
EXTAFT
Split text string
ER LEFat specific character
T RIGH
T LEN F
IND

MID SE
Split text string
QUENC to character array
E LEN

TRIM M
ID SUBS
Split text with delimiter
TITUTE
REPT L
EN

MIDtext
Strip html from LE or numbers
N

TEXTJO
IN SEQU
ENCE M
Strip non-numeric characters
ID LEN
ROW IN
DIRECT

TEXTJO
IN MID
ROW
Strip numeric IN
characters from cell
DIRECT
SEQUEN
CE LET

TEXTSP
LIT FIL
TERXM
Text split toLarray
SUBST
ITUTE T
RANSPO
SE

TEXTJO
IN VLO
Translate letters
OKUPtoMnumbers
ID ROW
T
 LEFT SU
Trim text toBSTITU
n words
TE FIND
Annual compound
FV interest schedule

Annuity solve
RATEfor interest rate

PV FV
Bond valuation P
example
RICE

GEOME
CAGR formula examples
AN RRI

Calculate compound
FV interest

Calculate cumulative
CUMIPMT loan interest

Calculate cumulative
CUMPRINCloan principal payments

Calculate interest
PPMT for given period

Calculate interest
RATE rate for loan

Calculate loan
CUMIPMT
interest in given year

Calculate original
PV loan amount

Calculate payment
PMT for a loan
Calculate payment
NPER periods for loan

PMT PV
Calculate periods for annuity
FV

Calculate principal
PPMT for given period

Calculate simple interest

Compare effect
FV of compounding periods

Currency exchange
STOCKHISTORY
rate example

EFFECT
Effective annual interest rate
RRI

PMT PVpayment
Estimate mortgage
FV

Future value
FVofPV
annuity

Future value
PVvs.FV
Present value

FIELDV
Get currentALUE S
stock price
TOCKHI
STORY

STOCK
HISTOR
Y TODA
Get stock price (latest close)
Y LOOK
UP INDE
X
 STOCK
Get stock price last n days
HISTOR
Y SORT
STOCK
HISTOR
Get stock price last n months
Y TODA
Y EDAT
E INDEX

Get stock price

STOCKHISTORY
on specific date

Income taxVLOOKUP
bracket calculation

LET SE
QUENC
E SCAN
PMT IP schedule
Mortgage payment
MT PPM
T HSTA
CK VST
ACK

NPV formula
NPVfor net present value

PMT
Payment for PV
annuity
FV

Present value
PV of
FVannuity

Required recovery rate

Tax rate calculation

IF with fixed base

Tax rate calculation

IF with two rates
 PI POW
Area of a circle
ER

Area of a parallelogram

Area of a trapezoid

Area of a triangle

Circumference
PI of a circle

Distance formula
SQRT

Pythagorean theorem

PIof
Surface area POW
a cone
ER

PIof
Surface area POW
a cylinder
ER

PIof
Surface area POW
a sphere
ER

Volume of PI POW
a cone
ER

Volume of PI POW
a cylinder
ER

Volume of a rectangular prism

Volume of PI POW
a sphere
ER
 SUMPR
ODUCT
ISERRO
Count errors in all sheets
R INDIR
ECT HY
PERLIN
K ISREF

Dynamic workbook
INDIRECTreference

Dynamic worksheet
INDIRECT reference

Get full workbook

CELL name and path

CELL T
EXTAFT
Get sheet name only
ER MID
FIND

CELL L
EFT FIN
Get workbook name and path without sheet
D SUBST
ITUTE L
ET

CELL T
EXTAFT
ER name
Get workbook TEX only
TBEFOR
E MID F
IND
CELL L
Get workbook
EFTpath
FINonly
D LET

Indirect named
INDIRECT
range different sheet

List sheet index

SHEETnumbers
 TEXTAF
TER TR
ANSPOS
List sheet names with formula
E INDEX
MID RO
W NOW
T

ISREF I
WorksheetNDIREC
name exists
T
Internet
Formula Related Functions

Create email address from name LEFT LOWER CONCAT

Create email with display name CONCAT CONCATENAT

E TEXTJOIN

Get domain from email address TEXTAFTER TEXTSPLIT

RIGHT LEN FIND

Get domain name from URL TEXTAFTER TEXTBEFO

RE LEFT FIND

Get name from email address TEXTSPLIT TEXTBEFOR

E LEFT FIND

Get page from URL TEXTAFTER MID LEN FI

ND

Get top level domain (TLD) TEXTAFTER RIGHT LEN

SUBSTITUTE FIND

Remove protocol from URL MID RIGHT LEN FIND

Remove trailing slash from url LEN RIGHT LEFT

Names
Formula Related Functions

Get first name from name TEXTBEFORE LEFT FIN

D

Get first name from name with comma TEXTAFTER RIGHT LEN
FIND

Get last name from name TEXTAFTER MID LEN SU

BSTITUTE FIND

Get last name from name with comma TEXTBEFORE LEFT FIN
D

Get middle name from full name TEXTAFTER TEXTBEFO

RE MID FIND IFERROR

Join first and last name TEXTJOIN CONCAT CON

CATENATE

Put names into proper case PROPER TRIM TEXTBEF

ORE TEXTAFTER
 LET TEXTSPLIT INDEX
Split full name into parts COUNTA DROP TEXTJOI
N HSTACK

Percentage
Formula Related Functions

Calculate percent variance ABS

Calculate percentage of number

Decrease by percentage

Difference is within specific percentage IF ABS

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

Random
Formula Related Functions

Random date between two dates RANDBETWEEN WORKD

AY

Random number between two numbers RANDBETWEEN

 CHOOSE RANDBETWEE
Random number from fixed set of options
N

Random number weighted probability RAND MATCH INDEX

Random text values CHOOSE RANDBETWEE

N

Random times at specific intervals RAND

Random value from list or table INDEX RANDBETWEEN

ROWS

Randomly assign data to groups RANDBETWEEN CHOOS

E

Randomly assign people to groups RAND ROUNDUP CEILIN

G

Range
Formula Related Functions

Add row numbers and skip blanks COUNTA IF ISBLANK

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

All values in a range are at least COUNTIF NOT

Automatic row numbers SEQUENCE ROW INDEX

COUNTA

Combine ranges with CHOOSE CHOOSE

Count cells in range ROWS COLUMNS COUN

TA

Count visible columns CELL N SUM

COUNTIFS with variable range COUNTIFS OFFSET ADD

RESS INDIRECT ROW

Define range based on cell value INDEX OFFSET

Dynamic named range with INDEX INDEX COUNTA

Dynamic named range with OFFSET OFFSET COUNTA

Dynamic range between two matches XLOOKUP INDEX MATC
H COUNT

First column number in range ROWS MIN

First match between two ranges INDEX MATCH COUNTIF

First row number in range ROW MIN

ADDRESS ROW COLUMN

Get address of named range or table ROWS COLUMNS LET T
AKE LAMBDA TOCOL

Get relative column numbers in range COLUMN SEQUENCE CO

LUMNS

Get relative row numbers in range ROW SEQUENCE ROWS

Last column number in range COLUMN COLUMNS MIN

Last n rows ROW ROWS INDEX

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

EXACT SUMPRODUCT C
Multiple cells have same value case sensitive
OUNTA

Multiple columns are equal AND COUNTIF

SUMPRODUCT MATCH I
Range contains a value not in another range
SNA

Range contains numbers SUMPRODUCT ISNUMBE

R

Range contains specific date COUNTIFS DATE TODAY

Row is blank SUMPRODUCT BYROW L
AMBDA

Total columns in range COLUMNS

Total rows in range ROWS

Tables
Formula Related Functions

Automatic row numbers in Table ROW INDEX

Average last N values in a table INDEX AVERAGE ROWS

Basic inventory formula example SUMIFS

Count table columns COLUMNS

Count table rows ROWS

COUNTIFS with variable table column COUNTIFS INDEX MATC

H INDIRECT

Dynamic reference to table INDIRECT

Get column index in Excel Table MATCH

Get column name from index in table INDEX

Percentile IF in table PERCENTILE

Running count in Table INDEX SUM

Running total in Table INDEX SUM

Sum multiple tables SUM

SUMIFS INDEX MATCH

SUMIFS vs other lookup formulas LOOKUP XLOOKUP SUM
PRODUCT

SUMIFS with Excel Table SUMIFS

Two-way lookup VLOOKUP in a Table VLOOKUP MATCH

Two-way summary with SUMIFS SUMIFS

Errors
Formula Related Functions

How to fix a circular reference error

How to fix the #### (hashtag) error

How to fix the #CALC! error IFERROR ISERROR ERR

OR.TYPE

How to fix the #DIV/0! error IFERROR ISERROR ERR

OR.TYPE

How to fix the #N/A error VLOOKUP IFERROR MA

TCH

How to fix the #NAME? error IFERROR ISERROR ERR

OR.TYPE

How to fix the #NULL! error IFERROR ISERROR ERR

OR.TYPE

How to fix the #NUM! error IFERROR ISERROR ERR

OR.TYPE

How to fix the #REF! error ISREF IFERROR

How to fix the #SPILL! error IFERROR ISERROR ERR

OR.TYPE

How to fix the #VALUE! error IFERROR ISERROR ERR

OR.TYPE
Miscellaneous
Formula Related Functions

Abbreviate state names VLOOKUP INDEX MATC

H

Add leading zeros to numbers TEXT

All dates in chronological order IF SUMPRODUCT SORT

Basic array formula example MAX MIN

Basic attendance tracking formula COUNTIF

Basic error trapping example IFERROR

Basic in cell histogram REPT CHAR

Basic numeric sort formula RANK COUNTIF

Basic outline numbering COUNTA MID FIND LEN

Basic text sort formula RANK COUNTIF

BMI calculation formula CONVERT POWER

Build hyperlink with VLOOKUP HYPERLINK VLOOKUP

Calculate a ratio from two numbers GCD

Calculate win loss tie totals SUMPRODUCT

Cap percentage at 100 MIN

Cap percentage at specific amount MIN

Carry-on baggage Inches to centimeters CONVERT

Cash denomination calculator SUMPRODUCT INT FLO

OR

Celsius to Fahrenheit conversion CONVERT NOT ISBLANK

Change negative numbers to positive ABS

Check register balance IF AND ISBLANK

Coefficient of variation STDEV.P STDEV.S AVER

AGE
Conditional median with criteria MEDIAN

Conditional mode with criteria MODE

Convert column letter to number COLUMN INDIRECT

Convert column number to letter ADDRESS SUBSTITUTE T

EXTBEFORE

Convert expense time units INDEX MATCH

Convert feet and inches to inches LEFT FIND MID SUBSTIT

UTE

Convert inches to feet and inches INT MOD TRUNC ABS

Convert negative numbers to zero MAX

Convert numbers to 1 or 0 IF

Convert pounds to kilograms CONVERT

Copy value from every nth column OFFSET COLUMN

Copy value from every nth row OFFSET ROW

Cost of living adjustment

Count consecutive monthly orders FREQUENCY MAX IF

Count values out of tolerance SUMPRODUCT ABS

Count with repeating values ROUNDUP COLUMN RO

W

Course completion status summary COUNTIFS

Course completion summary with criteriaCOUNTIFS

Create array of numbers INDIRECT ROW

Cube root of number POWER

Customer is new COUNTIFS

Display sorted values with helper columnINDEX MATCH ROWS

Dropdown sum with all option SUMIF SUM IF

Easy bundle pricing with SUMPRODUCT SUMPRODUCT

Expense begins on specific month IF

Extract unique items from a list INDEX MATCH COUNTIF

LOOKUP

Filter values in array formula ISNUMBER MATCH IF C

OUNT

Fixed value every N columns MOD COLUMN

Flag first duplicate in a list COUNTIF COUNTIFS

Flip table rows to columns TRANSPOSE

Forecast vs actual variance SUMIFS

Formula with locked absolute 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

Hyperlink to first blank cell HYPERLINK CELL INDE

X MATCH

Hyperlink to first match HYPERLINK CELL INDE

X MATCH

Increment a calculation with ROW or COLUMN

ROW COLUMN

Increment a number in a text string RIGHT TEXT

Increment cell reference with INDIRECT INDIRECT CELL

Leave a comment in a formula N

Link to multiple sheets HYPERLINK CELL INDE
X MATCH

List most frequently occuring numbers MODE ISNUMBER MATC

H

Longest winning streak FREQUENCY MAX IF

Lookup last file revision SEARCH ISERROR MAX

INDEX IF ROW

Mark rows with logical tests COUNTIFS IF

Most frequently occurring number MODE

Multiplication table formula

New customers per month COUNTIFS EOMONTH

Nightly hotel rate calculation SUMPRODUCT FILTER I

NDEX MATCH

Normalize size units to Gigabytes MATCH LEFT RIGHT

nth root of number POWER

Number is whole number MOD TRUNC INT

Odometer gas mileage log SUM MAX MIN

One or the other not both XOR

Pad a number with zeros TEXT REPT

Parse XML with formula FILTERXML

Random sort formula INDEX MATCH

Range contains duplicates OR COUNTIF SUMPROD

UCT

Range contains one of many substrings SUMPRODUCT COUNTIF

Range contains one of many values SUMPRODUCT ISNUMBE

R SEARCH

Range contains specific text COUNTIF

Repeat fixed value every 3 months MOD DATEDIF

Return array with INDEX function INDEX MATCH N

Reverse a list or range INDEX COUNTA ROW SO

RTBY SEQUENCE

Risk Matrix Example INDEX MATCH

Score quiz answers with key SUM COUNTA

Search entire worksheet for value COUNTIF

Search multiple worksheets for value COUNTIF INDIRECT

Send email with formula HYPERLINK

Show formula text with formula FORMULATEXT IFERRO

R ISFORMULA

Simple currency conversion VLOOKUP IF

Sort and extract unique values MMULT TRANSPOSE IN

DEX MATCH
Sort numbers ascending or descending SMALL LARGE

Sort text and numbers with formula RANK COUNTIF COUNT

Split payment across months AND

Square root of number SQRT POWER ABS

Standard deviation calculation STDEV.P STDEV.S STDEV

STDEVP

Student class enrollment with table IF COUNTIF

Sum every 3 cells OFFSET COLUMN

Sum Roman numbers ARABIC ROMAN SUMPR

ODUCT SUM

Sum text values like numbers INDEX MATCH N

Text is greater than number COUNTIF COUNTIFS SU

MPRODUCT ISNUMBER
Transpose table without zeros TRANSPOSE IF

Unwrap column into fields OFFSET TRANSPOSE RO

W

Validate input with check mark IF COUNTIF

Value exists in a range COUNTIF MATCH ISNU

MBER

Value is between two numbers AND MAX MIN

Value is within tolerance IF ABS

Volunteer hours requirement calculationAND COUNTIF SUM