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 ExistRANK COUNTIF
Rank Numbers without Repetitive RanksRANK COUNTIF
Rank data Based on the Number of OccurrencesRANK COUNTIF
Reverse Rank Order RANK
Sort Dynamic DataRANK VLOOKUP
Rank values based a specific value in another columnRANK COUNTIFS IF
Rank Data with Multiple CriteriaSUMPRODUCT
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 ExcelRIGHT Substitute LEN FIND
Sort Names by Middle Name in ExcelSubstitute IF MID FIND ISERR

DATE AND TIME EXAMPLES

Calculate Days OpenISBLANK IF TODAY DAYS
Add Workdays in ExcelWORKDAY WORKDAY.INTL
Add Months To Date In ExcelDAY EDATE DATE MONTH YEAR
Add Minutes to Time in ExcelTIME MOD
Add Hours To Time in ExcelTIME MOD
Check Dates in chronological orderSUM SUMPRODUCT COUNTIF IF SORT COUNTIFS
Add the Business Days to DateWORKDAY
Adding Days Exclude Specific Days of WeekWORKDAY WORKDAY.INTL
Add Days to Date in MS ExcelDAY DATE MONTH YEAR
Extract date from multiple cells containing both date and timeINT
Auto Fill Weekdays or Weekends in ExcelROW INT Ceiling TEXT
Convert Date & Time Format to Date in ExcelDAY MONTH YEAR
Extract Time From A Date And Time in ExcelTIME HOUR MINUTE SECOND
Convert Military Time to Standard Time in ExcelTIMEVALUE MID LEFT RIGHT TEXT
Calculate Number of Weekends between Two DatesINT WEEKDAY DAYS
Delete or Remove Year from a Date in ExcelMONTH DAY TEXT
Convert Days to Years, Months, Days in ExcelDATEDIF
Convert Julian Date to a Calendar date in ExcelIF 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 ExcelMAX MIN TEXT
Converting Dates to Fiscal Quarters and YearsYEAR 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 ExcelTEXT
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 DatesDATEDIF TODAY INT Round
 Group Time by Hour or MinuteFLOOR 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 FormatDATE 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 VLookupVLOOKUP
