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 |