A collection of various excel function usages and examples for beginners, mainly including lookup, string processing, finance, statistics, summation, filtering, maximum value, minimum value, date and time, and some key function usages.

RANK EXAMPLES

Sort/Rank Numeric Values with Duplicate Values Exist	RANK COUNTIF
Rank Numbers without Repetitive Ranks	RANK COUNTIF
Rank data Based on the Number of Occurrences	RANK COUNTIF
Reverse Rank Order	RANK
Sort Dynamic Data	RANK VLOOKUP
Rank values based a specific value in another column	RANK COUNTIFS IF
Rank Data with Multiple Criteria	SUMPRODUCT
Rank Numbers without Repetitive Ranks in Excel	RANK COUNTIF
Sort Data by Last Character in Excel	RIGHT
Rank data Based on the Number of Occurrences	RANK COUNTIF
Reverse Rank Order in Excel	RANK
Rank values in a column based a specific value in another column	RANK COUNTIFS
Sort by Second or Third character in a Column	MID LEFT RIGHT
Sort Name by Last Name in Excel	RIGHT Substitute LEN FIND
Sort Names by Middle Name in Excel	Substitute IF MID FIND ISERR

DATE AND TIME EXAMPLES

Calculate Days Open	ISBLANK IF TODAY DAYS
Add Workdays in Excel	WORKDAY WORKDAY.INTL
Add Months To Date In Excel	DAY EDATE DATE MONTH YEAR
Add Minutes to Time in Excel	TIME MOD
Add Hours To Time in Excel	TIME MOD
Check Dates in chronological order	SUM SUMPRODUCT COUNTIF IF SORT COUNTIFS
Add the Business Days to Date	WORKDAY
Adding Days Exclude Specific Days of Week	WORKDAY WORKDAY.INTL
Add Days to Date in MS Excel	DAY DATE MONTH YEAR
Extract date from multiple cells containing both date and time	INT
Auto Fill Weekdays or Weekends in Excel	ROW INT Ceiling TEXT
Convert Date & Time Format to Date in Excel	DAY MONTH YEAR
Extract Time From A Date And Time in Excel	TIME HOUR MINUTE SECOND
Convert Military Time to Standard Time in Excel	TIMEVALUE MID LEFT RIGHT TEXT
Calculate Number of Weekends between Two Dates	INT WEEKDAY DAYS
Delete or Remove Year from a Date in Excel	MONTH DAY TEXT
Convert Days to Years, Months, Days in Excel	DATEDIF
Convert Julian Date to a Calendar date in Excel	IF LEFT MOD
sort Dates by Month and Day Only in Excel	DAY MONTH YEAR
Add the Current Month or Year in a Cell in Excel	YEAR MONTH TODAY NOW
Display Negative Time Value in Excel	MAX MIN TEXT
Converting Dates to Fiscal Quarters and Years	YEAR MONTH Choose
Filter Out Weekends and Weekday	WEEKDAY
Convert Excel Date to Unix Time Stamp (or Time Stamp to Excel Date)	DATE
Determine If a Date Falls on the Weekend	IF WEEKDAY OR
Returning Value if the Dates Fall between Two Dates	IF AND
Split Dates into Separate Cells in Excel	INT
Extract Month and Year from Date in Excel	TEXT
Extract Time or Hour Only from a Date and Time in Excel	TIME HOUR MINUTE SECOND
Adding Hours, Minutes, or Seconds to a Date and Time in Excel	TIME
Compare Dates	IF DATEVALUE
Changing Date Format	TEXT
Combining Date and Time into One Cell	TEXT
Convert Weekday Name to Number	LOOKUP MATCH
Convert Month Name to Number	MONTH DATEVALUE TEXT DATE
Calculating Future Date	WORKDAY
Get the First Monday of a Given Year	YEAR Choose WORKDAY DATE
Calculate Days, Weeks, Months and Years between Two Dates	DATEDIF TODAY INT Round
Group Time by Hour or Minute	FLOOR TIME
Add / Subtract Days, Months and Years to Date	MONTH TODAY DAY YEAR EDATE
Convet Text Date dd/mm/yy to mm/dd/yyyy as Date Format	DATE MID RIGHT LEFT VALUE
Working Time Calculation Based on Timesheets	MOD
Calculate Number of Hours between Two Times	HOUR INT
Calculate Years Between Dates In Ms Excel	INT Round

LOOKUP EXAMPLES