Find the Closest Data to the Data Provided in ExcelINDEX MATCH MIN ABS
Extract Last Two Words From Multiple CellsMID SUBSTITUTE LEN FIND
Extract Multiple Lines From A CellTRIM MID SUBSTITUTE CHAR REPT LEN
Extract Multiple Match Values into Separate ColumnsINDEX MATCH IF ROW SMALL MIN IFERROR COLUMN
Faster Trick with 2 VLOOKUPSNA IF VLOOKUP
Extract Unique Items From A ListINDEX MATCH COUNTIF LOOKUP
Extract all the matches with helper ColumnINDEX MATCH MAX
VLOOKUP From Another Sheet Not WorkingIFERROR VLOOKUP INDEX MATCH
If Cell Begins with One of Three Supplied CharactersSUM COUNTIF
Fix #N/A Error For VLOOKUP From Another SheetIF IFERROR ISERROR VLOOKUP
Get Employee Information by VLOOKUPVLOOKUP
VLOOKUP with Two Lookup TablesVLOOKUP IF
VLOOKUP with Multiple Lookup ValuesVLOOKUP
VLOOKUP Data by DateVLOOKUP
VLOOKUP – Retrieve Data from Another WorkbookVLOOKUP
VLOOKUP – Retrieve Data from Another WorksheetVLOOKUP
Case Sensitive Lookup with SUMPRODUCT and EXACTVLOOKUP SUMPRODUCT MATCH
Basic Usage of INDEX & MATCH – Case Sensitive LookupINDEX MATCH EXACT
Basic Rates Calculation by VLOOKUP Based on Weight BandINDEX MATCH
Basic Grade Calculation by VLOOKUP Function – Approximate MatchINDEX MATCH VLOOKUP
Basic Discount Calculation with VLOOKUP FunctionINDEX MATCH VLOOKUP
Basic Usage of INDEX & MATCH – Exact MatchINDEX MATCH
Basic Usage of INDEX & MATCH – Approximate MatchINDEX MATCH
Find the Smallest Value and Smallest Positive ValueIF SMALL MIN COUNTIF
VLOOKUP with Dropdown List in ExcelVLOOKUP
Find the Last or First Value in a Range Greater Than X in ExcelIF INDEX MATCH LOOKUP
Lookup Values from Right to Left in ExcelIF INDEX MATCH VLOOKUP
Compare Two Lists in Different Worksheets using VLOOKUPIF ISERROR VLOOKUP
Extract a Unique List based on Criteria in ExcelIF INDEX MATCH COUNTIF
Vlookup to Return the SUM of Two or More ColumnsVLOOKUP SUMPRODUCT SUM
Pick a Random Name from a List in ExcelINDEX COUNTA RANDBETWEEN
Find Most Common Value in a Range in ExcelINDEX MATCH
Find Max And Min Value with Single or Multiple CriteriaIF MAX MIN
Get Cell Address of Max or Min Value in a Range in ExcelINDEX MATCH MAX MIN CELL
Find the First or Last Non-blank Cell in a Row or Column in ExcelINDEX MATCH LOOKUP ISBLANK
Extract a Unique Distinct List from a Column in ExcelINDEX MATCH IF COUNTIF
Vlookup to Return Blank Instead of 0 or NA Error in ExcelIF VLOOKUP IFERROR LEN
VLOOKUP to Return Value if Date Falls between Two dates in ExcelLOOKUP DATE
VLOOKUP Returns zero instead of #NA in Excel VLOOKUP IFERROR
Find the Earliest and Latest Date in a Range of Dates in ExcelMAX MIN SMALL LARGE
Find Closest Value or Nearest Value in a Range in ExcelINDEX MATCH MIN SMALL LARGE COUNTIF ABS
Find Missing Numbers in a Sequence in ExcelCOUNTIF IF MATCH SMALL LARGE ROW ISNA
VLOOKUP Return Multiple Values HorizontallyIF INDEX ROW SMALL VLOOKUP COLUMN
Excel Vlookup From Another WorkbookIF VLOOKUP ISNA
Excel Vlookup Return True or FalseIF VLOOKUP ISNA
Find the Maximal or Minimal String Based on Alphabetic OrderINDEX MATCH COUNTIF
get last match that cell contains one of several values in a rangeLOOKUP SEARCH
get first match that cell contains one of several values in a rangeINDEX MATCH SEARCH ISNUMBER
Get the First Match in Two Excel RangesCOUNTIF INDEX MATCH
Get Cell Address of a Lookup ValueCELL INDEX MATCH
Two-way Lookup FormulaINDEX MATCH
Lookup Entire Row using INDEX/MATCHINDEX MATCH
find the nth Largest ValueINDEX 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 TimesCHAR REPT
Trap Error or Replace Error by Specific Value with IFERROR functionIFERROR
Add Leading Zeros in ExcelTEXT
Abbreviate Names Or Words in ExcelIF TEXTJOIN ROW MID INDIRECT MATCH ISNUMBER CODE LEN LEFT FIND TRIM
Convert State Names To AbbreviationsVLOOKUP INDEX MATCH
Extract substring In ExcelMID LEFT RIGHT
Remove All Extra Spaces and Keep Only One Between WordsTRIM
Split Cells by the First Space in Texts in ExcelLEFT RIGHT LEN FIND
Filter Cells Starts with Number or Letter in ExcelLEFT ISNUMBER
Concatenate Text based on unique values in Another ColumnIFERROR COUNTIF INDEX MATCH
Extract Text between Two Text Strings in ExcelLEN MID SEARCH
Convert Date to YYYY-MM-DD format in ExcelTEXT
Remove the First/Last Word from Text string in CellLEFT RIGHT LEN FIND SUBSTITUTE TRIM
Remove Prefix and Suffix in Given Cells in ExcelLEFT RIGHT LEN
 Check If a Cell is Uppercase, Lowercases or CapitalizeUPPER LOWER Proper EXACT
