Formulas

Here you will find an exhaustive resource of formula examples for Excel. Each example title is clickable to a more complete description and the types of formulas are separated by type headings.



Count Formula Examples


Formula Associated Functions
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 MATCH ISNA 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 COUNTBLANK COUNTA
Count cells that are not blank COUNTBLANK COUNTA
Count cells that begin with COUNTIF
Count cells that contain either x or y COUNTIF SUMPRODUCT ISNUMBER FIND
Count cells that contain errors SUMPRODUCT ISERROR ISERR
Count cells that contain five characters COUNTIF
Count cells that contain negative characters 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 SUMPRODUCT ISTEXT COUNTIFS
Count cells that do not contain COUNTIF
Count cells that do not contain errors SUMPRODUCT ISERROR NOT
Count cells that do not contain many strings TRANSPOSE MMULT SEARCH ISNUMBER
Count cells that end with COUNTIF
Count dates by day of the week WEEKDAY SUMPRODUCT
Count dates in a given year YEAR SUMPRODUCT
Count if row meets internal criteria SUMPRODUCT
Count if row meets multiple internal criteria SUMPRODUCT
Count if two criteria match SUMPRODUCT COUNTIFS
Count items in list COUNTIFS
Count long numbers without COUNTIF SUMPRODUCT COUNTIFS
Count matches between two columns SUMPRODUCT
Count multiple criteria with NOT logic SUMPRODUCT ISNA MATCH
Count numbers by range with COUNTIFS COUNTIFS
Count numbers that begin with LEFT SUMPRODUCT
Count numbers third digit equals 5 MID SUMPRODUCT
Count occurrences in entire workbook COUNTIF SUMPRODUCT
Count paired items in listed combinations COUNTIFS CONCAT
Count rows that contain specific values TRANSPOSE COLUMN MMULT
Count rows with at least N matching values TRANSPOSE COLUMN MMULT
Count rows with multiple OR criteria SUMPRODUCT
Count sold and remaining COUNTA
Count total matches in two ranges COUNTIF SUMPRODUCT
Count unique numeric values in a range COUNTIF SUM FREQUENCY
Count unique numeric values with criteria SUM FREQUENCY
Count unique text values in a range MATCH FREQUENCY SUMPRODUCT ROW
Count unique text values with criteria MATCH FREQUENCY SUM ROW
Count unique values in range with COUNTIF COUNTIF SUMPRODUCT
Count visible rows in filtered list SUBTOTAL
Count visible rows only with criteria SUBTOTAL SUMPRODUCT OFFSET
COUNTIF with non-contiguous range COUNTIF INDIRECT
COUNTIFS with multiple criteria and OR logic 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 Formula Examples


Formula Associated Functions
3D sum multiple worksheets SUM
3D SUMIF multiple worksheets SUMIF SUMPRODUCT INDIRECT
Calculate running total SUM
Subtotal by color SUMIF
Subtotal by invoice number SUMIF COUNTIF
Subtotal invoice by age SUMIF
Sum 2D range with multiple criteria SUMPRODUCT
Sum bottom N values SUMPRODUCT INDIRECT ROW SMALL SUM
Sum bottom N values with criteria SMALL SUM
Sum by group SUMIF
Sum by month SUMIFS EOMONTH
Sum by month and ignore the year SUMPRODUCT MONTH
Sum by week SUMIFS
Sum by the week number SUMIFS WEEKNUM
Sum by the 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 MOD COLUMN
Sum formulas only SUMPRODUCT ISFORMULA NOT
Sum if begins with SUMIF SUMIFS
Sum if between SUMIFS
Sum if by year SUMIFS DATE
Sum if cells contain text in another cell SUMIFS SUMIF
Sum if cells are equal to SUMIFS SUMIF
Sum if cells are not equal to SUMIFS SUMIF
Sum if cells contain an asterisk SUMIFS SUMIF
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 SUMIFS SUMIF
Sum if date is between SUMIFS DATE
Sum if date is greater than SUMIFS DATE SUMIF
Sum if ends with SUMIFS SUMIF
Sum if equal to either X or Y SUMPRODUCT SUMIF
Sum if equal to one of many criteria SUMPRODUCT SUMIF
Sum if greater than SUMIFS SUMIF
Sum if less than SUMIFS SUMIF
Sum if multiple criteria SUMIFS
Sum if not blank SUMIFS SUMIF
Sum if one criteria and multiple columns SUMPRODUCT
Sum the last N columns COLUMN SUM INDEX
Sum matching columns SUMPRODUCT LEFT
Sum sales in the last 30 days using ID SUMIFS TODAY
Sum through N months SUM OFFSET
Sum top N values SUM LARGE SUMPRODUCT ROW INDIRECT
Sum top N values with given criteria LARGE SUMPRODUCT
SUMPRODUCT with IF SUMPRODUCT
SUMIFS with multiple criteria and OR logic SUM SUMIFS
Sum visible rows in a filtered list SUBTOTAL