Calculate Grades With VLookup	VLOOKUP
Find the Closest Data to the Data Provided in Excel	INDEX MATCH MIN ABS
Extract Last Two Words From Multiple Cells	MID SUBSTITUTE LEN FIND
Extract Multiple Lines From A Cell	TRIM MID SUBSTITUTE CHAR REPT LEN
Extract Multiple Match Values into Separate Columns	INDEX MATCH IF ROW SMALL MIN IFERROR COLUMN
Faster Trick with 2 VLOOKUPS	NA IF VLOOKUP
Extract Unique Items From A List	INDEX MATCH COUNTIF LOOKUP
Extract all the matches with helper Column	INDEX MATCH MAX
VLOOKUP From Another Sheet Not Working	IFERROR VLOOKUP INDEX MATCH
If Cell Begins with One of Three Supplied Characters	SUM COUNTIF
Fix #N/A Error For VLOOKUP From Another Sheet	IF IFERROR ISERROR VLOOKUP
Get Employee Information by VLOOKUP	VLOOKUP
VLOOKUP with Two Lookup Tables	VLOOKUP IF
VLOOKUP with Multiple Lookup Values	VLOOKUP
VLOOKUP Data by Date	VLOOKUP
VLOOKUP – Retrieve Data from Another Workbook	VLOOKUP
VLOOKUP – Retrieve Data from Another Worksheet	VLOOKUP
Case Sensitive Lookup with SUMPRODUCT and EXACT	VLOOKUP SUMPRODUCT MATCH
Basic Usage of INDEX & MATCH – Case Sensitive Lookup	INDEX MATCH EXACT
Basic Rates Calculation by VLOOKUP Based on Weight Band	INDEX MATCH
Basic Grade Calculation by VLOOKUP Function – Approximate Match	INDEX MATCH VLOOKUP
Basic Discount Calculation with VLOOKUP Function	INDEX MATCH VLOOKUP
Basic Usage of INDEX & MATCH – Exact Match	INDEX MATCH
Basic Usage of INDEX & MATCH – Approximate Match	INDEX MATCH
Find the Smallest Value and Smallest Positive Value	IF SMALL MIN COUNTIF
VLOOKUP with Dropdown List in Excel	VLOOKUP
Find the Last or First Value in a Range Greater Than X in Excel	IF INDEX MATCH LOOKUP
Lookup Values from Right to Left in Excel	IF INDEX MATCH VLOOKUP
Compare Two Lists in Different Worksheets using VLOOKUP	IF ISERROR VLOOKUP
Extract a Unique List based on Criteria in Excel	IF INDEX MATCH COUNTIF
Vlookup to Return the SUM of Two or More Columns	VLOOKUP SUMPRODUCT SUM
Pick a Random Name from a List in Excel	INDEX COUNTA RANDBETWEEN
Find Most Common Value in a Range in Excel	INDEX MATCH
Find Max And Min Value with Single or Multiple Criteria	IF MAX MIN
Get Cell Address of Max or Min Value in a Range in Excel	INDEX MATCH MAX MIN CELL
Find the First or Last Non-blank Cell in a Row or Column in Excel	INDEX MATCH LOOKUP ISBLANK
Extract a Unique Distinct List from a Column in Excel	INDEX MATCH IF COUNTIF
Vlookup to Return Blank Instead of 0 or NA Error in Excel	IF VLOOKUP IFERROR LEN
VLOOKUP to Return Value if Date Falls between Two dates in Excel	LOOKUP DATE
VLOOKUP Returns zero instead of #NA in Excel	VLOOKUP IFERROR
Find the Earliest and Latest Date in a Range of Dates in Excel	MAX MIN SMALL LARGE
Find Closest Value or Nearest Value in a Range in Excel	INDEX MATCH MIN SMALL LARGE COUNTIF ABS
Find Missing Numbers in a Sequence in Excel	COUNTIF IF MATCH SMALL LARGE ROW ISNA
VLOOKUP Return Multiple Values Horizontally	IF INDEX ROW SMALL VLOOKUP COLUMN
Excel Vlookup From Another Workbook	IF VLOOKUP ISNA
Excel Vlookup Return True or False	IF VLOOKUP ISNA
Find the Maximal or Minimal String Based on Alphabetic Order	INDEX MATCH COUNTIF
get last match that cell contains one of several values in a range	LOOKUP SEARCH
get first match that cell contains one of several values in a range	INDEX MATCH SEARCH ISNUMBER
Get the First Match in Two Excel Ranges	COUNTIF INDEX MATCH
Get Cell Address of a Lookup Value	CELL INDEX MATCH
Two-way Lookup Formula	INDEX MATCH
Lookup Entire Row using INDEX/MATCH	INDEX MATCH
find the nth Largest Value	INDEX MATCH LARGE
Find the nth Smallest Value	INDEX MATCH SMALL
find nth Occurrence with Multiple Criteria Using INDEX/MATCH	INDEX MATCH SMALL IF ROW
Find the Relative Position in a Range or Table	INDEX MATCH ROW
Basic Price Discount Calculation with Excel VLOOKUP Function	VLOOKUP
Calculate Total Cost with Excel VLOOKUP Function	VLOOKUP
Break ties with helper COUNTIF and column	INDEX MATCH LARGE COUNTIF
Build Hyperlink With VLOOKUP in Excel	VLOOKUP HYPERLINK

