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 |