1.
Logical Functions
Used for decision-making and conditional formulas.
Function Description Example
Tests a condition and returns one =IF(A1>50,"Pass","Fail")
IF
value if TRUE, another if FALSE.
AND / OR /
Combine multiple logical tests. =IF(AND(A1>50,B1>50),"Pass","Fail")
NOT
2. Lookup & Reference Functions
Used to search and retrieve data from tables or ranges.
Function Description Example
Finds a value vertically in a =VLOOKUP("John",A2:D10,3,FALSE)
VLOOKUP
table.
Finds a value horizontally in =HLOOKUP("Q1",A1:H2,2,FALSE)
HLOOKUP
a table.
Modern replacement for
=XLOOKUP("John",A2:A10,B2:B10,"Not
XLOOKUP VLOOKUP & HLOOKUP Found")
(Excel 365+).
INDEX + Flexible lookup alternative =INDEX(B2:B10,MATCH("John",A2:A10,0))
MATCH to VLOOKUP.
Returns a range offset from =SUM(OFFSET(A1,1,0,3,1))
OFFSET
a starting point.
INDIRECT Returns reference from text. =INDIRECT("A"&B1)
3. Text Functions
Used to clean, format, and extract text.
Function Description Example
Joins text strings with or
CONCAT / TEXTJOIN =TEXTJOIN(", ",TRUE,A1:A3)
without separators.
LEFT / RIGHT / MID Extract specific characters. =LEFT(A1,5)
LEN Counts number of characters. =LEN(A1)
TRIM Removes extra spaces. =TRIM(A1)
UPPER / LOWER /
Changes text case. =PROPER(A1)
PROPER
SEARCH / FIND Locates text position in a string. =SEARCH("a",A1)
SUBSTITUTE /
Replace parts of text. =SUBSTITUTE(A1,"old","new")
REPLACE
4. Statistical & Mathematical Functions
Used for analysis and calculations.
Function Description Example
Sum with one or
SUMIF / SUMIFS multiple =SUMIFS(C2:C10,A2:A10,"East",B2:B10,">100")
conditions.
Count cells
COUNTIF /
meeting =COUNTIF(A2:A10,">=50")
COUNTIFS
conditions.
AVERAGEIF / Average with =AVERAGEIF(B2:B10,">=60",C2:C10)
AVERAGEIFS condition(s).
Rank values in a =RANK(A2,$A$2:$A$10)
RANK / [Link]
list.
ROUND /
Round numbers =ROUND(A1,2)
ROUNDUP /
precisely.
ROUNDDOWN
5. Date & Time Functions
Used for scheduling, aging reports, and timelines.
Function Description Example
TODAY / NOW Returns current date/time. =TODAY()
DATEDIF Calculates difference between dates. =DATEDIF(A1,B1,"y")
EDATE / Returns date a set number of months =EDATE(A1,6)
EOMONTH before/after.
NETWORKDAYS Counts working days between dates. =NETWORKDAYS(A1,B1)
6. Financial Functions
Used for interest, loan, and investment analysis.
Function Description Example
PMT Calculates loan payment. =PMT(rate,nper,pv)
FV Future value of investment. =FV(rate,nper,pmt,pv)
NPV / IRR Investment valuation. =NPV(rate,values)
RATE Interest rate per period. =RATE(nper,pmt,pv,fv)
7. Data Analysis Functions
Useful for dashboards and reports.
Function Description Example
Extracts values
GETPIVOTDATA from a =GETPIVOTDATA("Sales",$A$3,"Region","East")
PivotTable.
Performs
SUBTOTAL calculations on =SUBTOTAL(9,A2:A100)
filtered lists.
Similar to
AGGREGATE SUBTOTAL but =AGGREGATE(9,6,A2:A100)
more flexible.
8. Array & Advanced Math Functions
For complex calculations.
Function Description Example
SUMPRODUCT Multiplies and sums arrays. =SUMPRODUCT(A2:A10,B2:B10)
Converts rows to columns or vice =TRANSPOSE(A1:C3)
TRANSPOSE
versa.
MMULT /
Matrix operations. =MMULT(A1:B2,C1:D2)
MINVERSE