TEXT EXAMPLES

Repeating Character n Times	CHAR REPT
Trap Error or Replace Error by Specific Value with IFERROR function	IFERROR
Add Leading Zeros in Excel	TEXT
Abbreviate Names Or Words in Excel	IF TEXTJOIN ROW MID INDIRECT MATCH ISNUMBER CODE LEN LEFT FIND TRIM
Convert State Names To Abbreviations	VLOOKUP INDEX MATCH
Extract substring In Excel	MID LEFT RIGHT
Remove All Extra Spaces and Keep Only One Between Words	TRIM
Split Cells by the First Space in Texts in Excel	LEFT RIGHT LEN FIND
Filter Cells Starts with Number or Letter in Excel	LEFT ISNUMBER
Concatenate Text based on unique values in Another Column	IFERROR COUNTIF INDEX MATCH
Extract Text between Two Text Strings in Excel	LEN MID SEARCH
Convert Date to YYYY-MM-DD format in Excel	TEXT
Remove the First/Last Word from Text string in Cell	LEFT RIGHT LEN FIND SUBSTITUTE TRIM
Remove Prefix and Suffix in Given Cells in Excel	LEFT RIGHT LEN
Check If a Cell is Uppercase, Lowercases or Capitalize	UPPER LOWER Proper EXACT
Concatenate Data with a Line Break in Excel	CHAR Concat
Remove Numbers from Text in Excel	IF INDIRECT ROW MID
Return a Value If a Cell Contains a Specific Text in Excel	IF ISNUMBER SEARCH
Insert Character or Text to Cells	LEFT MID
Convert Scientific Notation to Text or a Number in Excel	TRIM UPPER
Reverse Concatenate Formula in Excel	TRIM MID SUBSTITUTE REPT
Reverse Text String in Excel	INDIRECT LEN ROW MID TEXTJOIN
Change Uppercase to Title Case or Sentence Case in Excel	Proper
Removing Dash Characters in Excel	SUBSTITUTE
Extract First or Last N Characters	LEFT RIGHT
Extract Part of Text String	LEFT RIGHT MID
Generate Random Character Strings	CHAR RANDBETWEEN
Replace Last Comma in String with “and” word	SUBSTITUTE LEN
Remove Apostrophe or Text Indicator	VALUE
Sorting IP Address	LEFT RIGHT MID FIND TEXT
Add Prefix or suffix to Cell	Concat
Get the List of File Names From a Folder	IFERROR INDEX ROW
Remove Leading and Trailing Spaces	TRIM CHAR Substitute CLEAN
Generate Random Passwords	CHAR RANDBETWEEN
Add a Character or Letter before Each Word in a Cell	SUBSTITUTE Concat
Check If the First Letter is capitalized	LEN RIGHT Replace LEFT UPPER Proper Concat
insert leading zeros to number or text	Concat TEXT
Split full name to first and last name	LEFT RIGHT MID FIND SUBSTITUTE
add text to the end all cells	Concat
add text to the beginning of all cells	Concat
remove first and last characters from text string	LEFT RIGHT LEN
remove unwanted characters from text string	SUBSTITUTE CHAR
extract nth word from text string	SUBSTITUTE MID LEN REPT TRIM
get first word from text string	FIND IF LEFT ISERR
get last word from text string	FIND SUBSTITUTE LEN RIGHT
extract word that containing a specific character	FIND SUBSTITUTE MID REPT TRIM
replace all characters after the first specific character	FIND SUBSTITUTE MID SEARCH LEN
replace all characters before the first specific character	FIND SUBSTITUTE LEFT
remove text after a specific character	FIND LEFT Replace
remove text before the first match of a specific character	FIND Replace LEN RIGHT
extract text after the second or nth specific character (space or comma)	FIND SUBSTITUTE MID REPT TRIM SEARCH
extract text before the second or nth specific character (space or comma)	FIND SUBSTITUTE SEARCH LEFT
extract text after first comma or space	FIND MID LEN SEARCH
extract text before first comma or space	FIND LEFT
extract word that starting with a specific character	FIND SUBSTITUTE REPT TRIM LEFT LEN
join text from two or more cells into one cell separated by commas, space	SUBSTITUTE TRIM
Check if Cell Contains Certain Values but do not Contain Others Values	SEARCH AND COUNT
Extract Text between Commas	SUBSTITUTE MID REPT
Extract Text between Parentheses	SEARCH MID
Extract Text between Brackets	SEARCH MID
Split Text String to an Array	MID ROW LEN INDIRECT
Combine Text from Two or More Cells into One Cell	TEXTJOIN Concat CHAR
remove non numeric characters from a cell	TEXTJOIN MID ROW LEN INDIRECT IFERROR
Remove Numeric Characters from a Cell	TEXTJOIN MID ROW LEN INDIRECT ISERR IF
Get Last Name From Full Name	FIND SUBSTITUTE LEN RIGHT
Get First Name From Full Name	FIND LEFT
Get Path and Workbook name only	FIND SUBSTITUTE LEFT CELL
Get full File Name (workbook and worksheet) and Path	CELL
Get the Current Worksheet Name only	CELL FIND MID
Get the Current Workbook Name	CELL FIND MID
Get the Position of Second or Third of the Specified Character within a String	FIND SUBSTITUTE
Split Text and Numbers in Excel	FIND SUBSTITUTE LEFT LEN MIN RIGHT SUM
Split Text String by Line Break in Excel	FIND LEFT MID CHAR RIGHT
Split Text String by Specified Character in Excel	FIND LEFT RIGHT LEN SEARCH
Excel Replace Function Remove Text String	Replace
Combining the REPLACE Function with FIND Function to Remove Text in Excel	Replace FIND
Combining the Replace function with Text function in Excel	Replace TEXT

