EXCEL Formulas
🔢 1. Math & Trigonometry (Examples)
Formula Use Example
SUM Adds numbers =SUM(A1:A5) → adds values
SUMIF Conditional sum =SUMIF(A1:A10,"Apple",B1:B10)
SUMIFS Multi-condition sum =SUMIFS(C1:C10,A1:A10,"HR",B1:B10,"
Chennai")
ROUND Rounds value =ROUND(12.567,2) → 12.57
INT Removes decimal =INT(10.9) → 10
MOD Remainder =MOD(10,3) → 1
POWER Exponent =POWER(2,3) → 8
SQRT Square root =SQRT(16) → 4
RAND Random value =RAND()
RANDBETWE Random range =RANDBETWEEN(1,100)
EN
📌 All other trig/math formulas follow the same pattern:
=SIN(angle)
=COS(angle)
=LOG(number,base)
📊 2. Statistical Functions (Examples)
Formula Use Example
AVERAGE Mean =AVERAGE(A1:A10)
COUNT Count numbers =COUNT(A1:A10)
COUNTA Count non-blanks =COUNTA(A1:A10)
COUNTIF Conditional count =COUNTIF(A1:A10,"Male"
)
MAX Highest value =MAX(A1:A10)
MIN Lowest value =MIN(A1:A10)
MEDIAN Middle value =MEDIAN(A1:A10)
RANK Rank value =RANK(A2,A2:A10)
STDEV.S Std deviation =STDEV.S(A1:A10)
PERCENTILE. Percentile =[Link](A1:A10
INC ,0.9)
📌 Forecasting pattern
=[Link](x, known_y, known_x)
🔍 3. Lookup & Reference (MOST
IMPORTANT 🔥)
Formula Use Example
XLOOKUP Modern lookup =XLOOKUP(A2,A:A,B:B)
VLOOKUP Vertical lookup =VLOOKUP(A2,A:B,2,0)
INDEX Value by position =INDEX(A1:C10,5,2)
MATCH Find position =MATCH("HR",A1:A10,0)
INDEX+MAT Advanced lookup =INDEX(B:B,MATCH(A2,A:
CH A,0))
OFFSET Dynamic range =SUM(OFFSET(A1,0,0,5,1
))
INDIRECT Text reference =INDIRECT("A"&B1)
🔤 4. Text Functions (Examples)
Formula Use Example
LEFT Left text =LEFT(A1,4)
RIGHT Right text =RIGHT(A1,2)
MID Middle text =MID(A1,2,5)
LEN Length =LEN(A1)
TRIM Remove spaces =TRIM(A1)
UPPER Uppercase =UPPER(A1)
LOWER Lowercase =LOWER(A1)
CONCAT Join text =CONCAT(A1," ",B1)
TEXTJOIN Join with delimiter =TEXTJOIN(",",TRUE,A
1:A5)
SUBSTITU Replace text =SUBSTITUTE(A1,"-","
TE /")
🧠 5. Logical Functions
Formula Use Example
IF Condition =IF(A1>50,"Pass","Fa
il")
IFS Multi-condition =IFS(A1>90,"A",A1>75
,"B")
AND All true =AND(A1>50,B1>50)
OR Any true =OR(A1>50,B1>50)
IFERRO Error handling =IFERROR(A1/B1,0)
R
📅 6. Date & Time
Formula Use Example
TODAY Current date =TODAY()
NOW Date + time =NOW()
DATEDIF Date diff =DATEDIF(A1,B1,
"Y")
EOMONTH Month end =EOMONTH(A1,0)
NETWORKDA Working days =NETWORKDAYS(A1
YS ,B1)
💰 7. Financial
Formula Use Example
PMT Loan EMI =PMT(10%/12,60,50
0000)
FV Future value =FV(8%,10,1000)
PV Present =PV(10%,5,2000)
value
NPV Net present =NPV(10%,A1:A5)
IRR Return rate =IRR(A1:A6)
🔁 8. Dynamic Array (Modern Excel 🔥)
Formula Use Example
FILTER Filter rows =FILTER(A1:C10,B1:B10=
"HR")
SORT Sort data =SORT(A1:A10)
UNIQUE Unique values =UNIQUE(A1:A10)
SEQUENC Number =SEQUENCE(10)
E series
TEXTSPL Split text =TEXTSPLIT(A1,",")
IT
✅ EXCEL FORMULAS & THEIR USES
(COMPLETE)
🔢 1. Math & Trigonometry Functions – Use
Formula Use
ABS Returns absolute (positive) value
ACOS Inverse cosine of a number
ACOSH Hyperbolic inverse cosine
ASIN Inverse sine
ASINH Hyperbolic inverse sine
ATAN Inverse tangent
ATAN2 Angle from X and Y coordinates
ATANH Hyperbolic inverse tangent
CEILING Rounds up to nearest multiple
[Link] Rounds up with control over direction
[Link] Rounds up without sign dependency
COMBIN Number of combinations
COMBINA Combinations with repetition
COS Cosine of angle
COSH Hyperbolic cosine
COT Cotangent
COTH Hyperbolic cotangent
CSC Cosecant
CSCH Hyperbolic cosecant
DEGREES Converts radians to degrees
EVEN Rounds up to nearest even number
EXP Calculates e raised to power
FACT Factorial
FACTDOUBLE Double factorial
FLOOR Rounds down
[Link] Controlled rounding down
[Link] Rounds down without sign
GCD Greatest common divisor
INT Rounds down to integer
LCM Least common multiple
LN Natural logarithm
LOG Logarithm with base
LOG10 Base-10 logarithm
MOD Remainder after division
MROUND Rounds to nearest multiple
MULTINOMIAL Multinomial calculation
ODD Rounds up to odd number
PI Returns π value
POWER Raises number to power
PRODUCT Multiplies numbers
QUOTIENT Integer division result
RADIANS Converts degrees to radians
RAND Random number (0–1)
RANDBETWEEN Random number between limits
ROUND Rounds normally
ROUNDDOWN Rounds downward
ROUNDUP Rounds upward
SEC Secant
SECH Hyperbolic secant
SIGN Returns sign of number
SIN Sine
SINH Hyperbolic sine
SQRT Square root
SQRTPI Square root of (n×π)
SUBTOTAL Aggregated subtotal ignoring filters
SUM Adds numbers
SUMIF Conditional sum (1 condition)
SUMIFS Conditional sum (multiple conditions)
SUMPRODUCT Array-based multiplication + sum
TAN Tangent
TANH Hyperbolic tangent
TRUNC Removes decimal part
📊 2. Statistical Functions – Use
Formula Use
AVERAGE Mean value
AVERAGEA Average incl. text/logic
AVERAGEIF Conditional average
AVERAGEIFS Multi-condition average
COUNT Counts numeric values
COUNTA Counts non-empty cells
COUNTBLANK Counts empty cells
COUNTIF Conditional count
COUNTIFS Multi-condition count
MAX Highest value
MAXA Highest incl. text/logic
MIN Lowest value
MINA Lowest incl. text/logic
MEDIAN Middle value
MODE Most frequent value
[Link] Multiple modes
[Link] Single mode
LARGE nth largest value
SMALL nth smallest value
RANK Rank of number
[Link] Equal ranking
[Link] Average ranking
STDEV.P Population std deviation
STDEV.S Sample std deviation
STDEVA Std dev incl text
STDEVPA Population std dev incl text
VAR.P Population variance
VAR.S Sample variance
VARA Variance incl text
VARPA Population variance incl text
[Link] Percentile (exclusive)
[Link] Percentile (inclusive)
[Link] Quartiles (exclusive)
[Link] Quartiles (inclusive)
CORREL Correlation coefficient
COVARIANCE.P Population covariance
COVARIANCE.S Sample covariance
FORECAST Predict future value
[Link] Linear prediction
FREQUENCY Frequency distribution
GEOMEAN Geometric mean
HARMEAN Harmonic mean
KURT Kurtosis measure
SKEW Skewness
SLOPE Regression slope
INTERCEPT Regression intercept
LINEST Linear regression stats
LOGEST Exponential regression
RSQ R-squared
TREND Trend projection
🔍 3. Lookup & Reference – Use
Formula Use
XLOOKUP Modern lookup replacement
VLOOKUP Vertical lookup
HLOOKUP Horizontal lookup
LOOKUP Basic lookup
INDEX Returns value by position
MATCH Finds position
XMATCH Modern MATCH
OFFSET Dynamic range reference
INDIRECT Converts text to reference
ADDRESS Cell address as text
CHOOSE Selects from list
COLUMN Column number
COLUMNS Number of columns
ROW Row number
ROWS Number of rows
HYPERLINK Creates clickable link
TRANSPOSE Switch rows & columns
FORMULATEXT Shows formula as text
🔤 4. Text Functions – Use
Formula Use
LEFT Extract left text
RIGHT Extract right text
MID Extract middle text
LEN Text length
TRIM Remove extra spaces
UPPER Convert to uppercase
LOWER Convert to lowercase
PROPER Capitalize words
CONCAT Join text
CONCATENATE Old text join
TEXTJOIN Join with delimiter
FIND Find text (case-sensitive)
SEARCH Find text (not
case-sensitive)
SUBSTITUTE Replace text
REPLACE Replace by position
TEXT Format number as text
VALUE Convert text to number
EXACT Exact text match
CLEAN Remove non-printable chars
CHAR Character from code
CODE Code of character
UNICODE Unicode number
UNICHAR Unicode character
REPT Repeat text
T Returns text only
🧠 5. Logical Functions – Use
Formula Use
IF Conditional logic
IFS Multiple conditions
AND All conditions true
OR Any condition true
NOT Logical reverse
XOR Exclusive OR
SWITCH Value-based logic
IFERROR Handle errors
IFNA Handle #N/A
📅 6. Date & Time – Use
Formula Use
TODAY Current date
NOW Current date & time
DATE Create date
TIME Create time
DAY Day value
MONTH Month value
YEAR Year value
HOUR Hour value
MINUTE Minute value
SECOND Second value
WEEKDAY Day of week
WEEKNUM Week number
ISOWEEKNUM ISO week number
DATEDIF Date difference
EDATE Add months
EOMONTH End of month
NETWORKDAYS Workdays count
[Link] Workdays (custom)
WORKDAY Add working days
[Link] Add custom workdays
YEARFRAC Year fraction
💰 7. Financial – Use
Formula Use
PMT Loan payment
FV Future value
PV Present value
NPV Net present value
IRR Internal rate of return
XIRR IRR with dates
RATE Interest rate
NPER Number of periods
IPMT Interest payment
PPMT Principal payment
CUMIPMT Total interest
CUMPRINC Total principal
SLN Straight-line depreciation
DB Declining balance
DDB Double declining balance
SYD Sum-of-years
depreciation
EFFECT Effective interest
NOMINAL Nominal interest
📂 8. Database – Use
Formula Use
DSUM Conditional database
sum
DAVERAGE Conditional average
DCOUNT Numeric count
DCOUNTA Non-blank count
DMAX Max value
DMIN Min value
DGET Extract single record
🧮 9. Engineering – Use
Formula Use
CONVERT Unit conversion
DELTA Equality test
ERF Error function
ERFC Complementary error
FACT Factorial
GCD Greatest divisor
LCM Least multiple
ℹ️ 10. Information – Use
Formula Use
ISBLANK Check empty cell
ISNUMBER Check numeric
ISTEXT Check text
ISLOGICAL Check logical
ISERROR Any error check
ISNA #N/A check
ISEVEN Even check
ISODD Odd check
TYPE Data type
CELL Cell info
INFO System info
[Link] Error code
🔁 11. Dynamic Array – Use
Formula Use
FILTER Filter data dynamically
SORT Sort data
SORTBY Sort using another
column
UNIQUE Unique values
SEQUENCE Generate sequence
RANDARRAY Random array
TAKE Extract rows/cols
DROP Remove rows/cols
CHOOSEROWS Select rows
CHOOSECOLS Select columns
TEXTSPLIT Split text
TEXTAFTER Text after delimiter
TEXTBEFORE Text before delimiter
TOCOL Convert to column
TOROW Convert to row
WRAPROWS Wrap to rows
WRAPCOLS Wrap to columns
🌐 12. Web & XML – Use
Formula Use
WEBSERVICE Fetch web
data
FILTERXML Parse XML
ENCODEURL Encode URL
📦 13. Cube – Use
Formula Use
CUBEVALUE Fetch cube data
CUBEMEMBER Cube member
CUBERANKEDMEMBER Ranked
member
CUBESET Member set
CUBESETCOUNT Set count
🕰️ 14. Compatibility – Use
Formula Use
STDEV Old std deviation
VAR Old variance
FORECAST Old forecast
RANK Old rank
PERCENTILE Old percentile
QUARTILE Old quartile
🔢 1. Math & Trigonometry — Examples
ABS(-10) → 10
ACOS(0.5) → 1.047
ACOSH(2) → 1.317
ASIN(0.5) → 0.524
ASINH(1) → 0.881
ATAN(1) → 0.785
ATAN2(1,1) → 0.785
ATANH(0.5) → 0.549
CEILING(12.3,1) → 13
[Link](-12.3,1) → -12
[Link](-12.3,1) → -12
COMBIN(5,2) → 10
COMBINA(3,2) → 6
COS(0) → 1
COSH(1) → 1.543
COT(1) → 0.642
COTH(1) → 1.313
CSC(1) → 1.188
CSCH(1) → 0.851
DEGREES(PI()) → 180
EVEN(7) → 8
EXP(1) → 2.718
FACT(5) → 120
FACTDOUBLE(7) → 105
FLOOR(12.9,1) → 12
[Link](-12.9,1) → -13
[Link](-12.9,1) → -13
GCD(24,36) → 12
INT(9.8) → 9
LCM(4,6) → 12
LN(EXP(1)) → 1
LOG(100,10) → 2
LOG10(100) → 2
MOD(10,3) → 1
MROUND(14,5) → 15
MULTINOMIAL(2,3) → 10
ODD(8) → 9
PI() → 3.14159
POWER(2,3) → 8
PRODUCT(2,3,4) → 24
QUOTIENT(10,3) → 3
RADIANS(180) → 3.14159
RAND() → random
RANDBETWEEN(1,10) → random
ROUND(12.567,2) → 12.57
ROUNDDOWN(12.567,2) → 12.56
ROUNDUP(12.561,2) → 12.57
SEC(1) → 1.850
SECH(1) → 0.648
SIGN(-25) → -1
SIN(0) → 0
SINH(1) → 1.175
SQRT(16) → 4
SQRTPI(1) → 1.772
SUBTOTAL(9,A1:A10)
SUM(A1:A10)
SUMIF(A:A,"HR",B:B)
SUMIFS(C:C,A:A,"HR",B:B,"Chennai")
SUMPRODUCT(A1:A3,B1:B3)
TAN(1) → 1.557
TANH(1) → 0.761
TRUNC(12.9) → 12
📊 2. Statistical — Examples
AVERAGE(A1:A10)
AVERAGEA(A1:A10)
AVERAGEIF(A:A,"HR",B:B)
AVERAGEIFS(C:C,A:A,"HR",B:B,"Chennai")
COUNT(A1:A10)
COUNTA(A1:A10)
COUNTBLANK(A1:A10)
COUNTIF(A:A,"Male")
COUNTIFS(A:A,"HR",B:B,"Chennai")
MAX(A1:A10)
MAXA(A1:A10)
MIN(A1:A10)
MINA(A1:A10)
MEDIAN(A1:A10)
MODE(A1:A10)
[Link](A1:A10)
[Link](A1:A10)
LARGE(A1:A10,2)
SMALL(A1:A10,2)
RANK(A2,A2:A10)
[Link](A2,A2:A10)
[Link](A2,A2:A10)
STDEV.P(A1:A10)
STDEV.S(A1:A10)
STDEVA(A1:A10)
STDEVPA(A1:A10)
VAR.P(A1:A10)
VAR.S(A1:A10)
VARA(A1:A10)
VARPA(A1:A10)
[Link](A1:A10,0.9)
[Link](A1:A10,0.9)
[Link](A1:A10,1)
[Link](A1:A10,1)
CORREL(A1:A10,B1:B10)
COVARIANCE.P(A1:A10,B1:B10)
COVARIANCE.S(A1:A10,B1:B10)
FORECAST(11,B1:B10,A1:A10)
[Link](11,B1:B10,A1:A10)
FREQUENCY(A1:A10,B1:B5)
GEOMEAN(A1:A10)
HARMEAN(A1:A10)
KURT(A1:A10)
SKEW(A1:A10)
SLOPE(B1:B10,A1:A10)
INTERCEPT(B1:B10,A1:A10)
LINEST(B1:B10,A1:A10)
LOGEST(B1:B10,A1:A10)
RSQ(B1:B10,A1:A10)
TREND(B1:B10,A1:A10,A11)
🔍 3. Lookup & Reference — Examples
XLOOKUP(A2,A:A,B:B)
VLOOKUP(A2,A:B,2,FALSE)
HLOOKUP(A2,A1:D5,2,FALSE)
LOOKUP(A2,A:A,B:B)
INDEX(A1:C10,5,2)
MATCH("HR",A1:A10,0)
XMATCH("HR",A1:A10)
OFFSET(A1,1,0)
INDIRECT("A"&B1)
ADDRESS(2,3)
CHOOSE(2,"Red","Blue","Green")
COLUMN(A1)
COLUMNS(A1:D1)
ROW(A5)
ROWS(A1:A10)
HYPERLINK("[Link]
TRANSPOSE(A1:A5)
FORMULATEXT(A1)
🔤 4. Text — Examples
LEFT("Excel",2) → Ex
RIGHT("Excel",2) → el
MID("Excel",2,3) → xce
LEN("Excel") → 5
TRIM(" Hi ") → Hi
UPPER("excel") → EXCEL
LOWER("EXCEL") → excel
PROPER("excel sheet") → Excel Sheet
CONCAT("Data"," ","Science")
CONCATENATE("A","B")
TEXTJOIN(",",TRUE,A1:A5)
FIND("a","Data")
SEARCH("a","Data")
SUBSTITUTE("2024-01","-","/")
REPLACE("Excel",2,2,"XX")
TEXT(1234.5,"#,##0")
VALUE("123")
EXACT("Excel","excel")
CLEAN(A1)
CHAR(65) → A
CODE("A") → 65
UNICODE("A") → 65
UNICHAR(65) → A
REPT("*",5) → *****
T(A1)
EXCEL FUNCTIONS
✅ ALL EXCEL FUNCTIONS
(Use + Example)
🔢 1. Math & Trigonometry
Function Use Example
SUM Add numbers =SUM(A1:A5)
SUMIF Conditional sum =SUMIF(A:A,"HR",B:B)
SUMIFS Multi-condition sum =SUMIFS(C:C,A:A,"HR",B:B,"Ch
ennai")
PRODUCT Multiply numbers =PRODUCT(2,3,4)
ROUND Round number =ROUND(12.456,2)
ROUNDUP Round up =ROUNDUP(12.1,0)
ROUNDDOWN Round down =ROUNDDOWN(12.9,0)
INT Integer part =INT(9.8)
TRUNC Remove decimals =TRUNC(9.8)
ABS Absolute value =ABS(-10)
MOD Remainder =MOD(10,3)
POWER Exponent =POWER(2,3)
SQRT Square root =SQRT(16)
RAND Random 0–1 =RAND()
RANDBETWEEN Random range =RANDBETWEEN(1,100)
CEILING Round up =CEILING(12.3,1)
FLOOR Round down =FLOOR(12.9,1)
GCD Greatest divisor =GCD(24,36)
LCM Least multiple =LCM(4,6)
📊 2. Statistical Functions
Function Use Example
AVERAGE Mean =AVERAGE(A1:A10)
AVERAGEIF Conditional =AVERAGEIF(A:A,"HR",B:
average B)
COUNT Count numbers =COUNT(A1:A10)
COUNTA Count non-blanks =COUNTA(A1:A10)
COUNTBLANK Count blanks =COUNTBLANK(A1:A10)
COUNTIF Conditional count =COUNTIF(A:A,"Male")
MAX Maximum =MAX(A1:A10)
MIN Minimum =MIN(A1:A10)
MEDIAN Middle value =MEDIAN(A1:A10)
MODE Most frequent =MODE(A1:A10)
LARGE nth largest =LARGE(A1:A10,2)
SMALL nth smallest =SMALL(A1:A10,2)
STDEV.S Std deviation =STDEV.S(A1:A10)
VAR.S Variance =VAR.S(A1:A10)
[Link] Percentile =[Link](A1:A10
,0.9)
CORREL Correlation =CORREL(A1:A10,B1:B10)
🔍 3. Lookup & Reference (🔥 MOST IMPORTANT)
Function Use Example
XLOOKUP Modern lookup =XLOOKUP(A2,A:A,B:B)
VLOOKUP Vertical lookup =VLOOKUP(A2,A:B,2,FALS
E)
HLOOKUP Horizontal lookup =HLOOKUP(A2,A1:D5,2,FA
LSE)
INDEX Value by position =INDEX(A1:C10,5,2)
MATCH Find position =MATCH("HR",A:A,0)
INDEX+MATCH Advanced lookup =INDEX(B:B,MATCH(A2,A:
A,0))
OFFSET Dynamic range =SUM(OFFSET(A1,0,0,5,1
))
INDIRECT Text reference =INDIRECT("A"&B1)
ROW Row number =ROW(A5)
COLUMN Column number =COLUMN(C1)
🔤 4. Text Functions
Function Use Example
LEFT Left text =LEFT(A1,4)
RIGHT Right text =RIGHT(A1,2)
MID Middle text =MID(A1,2,3)
LEN Text length =LEN(A1)
TRIM Remove spaces =TRIM(A1)
UPPER Uppercase =UPPER(A1)
LOWER Lowercase =LOWER(A1)
PROPER Capitalize =PROPER(A1)
CONCAT Join text =CONCAT(A1," ",B1)
TEXTJOIN Join with delimiter =TEXTJOIN(",",TRUE,A
1:A5)
FIND Case-sensitive =FIND("a","Data")
search
SEARCH Case-insensitive =SEARCH("a","Data")
SUBSTITUTE Replace text =SUBSTITUTE(A1,"-","
/")
TEXT Format number =TEXT(1234,"#,##0")
VALUE Text to number =VALUE("123")
🧠 5. Logical Functions
Function Use Example
IF Condition =IF(A1>50,"Pass","Fa
il")
IFS Multiple IFs =IFS(A1>90,"A",A1>75
,"B")
AND All true =AND(A1>50,B1>50)
OR Any true =OR(A1>50,B1>50)
NOT Reverse logic =NOT(A1>50)
IFERROR Handle error =IFERROR(A1/B1,0)
📅 6. Date & Time
Function Use Example
TODAY Current date =TODAY()
NOW Date & time =NOW()
DATE Create date =DATE(2025,1,1)
DAY Day number =DAY(A1)
MONTH Month =MONTH(A1)
YEAR Year =YEAR(A1)
DATEDIF Date difference =DATEDIF(A1,B1,
"Y")
EOMONTH Month end =EOMONTH(A1,0)
NETWORKDAYS Working days =NETWORKDAYS(A1
,B1)
💰 7. Financial Functions
Function Use Example
PMT Loan EMI =PMT(10%/12,60,50
0000)
FV Future value =FV(8%,10,-1000)
PV Present value =PV(10%,5,-2000)
NPV Net present =NPV(10%,A1:A5)
value
IRR Rate of return =IRR(A1:A6)
🔁 8. Dynamic Array (Modern Excel)
Function Use Example
FILTER Filter data =FILTER(A1:C10,B1:B10=
"HR")
SORT Sort data =SORT(A1:A10)
UNIQUE Unique values =UNIQUE(A1:A10)
SEQUENCE Generate =SEQUENCE(10)
series
TEXTSPLIT Split text =TEXTSPLIT(A1,",")