Exceljet

Quick, clean, and to the point

500 Excel Formula Examples

Detailed formula examples for key functions, including VLOOKUP, INDEX, MATCH, RANK, SUMPRODUCT, AVERAGE, SMALL, LARGE, LOOKUP, ROUND, COUNTIFS, SUMIFS, CHOOSE, FIND, SEARCH, DATE, and many more.

Count

Count between dates by age range

Count cells between dates

Count cells between two numbers

Count cells equal to

Count cells equal to case sensitive

Count cells equal to either x or y

Count cells equal to one of many things

Count cells greater than

Count cells less than

Count cells not between two numbers

Count cells not equal to

Count cells not equal to many things

Count cells not equal to x or y

Count cells over 100 characters

Count cells that are blank

Count cells that are not blank

Count cells that begin with

Count cells that contain case sensitive

Count cells that contain either x or y

Count cells that contain errors

Count cells that contain five characters

Count cells that contain negative numbers

Count cells that contain numbers

Count cells that contain odd numbers

Count cells that contain positive numbers

Count cells that contain specific text

Count cells that contain text

Count cells that do not contain

Count cells that do not contain errors

Count cells that do not contain many strings

Count cells that end with

Count dates by day of week

Count dates in given year

Count if row meets internal criteria

Count if row meets multiple internal criteria

Count if two criteria match

Count items in list

Count long numbers without COUNTIF

Count matches between two columns

Count matching values in matching columns

Count multiple criteria with NOT logic

Count numbers by range with COUNTIFS

Count numbers that begin with

Count numbers third digit equals 3

Count occurrences in entire workbook

Count or sum variance

Count or sum whole numbers only

Count paired items in listed combinations

Count rows that contain specific values

Count rows with at least n matching values

Count rows with multiple OR criteria

Count rows with OR logic

Count sold and remaining

Count total matches in two ranges

Count unique dates

Count unique numeric values in a range

Count unique numeric values with criteria

Count unique text values in a range

Count unique text values with criteria

Count unique values in a range with COUNTIF

Count visible rows in a filtered list

Count visible rows only with criteria

COUNTIF with non-contiguous range

COUNTIFS with multiple criteria and OR logic

Histogram with FREQUENCY

Running count of occurrence in list

Summary count by month with COUNTIFS

Summary count of non-blank categories

Summary count with COUNTIF

Summary count with percentage breakdown

SUMPRODUCT count multiple OR criteria

Two-way summary count with COUNTIFS

Sum

3D sum multiple worksheets

3D SUMIF for multiple worksheets

Calculate running total

Subtotal by color

Subtotal by invoice number

Subtotal invoices by age

Sum and ignore errors

Sum bottom n values

Sum bottom n values with criteria

Sum by month ignore year

Sum by month in columns

Sum by week number

Sum columns based on adjacent criteria

Sum entire column

Sum every n rows

Sum every nth column

Sum first n matching values

Sum formulas only

Sum if begins with

Sum if cell contains text in another cell

Sum if cells are equal to

Sum if cells are not equal to

Sum if cells contain an asterisk

Sum if cells contain both x and y

Sum if cells contain either x or y

Sum if cells contain specific text

Sum if date is between

Sum if date is greater than

Sum if ends with

Sum if equal to either x or y

Sum if equal to one of many things

Sum if greater than

Sum if less than

Sum if multiple criteria

Sum if not blank

Sum if one criteria multiple columns

Sum last n columns

Sum matching columns

Sum matching columns and rows

Sum sales in last 30 days by ID

Sum through n months

Sum top n values

Sum top n values with criteria

Sum visible rows in a filtered list

SUMIFS with horizontal range

SUMIFS with multiple criteria and OR logic

SUMPRODUCT with IF

Average

Average and ignore errors

Average by month

Average if criteria not blank

Average last 5 values

Average last 5 values in columns

Average numbers

Average numbers ignore zero

Average pay per week

Average response time per month

Average the last 3 numeric values

Average top 3 scores

Average with multiple criteria

Moving average formula

Must pass 4 out of 6 subjects