FINANCIAL EXAMPLES

Calculate Cumulative Loan Interest	CUMIPMT
Calculate Cumulative Loan Principal Payments	CUMPRINC
Calculate Compound Interest	FV
Calculate Compound or Average Annual Growth Rate	AVERAGE RATE
remove all spaces between numbers or words	FIND Replace Replace
Calculate The Period of Loan or Investment	NPER
Calculate The Bond Valuation	PV
CAGR Formula Examples in Excel	RRI
Calculate Interest for Given Period	IPMT
Calculate Interest Rate for Loan	RATE
Calculate Cap Percentages to Specific Value	IF MIN
Cash Denomination Calculator	SUMPRODUCT INT

COUNT EXAMPLES

Count Attendance and Absence with COUNTIF function	COUNTIF
Count Cells that are Case Sensitive	SUMPRODUCT COUNTIF COUNTIFS ISNUMBER FIND
Count Not Empty or Blank Cells with COUNTIFS	COUNTIFS
Create a Summary Count by Month with COUNTIFS	COUNTIFS EDATE COUNTIF
Running Count of Occurrence List	COUNTIF IF
Use COUNTIFS function with Multiple Criteria and OR Logic	COUNTIFS SUM
Count Non-Contiguous Range using COUNTIF	COUNTIF SUM INDIRECT
Count Unique Numeric Values with Criteria in a Range	SUM IF FREQUENCY
Count Unique Numeric Values in a Range	SUM SUMPRODUCT FREQUENCY COUNTIF
Count Unique Dates in Excel	COUNT COUNTIF SUM SUMPRODUCT FREQUENCY COUNTA UNIQUE
Count Total Matches in Two Ranges	COUNTIF SUMPRODUCT SUM
Count Row That Contain Specific Value	COLUMN TRANSPOSE SUM
Count Occurrences in Entire Workbook in Excel	COUNTIF SUMPRODUC INDIRECT
Count Numbers Nth Digit Equals to Specific Number in Excel	SUMPRODUC MID
Count Number by Range with COUNTIFS Function	COUNTIFS
Count Matches between Two Columns in Excel	SUMPRODUC
Count Specific Items in List	COUNTIF COUNTIFS SUMIFS
Count the Cells that Match Two Criteria	COUNTIF COUNTIFS SUMPRODUCT SUMIFS
Count Dates of Given Year	SUMPRODUCT YEAR
Count Dates by Day of Week in Excel	SUMPRODUCT WEEKDAY
Count Cells that do not Contain Errors in Excel	SUMPRODUCT SUM ISERROR NOT
Count Cells that do not Contain Specific Text in Excel	COUNTIF
Count Cells that Contain Specific Text in Excel	COUNTIF
Count Cells that Contain Text in Excel	COUNTIF COUNTIFS
Count Cells that Contain Positive or Negative numbers	COUNTIF
Count Cells that Contain even or odd numbers	SUMPRODUCT MOD
Count Cells that Contain only numbers in Excel	SUMPRODUCT ISNUMBER COUNT NOT
Count Number of Cells that Contain Exactly N Characters in Excel	REPT COUNTIF
Count Number of Cells that Contain Errors in Excel	SUMPRODUCT SUM ISERROR COUNTIF
Count Cells that Contain X or Y in Excel	SUMPRODUCT COUNTIF FIND ISNUMBER
Count Cells Are Not Blank or Empty in Excel	COUNTIF COUNTA
Count Blank or Empty Cells in Excel	COUNTIF COUNTBLANK
Count Cells That Begin with Specific Text in Excel	COUNTIF
Count Cells Not Equal to One of Many Things	SUMPRODUCT COUNTIF COUNTIFS MATCH ISNA COUNTA SUM
Count Cells Not Equal to a Specific Value in Excel	COUNTIF EXACT
Count Cells Greater Than a Specific Value in Excel	COUNTIF
Count Cells equal to one of many cells in Excel	SUMPRODUCT COUNTIF EXACT
Count Cells equal to X or Y in Excel	COUNTIF EXACT SUM
Count Cells equals to Cells with Case Sensitive	SUMPRODUCT EXACT
Count Cells equals to a Specific Value in Excel	SUMPRODUCT COUNTIF EXACT
Count Cells between Two Numbers in Excel	COUNTIF COUNTIFS
Count Cells between Two Dates or Times in Excel	COUNTIFS DATE
Calculate the Percentage Based on Different Data Type and Table Structure	COUNTIF COUNTA
Count the Average Between Two Dates in Excel	SUMPRODUCT IF AVERAGE
Count Only Unique Values Excluding Duplicates in Excel	IF SUM COUNTIF SUMPRODUCT MATCH
Count or Sum Cells with Checked Box in Excel	COUNTIF SUMPRODUCT
Count Comma Separated Value in One Cell in Excel	SUBSTITUTE LEN TRIM
Count the Number of Letters or Numbers separately in a Cell in Excel	SUBSTITUTE LEN
Find the Largest Value in a Column and Return the Adjacent Cell Value in Excel	INDEX MATCH MAX VLOOKUP
Count the Number of Weekends between Two Date	SUM INT WEEKDAY NETWORKDAYS
Count the Number of Workdays between Two Dates	NETWORKDAYS
Count Positive or Negative Numbers in Excel	COUNTIF
Count Dates in Given Year/Month/Day in Excel	COUNTIF COUNTIFS IF SUMPRODUCT SUM YEAR MONTH
Count the Number of “Yes” / “No” in a Range	COUNTIF
COUNTIF with Multiple Criteria	COUNTIF COUNTIFS SUMPRODUCT
Count Cells between Two Dates with Multiple Criteria	COUNTIF SUMPRODUCT
Count Blank or Non-blank Cells in Filtered Range	SUBTOTAL
Count Cells That Contain Specific Text	SUMPRODUCT COUNTIF FIND ISNUMBER SUM
Count Spaces before the Text String	FIND LEN TRIM LEFT
Count Duplicates	COUNTIF IF
Count Numbers with Leading Zeros	IF SUM COUNTIF SUMPRODUCT
count the number of line breaks in a cell	SUBSTITUTE LEN CHAR
count the number of words in a range of cells in Excel	SUBSTITUTE LEN IF SUMPRODUCT TRIM
count the number of words in a cell in Excel	SUBSTITUTE LEN IF TRIM
count specific words in a cell or a range in Excel	SUBSTITUTE LEN SUMPRODUCT
Calculate Win Loss Tie	COUNTIF COUNTIFS SUMPRODUCT

