### 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 |