Average Formula Examples


Formula Associated Functions
Weighted average SUM SUMPRODUCT
Average top three scores LARGE AVERAGE
Average the last three numeric values LARGE AVERAGE ROW LOOKUP
Average response time per month EOMONTH AVERAGEIFS
Average numbers ignore zero AVERAGEIF
Average numbers AVERAGE
Average last five values in a column AVERAGE OFFSET COUNT
Average last five values AVERAGE OFFSET COUNT
Average by month EOMONTH AVERAGEIFS
Average and ignore errors AGGREGATE AVERAGEIF


Min & Max Formula Examples


Formula Associated Functions
Smaller of two values MIN
Nth smallest value with specific criteria SMALL
Nth smallest value SMALL
Nth largest value with duplicates MAX IF
Nth largest value with criteria LARGE
Nth largest value LARGE
Minimum value if MIN IF MINIFS
Minimum value MIN
Minimum value if multiple criteria MIN IF MINIFS
Maximum value if MAX IF MAXIFS
Maximum value MAX MAXIFS
Maximum value if multiple criteria MAX IF MAXIFS
Maximum value and ignore all errors AGGREGATE MAXIFS
Maximum of every Nth column MOD MAX COLUMN
Larger of two values MAX
Large and including criteria LARGE
First in, last out times MAX MAXIFS IF MINIFS


If Formula Examples


Formula Associated Functions
Force negative numbers to zero MAX
If cells begin with X, Y, or Z SUM COUNTIF
If cells contain IF
If cells contain one thing or another SUM COUNTIF
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
If using boolean logic IF
If using wildcards IF
Invoice status using nested IF IF TODAY
Nested IF example IF
Nested IF example with multiple AND IF AND OR
Return blank if IF ISBLANK COUNTBLANK
Tax rate calculation using a fixed base IF
Win/Loss points calculation IF VLOOKUP


Grouping Formula Examples


Formula Associated Functions
Running count group by N size COUNTA CEILING
Map text to numbers VLOOKUP
Map inputs to arbitrary values VLOOKUP CHOOSE
Group times into unequal blocks VLOOKUP
If cell contains one of many things INDEX MATCH SEARCH ISNUMBER
Group times into 3 hour blocks FLOOR
Group numbers using VLOOKUP VLOOKUP
Group numbers using uneven intervals LOOKUP
Group arbitrary text values VLOOKUP
Categorize text with keywords INDEX MATCH SEARCH ISNUMBER


Conditional Formatting Formula Examples


Formula Associated Functions
Highlight 5 smallest values with criteria SMALL AND
Conditional formatting column is blank NOT AND OR
Conditional formatting date past due TODAY
Conditional formatting date overlaps SUMPRODUCT
Conditional formatting Gantt chart AND
Conditional formatting Gantt chart weekends WEEKDAY
Find duplicate values in two columns AND COUNTIF
Highlight approximate match lookup conditional formatting AND LOOKUP OR
Highlight blank cells LEN ISBLANK
Highlight bottom values SMALL
Highlight cells that begin with IFERROR COUNTIF FIND
Highlight cells that contain ISNUMBER SEARCH FIND
Highlight cells that contain one of many ISNUMBER SEARCH FIND SUMPRODUCT
Highlight cells that end with RIGHT EXACT LEN COUNTIF
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 the 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 COUNTIF SUMPRODUCT
Highlight duplicate rows COUNTIF SUMPRODUCT COUNTIFS
Highlight duplicate values COUNTIF
Highlight every other row ISEVEN ISODD MOD ROW
Highlight integers only MOD
Highlight missing values COUNTIF
Highlight multiples of a specific value MOD
Highlight numbers that include symbols MID ISNUMBER
Highlight row & column intersection with exact match AND OR
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 not between X and Y AND NOT
Shade alternating groups of N rows ODD ROW ISEVEN CEILING