AVERAGE EXAMPLES

Average Of Numbers With Multiple Criteria	AVERAGEIFS COUNTIFS SUMIFS
Calculate Average Of Last 5 Or N Values In Columns	AVERAGE COUNT MIN
Calculating Average Of The Numbers	AVERAGE AVERAGEIF LARGE
Calculate Average Response Time Per Month	AVERAGEIFS
Average Only Positive or Negative Numbers of a Range	AVERAGE IF
Average of Working Hours by Formula in Excel	AVERAGE AVERAGEIF AVERAGEIFS
Average per Week by Formula in Excel	SUM COUNTIF AVERAGE SUMPRODUCT
Average with Multiple Criteria in Excel	AVERAGE
Average of Top N Values in Excel	AVERAGE LARGE
Average the Last N Numeric Values in Excel	ROW IF ISNUMBER LOOKUP AVERAGE LARGE
Average Last N Values in Multiple Columns	AVERAGE COUNT
Calculate Average by Month	AVERAGEIFS
Calculate Average If Criteria Not Blank/Ignore Blank Cell	AVERAGE AVERAGEIF AVERAGEIFS
Calculate Average Ignore Non-Numeric Values and Errors	AVERAGE AVERAGEIF
Average and Ignore Errors in Excel	AVERAGE AVERAGEIF ISERROR IF
Calculate Average among Multiple Different Worksheets	AVERAGE
Ignore Error Values When Calculating the Average	AVERAGE AVERAGEIF IF IFERROR ISNUMBER

