Math & Arithmetic
Formula Description Example
SUM(range) Adds numbers =SUM(A1:A5)
AVERAGE(range) Calculates average =AVERAGE(B2:B10)
ROUND(num, digits) Rounds a number =ROUND(12.567, 1) → 12.6
ABS(num) Absolute value =ABS(-10) → 10
INT(num) Rounds down to integer =INT(5.9) → 5
Text Functions
Formula Description Example
CONCAT(text1, text2,…) Joins text =CONCAT(A1, B1)
TEXT(value, format) Formats as text =TEXT(TODAY(), "dd-mmm-yyyy")
LEFT(text, num_chars) Extracts from left =LEFT("Excel", 2) → Ex
RIGHT(text, num_chars) Extracts from right =RIGHT("Excel", 3) → cel
LEN(text) Counts characters =LEN("Power") → 5
Logical Functions
Formula Description Example
IF(cond, if_true, if_false) Conditional check =IF(A1>50, "Pass", "Fail")
AND(cond1, cond2,…) TRUE if all conditions met =AND(A1>10, B1<20)
OR(cond1, cond2,…) TRUE if any condition met =OR(A1>100, B1=50)
NOT(logical) Reverses TRUE/FALSE =NOT(TRUE) → FALSE
Lookup & Reference
Formula Description Example
VLOOKUP(val, table, col, =VLOOKUP("John", A2:C10, 2,
Vertical lookup
[exact]) FALSE)
Returns value from
INDEX(array, row, [col]) =INDEX(A2:C5, 2, 3)
position
MATCH(val, range, [type]) Finds position of value =MATCH(42, A1:A5, 0)
Date & Time
Formula Description Example
TODAY() Current date =TODAY()
NOW() Current date & time =NOW()
DATEDIF(start, end, unit) Date difference =DATEDIF(A1, B1, "Y") → Years
Statistical Functions
Formula Description Example
COUNT(range) Counts numeric cells =COUNT(A1:A10)
MAX(range) Largest value =MAX(B2:B10)
MIN(range) Smallest value =MIN(B2:B10)
Error Handling
Formula Description Example
IFERROR(val, fallback) Returns fallback if error =IFERROR(A1/B1, "Error")
ISERROR(val) Checks if error exists =ISERROR(A1/B1)
Already know the basics of Excel? It’s time to unlock its real power.
A few months ago, I thought I had "mastered" Excel because I knew VLOOKUP, SUM, and IF. But when
I started working on real-world projects — dashboards, automation, data analysis — I realized Excel
was way deeper than I imagined.
That’s when I discovered advanced functions — and everything changed.
If you’re ready to go beyond basics and think like an Excel analyst, here are advanced Excel
functions, grouped by category, that will help you work smarter, not harder
Lookup & Reference (Advanced)
Formula Description Example
=XLOOKUP(lookup, lookup_array, Replaces
=XLOOKUP("ID123",
return_array, [not_found], VLOOKUP/HLOOKUP with
A2:A100, B2:B100)
[match_mode], [search_mode]) more flexibility
Returns a value from a
=INDEX(array, row, [col]) =INDEX(A1:C10, 3, 2)
specific row/column
=XMATCH(lookup, lookup_array, Advanced MATCH with =XMATCH("Q3",
[match_mode], [search_mode]) search modes Quarters)
Converts text into a cell
=INDIRECT("A"&B1) =INDIRECT("Sheet2!B5")
reference
Text Functions (Advanced)
Formula Description Example
Joins text with a
=TEXTJOIN(", ", TRUE, =TEXTJOIN("-", TRUE, "A", "B",
delimiter (ignores
A2:A10) "C") → "A-B-C"
blanks)
Splits text into
=TEXTSPLIT(A1, ",") columns/rows (Excel Splits "A,B,C" → A B C
365)
=TEXTAFTER("Name_ID", Extracts text after
Returns "ID"
"_") delimiter
=SUBSTITUTE(A1, "old", =SUBSTITUTE("Hello", "e",
Replaces specific text
"new") "a") → "Hallo"
3. Logical Functions (Advanced)
Formula Description Example
Replaces nested IFs (cleaner =IFS(Score>90, "A",
=IFS(A1>90, "A", A1>80, "B")
syntax) Score>80, "B")
=SWITCH(Weekday, 1, "Sun", 2, Clean alternative to multiple =SWITCH(2, 1, "A", 2,
"Mon") IFs "B") → "B"
Stores variables for complex =LET(Total, SUM(A1:A5),
=LET(x, A1+B1, x*2)
formulas Total*0.1)
4. Math & Trigonometry (Advanced)
Formula Description Example
=SUMPRODUCT(Quantity, Multiplies & sums arrays
=SUMPRODUCT(A2:A10, B2:B10)
Price) (weighted calcs)
Generates sequential =SEQUENCE(3,2) → 3 rows × 2
=SEQUENCE(5)
numbers (1,2,3,4,5) cols
Generates random numbers =RANDARRAY(2,2) → 2×2
=RANDARRAY(3,3,1,100)
in an array random nums
5. Date & Time (Advanced)
Formula Description Example
=WORKDAY(TODAY(), 7, Adds workdays (excludes
=WORKDAY(Start, 10)
Holidays) weekends/holidays)
Returns the last day of next =EOMONTH("1-Jan-2023",
=EOMONTH(TODAY(), 1)
month 0) → 31-Jan-2023
=NETWORKDAYS(Start, End, Counts workdays between
=NETWORKDAYS(A1, B1)
Holidays) dates
6. Dynamic Arrays (Excel 365)
Formula Description Example
=FILTER(A2:C100,
Filters data dynamically =FILTER(Data, Region="East")
B2:B100="Yes")
Sorts by another column =SORTBY(Employees, Salary,
=SORTBY(Data, Score, -1)
(descending) -1)
=UNIQUE(A2:A100) Extracts unique values =UNIQUE(Countries)
7. Statistical & Data Analysis
Formula Description Example
=FORECAST.LINEAR(x, known_y, =FORECAST.LINEAR(2024, B2:B10,
Predicts future values
known_x) A2:A10)
Population standard
=STDEV.P(Data) =STDEV.P(Scores)
deviation
Measures correlation
=CORREL(X, Y) =CORREL(Price, Demand)
(-1 to 1)
Formula Description Example & Output
=TEXTJOIN(", ", TRUE, Joins text with delimiter =TEXTJOIN("-", TRUE, "A", "B",
A2:A10) (ignores blanks) "C") → "A-B-C"
Splits text into columns =TEXTSPLIT("A,B,C",
=TEXTSPLIT(A1, ",")
(Excel 365) ",") → A | B | C (equal spacing)
=TEXTAFTER("Name_ID", Extracts text after
Returns "ID"
"_") delimiter
=SUBSTITUTE(A1, "old", =SUBSTITUTE("Hello", "e",
Replaces specific text
"new") "a") → "Hallo"