Rank Formula Examples


Formula Associated Functions
Rank function RANK
Rank if formula RANK COUNTIFS
Rank race results RANK
Rank without ties RANK COUNTIF
Rank with ordinal suffix ABS CHOOSE MOD


Date Series Formula Examples


Formula Associated Functions
Series of dates by month DAY EOMONTH MONTH DATE YEAR
Series of dates by weekends WEEKDAY IF
Series of dates by workdays WEEKDAY IF WORKDAY NETWORKDAYS.INTL
Series of dates by year DAY MONTH DATE YEAR


Lookup Formula Examples


Formula Associated Functions
Approximate match with multiple criteria INDEX MATCH IF
Basic Index Match approximate INDEX MATCH
Basic Index Match exact INDEX MATCH
Basic tax rate calculation using VLOOKUP VLOOKUP
Break ties with helper column and COUNTIF SMALL INDEX MATCH
Use VLOOKUP to calculate grades VLOOKUP
Use VLOOKUP to calculate shipping costs VLOOKUP
Case sensitive match EXACT MATCH
Count missing values ISNA MATCH COUNTIF SUMPRODUCT
INDIRECT with dynamic lookup table INDIRECT VLOOKUP
INDIRECT and MATCH with exact match lookup MATCH EXACT INDEX
SUMPRODUCT and exact match lookup SUMPRODUCT EXACT
Extract all partial matches ISNUMBER AGGREGATE INDEX SEARCH
Use helper column to extract data AND MATCH INDEX SUM
Extract multiple matches into separate columns SMALL IFERROR INDEX ROW COLUMNS
Extract multiple matches into separate rows SMALL IFERROR INDEX ROW
Faster VLOOKUP using 2 VLOOKUPS NA VLOOKUP
Find closest match ABS INDEX MIN MATCH
Find longest string in a column MAX INDEX LEN MATCH
Find longest string with specific criteria MAX INDEX LEN MATCH
Find lowest N values SMALL INDEX MATCH
Find missing values VLOOKUP COUNTIF MATCH
First match in range with wildcard INDEX MATCH
Return address of lookup result INDEX MATCH CELL
Return cell content at given row and column INDEX ADDRESS INDIRECT
Use VLOOKUP to return employee information VLOOKUP
Return first cell match cell contains INDEX MATCH ISNUMBER SEARCH
Return first non-blank value in a list INDEX MATCH ISBLANK
Return first text value in a list VLOOKUP
Return first text value using HLOOKUP HLOOKUP
Return information corresponding to max value MAX INDEX MATCH
Return last match MAX INDEX MATCH ROW
Return last match cell contains LOOKUP SEARCH
Return location of value in 2D array SUMPRODUCT ROW COLUMN
Return the Nth match SMALL ROW IF MIN
Return the Nth match using INDEX and MATCH SMALL INDEX
Return the Nth match using VLOOKUP VLOOKUP COUNTIF
INDEX and MATCH in descending order INDEX MATCH
INDEX and MATCH on multiple columns INDEX TRANSPOSE MMULT COLUMN
INDEX and MATCH using multiple criteria INDEX MATCH
INDEX and MATCH to join tables 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 last price LOOKUP
Lookup lowest value INDEX MATCH MIN
Lookup cost for product or service VLOOKUP
Lookup value between two numbers LOOKUP
Lookup with variable sheet name VLOOKUP INDIRECT
Match first does not begin with INDEX MATCH LEFT
Match first error ISERROR MATCH
Match first occurrence does not contain INDEX MATCH SEARCH ISNUMBER
Match the next highest value INDEX MATCH
Max if criteria match IF MAX
VLOOKUP to merge tables VLOOKUP
Multi-criteria lookup and transpose INDEX MATCH
Multiple chained VLOOKUPs VLOOKUP IFERROR
Multiple matches in comma separated list TEXTJOIN
MATCH to find next largest match INDEX MATCH
Partial match against numbers using wildcards TEXT MATCH
Partial match using VLOOKUP VLOOKUP
Return position of first partial match INDEX MATCH
Return position of max value in a list MAX MATCH
Self-contained VLOOKUP VLOOKUP
VLOOKUP without #N/A error VLOOKUP IFERROR IFNA
Find two client rates with VLOOKUP VLOOKUP
Using VLOOKUP with numbers and text VLOOKUP
Using VLOOKUP with multiple criteria VLOOKUP
Using VLOOKUP with two lookup tables VLOOKUP
Using VLOOKUP from another workbook VLOOKUP
Using VLOOKUP from another sheet VLOOKUP
Using VLOOKUP by date VLOOKUP
Two-way VLOOKUP VLOOKUP MATCH
Two-way lookup with INDEX and MATCH INDEX MATCH
Multiple criteria lookup in a table with SUMIFS SUMIFS
Use INDEX to sum range SUM INDEX
Use SUMIF to sum lookup values SUMIF SUMPRODUCT


