Excel Formula's
1) SUM
Task: Sum of numbers Formula: =SUM(A1:A10) Example: Sum of all numbers in A1–A10
2) AVERAGE
Task: Average Formula: =AVERAGE(B1:B10) Example: Calculate the average of marks.
3) COUNT
Task: Count cells containing numbers Formula: =COUNT(A1:A10) Example: Finds how many cells contain numbers.
4) COUNTA
Task: Count non-empty cells Formula: =COUNTA(A1:A10) Example: Count the number of names.
5) COUNTBLANK
Task: Counting blank cells Formula: =COUNTBLANK(A1:A10)
6) MAX
Task: Largest number Formula: =MAX(A1:A10)
7) MIN
Task: Smallest number Formula: =MIN(A1:A10)
8) LARGE
Task: Nth largest number
Formula: =LARGE(A1:A10, 2)
Example: Second largest number.
9) SMALL
Task: Nth Small Number
Formula: =SMALL(A1:A10, 2)
10) ROUND
Task: Round a number Formula: =ROUND(A1, 2 )
11) SUMIF
Task: Sum according to a condition Formula: =SUMIF(A:A, "Apple", B:B) Example: Total sales of Apple
12) SUMIFS
Task: Sum according to multiple conditions Formula: =SUMIFS(C:C, A:A, "Apple", B:B, "January")
13) COUNTIF
Task: Counting according to a condition Formula: =COUNTIF(A:A, "Male")
14) COUNTIFS
Task: Counting based on multiple conditions Formula: =COUNTIFS(A:A,"Male",B:B,"Mumbai")
15) AVERAGEIF
Task: Average according to condition
Formula: =AVERAGEIF(A:A,"Pass",B:B)
16) AVERAGEIFS
Work: Average according to multiple conditions Formula: =AVERAGEIFS(C:C, A:A,"Male", B:B,"Graduate")
17) IF
Task: Condition true/false logic
Formula: =IF(A1>=35,"Pass","Fail")
18) IFERROR
Task: Displays another valuean error . Formula: =IFERROR(A1/B1,"Error")
19) AND
Task: TRUE when all conditionsTRUE Formula: =AND(A1>10, B1<20)
20) OR
Work: Any condition will be TRUE Formula: =OR(A1="Male", B1="Female")
21) NOT
Work: Returns the opposite result Formula: =NOT(A1="Yes")
22) VLOOKUP
Work: Vertical Search ( most used in jobs)
Formula:
=VLOOKUP(A2, D2:E100, 2, FALSE)
23) HLOOKUP
Task: Horizontal Search
Formula:
=HLOOKUP(A1, A1:F2, 2, FALSE)
24) XLOOKUP ( new and powerful)
Formula:
=XLOOKUP(A2, D:D, E:E)
25) INDEX
Task: Finding a value in a table Formula: =INDEX(B2:B10, 3)
26) MATCH
Task: Find in which row/columnvalue is located. Formula: =MATCH("Mumbai", A2:A10, 0)
27) INDEX + MATCH ( more powerful than VLOOKUP )
Formula:
=INDEX(C:C, MATCH(A2, B:B, 0))
28) LEFT
Task: Remove letters from the left Formula: =LEFT(A1, 4)
29) RIGHT
Task: Remove letters from the right Formula: =RIGHT(A1, 3)
30) MID
Task: Extract text from the middle Formula: =MID(A1, 2, 3)
31) LEN
Function: Shows how many letters there are Formula: =LEN(A1)
32) TRIM
Function: Removes extra spaces
Formula: =TRIM(A1)
33) CONCATENATE / CONCAT / TEXTJOIN
Task: Concatenating two or more texts Formula: =CONCAT(A1," ",B1)
34) UPPER
Task: Convert to Capital letters
Formula: =UPPER(A1)
35) LOWER
Task: Convert to Small letters
Formula: =LOWER(A1)
36) PROPER
Task: Capitalize the first letter of each word Formula: =PROPER(A1)
37) TODAY
Task: Today's date Formula: =TODAY()
38) NOW
Work: current date + time
Formula: =NOW()
39) DAY
Task: Getting the date Formula: =DAY(A1)
40) MONTH
Work: Month Number Formula: =MONTH(A1)
41) YEAR
Work: Year Formula: =YEAR(A1)
42) DATEDIF
Task: Difference between two dates Formula: =DATEDIF(A1, B1, "D") (Days)
43) EDATE
Task: Date forward/backward by a few months Formula: =EDATE(A1, 3) (3 months ahead)
44) EOMONTH
Task: End of month Formula: =EOMONTH(A1, 0)
45) TIME
Task: Create hours , minutes , seconds Formula: =TIME(10,30,0)
46) TEXT
Task: Format a number or date Formula: =TEXT ( A1,"dd-mm-yyyy")
47) ABS
Task: Makes Negative Positive Formula : = ABS (A1)
48) POWER
Work: Number Exponential Formula: =POWER(A1, 2) (Square)
49) RANK
Task: Rank of a number Formula: =RANK(A1, A1:A10)
50) SUBTOTAL
Task: Correct sum of filtered data
Formula: =SUBTOTAL(9, A1:A10)
