Excel Formulas: 200+ Basic to Advanced Excel Functions with Examples

Excel 365

Excel 2021+

Basic → Advanced

Excel Formulas Master Guide

Learn Excel formulas from beginner to advanced level with practical examples, explanations and ready-to-use formulas.

0 Formula Examples

15+ Categories

100% Responsive

PDF Download Ready

🟢 1. Basic Excel Formulas

SUM

BASIC

Adds numbers from selected cells or ranges.

=SUM(A2:A10)

Example: If A2:A10 contains sales values, this returns total sales.

AVERAGE

BASIC

Calculates the arithmetic average.

=AVERAGE(A2:A10)

MIN

BASIC

Returns the smallest value.

=MIN(A2:A10)

MAX

BASIC

Returns the largest value.

=MAX(A2:A10)

COUNT

BASIC

Counts cells containing numbers.

=COUNT(A2:A100)

COUNTA

BASIC

Counts non-empty cells.

=COUNTA(A2:A100)

ROUND

BASIC

Rounds a number to specified decimal places.

=ROUND(A2,2)

ROUNDUP

BASIC

=ROUNDUP(A2,0)

ROUNDDOWN

BASIC

=ROUNDDOWN(A2,0)

ABS

BASIC

=ABS(A2)

MOD

BASIC

=MOD(A2,2)

PRODUCT

BASIC

=PRODUCT(A2:A5)

🧠 2. Logical & Conditional Formulas

IF

BASIC

Returns one result when a condition is TRUE and another when FALSE.

=IF(A2>=50,"Pass","Fail")

IFS

INTERMEDIATE

=IFS(A2>=90,"A+",A2>=75,"A",A2>=60,"B",TRUE,"C")

AND

BASIC

=AND(A2>=50,B2="Yes")

OR

BASIC

=OR(A2="Delhi",A2="Mumbai")

NOT

BASIC

=NOT(A2="Closed")

IFERROR

INTERMEDIATE

=IFERROR(A2/B2,0)

IFNA

INTERMEDIATE

=IFNA(XLOOKUP(E2,A:A,B:B),"Not Found")

📊 3. SUMIF, COUNTIF & AVERAGEIF

SUMIF

INTERMEDIATE

=SUMIF(A2:A100,"Delhi",B2:B100)

SUMIFS

ADVANCED

=SUMIFS(C:C,A:A,"Delhi",B:B,"Laptop")

COUNTIF

INTERMEDIATE

=COUNTIF(A2:A100,"Completed")

COUNTIFS

ADVANCED

=COUNTIFS(A:A,"Delhi",B:B,"Completed")

AVERAGEIF

INTERMEDIATE

=AVERAGEIF(A:A,"Delhi",B:B)

AVERAGEIFS

ADVANCED

=AVERAGEIFS(C:C,A:A,"Delhi",B:B,"Laptop")

🔍 4. Lookup & Reference Formulas

VLOOKUP

INTERMEDIATE

Looks for a value in the first column of a table.

=VLOOKUP(E2,A2:C100,3,FALSE)

HLOOKUP

INTERMEDIATE

=HLOOKUP(B1,A1:F5,4,FALSE)

XLOOKUP

ADVANCED

Modern replacement for many VLOOKUP and HLOOKUP use cases.

=XLOOKUP(E2,A2:A100,C2:C100,"Not Found")

INDEX + MATCH

ADVANCED

=INDEX(C:C,MATCH(E2,A:A,0))

XMATCH

ADVANCED

=XMATCH(E2,A2:A100,0)

CHOOSE

INTERMEDIATE

=CHOOSE(A2,"Low","Medium","High")

🔤 5. Text Formulas

CONCAT

BASIC

=CONCAT(A2," ",B2)

CONCATENATE

BASIC

=CONCATENATE(A2," ",B2)

TEXTJOIN

ADVANCED

=TEXTJOIN(", ",TRUE,A2:A10)

LEFT

BASIC

=LEFT(A2,5)

RIGHT

BASIC

=RIGHT(A2,4)

MID

INTERMEDIATE

=MID(A2,3,5)

LEN

BASIC

=LEN(A2)

TRIM

BASIC

=TRIM(A2)

UPPER

BASIC

=UPPER(A2)

LOWER

BASIC

=LOWER(A2)

PROPER

BASIC

=PROPER(A2)

SUBSTITUTE

INTERMEDIATE

=SUBSTITUTE(A2,"Old","New")

TEXT

INTERMEDIATE

=TEXT(A2,"dd-mmm-yyyy")

FIND

INTERMEDIATE

=FIND("@",A2)

