MS Excel - Excel functions

1. What Are Excel Functions?

An Excel function is a predefined formula that performs calculations using specific inputs (called arguments).

Formula Structure:

=FUNCTION_NAME(argument1, argument2, ...)

Example:

=SUM(A1:A10)

Adds all numbers from A1 to A10.

These are used daily for simple calculations:

Function	Description	Example	Result
`SUM`	Adds numbers	`=SUM(A1:A5)`	150
`AVERAGE`	Finds average	`=AVERAGE(A1:A5)`	30
`MAX`	Finds largest value	`=MAX(A1:A5)`	50
`MIN`	Finds smallest value	`=MIN(A1:A5)`	10
`ROUND`	Rounds numbers	`=ROUND(3.456,2)`	3.46
`ROUNDUP`	Always rounds up	`=ROUNDUP(3.1,0)`	4
`ROUNDDOWN`	Always rounds down	`=ROUNDDOWN(3.9,0)`	3

Used to manipulate text, names, and strings.

Function	Description	Example	Result
`CONCAT`	Combines text	`=CONCAT(A1," ",B1)`	John Doe
`TEXTJOIN`	Joins with separator	`=TEXTJOIN("-",TRUE,A1:A3)`	A-B-C
`LEFT`	Extracts left characters	`=LEFT("Excel",2)`	Ex
`RIGHT`	Extracts right characters	`=RIGHT("Excel",3)`	cel
`MID`	Extracts middle characters	`=MID("Excel",2,3)`	xce
`LEN`	Counts characters	`=LEN("Excel")`	5
`TRIM`	Removes extra spaces	`=TRIM(" Excel ")`	Excel
`UPPER`	Converts to uppercase	`=UPPER("excel")`	EXCEL
`LOWER`	Converts to lowercase	`=LOWER("EXCEL")`	excel
`PROPER`	Capitalizes words	`=PROPER("excel tips")`	Excel Tips

Helpful for managing schedules, deadlines, and reports.

Function	Description	Example	Result
`TODAY`	Current date	`=TODAY()`	22-Aug-2025
`NOW`	Current date & time	`=NOW()`	22-Aug-2025 15:30
`DAY`	Extracts day	`=DAY(A1)`	22
`MONTH`	Extracts month	`=MONTH(A1)`	8
`YEAR`	Extracts year	`=YEAR(A1)`	2025
`WEEKDAY`	Returns weekday number	`=WEEKDAY(A1)`	6
`TEXT`	Formats date/time	`=TEXT(TODAY(),"dddd")`	Friday
`DATEDIF`	Difference between dates	`=DATEDIF(A1,B1,"D")`	365 days

Used for conditions and decision-making.

Function	Description	Example	Result
`IF`	Checks condition	`=IF(A1>50,"Pass","Fail")`	Pass
`IFERROR`	Handles errors	`=IFERROR(A1/B1,0)`	0
`AND`	Returns TRUE if all conditions are met	`=AND(A1>50,B1<100)`	TRUE
`OR`	Returns TRUE if any condition is met	`=OR(A1>50,B1<100)`	TRUE
`NOT`	Reverses logic	`=NOT(A1>50)`	FALSE

Useful for searching and retrieving data.

Function	Description	Example	Result
`VLOOKUP`	Vertical lookup	`=VLOOKUP(101,A2:D10,2,FALSE)`	Returns data from 2nd column
`HLOOKUP`	Horizontal lookup	`=HLOOKUP("Jan",A1:H3,2,FALSE)`	Returns matching value
`INDEX`	Returns value at position	`=INDEX(A2:A10,3)`	3rd value
`MATCH`	Finds position	`=MATCH(500,A2:A10,0)`	5
`XLOOKUP`	Newer & better than VLOOKUP	`=XLOOKUP(101,A2:A10,B2:B10,"Not Found")`	Value or "Not Found"

Useful for budgets, loans, and investments.

Function	Description	Example	Result
`PMT`	Loan payment	`=PMT(5%/12,60,-30000)`	-566.14
`FV`	Future value	`=FV(5%,10,-2000,0)`	25,155.29
`PV`	Present value	`=PV(5%,10,-2000)`	-15,443.29
`NPV`	Net present value	`=NPV(8%,A2:A10)`	Returns NPV
`IRR`	Internal rate of return	`=IRR(A2:A10)`	12.3%

Useful for analysis and reports.

Function	Description	Example	Result
`COUNT`	Counts numbers	`=COUNT(A1:A10)`	7
`COUNTA`	Counts non-empty cells	`=COUNTA(A1:A10)`	9
`COUNTIF`	Counts by condition	`=COUNTIF(A1:A10,">100")`	3
`COUNTIFS`	Multiple conditions	`=COUNTIFS(A1:A10,">100",B1:B10,"Yes")`	2
`RANK`	Ranks numbers	`=RANK(A1,A1:A10)`	2

These make working with large datasets faster.

Function	Description	Example	Result
`UNIQUE`	Removes duplicates	`=UNIQUE(A1:A10)`	Unique values
`SORT`	Sorts a range	`=SORT(A1:A10)`	Sorted list
`FILTER`	Filters data dynamically	`=FILTER(A1:C10,B1:B10="Yes")`	Matching rows
`SEQUENCE`	Generates number series	`=SEQUENCE(5)`	1,2,3,4,5
`RANDARRAY`	Random numbers array	`=RANDARRAY(5,1,1,100,TRUE)`	Random integers