Data Validation Formula Examples


Formula Associated Functions
Allow numbers only ISNUMBER
Allow text only ISTEXT
Allow uppercase only UPPER EXACT AND
Allow weekday only YEAR TODAY
Date in next 30 days AND TODAY
Date in specific year YEAR TODAY
Don't exceed total SUM
Exists in list COUNTIF
Must begin with COUNTIF LEFT EXACT
Must contain specific text FIND ISNUMBER
Must not contain SEARCH ISNUMBER SUMPRODUCT
Must not exist in list COUNTIF
No punctuation COUNT FIND
Number multiple 100 MOD
Only dates between AND DATE
Require unique number AND ISNUMBER COUNTIF
Specific characters only MATCH LEN COUNT MID INDIRECT
Unique values only COUNTIF
Data validation used with conditional list IF
Whole percentage only TRUNC AND


Round Formula Examples


Formula Associated Functions
Return decimal part of number TRUNC
Return integer part of number INT TRUNC
Return number at place value MOD
Round a number ROUND
Round a number down ROUNDDOWN
Round a number down to the 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 the nearest multiple CEILING
Round a number up to the next half CEILING
Round price to end in .99 ROUND
Round by the bundle size CEILING
Round the time to nearest 15 minutes CEILING MROUND FLOOR
Round to the nearest 1,000 ROUND
Round to the nearest 5 MROUND CEILING FLOOR


Date and Time Formula Examples


