1. SUM
The SUM function adds up all the numbers in a range of cells.
=SUM(A1:A10)
2. AVERAGE
The AVERAGE function calculates the mean of a group of numbers.
=AVERAGE(B1:B10)
3. IF
The IF function performs a logical test and returns one value for a TRUE result and another for a FALSE result.
=IF(C1>50, “Pass”, “Fail”)
4. COUNT
The COUNT function counts the number of cells that contain numbers.
=COUNT(D1:D10)
5. COUNTA
The COUNTA function counts the number of cells that are not empty.
=COUNTA(E1:E10)
6. MIN
The MIN function returns the smallest number in a set of values.
=MIN(F1:F10)
7. MAX
The MAX function returns the largest number in a set of values.
=MAX(G1:G10)
8. VLOOKUP
The VLOOKUP function searches for a value in the first column of a range and returns a value in the same row from a specified column
=VLOOKUP(H1, A1:D10, 3, FALSE)
9. HLOOKUP
The HLOOKUP function searches for a value in the first row of a range and returns a value in the same column from a specified row.
=HLOOKUP(I1, A1:D10, 2, FALSE)
10. MATCH
The MATCH function searches for a specified item in a range of cells and returns the relative position of that item.
=MATCH(J1, A1:A10, 0)
11. INDEX
The INDEX function returns the value of a cell in a table based on the row and column number.
=INDEX(A1:C10, 2, 3)
12. LEN
The LEN function returns the number of characters in a text string.
=LEN(K1)
13. TRIM
The TRIM function removes all spaces from a text string except for single spaces between words.
=TRIM(L1)
14. CONCATENATE
The CONCATENATE function joins two or more text strings into one string. (In newer versions of Excel, use CONCAT or TEXTJOIN instead.)
=CONCATENATE(M1, ” “, N1)
15. LEFT
The LEFT function returns the specified number of characters from the start of a text string.
=LEFT(O1, 5)
16. RIGHT
The RIGHT function returns the specified number of characters from the end of a text string.
=RIGHT(P1, 3)
17. MID
The MID function returns a specific number of characters from a text string, starting at the position you specify.
=MID(Q1, 3, 4)
18. ROUND
The ROUND function rounds a number to a specified number of digits.
=ROUND(R1, 2)
19. SUMIF
The SUMIF function adds the cells specified by a given condition or criteria.
=SUMIF(S1:S10, “>100”, T1:T10)
20. COUNTIF
The COUNTIF function counts the number of cells that meet a criterion.
=COUNTIF(U1:U10, “>=50”) By mastering these 20 essential Excel formulas, you’ll be able to perform a wide range of tasks more efficiently and effectively. Whether you’re analyzing data, managing projects, or generating reports, these formulas will help you get the job done quickly and accurately. Happy Excel-ing! Or if you are looking for Excel training – you can connect with us for at aman.khanna@itsgroup.in