digital content revolution

Wednesday, 25 May 2016

300 Excel Formula Examples

Source By: Exceljet

Find the formula you need in our list of ready-to-use examples. Click a formula link for a full description. Related functions appear to the right and are also links. Also see our function guide: Excel functions for the minimalist.

Count

Count cells between dates

Count cells between two numbers

COUNTIFS COUNTIF

Count cells equal to

Count cells equal to case sensitive

SUMPRODUCT EXACT

Count cells equal to either x or y

Count cells equal to one of many things

COUNTIF SUMPRODUCT

Count cells greater than

Count cells less than

Count cells not equal to

Count cells not equal to x or y

COUNTIFS SUMPRODUCT

Count cells that are blank

COUNTBLANK COUNTA

Count cells that are not blank

COUNTA COUNTBLANK

Count cells that begin with

Count cells that contain either x or y

COUNTIF SUMPRODUCT ISNUMBER FIND

Count cells that contain errors

ISERR SUMPRODUCT ISERROR

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

COUNTIF SUMPRODUCT FIND ISNUMBER

Count cells that contain text

COUNTIF ISTEXT SUMPRODUCT

Count cells that do not contain

Count cells that do not contain errors

ISERROR NOT SUMPRODUCT

Count cells that end with

Count if row meets internal criteria

Count if row meets multiple internal criteria

Count if two criteria match

COUNTIFS SUMPRODUCT

Count matches between two columns

Count sold and remaining

Count total matches in two ranges

SUMPRODUCT COUNTIF

Count unique numeric values in a range

FREQUENCY SUM COUNTIF

Count unique numeric values with criteria

Count unique text values in a range

FREQUENCY MATCH ROW SUMPRODUCT

Count unique text values with criteria

FREQUENCY MATCH ROW SUM

Count unique values in a range with COUNTIF

SUMPRODUCT COUNTIF

Count visible rows in a filtered list

Count visible rows only with criteria

COUNTIFS with multiple criteria and OR logic

Running count of occurrence in list

Summary count with COUNTIF

Sum

3D SUMIF for multiple worksheets

SUMPRODUCT SUMIF INDIRECT

Calculate running total

Subtotal by color

Subtotal invoices by age

Sum bottom n values

SUMPRODUCT SMALL SUM ROW INDIRECT

Sum columns based on adjacent criteria

Sum entire column

Sum every nth column

SUMPRODUCT COLUMN MOD

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

SUMIFS SUMPRODUCT ISNUMBER FIND SEARCH

Sum if cells contain specific text

Sum if date is between

Sum if date is greater than

SUMIF SUMIFS DATE

Sum if ends with

Sum if equal to either x or y

SUMIF SUMPRODUCT

Sum if greater than

Sum if less than

Sum if multiple criteria

Sum if not blank

Sum top n values

SUMPRODUCT LARGE SUM ROW INDIRECT

Sum visible rows in a filtered list

SUMIFS with multiple criteria and OR logic

Average

Average numbers

Average numbers ignore zero

Average top 3 scores

Weighted average

Min and Max

Maximum if multiple criteria

Maximum value if

Minimum if multiple criteria

Minimum value if

nth largest value

nth largest value with criteria

nth smallest value

nth smallest value with criteria

Conditional formatting

Highlight blank cells

Highlight cells that begin with

COUNTIF FIND IFERROR

Highlight cells that contain

SEARCH FIND ISNUMBER

Highlight cells that end with

COUNTIF RIGHT LEN EXACT

Highlight cells that equal

Highlight column differences

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 rows

COUNTIFS COUNTIF SUMPRODUCT

Highlight duplicate values

Highlight entire rows

Highlight every other row

ISEVEN ISODD ROW MOD

Highlight integers only

Highlight rows that contain

Highlight rows with dates between

Highlight unique values

Highlight values between

Highlight values greater than

Highlight values not between X and Y

Highlight values that don't exist

Lookup

Approximate match with multiple criteria

Basic INDEX MATCH approximate

Basic INDEX MATCH exact

Calculate grades with VLOOKUP

Calculate shipping cost with VLOOKUP

Count missing values

COUNTIF SUMPRODUCT MATCH ISNA

Dynamic lookup table with INDIRECT

INDIRECT VLOOKUP

Exact match lookup with INDEX and MATCH

INDEX MATCH EXACT

Exact match lookup with SUMPRODUCT

SUMPRODUCT EXACT