Formula Associated Functions
Adding business days to dates WORKDAY NETWORKDAYS
Adding days and exclude certain days of the week WORKDAY.INTL
Adding days to a date DAY
Adding decimal hours to a time MOD TIME
Adding decimal minutes to a time MOD TIME
Adding months to a date EDATE
Adding workdays and no weekends WORKDAY.INTL NETWORKDAYS
Adding workdays to a date with custom weekends WORKDAY.INTL NETWORKDAYS
Adding years to a date DAY MONTH YEAR DATE
Assigning points based on late time IF VALUE
Basic overtime calculation MIN
Basic time sheet with breaks MOD
Calculating date overlap in days MIN MAX
Calculating days remaining TODAY
Calculating the expiration date EOMONTH EDATE
Calculating the number of hours between two times IF MOD
Calculating retirement date EOMONTH EDATE
Calculating the number of years between dates INT YEARFRAC
Converting a date string to date time DATEVALUE LEFT MID TIMEVALUE
Converting a date to Julian format DATE YEAR TEXT
Converting a date to month and year TEXT
Converting a date to text TEXT
Converting Excel time to Unix time DATE
Converting text date to in dd/mm/yy to mm/dd/yy DATE LEFT MID RIGHT TRIM
Converting text timestamp into time MID TIME
Converting text into date DATE LEFT MID RIGHT
Converting time into time zone MOD
Converting Unix time stamp into an Excel date DATE
Counting birthdays by month MONTH SUMPRODUCT
Counting dates in the current month N SUMPRODUCT EOMONTH COUNTIFS
Counting days of week between dates WEEKDAY SUMPRODUCT ROW INDIRECT
Counting holidays between dates SUMPRODUCT
Counting times in a specific range TIME COUNTIFS
Creating a date range from two dates TEXT IF
Creating a weekday abbreviation WEEKDAY CHOOSE
Date is the same month MONTH
Date is the same month and year MONTH YEAR
Date is a workday WORKDAY WORKDAY.INTL
Days in the month DAY EOMONTH
Display the current date TODAY
Display the current date and time NOW
Display dynamic calendar grid TODAY WEEKDAY CHOOSE
Display dynamic date list TODAY ROWS
Extract the date from date and time INT TRUNC
Extract the time from date and time MOD
Return age given birthday INT TODAY YEARFRAC
Return date given day number DATE RIGHT LEFT
Return day given date DAY
Return day name given date WEEKDAY CHOOSE
Return days before a date TODAY
Return days between dates TODAY DAYS
Return days between dates ignoring years DATEDIF
Return days, hours, and minutes between dates INT TEXT
Return days, months, and years between dates DATEDIF
Return first day of the month DAY EOMONTH
Return first day of the previous month EOMONTH
Return first Monday before any date WEEKDAY
Return fiscal quarter from a date MONTH CHOOSE
Return fiscal year from a date MONTH YEAR
Return last day of the month EOMONTH DATE
Return last weekday of the month EOMONTH WEEKDAY
Return last workday of the month EOMONTH WEEKDAY
Return month from the date MONTH DATE
Return month name from the date MONTH TEXT CHOOSE
Return months between dates DATEDIF
Return most recent day of the week MOD
Return the next day of the week WEEKDAY
Return the next scheduled event MIN IF MINIFS INDEX MATCH
Return the Nth day of the week in month WEEKDAY DAY
Return the Nth day of the year DATE YEAR
Return percent of the completed year DATE YEAR YEARFRAC
Return project end date WORKDAY WORKDAY.INTL
Return project midpoint date WORKDAY WORKDAY.INTL
Return project start date WORKDAY WORKDAY.INTL
Return quarter from date MONTH ROUNDUP
Return same date in the next month EDATE
Return same date in the next year EDATE
Return week number from a date WEEKNUM ISOWEEKNUM
Return hours worked between dates NETWORKDAY NETWORKDAY.INTL
Return hours worked between dates and times NETWORKDAY NETWORKDAY.INTL
Return hours worked between dates with custom schedule MID ROW INDIRECT WEEKDAY SUMPRODUCT
Return workdays between dates NETWORKDAY NETWORKDAY.INTL
Return year from date YEAR
If a specific day, roll back to another day WEEKDAY IF
Join date and text TEXT
Last updated date stamp TEXT
Return holidays between two dates TEXTJOIN IF
Return next anniversary date EDATE DATEDIF
Return next biweekly payday from a date CEILING
Return next business day N months in the future WORKDAY WORKDAY.INTL
Return next working day WORKDAY WORKDAY.INTL
Pad week numbers with zeros TEXT WEEKNUM
Sum race time splits SUM
Sum time over N minutes SUMPRODUCT TIME SUMIFS COUNTIFS
Return time difference in hours as a decimal value MOD
Return total hours that fall between two times MIN MAX
Determine if year is a leap year DAY MONTH YEAR
Return working days left in a month NETWORKDAYS NETWORKDAYS.INTL TODAY
Return working days in a month NETWORKDAYS NETWORKDAYS.INTL EOMONTH


Error Formula Examples


Formula Associated Functions
Fixing the #DIV/0! error IF IFERROR
Fixing the #N/A error VLOOKUP IFERROR MATCH
Fixing the #NAME? error IF IFERROR
Fixing the #REF! error ISREF IFERROR


Names Formula Examples


Formula Associated Functions
Return first name from a given name LEFT FIND
Return first name from a given name with comma RIGHT FIND LEN
Return last name from a given name RIGHT FIND LEN SUBSTITUTE
Return last name from a given name with comma LEN LEFT
Return middle name from a given name LEN MID TRIM
Join the first and last name CONCATENATE
Return names in proper case PROPER TRIM


Internet Formula Examples


Formula Associated Functions
Create an email address from a name LEFT LOWER
Create an email address with a name and domain LEFT LOWER
Return a domain from an email address RIGHT LEN FIND
Return a domain from an email URL LEFT FIND
Return a name from an email address LEFT FIND
Return a page from a URL RIGHT TRIM REPT SUBSTITUTE
Return a top level domain (TLD) RIGHT LEN FIND SUBSTITUTE
Remove the trailing slase from a URL RIGHT LEN LEFT
Remove the protocol and trailing slash from URL RIGHT LEN FIND MID


Workbook Formula Examples


Formula Associated Functions
Dynamic workbook reference INDIRECT
Dynamic worksheet reference INDIRECT
Return full workbook name and path CELL
Return sheet name only CELL FIND MID
Return full workbook name and path without sheet CELL FIND LEFT SUBSTITUTE
Return workbook name only CELL FIND MID
Return workbook path only CELL FIND LEFT
Indirect named range on a different sheet INDIRECT
List the sheet index numbers SHEET
List the sheet name with formulas INDEX MID ROW NOW T
Test if a worksheet name exists in a workbook INDIRECT ISREF