SEARCH

INTERMEDIATE

=SEARCH("excel",A2)

📅 6. Date & Time Formulas

TODAY

BASIC

=TODAY()

NOW

BASIC

=NOW()

DATE

BASIC

=DATE(2026,8,25)

YEAR

BASIC

=YEAR(A2)

MONTH

BASIC

=MONTH(A2)

DAY

BASIC

=DAY(A2)

DATEDIF

ADVANCED

=DATEDIF(A2,B2,"Y")

EOMONTH

ADVANCED

=EOMONTH(A2,0)

EDATE

ADVANCED

=EDATE(A2,3)

NETWORKDAYS

ADVANCED

=NETWORKDAYS(A2,B2)

WORKDAY

ADVANCED

=WORKDAY(A2,10)

🚀 7. Dynamic Array & Modern Excel

FILTER

ADVANCED

=FILTER(A2:C100,B2:B100="Delhi")

UNIQUE

ADVANCED

=UNIQUE(A2:A100)

SORT

ADVANCED

=SORT(A2:C100,2,1)

SORTBY

ADVANCED

=SORTBY(A2:C100,C2:C100,-1)

SEQUENCE

ADVANCED

=SEQUENCE(10,1,1,1)

TRANSPOSE

ADVANCED

=TRANSPOSE(A2:A10)

TAKE

ADVANCED

=TAKE(A2:C100,10)

DROP

ADVANCED

=DROP(A2:C100,5)

🧩 8. Advanced Excel Formulas

LET

ADVANCED

Creates variables inside a formula to improve readability and efficiency.

=LET( sales,B2*C2, tax,sales*18%, sales+tax )

LAMBDA

EXPERT

Creates custom reusable functions without VBA.

=LAMBDA(x,x*18%)(1000)

REDUCE

EXPERT

=REDUCE(0,A2:A10,LAMBDA(a,b,a+b))

SCAN

EXPERT

=SCAN(0,A2:A10,LAMBDA(a,b,a+b))

BYROW

EXPERT

=BYROW(A2:C10,LAMBDA(row,SUM(row)))

BYCOL

EXPERT

=BYCOL(A2:C10,LAMBDA(col,SUM(col)))

MAP

EXPERT

=MAP(A2:A10,LAMBDA(x,x*18%))

💡 9. Real-World Formula Combinations

Extract Email Domain

=TEXTAFTER(A2,"@")

Example: abc@gmail.com → gmail.com

Extract First Name

=TEXTBEFORE(A2," ")

Last Non-Blank Value

=XLOOKUP(2,1/(A:A<>""),A:A)

Multiple Criteria Lookup

=XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2),C2:C100)

Unique Sorted List

=SORT(UNIQUE(A2:A100))

Top 10 Records

=TAKE(SORT(A2:C100,3,-1),10)

📚 10. Excel Formula Quick Reference

Category	Functions	Typical Use
Math	SUM, SUMIF, SUMIFS, ROUND, ROUNDUP, ROUNDDOWN, ABS, MOD	Calculations and totals
Statistics	AVERAGE, MIN, MAX, MEDIAN, MODE, LARGE, SMALL	Analysis
Logical	IF, IFS, AND, OR, NOT, IFERROR, IFNA	Decision making
Lookup	VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH, XMATCH	Finding information
Text	LEFT, RIGHT, MID, LEN, TRIM, TEXTJOIN, CONCAT	Text manipulation
Date	TODAY, NOW, DATE, YEAR, MONTH, DAY, EDATE, EOMONTH	Date calculations
Dynamic Array	FILTER, UNIQUE, SORT, SORTBY, SEQUENCE, TAKE, DROP	Modern Excel analysis
Advanced	LET, LAMBDA, MAP, REDUCE, SCAN, BYROW, BYCOL	Advanced automation

🎯 Excel Formula Learning Roadmap

Level 1 — Beginner
Learn: SUM → AVERAGE → COUNT → MIN → MAX → ROUND → IF

Level 2 — Intermediate
Learn: SUMIF → SUMIFS → COUNTIF → COUNTIFS → Text Functions → Date Functions → VLOOKUP

Level 3 — Advanced
Learn: XLOOKUP → INDEX/MATCH → FILTER → UNIQUE → SORT → TEXTJOIN → Dynamic Arrays

Level 4 — Expert
Learn: LET → LAMBDA → MAP → REDUCE → SCAN → BYROW → BYCOL

🚀 Learn Excel with ChiragCoder

Excel • VBA • SQL • Programming • Automation

Smarter Work Beats Hard Work

© 2026 ChiragCoder