Extract multiple matches into separate columns

INDEX MATCH SMALL IFERROR ROW COLUMNS

Faster VLOOKUP with 2 VLOOKUPS

Find missing values

COUNTIF VLOOKUP MATCH

Get address of lookup result

INDEX MATCH CELL

Get employee information with VLOOKUP

Get first match cell contains

INDEX MATCH ISNUMBER SEARCH

Get first non-blank value in a list

INDEX MATCH ISBLANK

Get first partial match in a range

Get first text value in a list

Get nth match with INDEX / MATCH

INDEX MATCH SMALL

Get nth match with VLOOKUP

VLOOKUP COUNTIF

Group arbitrary text values

Group numbers with VLOOKUP

Lookup up cost for product or service

Map inputs to arbitrary values

Map text to numbers

Match first error

Max if criteria match

Partial match against numbers with wildcard

Partial match with VLOOKUP

Position of max value in list

Related info for max value in range

MAX MATCH INDEX

Self-contained VLOOKUP

Two-way lookup with INDEX and MATCH

Two-way lookup with VLOOKUP

VLOOKUP with 2 lookup tables

VLOOKUP with calculated column index

VLOOKUP with numbers and text

VLOOKUP with two or more criteria

VLOOKUP without #N/A error

VLOOKUP IFERROR

If

If cell begins with x, y, or z

If cell contains

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 NOT this or that

If this AND that

Nested IF function example

Round

Get decimal part of a number

Get integer part of a number

Round a number down

Round a number down to nearest multiple

Round a number to n significant digits

ROUND LOG10 INT ABS

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 time to nearest 15 minutes

Round to nearest 1000

Round to nearest 5

MROUND CEILING FLOOR

Date and Time

Add business days to date

WORKDAY NETWORKDAYS

Add workdays to date custom weekends

WORKDAY.INTL NETWORKDAYS

Calculate elapsed work time

Calculate years between dates

Convert date string to date time

DATEVALUE LEFT MID TIMEVALUE

Convert date to Julian format

Convert date to text

Convert hours to time

Convert minutes to time

Convert seconds to time

Convert text to date

DATE LEFT MID RIGHT

Convert time to time zone

Count day of week between dates

WEEKDAY ROW INDIRECT SUMPRODUCT

Custom weekday abbreviation

Date is same month

Date is same month and year

Date is workday

WORKDAY WORKDAY.INTL

Display the current date

Display the current date and time

Extract date from a date and time

Extract time from a date and time

Get age from birthday

INT TODAY YEARFRAC

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 first day of month

Get first day of previous month

Get first Monday before any date

Get fiscal quarter from date

Get last day of month

Get month from date

Get month name from date

MONTH CHOOSE TEXT

Get months between dates

Get most recent day of week

Get next day of week

Get nth day of year

Get percent of year complete

YEARFRAC YEAR DATE

Get quarter from date

Get same date next month

Get same date next year

Get start date based on target date

WORKDAY WORKDAY.INTL

Get week number from date

WEEKNUM ISOWEEKNUM

Get work hours between dates

NETWORKDAYS NETWORKDAYS.INTL

Get work hours between dates custom schedule

CHOOSE ROW INDIRECT WEEKDAY SUM

Get workdays between dates

NETWORKDAYS NETWORKDAYS.INTL

Get year from date

Group times into 3 hour buckets

Group times into unequal buckets

Join date and text

Next business day 6 months in future

WORKDAY WORKDAY.INTL

Next working day

WORKDAY WORKDAY.INTL

Pad week numbers with zeros

Sum race time splits

Total hours that fall between two times

Workdays per month

NETWORKDAYS NETWORKDAYS.INTL EOMONTH

Year is a leap year

DATE YEAR MONTH

Date series

Series of dates by day

Series of dates by month

DAY MONTH YEAR DATE EOMONTH

Series of dates by weekends

Series of dates by workdays

WORKDAY NETWORKDAYS.INTL IF WEEKDAY

Series of dates by year

DAY MONTH YEAR DATE

Text

Add a line break with a formula

Add line break based on OS

Cell contains all of many things

SUMPRODUCT ISNUMBER SEARCH COUNTA

Cell contains any numbers

Cell contains one of many things

SUMPRODUCT ISNUMBER SEARCH

Cell contains some words but not others

COUNT SEARCH AND

Cell contains specific text

SEARCH ISNUMBER FIND