Weighted average

Dynamic array

Basic filter example

Count unique values

Count unique values with criteria

Distinct values

Extract common values from two lists

Filter and transpose horizontal to vertical

Filter by column, sort by row

Filter contains one of many

Filter data between dates

Filter every nth row

Filter exclude blank values

Filter horizontal data

FILTER last n valid entries

FILTER on first or last n values

FILTER on top n values

FILTER on top n values with criteria

Filter on upcoming birthdays

Filter text contains

Filter this or that

Filter to extract matching values

FILTER to remove columns

FILTER with complex multiple criteria

Filter with exact match

FILTER with multiple OR criteria

FILTER with partial match

Generate random text strings

Get column totals

LAMBDA append range

LAMBDA append range horizontal

LAMBDA contains one of many

LAMBDA contains which things

LAMBDA count words

LAMBDA replace characters recursive

LAMBDA split text to array

LAMBDA strip characters

LAMBDA strip trailing characters recursive

Minimum value if unique

Random list of names

Sequence of days

Sequence of months

Sequence of times

Sequence of years

Sort birthdays by month and day

Sort by custom list

Sort by one column

Sort by two columns

Sort text by length

Sort values by columns

Unique values by count

Unique values ignore blanks

Unique values with criteria

Unique values with multiple criteria

UNIQUE with non-adjacent columns

XLOOKUP match any column

Min and Max

Cap percentage between 0 and 100

First in, last out times

Large with criteria

Larger of two values

Max of every nth column

Max value ignore all errors

Max value in given month

Max value on given weekday

Max value with variable column

Maximum if multiple criteria

Maximum value if

Minimum difference if not blank

Minimum if multiple criteria

Minimum value if

Name of nth largest value

Name of nth largest value with criteria

nth largest value

nth largest value with criteria

nth largest value with duplicates

nth smallest value

nth smallest value with criteria

Smaller of two values

Lookup

Approximate match with multiple criteria

Basic INDEX MATCH approximate

Basic INDEX MATCH exact

Basic Tax Rate calculation with VLOOKUP

Break ties with helper column and COUNTIF

Calculate grades with VLOOKUP

Calculate shipping cost with VLOOKUP

Case sensitive lookup

Commission split formula example

Count missing values

Exact match lookup with INDEX and MATCH

Exact match lookup with SUMPRODUCT

Extract all matches with helper column

Extract all partial matches

Extract multiple matches into separate columns

Extract multiple matches into separate rows

Faster VLOOKUP with 2 VLOOKUPS

Find closest match

Find longest string in column

Find longest string with criteria

Find lowest n values

Find missing values

First match in range with wildcard

Get address of lookup result

Get cell content at given row and column

Get employee information with VLOOKUP

Get first match cell contains

Get first non-blank value in a list

Get first text value in a list

Get first text value with HLOOKUP

Get information corresponding to max value

Get last match cell contains

Get location of value in 2D array

Get nth match with INDEX / MATCH

Get nth match with VLOOKUP

INDEX and MATCH descending order

Index and match on multiple columns

INDEX and MATCH with multiple criteria

INDEX with variable array

Join tables with INDEX and MATCH

Left lookup with INDEX and MATCH

Left lookup with VLOOKUP

Lookup and sum column

Lookup entire column

Lookup entire row

Lookup last file version

Lookup latest price

Lookup lowest Monday tide

Lookup lowest value

Lookup up cost for product or service

Lookup value between two numbers

Lookup with variable sheet name

Match first does not begin with

Match first error

Match first occurrence does not contain

Match long text

Match next highest value

Max if criteria match

Merge tables with VLOOKUP

Multi-criteria lookup and transpose

Multiple chained VLOOKUPs

Multiple matches in comma separated list

Nearest location with XMATCH

Next largest match with the MATCH function

Partial match with numbers and wildcard

Partial match with VLOOKUP

Position of first partial match

Position of max value in list

Reverse VLOOKUP example

Self-contained VLOOKUP

Sum lookup values using SUMIF

Sum range with INDEX

SUMIFS multiple criteria lookup in table