Concatenate Data with a Line Break in ExcelCHAR Concat
Remove Numbers from Text in ExcelIF INDIRECT ROW MID
Return a Value If a Cell Contains a Specific Text in ExcelIF ISNUMBER SEARCH
Insert Character or Text to CellsLEFT MID
Convert Scientific Notation to Text or a Number in ExcelTRIM UPPER
Reverse Concatenate Formula in ExcelTRIM MID SUBSTITUTE REPT
Reverse Text String in ExcelINDIRECT LEN ROW MID TEXTJOIN
Change Uppercase to Title Case or Sentence Case in ExcelProper
Removing Dash Characters in ExcelSUBSTITUTE
Extract First or Last N CharactersLEFT RIGHT
Extract Part of Text StringLEFT RIGHT MID
Generate Random Character StringsCHAR RANDBETWEEN
Replace Last Comma in String with “and” wordSUBSTITUTE LEN
Remove Apostrophe or Text IndicatorVALUE
Sorting IP AddressLEFT RIGHT MID FIND TEXT
Add Prefix or suffix to CellConcat
Get the List of File Names From a FolderIFERROR INDEX ROW
Remove Leading and Trailing SpacesTRIM CHAR Substitute CLEAN
Generate Random PasswordsCHAR RANDBETWEEN
Add a Character or Letter before Each Word in a CellSUBSTITUTE Concat
Check If the First Letter is capitalizedLEN RIGHT Replace LEFT UPPER Proper Concat
insert leading zeros to number or textConcat TEXT
Split full name to first and last nameLEFT RIGHT MID FIND SUBSTITUTE
add text to the end all cellsConcat
add text to the beginning of all cellsConcat
remove first and last characters from text stringLEFT RIGHT LEN
 remove unwanted characters from text stringSUBSTITUTE CHAR
extract nth word from text stringSUBSTITUTE MID LEN REPT TRIM
get first word from text stringFIND IF LEFT ISERR
get last word from text stringFIND SUBSTITUTE LEN RIGHT
extract word that containing a specific characterFIND SUBSTITUTE MID REPT TRIM
replace all characters after the first specific characterFIND 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 StringReplace
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 PaymentsCUMPRINC
Calculate Compound InterestFV
Calculate Compound or Average Annual Growth RateAVERAGE RATE
remove all spaces between numbers or words FIND Replace Replace
Calculate The Period of Loan or InvestmentNPER
Calculate The Bond Valuation PV
CAGR Formula Examples in ExcelRRI
Calculate Interest for Given PeriodIPMT
Calculate Interest Rate for LoanRATE
Calculate Cap Percentages to Specific Value IF MIN
Cash Denomination CalculatorSUMPRODUCT INT

COUNT EXAMPLES

Count Attendance and Absence with COUNTIF functionCOUNTIF
Count Cells that are Case SensitiveSUMPRODUCT COUNTIF COUNTIFS ISNUMBER FIND
Count Not Empty or Blank Cells with COUNTIFS COUNTIFS
Create a Summary Count by Month with COUNTIFSCOUNTIFS EDATE COUNTIF
Running Count of Occurrence ListCOUNTIF IF
Use COUNTIFS function with Multiple Criteria and OR Logic COUNTIFS SUM
Count Non-Contiguous Range using COUNTIFCOUNTIF SUM INDIRECT
Count Unique Numeric Values with Criteria in a RangeSUM IF FREQUENCY
Count Unique Numeric Values in a RangeSUM SUMPRODUCT FREQUENCY COUNTIF
Count Unique Dates in ExcelCOUNT 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 ExcelCOUNTIF SUMPRODUC INDIRECT
Count Numbers Nth Digit Equals to Specific Number in ExcelSUMPRODUC MID
Count Number by Range with COUNTIFS FunctionCOUNTIFS
Count Matches between Two Columns in ExcelSUMPRODUC
Count Specific Items in ListCOUNTIF COUNTIFS SUMIFS
Count the Cells that Match Two CriteriaCOUNTIF COUNTIFS SUMPRODUCT SUMIFS
Count Dates of Given Year SUMPRODUCT YEAR
Count Dates by Day of Week in ExcelSUMPRODUCT WEEKDAY
Count Cells that do not Contain Errors in ExcelSUMPRODUCT SUM ISERROR NOT
Count Cells that do not Contain Specific Text in ExcelCOUNTIF
Count Cells that Contain Specific Text in ExcelCOUNTIF
Count Cells that Contain Text in ExcelCOUNTIF COUNTIFS
 Count Cells that Contain Positive or Negative numbersCOUNTIF