Cell contains which things

SEARCH ISNUMBER FIND

Cell equals one of many things

Clean and reformat telephone numbers

Compare two strings

Convert numbers to text

Count line breaks in cell

LEN SUBSTITUTE CHAR ISBLANK

Count specific characters in a cell

LEN SUBSTITUTE UPPER

Count specific characters in a range

LEN SUBSTITUTE UPPER SUMPRODUCT

Count specific words in a cell

LEN SUBSTITUTE UPPER

Count specific words in a range

LEN SUBSTITUTE UPPER SUMPRODUCT

Count total characters in a cell

Count total characters in a range

Count total words in a cell

LEN SUBSTITUTE TRIM ISBLANK

Count total words in a range

SUMPRODUCT LEN SUBSTITUTE TRIM

Double quotes inside a formula

Extract nth word from text string

TRIM MID SUBSTITUTE REPT LEN

LEFT FIND ISERROR

TRIM SUBSTITUTE RIGHT REPT

Position of 2nd 3rd etc instance of character

SUBSTITUTE FIND

Remove last n characters from text

Remove leading and trailing spaces from text

CLEAN TRIM SUBSTITUTE

Remove text by matching

Remove text by position

Remove text by variable position

Replace one character with another

Split dimensions into three parts

LEFT RIGHT MID LEN SUBSTITUTE

Split dimensions into two parts

SUBSTITUTE LEFT RIGHT FIND

Split numbers from units of measure

MAX LEFT RIGHT ISNUMBER VALUE MID

Split text and numbers

FIND MIN RIGHT LEFT

Strip html from text or numbers

Financial

Calculate compound interest

Calculate simple interest

Workbook

Get full workbook name and path

Get sheet name only

Get workbook name and path without sheet

CELL LEFT FIND SUBSTITUTE

Get workbook name only

Get workbook path only

Internet

Get domain from email address

Get domain name from URL

Get name from email address

Get top level domain (TLD)

RIGHT LEN SUBSTITUTE FIND

Names

Get first name from Last, First

Get first name from name

Get last name from Last, First

Get last name from name

RIGHT LEN SUBSTITUTE FIND

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

Range

Address of first cell in range

ADDRESS ROW COLUMN CELL

Address of last cell in range

ADDRESS ROW COLUMN ROWS COLUMNS

All cells in range are blank

First column number in range

First row number in range

Get relative column numbers in range

Get relative row numbers in range

Last column number in range

COLUMN COLUMNS MIN

Last row number in range

Range contains a value not in another range

SUMPRODUCT MATCH ISNA

Range contains numbers

SUMPRODUCT ISNUMBER

Total cells in a range

Total columns in range

Total rows in range

Miscellaneous

Build hyperlink with VLOOKUP

HYPERLINK VLOOKUP

Calculate a ratio from two numbers

Change negative numbers to positive

Extract unique items from a list

INDEX MATCH COUNTIF

Flag first duplicate in a list

Flip table rows to columns

Get value of last non-empty cell

Increment a calculation with ROW or COLUMN

List contains duplicates

SUMPRODUCT COUNTIF

Longest winning streak

FREQUENCY MAX IF

Mixed reference for multiplication table

Pad a number with zeros

Random date between two dates

RANDBETWEEN WORKDAY

Random number between two numbers

Random text values

CHOOSE RANDBETWEEN

Randomly assign data to groups

RANDBETWEEN CHOOSE

Range contains one of many substrings

SUMPRODUCT ISNUMBER SEARCH

Range contains one of many values

SUMPRODUCT ISNUMBER SEARCH

Range contains specific text

Range contains specific value

Reverse a list or range

INDEX COUNTA ROW

Transpose table without zeros

If this page is useful to you, can you please share it? Thank you!

Love your tutorials, they've been awesome in helping me learn all that Excel can do.

Posted by adsguru at 01:18 No comments:

Email This BlogThis!Share to X Share to Facebook Share to Pinterest

Newer Posts Older Posts Home

Subscribe to: Posts (Atom)

About Me

adsguru

View my complete profile

Blog Archive

▼ 2016 (54)
- ► July (11)
- ► June (6)
- ▼ May (3)
- ► March (4)
- ► February (4)
- ► January (26)

► 2015 (31)
- ► December (4)
- ► November (1)
- ► October (1)
- ► September (14)
- ► August (11)

Ethereal theme. Powered by Blogger.