Innovative Technology Solutions

Top 20 Essential Excel Formulas

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