Two-way approximate match multiple criteria

Two-way lookup with INDEX and MATCH

Two-way lookup with VLOOKUP

VLOOKUP by date

VLOOKUP from another sheet

VLOOKUP from another workbook

VLOOKUP if blank return blank

VLOOKUP override output

VLOOKUP with 2 lookup tables

VLOOKUP with multiple criteria

VLOOKUP with numbers and text

VLOOKUP with two client rates

VLOOKUP with variable table array

VLOOKUP without #N/A error

XLOOKUP basic approximate match

XLOOKUP basic exact match

XLOOKUP case-sensitive

XLOOKUP date of max value

XLOOKUP horizontal lookup

XLOOKUP last match

XLOOKUP latest by date

XLOOKUP lookup left

XLOOKUP lookup row or column

XLOOKUP match text contains

XLOOKUP rearrange columns

XLOOKUP two-way exact match

XLOOKUP wildcard match example

XLOOKUP with boolean OR logic

XLOOKUP with complex multiple criteria

XLOOKUP with logical criteria

XLOOKUP with multiple criteria

XMATCH reverse search

Zodiac sign lookup

If

If cell begins with x, y, or z

If cell contains

if cell contains this or that

If cell is blank

If cell is greater than

If cell is not blank

If cell is this OR that

If cell is x or y and z

If else

If not blank multiple cells

If NOT this or that

If this AND that

If this AND that OR that

IF with boolean logic

IF with other calculations

IF with wildcards

Invoice status with nested if

Nested IF function example

Nested IF with multiple AND

Only calculate if not blank

Return blank if

Show checkmark if complete

Tax rate calculation with fixed base

Two-tier sales tax calculation

Win loss points calculation

Grouping

Categorize text with keywords

Group arbitrary text values

Group numbers at uneven intervals

Group numbers with VLOOKUP

Group times into 3 hour buckets

Group times into unequal buckets

If cell contains one of many things

Map inputs to arbitrary values

Map text to numbers

Running count group by n size

Conditional formatting

Highlight 3 smallest values with criteria

Conditional formatting based on another cell

Conditional formatting based on another column

Conditional formatting column is blank

Conditional formatting date past due

Conditional formatting dates overlap

Conditional formatting highlight target percentage

Conditional formatting last n rows

Find duplicate values in two columns

Gantt chart by week

Gantt chart time schedule

Gantt chart with weekends

Highlight approximate match lookup conditional formatting

Highlight blank cells

Highlight bottom values

Highlight cells that begin with

Highlight cells that contain

Highlight cells that contain one of many

Highlight cells that end with

Highlight cells that equal

Highlight column differences

Highlight data by quartile

Highlight dates between

Highlight dates greater than

Highlight dates in same month and year

Highlight dates in the next N days

Highlight dates that are weekends

Highlight duplicate columns

Highlight duplicate rows

Highlight duplicate values

Highlight entire rows

Highlight every other row

Highlight integers only

Highlight many matching values

Highlight missing values

Highlight multiples of specific value

Highlight numbers that include symbols

Highlight row and column intersection exact match

Highlight rows that contain

Highlight rows with blank cells

Highlight rows with dates between

Highlight top values

Highlight unique values

Highlight unprotected cells

Highlight values between

Highlight values greater than

Highlight values not between X and Y

Shade alternating groups of n rows

Data validation

Data validation allow numbers only

Data validation allow text only

Data validation allow uppercase only

Data validation allow weekday only

Data validation date in next 30 days

Data validation date in specific year

Data validation don't exceed total

Data validation exists in list

Data validation must begin with

Data validation must contain specific text

Data validation must not contain

Data validation must not exist in list

Data validation no punctuation

Data validation number multiple 100

Data validation only dates between

Data validation require unique number

Data validation specific characters only

Data validation unique values only

Data validation whole percentage only

Data validation with conditional list

Rank

Rank function example

Rank if formula

Rank race results

Rank values by month

Rank with ordinal suffix

Rank without ties

Round

Get decimal part of a number

Get integer part of a number