IF FUNCTION EXAMPLES

Add Row Numbers And Skip Blanks	IF ISBLANK COUNTA
Assigning Points based on Late Time	IF VALUE
Check If a Cell is Blank or Empty	ISBLANK IF COUNTIF COUNTBLANK
If Cell is This Value or That Value	IF OR
If Value is Greater Than A Certain Value	IF ISBLANK
If Cell is Not Blank	IF ISBLANK
If Cell is Blank	IF ISBLANK
If Cell Equals Certain Text String	IF COUNTIF
If Cell Contains Either Text1 or Text2	IF COUNTIF
If Cell Contains Certain Text OR Equals Certain Text	IF ISNUMBER SEARCH
If Cell Begins with One of Three Supplied Characters	SUM COUNTIF
Statistic Pass 4 Out of 5 Rounds in a Competition	COUNTIF COUNTIFS AND IF
Calculate the Median in Different Cases in Excel	MEDIAN IF
Check If value Is between Two Numbers in Excel	AND IF MAX MIN
Replace Duplicates with Blank Cells in Excel	IF COUNTIF
check if Cell contains one of many values from range	IF COUNTA SEARCH SUMPRODUCT ISNUMBER
Check If Cell Contains All Values from Range	IF COUNTA SEARCH SUMPRODUCT ISNUMBER
Excel IF Function With Dates	IF Date
Excel IF function with text values	IF COUNTIF EXACT ISNUMBER SEARCH
Excel IF Function With Numbers	AND IF ABS
Excel IF formula with NOT logical function	AND IF ISBLANK NOT
Excel IF formula with operator : greater than,less than	IF DATEVALUE AND OR
Excel IF formula with AND & OR logical functions	AND IF OR
Excel IF formula with OR logical function	AND IF OR
Excel IF formula with AND logical function	AND IF
Excel IF formula with “Equal to” logical operators	IF DATEVALUE
Excel nested if statements with ranges	AND IF COUNTIF INDEX MATCH
Excel Nexted IF Functions (Statements) Tutorial (15 IF Formulas examples)	AND IF COUNTA VLOOKUP Choose ISBLANK

EXCEL TABLE EXAMPLES

Creating a Table with Automatic Row Numbering

INDEX ROW

COLUMN/ROW EXAMPLES

Filter or Remove Columns	ISNUMBER FILTER MATCH
Split Columns to Seprated Negative and Positive Values in Excel	IF
Transpose Every N Rows of Data into Muliptle Columns in Excel	INDEX ROW COLUMN
Get Row Number From a Vlookup in Excel	ROW MATCH
Move Every Other Row to a New Column in Excel	ROW IF
Convert Horizontal List of Data into Vertical in Excel	INDEX ROWS
Highlight Cell or Row If Date Is In Current Day/Week/Month	WEEKDAY TODAY TEXT
Convert Multiple Rows into a Single Row in Excel	COLUMN ROW FLOOR
Extract the Column Header of the Largest Value in a Row in Excel	INDEX MATCH MAX
Multiply two columns and Sum The Result in Excel	SUMPRODUCT
Compare Two Columns and Return Values in Third Column in Excel	VLOOKUP
Find and Highlight Duplicate Rows in Excel	SUMPRODUCT IF Concat TEXTJOIN COUNTIF
convert Column Letter to Number in Excel	ADDRESS INDIRECT COLUMN Substitute
Check If Value in a Column exists in Another Column in Excel	IF VLOOKUP ISERROR
Copy and Paste Only Non-blank Cells	IF INDEX ROW ROWS LOOKUP
Split Data in One Column to Multiple Columns	INDEX ROW ROWS COLUMNS
Find Duplicate Values in Two Columns	IF ISERROR MATCH COUNTIF AND
Find Duplicate Rows	SUMPRODUCT IF
Highlight Duplicate Rows	COUNTIF COUNTIFS COUNTA
Highlight Rows	SEARCH LEFT
combine columns without losing data	COUNTA
convert column letter to number	INDIRECT COLUMN
convert column number to letter	Substitute ADDRESS
Split Multiple Lines from a Cell into Rows	TRIM MID Substitute CHAR REPT LEN
Extract multiple match Values into different Columns or Rows	IF IFERROR INDEX ROW MIN ROWS COLUMNS SMALL
get the position of nth occurrence of a value in column	IF INDEX ROW MIN SMALL
Get the Position of the nth Occurrence of a Character in a Cell	Substitute ROW Find
Get the position of Last Occurrence of a character or string in a cell	ROW INDIRECT LEN MID LOOKUP Substitute SEARCH
get the position of Last Occurrence of a value in a column	ROW IF COUNTIF MIN MAX INDEX
Three Ways to Get the Last Row Number in a Range	ROW MIN INDEX ROWS
Three Ways to get the First Row Number in a Range	ROW MIN INDEX