Count Cells that Contain even or odd numbersSUMPRODUCT MOD
Count Cells that Contain only numbers in ExcelSUMPRODUCT ISNUMBER COUNT NOT
 Count Number of Cells that Contain Exactly N Characters in ExcelREPT COUNTIF
Count Number of Cells that Contain Errors in ExcelSUMPRODUCT SUM ISERROR COUNTIF
Count Cells that Contain X or Y in ExcelSUMPRODUCT COUNTIF FIND ISNUMBER
Count Cells Are Not Blank or Empty in ExcelCOUNTIF COUNTA
Count Blank or Empty Cells in ExcelCOUNTIF COUNTBLANK
 Count Cells That Begin with Specific Text in ExcelCOUNTIF
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 ExcelSUBSTITUTE 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 ExcelCOUNTIF
Count Dates in Given Year/Month/Day in ExcelCOUNTIF COUNTIFS IF SUMPRODUCT SUM YEAR MONTH
Count the Number of “Yes” / “No” in a RangeCOUNTIF
COUNTIF with Multiple CriteriaCOUNTIF COUNTIFS SUMPRODUCT
Count Cells between Two Dates with Multiple Criteria COUNTIF SUMPRODUCT
Count Blank or Non-blank Cells in Filtered RangeSUBTOTAL
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 CriteriaAVERAGEIFS COUNTIFS SUMIFS
Calculate Average Of Last 5 Or N Values In ColumnsAVERAGE COUNT MIN
Calculating Average Of The NumbersAVERAGE AVERAGEIF LARGE
Calculate Average Response Time Per MonthAVERAGEIFS
Average Only Positive or Negative Numbers of a RangeAVERAGE IF
Average of Working Hours by Formula in ExcelAVERAGE AVERAGEIF AVERAGEIFS
Average per Week by Formula in ExcelSUM COUNTIF AVERAGE SUMPRODUCT
Average with Multiple Criteria in ExcelAVERAGE
Average of Top N Values in ExcelAVERAGE LARGE
Average the Last N Numeric Values in ExcelROW IF ISNUMBER LOOKUP AVERAGE LARGE
Average Last N Values in Multiple ColumnsAVERAGE COUNT
Calculate Average by MonthAVERAGEIFS
Calculate Average If Criteria Not Blank/Ignore Blank CellAVERAGE AVERAGEIF AVERAGEIFS
Calculate Average Ignore Non-Numeric Values and ErrorsAVERAGE AVERAGEIF
Average and Ignore Errors in ExcelAVERAGE AVERAGEIF ISERROR IF
Calculate Average among Multiple Different WorksheetsAVERAGE
Ignore Error Values When Calculating the AverageAVERAGE AVERAGEIF IF IFERROR ISNUMBER

IF FUNCTION EXAMPLES

Add Row Numbers And Skip BlanksIF ISBLANK COUNTA
Assigning Points based on Late TimeIF VALUE
Check If a Cell is Blank or EmptyISBLANK IF COUNTIF COUNTBLANK
If Cell is This Value or That ValueIF OR
If Value is Greater Than A Certain ValueIF ISBLANK
If Cell is Not BlankIF ISBLANK
If Cell is BlankIF ISBLANK
If Cell Equals Certain Text StringIF COUNTIF
If Cell Contains Either Text1 or Text2IF COUNTIF
If Cell Contains Certain Text OR Equals Certain TextIF 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 ExcelMEDIAN IF
Check If value Is between Two Numbers in ExcelAND IF MAX MIN
Replace Duplicates with Blank Cells in ExcelIF COUNTIF
check if Cell contains one of many values from rangeIF 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 NumbersAND 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 NumberingINDEX ROW

