600+ Basic Excel Formulas & Functions with Examples

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

Sidebar