FILTER EXAMPLES

Filter with Multiple Criteria	FILTER LEFT
Filter with Exact Match(Case-Sensitive)	FILTER EXACT
Filter or Extract for Multiple OR Criteria	ISNUMBER MATCH FILTER
Filter or Extract with a Partial Match	INDEX ROW SMALL IF ISNUMBER SEARCH AGGREGATE FILTER
Filter Data between Date Values	FILTER
Filter Data By Date Field	FILTER MONTH YEAR
Extract or Filter Every Nth Row	FILTER MOD ROWS
Extract or Filter exclude blank values	FILTER
Extract or Filter Horizontal Data	FILTER TRANSPOSE
Extract or Filter on The Top N Values with Criteria	LARGE SORT FILTER IF
Extract matching values From Two Lists	COUNTIF INDEX IF ROW SMALL IFERROR NOT
Extract all Partial Matches	INDEX ROW SMALL IF ISNUMBER SEARCH AGGREGATE
Extract Common Values in Two Lists	COUNTIF UNIQUE SORT FILTER
Filter Data by Column and Sort by Row	SORT FILTER
Filter And Transpose Data From Horizontal To Vertical	FILTER TRANSPOSE
Extract Attribute Values from XML/HTML Data	MID LEN
Extract a List of Unique Values from a Column Range	INDEX MATCH IFERROR COUNTIF
Extract the Entire Column of a Matched Value	INDEX MATCH

SUM EXAMPLES