Get number at place value

Round a number down

Round a number down to nearest multiple

Round a number to n significant digits

Round a number to nearest multiple

Round a number up

Round a number up to nearest multiple

Round a number up to next half

Round a price to end in .99

Round by bundle size

Round price to end in .45 or .95

Round time to nearest 15 minutes

Round to nearest 1000

Round to nearest 5

Date and Time

Add business days to date

Add days exclude certain days of week

Add days to date

Add decimal hours to time

Add decimal minutes to time

Add months to date

Add workdays no weekends

Add workdays to date custom weekends

Add years to date

Assign points based on late time

Basic timesheet formula with breaks

Calculate date overlap in days

Calculate days open

Calculate days remaining

Calculate expiration date

Calculate number of hours between two times

Calculate retirement date

Calculate years between dates

Convert date string to date time

Convert date to Julian format

Convert date to month and year

Convert date to text

Convert decimal hours to Excel time

Convert decimal minutes to Excel time

Convert decimal seconds to Excel time

Convert Excel time to decimal hours

Convert Excel time to decimal minutes

Convert Excel time to decimal seconds

Convert Excel time to Unix time

Convert text date dd/mm/yy to mm/dd/yy

Convert text timestamp into time

Convert text to date

Convert time to money

Convert time to time zone

Convert Unix time stamp to Excel date

Count birthdays by month

Count calls at specific times

Count dates in current month

Count day of week between dates

Count holidays between two dates

Count times in a specific range

Create date range from two dates

Custom weekday abbreviation

Date is same month

Date is same month and year

Date is workday

Days until expiration date

Display the current date

Display the current date and time

Dynamic calendar grid

Dynamic date list

Extract date from a date and time

Extract time from a date and time

Get age from birthday

Get date from day number

Get day from date

Get day name from date

Get days before a date

Get days between dates

Get days between dates ignoring years

Get days, hours, and minutes between dates

Get days, months, and years between dates

Get earliest and latest project dates

Get first day of month

Get first day of previous month

Get fiscal quarter from date

Get fiscal year from date

Get last day of month

Get last weekday in month

Get last working day in month

Get Monday of the week

Get month from date

Get month name from date

Get months between dates

Get most recent day of week

Get next day of week

Get next scheduled event

Get nth day of week in month

Get nth day of year

Get percent of year complete

Get project end date

Get project midpoint

Get project start date

Get quarter from date

Get same date next month

Get same date next year

Get week number from date

Get work hours between dates

Get work hours between dates and times

Get work hours between dates custom schedule

Get workdays between dates

Get year from date

If Monday, roll back to Friday

Join date and text

Last updated date stamp

List holidays between two dates

Month number from name

Next anniversary date

Next biweekly payday from date

Next business day 6 months in future

Next working day

Pad week numbers with zeros

Parse time string to time

Sum by fiscal year

Sum race time splits

Sum time by week and project

Sum time over 30 minutes

Sum time with SUMIFS

Time difference in hours as decimal value

Time duration with days

Time since start in day ranges

Timesheet overtime calculation formula

Total hours that fall between two times

Workdays per month

Working days in year

Working days left in month

Year is a leap year

Date series

Series of dates by custom days

Series of dates by day

Series of dates by month

Series of dates by weekends

Series of dates by workdays

Series of dates by year

Text

Abbreviate names or words

Add a line break with a formula

Add line break based on OS

Capitalize first letter

Cell contains all of many things

Cell contains number

Cell contains one of many things

Cell contains one of many with exclusions

Cell contains some words but not others

Cell contains specific text

Cell contains which things

Cell equals one of many things

Clean and reformat telephone numbers

Compare two strings

Conditional message with REPT function

Convert numbers to text

Convert string to array

Convert text to numbers

Count keywords cell contains

Count line breaks in cell

Count specific characters in a cell

Count specific characters in a range

Count specific words in a cell

Count specific words in a range

Count total characters in a cell

Count total characters in a range

Count total words in a cell

Count total words in a range

Double quotes inside a formula

Extract last two words from cell

Extract multiple lines from a cell