Tables Formula Examples


Formula Associated Functions
Two-way lookup VLOOKUP in a table VLOOKUP MATCH
Using SUMIFS with an Excel table SUMIFS
Summing multiple tables in Excel SUM
Running a total in Excel tables SUM INDEX
Percentile IF in Excel tables PERCENTILE
Return the column name from an index in an Excel table INDEX
Return the column index in an Excel table MATCH
Dynamic reference table name INDIRECT
Using COUNTIFS with variable table column COUNTIFS INDEX MATCH INDIRECT
Count table rows ROWS
Count table columns COLUMNS
Basic inventory formula example SUMIFS
Average the last N values in a table AVERAGE INDEX ROWS


Financial Formula Examples


Formula Associated Functions
Annual compound interest schedule FV
Find annuity interest rate RATE
Bond valuations PRICE FV PV
Compound Annual Growth Rate (CAGR) RRI GEOMEAN
Calculating compound interest FV
Calculating cumulative loan interest CUMIPMT
Calculating cumulative loan principal CUMPRINC
Calculating interest for a given period PPMT
Calculating interest rate for a loan RATE
Calculating loan interest for a given year CUMIPMT
Calculating the original loan amount PV
Calculating a loan payment PMT
Calculating the payment periods for a loan NPER
Calculating the periods for an annuity PMT PV FV
Calculating the principal for a given period PPMT
Calculating the future value of an annuity PV FV
Calculating future value and present value PV FV
Using NPV to find the net present value NPV
Calculating an annuity payment PV PMT FV
Calculating the present value of an annuity PV FV


Percentage Formula Examples


Formula Associated Functions
Calculate the percentage variance ABS
Project the complete percentage COUNTA
Decrease number by given percentage = number * (1 - percent)
Increase number by given percentage = number * (1 + percent)
Calculate amount of total represented by a percentage = total * percent
Calculate original number given current value and percentage change = current number / (percent + 1)
Calculate original price given discount price and discount percentage = price / (1 - discount)
Find percentage change = (new value - old value) / old value
Find percentage discount = 1 - (discount price / original price)
Find percentage of the total = amount / total
Find profit margin percentage = (price - cost) / price
Return total from percentage = amount / percent
Find the percent of a goal = actual / goal
Find the percent of goods sold = sold / total


Random Formula Examples


Formula Associated Functions
Generate random date between two dates RANDBETWEEN WORKDAY
Generate random number between two numbers RANDBETWEEN
Generate random number from fixed set of options RANDBETWEEN CHOOSE
Generate random number weighted probability RAND MATCH INDEX
Generate random text values RANDBETWEEN CHOOSE
Generate random times at specified intervals RAND
Generate random values from a list or table INDEX RANDBETWEEN ROWS
Randomly assign data to groups RANDBETWEEN CHOOSE
Randomly assign people to groups RAND ROUNDUP CEILING


Range Formula Examples


Formula Associated Functions
Count rows in a range ROWS
Count columns in a range COLUMNS
Count cells in a range COLUMNS ROWS
Add sequential row numbers to data set ROWS
Test a range for numbers SUMPRODUCT ISNUMBER
Test if a range contains a value not in another range SUMPRODUCT ISNA MATCH
Test multiple cells have same value (case sensitive) SUMPRODUCT COUNTA EXACT
Test multiple cells have same value COUNTIF
Test multiple cells are equal AND EXACT
Return last row number in a range ROW ROWS MIN
Return last row in text data MATCH REPT
Return last row in numeric data MATCH
Return last row in mixed data (no blanks) COUNTA
Return last row in mixed data (with blanks) MATCH
Return last column number in a range COLUMN COLUMNS MIN
Return relative row numbers in a range ROW
Return relative column numbers in a range COLUMN
Return address of named range COLUMN COLUMNS ROW ROWS ADDRESS
Return first row number in a range ROW MIN
Return first match between two ranges INDEX MATCH COUNTIF
Return first column number in a range ROWS MIN
Creating a dynamic named range with OFFSET OFFSET COUNTA
Creating a dynamic named range with INDEX INDEX COUNTA
Test if values in range meet specific threshold NOT COUNTIF
Test if all cells in a range are blank SUMPRODUCT
Return address of last cell in a named range ADDRESS COLUMN COLUMNS ROW ROWS
Return address of first cell in a named range ADDRESS COLUMN CELL ROW


Text Formula Examples


