10x your productivity with these 21 Excel must known formulas
1. SUM
Adds all numbers in a range
Example: =SUM(A1:A9)
Add values from cell A1 to A9
2. AVERAGE
Calculates the average of numbers in a range
Example : =AVERAGE(B1:B9)
Average of B1 to B9
3. ROUND
Rounds a number to a specified number of digits
Example: =ROUND(C1;2)
Round C1 to 2 decimal place s
4. MIN
Identifies the lowest number in a range
Example: =MIN(D1:D9)
Find the minimum in D1 to D9
5. MAX
Finds the highest number in a range
Example: =MAX(D1:D9)
Get the maximum value in D1 to D9
6. COUNT
Counts the number of cells that contain numbers
Example: =COUNT(F1:F9)
Count numeric cells in F1 to F9
*It only counts the numerical inputs
7. INDEX
Returns a value in table based on row & column number
Example: =INDEX(K1:L10,2,3)
For the value in the 2nd row & 3rd column from the range K1:K10
8. MATCH
Searches for a value and returns its relative position
Example: =MATCH(L1,M1:M9,0)
To find L1’s position from the range M1:M9
9. VLOOKUP
Searches for a value in the first column of a range
Example: =VLOOKUP (G1,H1:H10,2,FALSE)
To find G1 in H1:H10
10. HLOOKUP
Searches for a value in the top row of a table
*Number of rows that contains the data you want to retrieve
Example: =HLOOKUP(I1,J1:S2,2,FALSE)
To find I1 in the top row J1:S2
11. COUNTIF/COUNTIFS
Counts cells that meet a condition
Example: =COUNTIF (N1:N9;”>5″)
To count cells greater than 5
12. COUNTA
Counts non-empty cells
Example: =COUNTA(O1:O9)
To count all non-empty cells in a range
13. SUMIF / SUMIFS
Counts non-empty cells
Example: =SUMIF(P1:P9,”>5″)
Add cells that meet a condition
14. IF / NESTED IFS
Performs conditional tests
Example: =IF(R1>5;”Yes”;”No”)
For simple conditions
Note:
IF: 1 variable
IFS: multiple variables
=IF(test, true, false)
=IF(test1, true, IF(test2, true, false)) Nested IF
=IFS(test1, true, test2, true, test3, true)
15. AVERAGEIF / AVERAGEIFS
Calculates the average for cells that meet criteria
=AVERAGEIF(Q1:Q9;”>5″)
Average of values over 5
16. Logical Operators (AND, OR)
Combines multiple conditions
Examples:
=AND(S1>5;S2<2) Both conditions are true => Output is true
=OR(S1>5;S2<2) Just 1 condition is true => Output is true
17. CONCATENATE
Joins two or more text strings
Example: =CONCATENATE(T1,” “,T2)
Combine first name and last name in one cell
18. LEFT/RIGTH
Extracts characters from the start/end of a text string
Examples:
=LEFT(U1;5)
Excel
=RIGHT(U1;5)
Class
19. MID
Extracts characters from the middle of a text string
Example: =MID(V1;7;7)
7 characters starting from the 7th => Mastery
20. TEXT
Converts a value to text in a specific number format
Ex: =TEXT(V1;”MMM-YY”)
7/02/2024 => Feb-24
21. IFERRROR
Returns a custom result if a formula errors out
Ex: =IFERROR(W1,”0″)
Give 0 as output, instead of “# div” error