Extract nth word from text string

Extract substring

Extract text between parentheses

Extract word containing specific text

Extract word that begins with specific character

Find and replace multiple values

Find nth occurrence of character

Get last line in cell

Join cells with comma

MAC address format

Most frequent text with criteria

Most frequently occurring text

Pad text to equal length

Position of 2nd 3rd etc instance of character

Remove characters from right

Remove file extension from filename

Remove first character

Remove last word

Remove leading and trailing spaces from text

Remove line breaks

Remove text by matching

Remove text by position

Remove text by variable position

Remove unwanted characters

Replace one character with another

Reverse text string

Sort comma separated values

Split dimensions into three parts

Split dimensions into two parts

Split numbers from units of measure

Split text and numbers

Split text string at specific character

Split text with delimiter

Strip html from text or numbers

Strip non-numeric characters

Strip numeric characters from cell

Text split to array

Translate letters to numbers

Trim text to n words

Financial

Annual compound interest schedule

Annuity solve for interest rate

Bond valuation example

CAGR formula examples

Calculate compound interest

Calculate cumulative loan interest

Calculate cumulative loan principal payments

Calculate interest for given period

Calculate interest rate for loan

Calculate loan interest in given year

Calculate original loan amount

Calculate payment for a loan

Calculate payment periods for loan

Calculate periods for annuity

Calculate principal for given period

Calculate simple interest

Compare effect of compounding periods

Effective annual interest rate

Estimate mortgage payment

Future value of annuity

Future value vs. Present value

Income tax bracket calculation

NPV formula for net present value

Payment for annuity

Present value of annuity

Geometry

Area of a circle

Area of a parallelogram

Area of a trapezoid

Area of a triangle

Circumference of a circle

Distance formula

Pythagorean theorem

Surface area of a cone

Surface area of a cylinder

Surface area of a sphere

Volume of a cone

Volume of a cylinder

Volume of a rectangular prism

Volume of a sphere

Workbook

Dynamic workbook reference

Dynamic worksheet reference

Get full workbook name and path

Get sheet name only

Get workbook name and path without sheet

Get workbook name only

Get workbook path only

Indirect named range different sheet

List sheet index numbers

List sheet names with formula

Worksheet name exists

Internet

Create email address from name

Create email address with name and domain

Create email with display name

Get domain from email address

Get domain name from URL

Get name from email address

Get page from URL

Get top level domain (TLD)

Remove trailing slash from url

Strip protocol and trailing slash from URL

Names

Get first name from name

Get first name from name with comma

Get last name from name

Get last name from name with comma

Get middle name from full name

Join first and last name

Put names into proper case

Percentage

Calculate percent variance

Decrease by percentage

Get amount with percentage

Get original number from percent change

Get original price from percentage discount

Get percent change

Get percentage discount

Get percentage of total

Get profit margin percentage

Get total from percentage

Increase by percentage

Percent of goal

Percent of students absent

Project complete percentage

Random

Random date between two dates

Random number between two numbers

Random number from fixed set of options

Random number weighted probability

Random text values

Random times at specific intervals

Random value from list or table

Randomly assign data to groups

Randomly assign people to groups

Range

Add row numbers and skip blanks

Address of first cell in range

Address of last cell in range

All cells in range are blank

All values in a range are at least

Count visible columns

COUNTIFS with variable range

Define range based on cell value

Dynamic named range with INDEX

Dynamic named range with OFFSET

First column number in range

First match between two ranges

First row number in range

Get address of named range

Get relative column numbers in range

Get relative row numbers in range

Last column number in range

Last row in mixed data with blanks

Last row in mixed data with no blanks

Last row in numeric data

Last row in text data

Last row number in range

Multiple cells are equal

Multiple cells have same value

Multiple cells have same value case sensitive

Multiple columns are equal

Range contains a value not in another range

Range contains numbers

Range contains specific date

Sequential row numbers

Total cells in a range

Total columns in range

Total rows in range

Tables

Automatic row numbers in Table

Average last N values in a table

Basic inventory formula example

Count table columns