COLUMN/ROW EXAMPLES

Filter or Remove ColumnsISNUMBER FILTER MATCH
Split Columns to Seprated Negative and Positive Values in ExcelIF
Transpose Every N Rows of Data into Muliptle Columns in ExcelINDEX ROW COLUMN
Get Row Number From a Vlookup in ExcelROW MATCH
Move Every Other Row to a New Column in ExcelROW IF
Convert Horizontal List of Data into Vertical in ExcelINDEX ROWS
Highlight Cell or Row If Date Is In Current Day/Week/MonthWEEKDAY TODAY TEXT
Convert Multiple Rows into a Single Row in ExcelCOLUMN ROW FLOOR
Extract the Column Header of the Largest Value in a Row in ExcelINDEX MATCH MAX
Multiply two columns and Sum The Result in ExcelSUMPRODUCT
Compare Two Columns and Return Values in Third Column in ExcelVLOOKUP
Find and Highlight Duplicate Rows in ExcelSUMPRODUCT IF Concat TEXTJOIN COUNTIF
convert Column Letter to Number in ExcelADDRESS INDIRECT COLUMN Substitute
Check If Value in a Column exists in Another Column in ExcelIF VLOOKUP ISERROR
Copy and Paste Only Non-blank CellsIF INDEX ROW ROWS LOOKUP
Split Data in One Column to Multiple ColumnsINDEX ROW ROWS COLUMNS
Find Duplicate Values in Two ColumnsIF ISERROR MATCH COUNTIF AND
Find Duplicate RowsSUMPRODUCT IF
Highlight Duplicate RowsCOUNTIF COUNTIFS COUNTA
Highlight RowsSEARCH LEFT
combine columns without losing dataCOUNTA
convert column letter to numberINDIRECT COLUMN
convert column number to letterSubstitute ADDRESS
Split Multiple Lines from a Cell into RowsTRIM 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 CriteriaFILTER LEFT
Filter with Exact Match(Case-Sensitive)FILTER EXACT
Filter or Extract for Multiple OR CriteriaISNUMBER MATCH FILTER
Filter or Extract with a Partial MatchINDEX ROW SMALL IF ISNUMBER SEARCH AGGREGATE FILTER
Filter Data between Date Values FILTER
Filter Data By Date FieldFILTER MONTH YEAR
Extract or Filter Every Nth RowFILTER MOD ROWS
Extract or Filter exclude blank valuesFILTER
Extract or Filter Horizontal DataFILTER TRANSPOSE
Extract or Filter on The Top N Values with CriteriaLARGE SORT FILTER IF
Extract matching values From Two ListsCOUNTIF INDEX IF ROW SMALL IFERROR NOT
Extract all Partial MatchesINDEX ROW SMALL IF ISNUMBER SEARCH AGGREGATE
Extract Common Values in Two ListsCOUNTIF UNIQUE SORT FILTER
Filter Data by Column and Sort by RowSORT FILTER
Filter And Transpose Data From Horizontal To VerticalFILTER TRANSPOSE
Extract Attribute Values from XML/HTML DataMID 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 WorksheetsSUM
Sum if Equal to Many Items or A Range in ExcelSUMIFS SUMPRODUCT SUMIF
Sum if Equal to X or Y in ExcelSUMIFS SUMPRODUCT SUMIF
Sum Last N Days SUMIFS SUMIF TODAY SUM
Sum if Contains an AsteriskSUMIFS SUMIF SUM
Sum in Vertical RangeSUMIFS SUMIF SUM
Sum in Horizontal RangeSUMIFS SUMIF SUM
Sum with Criteria and Or LogicSUMIFS SUMIF SUM
Sum the Largest N ValuesROW SUMPRODUCT LARGE INDIRECT SUM
Sum for Cell Contains Formula OnlySUMPRODUCT SUM
Sum the Smallest N Values ROW SUMPRODUCT INDIRECT SUM SMALL
Subtotal Values for Groups and Only Keep One Subtotal for A Group in ColumnSUMIFS SUMIF SUM IF COUNTIF SUBTOTAL
Sum by Formula If Cells Are Not Blank in Criteria RangeSUMIFS SUMIF
Sum by SUMPRDUCT with One Specific Criteria Multiple Columns SUMPRODUCT
Sum by Formula if Cell Ends withSUMIFS SUMIF
Sum by SUMPRDUCT with Specific CriteriaSUMPRODUCT
Sum if Less Than A Number in ExcelSUMIFS SUMIF
Sum if Greater Than A Number in ExcelSUMIFS SUMIF
Sum if Date is Greater Than A Date in ExcelSUMIFS SUMIF DATE
Sum if Date Between Two Dates in ExcelSUMIFS DATE
Sum by Formula if Cell Contains Special CharacterSUMIFS SUMIF
Sum by Formula if Cell Contains Both A and BSUMIFS
Sum Numbers by Formula if Cells Are Not Equal to Certain ValueSUMIFS SUMIF
Sum Numbers by Formula if Cells Are Equal to A Certain Value SUMIFS SUMIF
 Sum if Cell Contains Text in Another ColumnSUMIFS
