Basic and Advanced MS Excel Formulas Lists

Posted By EduStudy on 2018-08-04 11:35 AM

Description

Here is Basic & Advanced Excel Formulas List with Function Name:

Count cells over 100 characters
SUMPRODUCTLENN
Count cells that are blank
COUNTBLANKCOUNTA
Count cells that are not blank
COUNTACOUNTBLANK
Count cells that begin with
COUNTIF
Count cells that contain either x or y
COUNTIFSUMPRODUCTISNUMBERFIND
Count cells that contain errors
SUMPRODUCTISERRORISERR
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
SUMPRODUCTMOD
Count cells that contain positive numbers
COUNTIF
Count cells that contain specific text
COUNTIFSUMPRODUCTFINDISNUMBER
Count cells that contain text
COUNTIFISTEXTSUMPRODUCTCOUNTIFS
Count cells that do not contain
COUNTIF
Count cells that do not contain errors
ISERRORNOTSUMPRODUCT
Count cells that end with
COUNTIF
Count dates by day of week
SUMPRODUCTWEEKDAY
Count dates in given year
YEARSUMPRODUCT
Count if row meets internal criteria
SUMPRODUCT
Count if row meets multiple internal criteria
SUMPRODUCT
Count if two criteria match
COUNTIFSSUMPRODUCT
Count items in list
COUNTIFS
Count long numbers without COUNTIF
SUMPRODUCTCOUNTIF
Count matches between two columns
SUMPRODUCT
Count multiple criteria with NOT logic
SUMPRODUCTISNAMATCH
Count numbers by range with COUNTIFS
COUNTIFS
Count numbers that begin with
SUMPRODUCTLEFT
Count numbers third digit equals 3
SUMPRODUCTMID
Count occurrences in entire workbook
COUNTIFSUMPRODUCT
Count paired items in listed combinations
COUNTIFSCONCAT
Count rows that contain specific values
MMULTCOLUMNTRANSPOSE
Count rows with at least n matching values
MMULTCOLUMNTRANSPOSE
Count rows with multiple OR criteria
SUMPRODUCT
Count sold and remaining
COUNTA
Count total matches in two ranges
SUMPRODUCTCOUNTIF
Count unique numeric values in a range
FREQUENCYSUMCOUNTIF
Count unique numeric values with criteria
FREQUENCYSUM
Count unique text values in a range
FREQUENCYMATCHROWSUMPRODUCT
Count unique text values with criteria
FREQUENCYMATCHROWSUM
Count unique values in a range with COUNTIF
SUMPRODUCTCOUNTIF
Count visible rows in a filtered list
SUBTOTAL
Count visible rows only with criteria
SUBTOTALOFFSET
COUNTIFS with multiple criteria and OR logic
COUNTIFS
Running count of occurrence in list
COUNTIF
Summary count by month with COUNTIFS
COUNTIFSEDATE
Summary count of non-blank categories
COUNTIFS
Summary count with COUNTIF
COUNTIF
Summary count with percentage breakdown
COUNTIFCOUNTA
SUMPRODUCT count multiple OR criteria
SUMPRODUCT
Two-way summary count with COUNTIFS
COUNTIFS
Sum
3D sum multiple worksheets
SUM
3D SUMIF for multiple worksheets
SUMPRODUCTSUMIFINDIRECT
Calculate running total
SUM
Subtotal by color
SUMIF
Subtotal by invoice number
SUMIFCOUNTIF
Subtotal invoices by age
SUMIF
Sum 2d range with multiple criteria
SUMPRODUCT
Sum bottom n values
SUMPRODUCTSMALLSUMROWINDIRECT
Sum bottom n values with criteria
SMALLSUM
Sum by group
SUMIF
Sum by month
SUMIFSEOMONTH
Sum by month ignore year
Sum by week
SUMIFS
Sum by week number
SUMIFSWEEKNUM
Sum by weekday
SUMPRODUCTWEEKDAY
Sum columns based on adjacent criteria
SUMPRODUCT
Sum entire column
SUM
Sum every n rows
SUMOFFSET
Sum every nth column
SUMPRODUCTCOLUMNMOD
Sum if begins with
SUMIFSUMIFS
Sum if between
SUMIFS
Sum if by year
SUMIFSDATE
Sum if cell contains text in another cell
SUMIFSUMIFS
Sum if cells are equal to
SUMIFSUMIFS
Sum if cells are not equal to
SUMIFSUMIFS
Sum if cells contain an asterisk
SUMIFSUMIFS
Sum if cells contain both x and y
SUMIFS
Sum if cells contain either x or y
SUMIFSSUMPRODUCTISNUMBERFINDSEARCH
Sum if cells contain specific text
SUMIFSUMIFS
Sum if date is between
SUMIFSDATE
Sum if date is greater than
SUMIFSUMIFSDATE
Sum if ends with
SUMIFSUMIFS
Sum if equal to either x or y
SUMIFSUMPRODUCT
Sum if equal to one of many things
SUMIFSUMPRODUCT
Sum if greater than
SUMIFSUMIFS
Sum if less than
SUMIFSUMIFS
Sum if multiple criteria
SUMIFS
Sum if not blank
SUMIFSUMIFS
Sum if one criteria multiple columns
SUMPRODUCT
Sum last n columns
INDEXCOLUMNSUM
Sum sales in last 30 days by ID
SUMIFSTODAY
Sum top n values
SUMPRODUCTLARGESUMROWINDIRECT
Sum top n values with criteria
SUMPRODUCTLARGE
Sum visible rows in a filtered list
SUBTOTAL
SUMIFS with multiple criteria and OR logic
SUMIFSSUM
Average
Average and ignore errors
AVERAGEIFAGGREGATE
Average by month
AVERAGEIFSEOMONTH
Average last 5 values
OFFSETCOUNTAVERAGE
Average last 5 values in columns
OFFSETCOUNTAVERAGE
Average numbers
AVERAGE
Average numbers ignore zero
AVERAGEIF
Average response time per month
AVERAGEIFSEOMONTH
Average the last 3 numeric values
LOOKUPLARGEROWAVERAGE
Average top 3 scores
LARGEAVERAGE
Weighted average
SUMPRODUCTSUM
Min and Max
First in, last out times
MINIFSMAXIFSMAXIF
Large with criteria
LARGE
Larger of two values
MAX
Max of every nth column
MAXCOLUMNMOD
Max value ignore all errors
AGGREGATEMAXIFS
Maximum if multiple criteria
MAXIFMAXIFS
Maximum value
MAXMAXIFS
Maximum value if
MAXIFMAXIFS
Minimum if multiple criteria
MINIFMINIFS
Minimum value
MIN
Minimum value if
MINIFMINIFS
nth largest value
LARGE
nth largest value with criteria
LARGE
nth largest value with duplicates
MAXIF
nth smallest value
SMALL
nth smallest value with criteria
SMALL
Smaller of two values
MIN
Lookup
Approximate match with multiple criteria
INDEXMATCHIF
Basic INDEX MATCH approximate
INDEXMATCH
Basic INDEX MATCH exact
INDEXMATCH
Basic Tax Rate calculation with VLOOKUP
VLOOKUP
Break ties with helper column and COUNTIF
SMALLINDEXMATCH
Calculate grades with VLOOKUP
VLOOKUP
Calculate shipping cost with VLOOKUP
VLOOKUP
Case sensitive match
MATCHEXACT
Count missing values
COUNTIFSUMPRODUCTMATCHISNA
Dynamic lookup table with INDIRECT
INDIRECTVLOOKUP
Exact match lookup with INDEX and MATCH
INDEXMATCHEXACT
Exact match lookup with SUMPRODUCT
SUMPRODUCTEXACT
Extract data with helper column
INDEXMATCHANDSUM
Extract multiple matches into separate columns
INDEXSMALLIFERRORROWCOLUMNS
Extract multiple matches into separate rows
INDEXSMALLIFERRORROWROWS
Faster VLOOKUP with 2 VLOOKUPS
VLOOKUPNA
Find closest match
INDEXMATCHABSMIN
Find longest string in column
INDEXMATCHMAXLEN
Find longest string with criteria
INDEXMATCHMAXLEN
Find lowest n values
SMALLINDEXMATCH
Find missing values
COUNTIFVLOOKUPMATCH
First match in range with wildcard
INDEXMATCH
Get address of lookup result
INDEXMATCHCELL
Get cell content at given row and column
ADDRESSINDIRECTINDEX
Get employee information with VLOOKUP
VLOOKUP
Get first match cell contains
INDEXMATCHISNUMBERSEARCH
Get first non-blank value in a list
INDEXMATCHISBLANK
Get first text value in a list
VLOOKUP
Get first text value with HLOOKUP
HLOOKUP
Get information corresponding to max value
MAXMATCHINDEX
Get last match
INDEXMATCHMAXROW
Get last match cell contains
LOOKUPSEARCH
Get location of value in 2D array
SUMPRODUCTROWCOLUMN
Get nth match
SMALLIFMINROW
Get nth match with INDEX / MATCH
INDEXMATCHSMALL
Get nth match with VLOOKUP
VLOOKUPCOUNTIF
INDEX and MATCH descending order
INDEXMATCH
Index and match on multiple columns
MMULTCOLUMNTRANSPOSEINDEX
INDEX and MATCH with multiple criteria
INDEXMATCH
Join tables with INDEX and MATCH
INDEXMATCH
Lookup and sum column
INDEXMATCHSUM
Lookup entire column
INDEXMATCH
Lookup entire row
INDEXMATCH
Lookup last file version
LOOKUPISNUMBERFIND
Lookup latest price
LOOKUP
Lookup lowest value
INDEXMATCHMIN
Lookup up cost for product or service
VLOOKUP
Lookup value between two numbers
LOOKUP
Lookup with variable sheet name
VLOOKUPINDIRECT
Match first error
MATCHISERROR
Match next highest value
INDEXMATCH
Max if criteria match
MAXIF
Merge tables with VLOOKUP
VLOOKUP
Multi-criteria lookup and transpose
INDEXMATCH
Multiple chained VLOOKUPs
VLOOKUPIFERROR
Multiple matches in comma separated list
TEXTJOIN
Next largest match with the MATCH function
INDEXMATCH
Partial match against numbers with wildcard
MATCHTEXT
Partial match with VLOOKUP
VLOOKUP
Position of first partial match
MATCHINDEX
Position of max value in list
MAXMATCH
Self-contained VLOOKUP
VLOOKUP
Sum lookup values using SUMIF
SUMIFSUMPRODUCT
Sum range with INDEX
INDEXSUM
SUMIFS multiple criteria lookup in table
SUMIFS
Two-way lookup with INDEX and MATCH
INDEXMATCH
Two-way lookup with VLOOKUP
VLOOKUPMATCH
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
VLOOKUPIFERRORIFNA
If
Force negative numbers to zero
MAX
If cell begins with x, y, or z
SUMCOUNTIF
If cell contains
IF
if cell contains this or that
COUNTIFSUM
If cell equals
IF
If cell is blank
IFISBLANK
If cell is greater than
IF
If cell is not blank
IFISBLANKNOT
If cell is this OR that
IFOR
If cell is x or y and z
IFORAND
If else
IF
If NOT this or that
IFORNOT
If this AND that
IFAND
If this AND that OR that
IFANDOR
Invoice status with nested if
IFTODAY
Nested IF function example
IF
Tax rate calculation with fixed base
IF
Win loss points calculation
VLOOKUPIF
Grouping
Categorize text with keywords
INDEXMATCHISNUMBERSEARCH
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
INDEXMATCHISNUMBERSEARCH
Map inputs to arbitrary values
VLOOKUPCHOOSE
Map text to numbers
VLOOKUP
Running count group by n size
COUNTACEILING
Conditional formatting
Highlight 3 smallest values with criteria
SMALLAND
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
COUNTIFAND
Highlight approximate match lookup conditional formatting
LOOKUPORAND
Highlight blank cells
ISBLANKLEN
Highlight bottom values
SMALL
Highlight cells that begin with
COUNTIFFINDIFERROR
Highlight cells that contain
SEARCHFINDISNUMBER
Highlight cells that contain one of many
SEARCHFINDISNUMBERSUMPRODUCT
Highlight cells that end with
COUNTIFRIGHTLENEXACT
Highlight cells that equal
EXACT
Highlight column differences
EXACTNOT
Highlight data by quartile
QUARTILE
Highlight dates between
DATEAND
Highlight dates greater than
DATE
Highlight dates in same month and year
DATETEXT
Highlight dates in the next N days
TODAYAND
Highlight dates that are weekends
WEEKDAYOR
Highlight duplicate columns
SUMPRODUCTCOUNTIF
Highlight duplicate rows
COUNTIFSCOUNTIFSUMPRODUCT
Highlight duplicate values
COUNTIF
Highlight entire rows
Highlight every other row
ISEVENISODDROWMOD
Highlight integers only
MOD
Highlight missing values
COUNTIF
Highlight multiples of specific value
MOD
Highlight numbers that include symbols
ISNUMBERMID
Highlight row and column intersection exact match
ORAND
Highlight rows that contain
SEARCHFIND
Highlight rows with blank cells
COUNTBLANK
Highlight rows with dates between
DATEAND
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
ANDNOT
Shade alternating groups of n rows
ROWCEILINGISEVENISODD
Data validation
Data validation allow numbers only
ISNUMBER
Data validation allow text only
ISTEXT
Data validation allow uppercase only
UPPEREXACTAND
Data validation allow weekday only
YEARTODAY
Data validation contains specific text
FINDISNUMBER
Data validation date in next 30 days
ANDTODAY
Data validation date in specific year
YEARTODAY
Data validation don't exceed total
SUM
Data validation exists in list
COUNTIF
Data validation must begin with
EXACTLEFTCOUNTIF
Data validation must not contain
ISNUMBERSEARCHSUMPRODUCT
Data validation must not exist in list
COUNTIF
Data validation only dates between
ANDDATE
Data validation require unique number
ANDISNUMBERCOUNTIF
Data validation unique values only
COUNTIF
Data validation whole percentage only
TRUNCAND
Data validation with conditional list
IF
Rank
Rank function example
RANK
Rank race results
RANK
Rank with criteria
COUNTIFS
Rank with ordinal suffix
CHOOSEABSMOD
Round
Get decimal part of a number
TRUNC
Get integer part of a number
TRUNCINT
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
ROUNDLOG10INTABS
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
MROUNDCEILINGFLOOR
Date and Time
Add business days to date
WORKDAYNETWORKDAYS
Add days exclude certain days of week
WORKDAY.INTL
Add days to date
DAY
Add decimal hours to time
TIMEMOD
Add decimal minutes to time
TIMEMOD
Add months to date
EDATE
Add workdays no weekends
WORKDAY.INTLNETWORKDAYS
Add workdays to date custom weekends
WORKDAY.INTLNETWORKDAYS
Add years to date
DATEYEARMONTHDAY
Assign points based on late time
IFVALUE
Basic overtime calculation formula
MIN
Basic timesheet formula with breaks
MOD
Calculate date overlap in days
MAXMIN
Calculate days remaining
TODAY
Calculate expiration date
EOMONTHEDATE
Calculate number of hours between two times
IFMOD
Calculate retirement date
EDATEEOMONTH
Calculate years between dates
YEARFRACINT
Convert date string to date time
DATEVALUELEFTMIDTIMEVALUE
Convert date to Julian format
DATEYEARTEXT
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
DATELEFTMIDRIGHTTRIM
Convert text to date
DATELEFTMIDRIGHT
Convert time to time zone
MOD
Count birthdays by month
SUMPRODUCTMONTH
Count dates in current month
COUNTIFSEOMONTHSUMPRODUCTN
Count day of week between dates
WEEKDAYROWINDIRECTSUMPRODUCT
Count holidays between two dates
SUMPRODUCT
Count times in a specific range
COUNTIFSTIME
Create date range from two dates
TEXTIF
Custom weekday abbreviation
WEEKDAYCHOOSE
Date is same month
MONTH
Date is same month and year
MONTHYEAR
Date is workday
WORKDAYWORKDAY.INTL
Days in month
DAYEOMONTH
Days until expiration date
Display the current date
TODAY
Display the current date and time
NOW
Extract date from a date and time
INTTRUNC
Extract time from a date and time
MOD
Get age from birthday
INTTODAYYEARFRAC
Get date from day number
DATERIGHTLEFT
Get day from date
DAY
Get day name from date
WEEKDAYCHOOSE
Get days before a date
TODAY
Get days between dates
TODAYDAYS
Get days between dates ignoring years
DATEDIF
Get days, hours, and minutes between dates
INTTEXT
Get days, months, and years between dates
DATEDIF
Get first day of month
DAYEOMONTH
Get first day of previous month
EOMONTH
Get first Monday before any date
WEEKDAY
Get fiscal quarter from date
MONTHCHOOSE
Get fiscal year from date
MONTHYEAR
Get last day of month
EOMONTHDATE
Get last weekday in month
EOMONTHWEEKDAY
Get last working day in month
WEEKDAYEOMONTH
Get month from date
MONTHDATE
Get month name from date
MONTHCHOOSETEXT
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
DAYWEEKDAY
Get nth day of year
DATEYEAR
Get percent of year complete
YEARFRACYEARDATE
Get quarter from date
MONTHROUNDUP
Get same date next month
EDATE
Get same date next year
EDATE
Get start date based on target date
WORKDAYWORKDAY.INTL
Get week number from date
WEEKNUMISOWEEKNUM
Get work hours between dates
NETWORKDAYSNETWORKDAYS.INTL
Get work hours between dates custom schedule
CHOOSEROWINDIRECTWEEKDAYSUM
Get workdays between dates
NETWORKDAYSNETWORKDAYS.INTL
Get year from date
YEAR
If Monday, roll back to Friday
WEEKDAYIF
Join date and text
TEXT
Last updated date stamp
TEXT
List holidays between two dates
TEXTJOINIF
Next anniversary date
EDATEDATEDIF
Next biweekly payday from date
CEILING
Next business day 6 months in future
WORKDAYWORKDAY.INTL
Next working day
WORKDAYWORKDAY.INTL
Pad week numbers with zeros
TEXTWEEKNUM
Sum race time splits
SUM
Time difference in hours as decimal value
MOD
Total hours that fall between two times
MINMAX
Workdays per month
NETWORKDAYSNETWORKDAYS.INTLEOMONTH
Working days left in month
NETWORKDAYSNETWORKDAYS.INTLTODAY
Year is a leap year
DATEYEARMONTH
Date series
Series of dates by day
Series of dates by month
DAYMONTHYEARDATEEOMONTH
Series of dates by weekends
IFWEEKDAY
Series of dates by workdays
WORKDAYNETWORKDAYS.INTLIFWEEKDAY
Series of dates by year
DAYMONTHYEARDATE
Text
Abbreviate names or words
TEXTJOINMIDROWCODELENINDIRECT
Add a line break with a formula
CHAR
Add line break based on OS
INFOCHAR
Cell contains all of many things
SUMPRODUCTISNUMBERSEARCHCOUNTA
Cell contains number
FINDCOUNT
Cell contains one of many things
SUMPRODUCTISNUMBERSEARCH
Cell contains one of many with exclusions
SUMPRODUCTISNUMBERSEARCH
Cell contains some words but not others
COUNTSEARCHAND
Cell contains specific text
SEARCHISNUMBERFIND
Cell contains which things
SEARCHISNUMBERFIND
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
MIDROWLENINDIRECT
Convert text to numbers
VALUELEFTRIGHT
Count line breaks in cell
LENSUBSTITUTECHARISBLANK
Count specific characters in a cell
LENSUBSTITUTEUPPER
Count specific characters in a range
LENSUBSTITUTEUPPERSUMPRODUCT
Count specific words in a cell
LENSUBSTITUTEUPPER
Count specific words in a range
LENSUBSTITUTEUPPERSUMPRODUCT
Count total characters in a cell
LEN
Count total characters in a range
LENSUMPRODUCT
Count total words in a cell
LENSUBSTITUTETRIMISBLANK
Count total words in a range
SUMPRODUCTLENSUBSTITUTETRIM
Double quotes inside a formula
CHAR
Extract last two words from cell
MIDLENSUBSTITUTEFIND
Extract multiple lines from a cell
TRIMMIDSUBSTITUTEREPTLEN
Extract nth word from text string
TRIMMIDSUBSTITUTEREPTLEN
Extract text between parentheses
MIDSEARCH
Extract word containing specific text
MIDMAXFINDSUBSTITUTEREPTTRIM
Extract word that begins with specific character
MIDLENFINDSUBSTITUTEREPTTRIM
Find nth occurrence of character
SUBSTITUTEFINDCHAR
Get first word
LEFTFINDISERROR
Get last line in cell
TRIMSUBSTITUTERIGHTREPTCHAR
Get last word
TRIMSUBSTITUTERIGHTREPT
Join cells with comma
TRIMSUBSTITUTETEXTJOIN
Most frequently occurring text
INDEXMATCHMODE
Normalize text
SUBSTITUTETRIMLOWER
Position of 2nd 3rd etc instance of character
SUBSTITUTEFIND
Remove characters from left
RIGHTLEN
Remove characters from right
LEFTLENVALUE
Remove file extension from filename
LEFTFIND
Remove leading and trailing spaces from text
CLEANTRIMSUBSTITUTE
Remove line breaks
SUBSTITUTECLEAN
Remove text by matching
SUBSTITUTE
Remove text by position
REPLACE
Remove text by variable position
REPLACEFIND
Remove unwanted characters
SUBSTITUTECHARCODELEFT
Replace one character with another
SUBSTITUTE
Split dimensions into three parts
LEFTRIGHTMIDLENSUBSTITUTE
Split dimensions into two parts
SUBSTITUTELEFTRIGHTFIND
Split numbers from units of measure
MAXLEFTRIGHTISNUMBERVALUEMID
Split text and numbers
FINDMINRIGHTLEFT
Split text string at specific character
LEFTRIGHTLENFIND
Split text with delimiter
TRIMMIDSUBSTITUTEREPTLEN
Strip html from text or numbers
MIDLEN
Strip non-numeric characters
TEXTJOINMIDROWINDIRECT
Strip numeric characters from cell
TEXTJOINMIDROWINDIRECT
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
CELLMIDFIND
Get workbook name and path without sheet
CELLLEFTFINDSUBSTITUTE
Get workbook name only
CELLMIDFIND
Get workbook path only
CELLLEFTFIND
Indirect named range different sheet
INDIRECT
List sheet index numbers
SHEET
Worksheet name exists
ISREFINDIRECT
Internet
Create email address from name
LEFTLOWER
Create email address with name and domain
LEFTLOWER
Get domain from email address
RIGHTLENFIND
Get domain name from URL
LEFTFIND
Get name from email address
LEFTFIND
Get page from URL
TRIMRIGHTSUBSTITUTEREPT
Get top level domain (TLD)
RIGHTLENSUBSTITUTEFIND
Remove trailing slash from url
LENRIGHTLEFT
Strip protocol and trailing slash from URL
MIDRIGHTLENFIND
Names
Get first name from name
LEFTFIND
Get first name from name with comma
RIGHTLENFIND
Get last name from name
RIGHTLENSUBSTITUTEFIND
Get last name from name with comma
LENLEFT
Get middle name from full name
MIDLENTRIM
Join first and last name
CONCATENATE
Put names into proper case
PROPERTRIM
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
Random
Random date between two dates
RANDBETWEENWORKDAY
Random number between two numbers
RANDBETWEEN
Random number from fixed set of options
CHOOSERANDBETWEEN
Random number weighted probability
RANDMATCHINDEX
Random text values
CHOOSERANDBETWEEN
Random times at specific intervals
RAND
Random value from list or table
INDEXRANDBETWEENROWS
Randomly assign data to groups
RANDBETWEENCHOOSE
Randomly assign people to groups
RANDROUNDUPCEILING
Range
Address of first cell in range
ADDRESSROWCOLUMNCELL
Address of last cell in range
ADDRESSROWCOLUMNROWSCOLUMNS
All cells in range are blank
SUMPRODUCT
All values in a range are at least
COUNTIFNOT
Dynamic named range with INDEX
INDEXCOUNTA
Dynamic named range with OFFSET
OFFSETCOUNTA
First column number in range
ROWSMIN
First match between two ranges
INDEXMATCHCOUNTIF
First row number in range
ROWMIN
Get address of named range
ADDRESSROWCOLUMNROWSCOLUMNS
Get relative column numbers in range
COLUMN
Get relative row numbers in range
ROW
Last column number in range
COLUMNCOLUMNSMIN
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
MATCHREPT
Last row number in range
ROWROWSMIN
Multiple cells are equal
ANDEXACT
Multiple cells have same value
COUNTIF
Multiple cells have same value case sensitive
EXACTSUMPRODUCTCOUNTA
Range contains a value not in another range
SUMPRODUCTMATCHISNA
Range contains numbers
SUMPRODUCTISNUMBER
Total cells in a range
ROWSCOLUMNS
Total columns in range
COLUMNS
Total rows in range
ROWS
Tables
Average last N values in a table
INDEXAVERAGEROWS
Basic inventory formula example
SUMIFS
Count table columns
COLUMNS
Count table rows
ROWS
COUNTIFS with variable table column
COUNTIFSINDEXMATCHINDIRECT
Dynamic reference Table name
INDIRECT
Get column index in Excel Table
MATCH
Get column name from index in table
INDEX
Percentile IF in table
PERCENTILE
Running total in Table
INDEXSUM
SUMIFS with Excel Table
SUMIFS
Two-way lookup VLOOKUP in a Table
VLOOKUPMATCH
Miscellaneous
Abbreviate state names
VLOOKUPINDEXMATCH
Basic attendance tracking formula
COUNTIF
Basic error trapping example
IFERROR
Basic in cell histogram
REPTCHAR
Build hyperlink with VLOOKUP
HYPERLINKVLOOKUP
Calculate a ratio from two numbers
GCD
Cap percentage at specific amount
MIN
Cash denomination calculator
SUMPRODUCTINTFLOOR
Change negative numbers to positive
ABS
Check register balance
IFANDISBLANK
Convert column letter to number
COLUMNINDIRECT
Convert column number to letter
ADDRESSSUBSTITUTE
Copy value from every nth column
OFFSETCOLUMN
Copy value from every nth row
OFFSETROW
Count consecutive monthly orders
FREQUENCYMAXIF
Count with repeating values
ROUNDUPCOLUMNROW
Course completion status summary
COUNTIFS
Course completion summary with criteria
COUNTIFS
Create array of numbers
INDIRECTROW
Cube root of number
POWER
Easy bundle pricing with SUMPRODUCT
SUMPRODUCT
Extract unique items from a list
INDEXMATCHCOUNTIFLOOKUP
Filter values in array formula
ISNUMBERMATCH
Fixed value every N months
MODCOLUMN
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
INDEXMATCHTEXT
Get last entry by month and year
LOOKUPTEXT
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
HYPERLINKCELLINDEXMATCH
Hyperlink to first match
HYPERLINKCELLINDEXMATCH
Increment a calculation with ROW or COLUMN
ROWCOLUMN
Increment a number in a text string
RIGHTTEXT
Increment cell reference with INDIRECT
INDIRECTCELL
Issue response time calculation
VLOOKUP
Leave a comment in a formula
N
List contains duplicates
SUMPRODUCTCOUNTIF
List most frequently occuring numbers
MODEISNUMBERMATCH
Longest winning streak
FREQUENCYMAXIF
Lookup last file revision
SEARCHISERRORMAXINDEXIFROW
Most frequently occurring number
MODE
Multiplication table formula
Normalize size units to Gigabytes
MATCHLEFTRIGHT
nth root of number
POWER
One or the other not both
XOR
Pad a number with zeros
TEXTREPT
Range contains one of many substrings
SUMPRODUCTCOUNTIF
Range contains one of many values
SUMPRODUCTISNUMBERSEARCH
Range contains specific text
COUNTIF
Reverse a list or range
INDEXCOUNTAROW
Search entire worksheet for value
COUNTIF
Search multiple worksheets for value
COUNTIFINDIRECT
Simple currency conversion
VLOOKUPINDEXMATCH
Split payment across months
AND
Square root of number
SQRTPOWER
Student class enrollment with table
IFCOUNTIF
Sum every 3 cells
OFFSETCOLUMN
Transpose table without zeros
TRANSPOSEIF
Validate input with check mark
IFCOUNTIF
Value exists in a range
COUNTIFMATCH
Value is between two numbers
ANDMAXMIN
Value is within tolerance
IFABS
Volunteer hours requirement calculation
ANDCOUNTIFSUM

Related Blogs

Ratings & Review

Send Business Query

×

Message*

Name*

Phone

Email*

Text within image*

Close

Post Review

×

Uh oh! You must log in to submit a review. Click Here to Login.

Close

Send Business Info via SMS

×

Please fill below given details

Mobile*

Text within image*

Close

Send Business Info via Email

×

Please fill below given details

Email To*

Email From*

Name*

Text within image*

Close