Count table rows

COUNTIFS with variable table column

Dynamic reference Table name

Get column index in Excel Table

Get column name from index in table

Percentile IF in table

Running count in Table

Running total in Table

Sum multiple tables

SUMIFS vs other lookup formulas

SUMIFS with Excel Table

Two-way lookup VLOOKUP in a Table

Two-way summary with SUMIFS

Errors

How to fix a circular reference error

How to fix the #### (hashtag) error

How to fix the #CALC! error

How to fix the #DIV/0! error

How to fix the #N/A error

How to fix the #NAME? error

How to fix the #NULL! error

How to fix the #NUM! error

How to fix the #REF! error

How to fix the #SPILL! error

How to fix the #VALUE! error

Miscellaneous

Abbreviate state names

All dates in chronological order

Basic array formula example

Basic attendance tracking formula

Basic error trapping example

Basic in cell histogram

Basic numeric sort formula

Basic outline numbering

Basic text sort formula

BMI calculation formula

Build hyperlink with VLOOKUP

Calculate a ratio from two numbers

Calculate win loss tie totals

Cap percentage at 100

Cap percentage at specific amount

Carry-on baggage Inches to centimeters

Cash denomination calculator

Celsius to Fahrenheit conversion

Change negative numbers to positive

Check register balance

Coefficient of variation

Conditional median with criteria

Conditional mode with criteria

Convert column letter to number

Convert column number to letter

Convert expense time units

Convert feet and inches to inches

Convert inches to feet and inches

Convert pounds to kilograms

Copy value from every nth column

Copy value from every nth row

Cost of living adjustment

Count consecutive monthly orders

Count values out of tolerance

Count with repeating values

Course completion status summary

Course completion summary with criteria

Create array of numbers

Cube root of number

Customer is new

Display sorted values with helper column

Dropdown sum with all option

Easy bundle pricing with SUMPRODUCT

Expense begins on specific month

Extract unique items from a list

Filter values in array formula

Find and retrieve missing values

Fixed value every N columns

Flag first duplicate in a list

Flip table rows to columns

Force negative numbers to zero

Forecast vs actual variance

Formula with locked reference

Get date associated with last entry

Get first entry by month and year

Get last entry by month and year

Get pivot table grand total

Get pivot table subtotal

Get pivot table subtotal grouped date

Get value of last non-empty cell

Hyperlink to first blank cell

Hyperlink to first match

Increment a calculation with ROW or COLUMN

Increment a number in a text string

Increment cell reference with INDIRECT

Issue response time calculation

Leave a comment in a formula

Link to multiple sheets

List contains duplicates

List most frequently occuring numbers

Longest winning streak

Lookup last file revision

Most frequently occurring number

Multiplication table formula

New customers per month

Nightly hotel rate calculation

Normalize size units to Gigabytes

nth root of number

Number is whole number

Odometer gas mileage log

One or the other not both

Pad a number with zeros

Parse XML with formula

Random sort formula

Range contains one of many substrings

Range contains one of many values

Range contains specific text

Repeat fixed value every 3 months

Return array with INDEX function

Reverse a list or range

Risk Matrix Example

Score quiz answers with key

Search entire worksheet for value

Search multiple worksheets for value

Send email with formula

Show formula text with formula

Simple currency conversion

Sort and extract unique values

Sort numbers ascending or descending

Sort text and numbers with formula

Split payment across months

Square root of number

Standard deviation calculation

Student class enrollment with table

Sum every 3 cells

Sum Roman numbers

Sum text values like numbers

Transpose table without zeros

Validate input with check mark

Value exists in a range

Value is between two numbers

Value is within tolerance

Volunteer hours requirement calculation

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

I would highly recommend that anyone seeking Excel advice turn to this resource as their first stop. You can spend hours on Google trying to find a way to do something and get nowhere, at Exceljet I tend to find the answer very quickly and it is easily implemented. -Tony

Excel foundational video course

Excel Pivot Table video training course

Excel formulas and functions video training course

Excel Charts video training course

Video training for Excel Tables

Dynamic Array Formulas