Sum Data if Between Two NumbersSUMIFS
Sum Data if Begins with/End with/ContainsSUMIFS SUMIF
 Sum Every Nth Column in ExcelSUMPRODUCT MOD COLUMN
Only Sum Visible Cells/Rows in a Filtered List SUM SUBTOTAL
 Sum Data Every N Rows in ExcelSUM
Sum Entire Column or Row in ExcelSUM
Sum Data Based on Criteria Adjacent ColumnsSUMIFS SUMPRODUCT
Sum Data by Weekday with Different Formulas/FunctionsSUMIFS SUMPRODUCT WEEKDAY
Sum by Week Number in ExcelSUMIFS WEEKNUM
Sum/Extract Data by Month in Columns/TablesSUMIFS
Sum by Week in ExcelSUMIFS
Sum by Month in ExcelSUMIFS EOMONTH
Sum by Group in ExcelSUMIF SUM IF
Sum Data by Month Ignore Year in ExcelSUMPRODUCT MONTH
Subtotal for Value in Specific Cell in ExcelSUMIF
Calculate Sum of a Column Ignore #N/A in ExcelSUMIF
SUM Cells If the Adjacent Cell Match One Criteria in ExcelIF SUM ISBLANK
Sum Range Ignoring Errors in ExcelIF SUM ISERROR
Use SUMIF with Multiple Criteria in Same Column in ExcelSUMIF SUM
Sum Same Cell in Multiple Worksheets in Microsoft ExcelSUM
Sum Only Positive Numbers or Negative Numbers in ExcelSUMIF
Sum the Absolute Values in ExcelSUMIF 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 ExcelPI POWER
Get Address of First Cell in RangeINDEX ROW ADDRESS COLUMN CELL MIN
Insert Line BreakCHAR
Sort Positive Numbers and Negative Numbers by Absolute ValuesABS
Create Dynamic Named Range in ExcelINDEX COUNTA
Check If a Number is Integer in ExcelINT
Limit the Number of Decimal Places in ExcelRound
Round a Range of Cells in ExcelRound
Countif across Multiple WorksheetsSUMPRODUCT INDIRECT COUNTIF
Ignoring Blank or Zero Cells with Conditional formattingIF SMALL AND
Calculating Discount RateABS
Remove Trailing Spaces from Cells in ExcelTRIM
Repeat Cell Value N times in ExcelVLOOKUP
Generate All Possible Combinations of Two Lists in ExcelCOUNTA IF INDEX INT MOD ROW
Return Larger or Smaller of Two ValuesMAX MIN
Get Workbook Path OnlyCELL Find LEFT
Generate a List of Random Numbers without DuplicatesRAND
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 DateDATE
Create an Array of NumbersINDEX INDIRECT MIN ROW
Data Validation for Specified Text onlyFind ISNUMBER
The difference between Replace function and Substitute function in Excel Substitute Replace
BMI Calculation Formula In Ms ExcelCONVERT
Send Email Using Hyperlink FunctionHYPERLINK
Calculate A Ratio From Two Numbers In ExcelGCD

Sidebar