Formula Associated Functions
Abbreviate names or words TEXTJOIN MID ROW CODE LEN INDIRECT
Add a line break with a formula CHAR
Add a line break based on OS CHAR INFO
Capitalize the first letter LEFT MID LEN
Cell contains all items in a list SUMPRODUCT ISNUMBER SEARCH COUNTA
Check if cell contains a number FIND COUNT
Check if cell contains one of many SUMPRODUCT ISNUMBER SEARCH
Check if cell contains one of many with exclusions SUMPRODUCT ISNUMBER SEARCH
Check if cell contains some words but not others AND COUNT SEARCH
Check if cell contains specific text FIND ISNUMBER SEARCH
Check if cell contains items from list FIND ISNUMBER SEARCH
Check if cell equals one of many things SUMPRODUCT
Clear and reformat a telephone number SUBSTITUTE
Compare two strings EXACT
Conditional messages with the REPT function REPT AND IF
Convert numbers to text TEXT
Convert a string to an array MID ROW LEN INDIRECT
Convert text to numbers VALUE LEFT RIGHT
Count keywords contained in a cell SEARCH MATCH ISNUMBER SUMPRODUCT
Count line breaks contained in a cell LEN SUBSTITUTE CHAR ISBLANK
Count specific characters contained in a cell LEN SUBSTITUTE UPPER
Count specific characters contained in a range LEN SUBSTITUTE UPPER SUMPRODUCT
Count specific words contained in a cell LEN SUBSTITUTE UPPER
Count specific words contained 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 LEN SUBSTITUTE TRIM SUMPRODUCT
Add double quotes within a formula CHAR
Extract the last two words from a cell FIND SUBSTITUTE LEN MID
Extract multiple lines from a cell TRIM SUBSTITUTE LEN MID REPT
Extract the Nth word from a text string TRIM SUBSTITUTE LEN MID REPT
Extract a substring MID
Extract text between parentheses MID SEARCH
Extract word containing specific text MID MAX FIND SUBSTITUTE REPT TRIM
Extract word starting with specific character MID LEN FIND SUBSTITUTE REPT TRIM
Find and replace multiple values SUBSTITUTE INDEX
Find the Nth occurrence of a character SUBSTITUTE FIND CHAR
Return the first word LEFT FIND ISERROR
Return the last line in a cell TRIM SUBSTITUTE RIGHT REPT CHAR
Return the last word TRIM SUBSTITUTE RIGHT REPT
Join cells with a comma TRIM SUBSTITUTE TEXTJOIN
Return the most frequent text with criteria INDEX MATCH MODE IF
Return the most frequently occurring text INDEX MATCH MODE
Normalize text TRIM SUBSTITUTE LOWER
Pad text to equal length REPT LEN
Return position of 2nd, 3rd, etc. instance of a character FIND SUBSTITUTE
Remove characters from the right LEFT LEN VALUE
Remove file extension from file name LEFT FIND
Remove the first character LEFT REPLACE LEN RIGHT
Remove leading and trailing spaces from text CLEAN TRIM SUBSTITUTE
Remove line breaks CLEAN SUBSTITUTE
Remove text by matching SUBSTITUTE
Remove text by position REPLACE
Remove text by variable position REPLACE FIND
Remove unwanted characters CODE CHAR SUBSTITUTE LEFT
Replace one character with another SUBSTITUTE
Reverse a text string TEXTJOIN MID INDIRECT SEQUENCE
Split dimensions into 3 parts LEFT MID RIGHT SUBSTITUTE LEN
Split dimensions into 2 parts LEFT FIND RIGHT SUBSTITUTE
Split numbers from units of measure LEFT MAX RIGHT ISNUMBER MID VALUE
Split text and numbers LEFT MIN RIGHT FIND
Split text string at a specific character LEFT LEN RIGHT FIND
Split text with delimiter TRIM LEN MID SUBSTITUTE REPT
Remove HTML from text or numbers LEN MID
Remove non-numeric characters ROW INDIRECT MID TEXTJOIN
Remove numeric characters ROW INDIRECT MID TEXTJOIN
Convert letters to numbers VLOOKUP ROW T MID TEXTJOIN


Other Formula Examples