3D SUM Multiple Worksheets	SUM
Sum if Equal to Many Items or A Range in Excel	SUMIFS SUMPRODUCT SUMIF
Sum if Equal to X or Y in Excel	SUMIFS SUMPRODUCT SUMIF
Sum Last N Days	SUMIFS SUMIF TODAY SUM
Sum if Contains an Asterisk	SUMIFS SUMIF SUM
Sum in Vertical Range	SUMIFS SUMIF SUM
Sum in Horizontal Range	SUMIFS SUMIF SUM
Sum with Criteria and Or Logic	SUMIFS SUMIF SUM
Sum the Largest N Values	ROW SUMPRODUCT LARGE INDIRECT SUM
Sum for Cell Contains Formula Only	SUMPRODUCT SUM
Sum the Smallest N Values	ROW SUMPRODUCT INDIRECT SUM SMALL
Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column	SUMIFS SUMIF SUM IF COUNTIF SUBTOTAL
Sum by Formula If Cells Are Not Blank in Criteria Range	SUMIFS SUMIF
Sum by SUMPRDUCT with One Specific Criteria Multiple Columns	SUMPRODUCT
Sum by Formula if Cell Ends with	SUMIFS SUMIF
Sum by SUMPRDUCT with Specific Criteria	SUMPRODUCT
Sum if Less Than A Number in Excel	SUMIFS SUMIF
Sum if Greater Than A Number in Excel	SUMIFS SUMIF
Sum if Date is Greater Than A Date in Excel	SUMIFS SUMIF DATE
Sum if Date Between Two Dates in Excel	SUMIFS DATE
Sum by Formula if Cell Contains Special Character	SUMIFS SUMIF
Sum by Formula if Cell Contains Both A and B	SUMIFS
Sum Numbers by Formula if Cells Are Not Equal to Certain Value	SUMIFS SUMIF
Sum Numbers by Formula if Cells Are Equal to A Certain Value	SUMIFS SUMIF
Sum if Cell Contains Text in Another Column	SUMIFS
Sum Data if Between Two Numbers	SUMIFS
Sum Data if Begins with/End with/Contains	SUMIFS SUMIF
Sum Every Nth Column in Excel	SUMPRODUCT MOD COLUMN
Only Sum Visible Cells/Rows in a Filtered List	SUM SUBTOTAL
Sum Data Every N Rows in Excel	SUM
Sum Entire Column or Row in Excel	SUM
Sum Data Based on Criteria Adjacent Columns	SUMIFS SUMPRODUCT
Sum Data by Weekday with Different Formulas/Functions	SUMIFS SUMPRODUCT WEEKDAY
Sum by Week Number in Excel	SUMIFS WEEKNUM
Sum/Extract Data by Month in Columns/Tables	SUMIFS
Sum by Week in Excel	SUMIFS
Sum by Month in Excel	SUMIFS EOMONTH
Sum by Group in Excel	SUMIF SUM IF
Sum Data by Month Ignore Year in Excel	SUMPRODUCT MONTH
Subtotal for Value in Specific Cell in Excel	SUMIF
Calculate Sum of a Column Ignore #N/A in Excel	SUMIF
SUM Cells If the Adjacent Cell Match One Criteria in Excel	IF SUM ISBLANK
Sum Range Ignoring Errors in Excel	IF SUM ISERROR
Use SUMIF with Multiple Criteria in Same Column in Excel	SUMIF SUM
Sum Same Cell in Multiple Worksheets in Microsoft Excel	SUM
Sum Only Positive Numbers or Negative Numbers in Excel	SUMIF
Sum the Absolute Values in Excel	SUMIF SUM SUMPRODUCT ABS
Sum Every Nth Row or Column	SUM SUMPRODUCT MOD ROW
Sum Multiple Columns based on One Criteria	SUMIF SUM
Sum Values between Two Dates	SUMIF
Sum Values in a Column based on Criteria in Another Column	SUMIF
Sum if Cells Begin with Specific Text or Number in Another Cells	SUMIF SUMPRODUCT LEFT
Sumif with 3D Reference for Multiple Worksheet	SUMIF SUMPRODUCT INDIRECT
SUM a Column with Lookup value in a Range	INDEX MATCH SUM
Calculate Cumulative Totals with Excel SUM Function	SUM

OTHER FORMULAR EXAMPLES

Calculate the Area in Excel	PI POWER
Get Address of First Cell in Range	INDEX ROW ADDRESS COLUMN CELL MIN
Insert Line Break	CHAR
Sort Positive Numbers and Negative Numbers by Absolute Values	ABS
Create Dynamic Named Range in Excel	INDEX COUNTA
Check If a Number is Integer in Excel	INT
Limit the Number of Decimal Places in Excel	Round
Round a Range of Cells in Excel	Round
Countif across Multiple Worksheets	SUMPRODUCT INDIRECT COUNTIF
Ignoring Blank or Zero Cells with Conditional formatting	IF SMALL AND
Calculating Discount Rate	ABS
Remove Trailing Spaces from Cells in Excel	TRIM
Repeat Cell Value N times in Excel	VLOOKUP
Generate All Possible Combinations of Two Lists in Excel	COUNTA IF INDEX INT MOD ROW
Return Larger or Smaller of Two Values	MAX MIN
Get Workbook Path Only	CELL Find LEFT
Generate a List of Random Numbers without Duplicates	RAND
Check If Multiple Cells are Equal In Excel	COUNTIF AND EXACT
Changing Negative Number to Zero in Excel	IF MAX SMALL
Create a Chart That Updates with New Data Automatically	COUNTA
Insert The File Path and Filename into Cell	CELL Find LEFT LEN MID Substitute SEARCH RIGHT
List all Worksheet Names	INDEX ROWS MID LOOKUP Choose FIND
Randomly Select Cells	INDEX ROWS RANDBETWEEN
Reference Tab Name	Find MID CELL
Show Only Positive Values	IF SUM
Removing Salutation from Name	Find LEFT LEN MID RIGHT
Replacing Cell References in Multiple Cells (Formulas)	DATE Choose
Combine 3 Cells to Generate One Date	DATE
Create an Array of Numbers	INDEX INDIRECT MIN ROW
Data Validation for Specified Text only	Find ISNUMBER
The difference between Replace function and Substitute function in Excel	Substitute Replace
BMI Calculation Formula In Ms Excel	CONVERT
Send Email Using Hyperlink Function	HYPERLINK
Calculate A Ratio From Two Numbers In Excel	GCD