Formula Associated Functions
Abbreviate state names MATCH INDEX VLOOKUP
Basic array formula MAX MIN
Attendance tracking COUNTIF
Error trapping IFERROR
In-cell histogram CHAR REPT
Numeric sort COUNTIF RANK
Outline numbering COUNTA MID FIND LEN
Text sort COUNTIF RANK
Add hyperlink using VLOOKUP VLOOKUP HYPERLINK
Calculate a ratio given two numbers GCD
Cap a percentage MIN
Cash denomination calculator FLOOR INT SUMPRODUCT
Change negative to positive using absolute value ABS
Check register balance IF AND ISBLANK
Conditional mode with specific criteria MODE
Convert column letter to a number COLUMN INDIRECT
Convert column number to a letter ADDRESS SUBSTITUTE
Convert feet and inches to inches LEFT MID SUBSTITUTE FIND
Convert inches to feet and inches INT MODE
Copy the value from every Nth column COLUMN OFFSET
Copy the value from every Nth row ROW OFFSET
Count consecutive monthly orders MAX FREQUENCY IF
Count values out of tolerance ABS SUMPRODUCT
Count with repeating values ROW COLUMN ROUNDUP
Completion status summary COUNTIFS
Completion status summary with criteria COUNTIFS
Create an array of numbers ROW INDIRECT
Return the cube root of numbers POWER
Display sorted values with the helper column ROWS MATCH INDEX
Bundle pricing with SUMPRODUCT SUMPRODUCT
Extract unique items from a list MATCH INDEX COUNTIF LOOKUP
Filter values in an array formula ISNUMBER MATCH
Filter and retrieve missing values ISNA MATCH INDEX
Fixed value for every N columns MOD COLUMN
Flag first duplicate in a list COUNTIF
Convert table rows to columns TRANSPOSE
Formula with locked reference INDIRECT
Get date associated with last entry LOOKUP
Return first entry by month and year INDEX TEXT MATCH
Return last entry by month and year LOOKUP TEXT
Return pivot table total GETPIVOTDATA
Return pivot table subtotal GETPIVOTDATA
Return pivot table subtotal grouped by date GETPIVOTDATA
Return value of last non-empty cell LOOKUP
Create hyperlink to first blank cell HYPERLINK CELL INDEX MATCH
Create hyperlink to first match HYPERLINK CELL INDEX MATCH
Use ROW or COLUMN to increment a calculation COLUMN ROW
Increment a number in a text string RIGHT TEXT
Use INDIRECT to increment a cell reference INDIRECT CELL
Response time calculation VLOOKUP
Comment in a formula N
List contains duplicates COUNTIF SUMPRODUCT
List most frequently occurring numbers MODE ISNUMBER MATCH
Identify longest winning streak FREQUENCY MAX IF
Find the last file revision ROW MAX IF INDEX ISERROR SEARCH
Find the most frequently occurring number MODE
Normalize size units to Gigabytes (GB) MATCH LEFT RIGHT
Find the Nth root of a number POWER
Find one or the other, but not both XOR
Pad a number with zeros TEXT REPT
Random sort INDEX MATCH
Test if range contains one of many substrings COUNTIF SUMPRODUCT
Test if range contains one of many values ISNUMBER SUMPRODUCT SEARCH
Test if range contains specific text COUNTIF
Repeat a fixed value every N months MOD DATEDIF
Return an array using INDEX INDEX MATCH N
Reverse a list or range INDEX COUNTA ROW
Risk matrix INDEX MATCH
Search entire worksheet for a value COUNTIF
Search multiple worksheets for a value COUNTIF INDIRECT
Currency conversion VLOOKUP INDEX MATCH
Sort and extract unique values TRANSPOSE MMULT INDEX MATCH
Sort numbers by ascending or descending SMALL LARGE
Sort text and numbers using a formula RANK COUNT COUNTIF
Split payments into months AND
Return the square root of a number SQRT POWER
Standard deviation STDEV STDEVP STDEV.P STDEV.S
Class enrollment with table IF COUNTIF
Sum every N cells OFFSET COLUMN
Sum text values as if they were numbers INDEX MATCH N
Transpose table without zeros TRANSPOSE IF
Use check mark to validate input COUNTIF IF
Confirm values exist in a range COUNTIF MATCH
Find if value is between two numbers AND MIN MAX
Find if value is within tolerance ABS IF
Required hours calculation AND COUNTIF SUM

Congrats! You've made it to the end of the Excel formula examples. If you found this helpful, we'd appreciate you sharing this page. If you're ready for a new challenge, check out our list of shortcuts for Mac and PC or master Excel's multiple functions.