EXCEL BASIC

Welcome to My Vidyaarthee's Excel Basics Training! Excel is a powerful tool used worldwide for data analysis, reporting, and much more. Whether you're a beginner or looking to enhance your skills, this guide will provide you with essential formulas to navigate Excel effectively. Let's dive in!

1. SUM Formula:

   - =SUM(A1:A10) - Adds up the values in cells A1 to A10.

2. AVERAGE Formula:

   - =AVERAGE(B1:B5) - Calculates the average of values in cells B1 to B5.

3. MAX Formula:

   - =MAX(C1:C20) - Returns the maximum value in cells C1 to C20.

4. MIN Formula:

   - =MIN(D1:D15) - Returns the minimum value in cells D1 to D15.

5. COUNT Formula:

   - =COUNT(E1:E30) - Counts the number of cells containing numbers in range E1 to E30.

6. COUNTA Formula:

   - =COUNTA(F1:F25) - Counts the number of non-empty cells in range F1 to F25.

7. IF Formula:

   - =IF(G1>10, "Pass", "Fail") - Returns "Pass" if the value in G1 is greater than 10, otherwise "Fail".

8. VLOOKUP Formula:

   - =VLOOKUP(H1, A1:B10, 2, FALSE) - Searches for a value in column A and returns the corresponding value in column B.

9. HLOOKUP Formula:

   - =HLOOKUP(I1, A1:D5, 3, FALSE) - Searches for a value in row 1 and returns the corresponding value in row 3.

10. CONCATENATE Formula:

    - =CONCATENATE(J1, " ", J2) - Combines the text in cells J1 and J2 with a space in between.

11. LEFT Formula:

    - =LEFT(K1, 5) - Returns the leftmost 5 characters of the text in cell K1.

12. RIGHT Formula:

    - =RIGHT(L1, 3) - Returns the rightmost 3 characters of the text in cell L1.

13. LEN Formula:

    - =LEN(M1) - Returns the number of characters in cell M1.

14. TRIM Formula:

    - =TRIM(N1) - Removes extra spaces from the text in cell N1.

15. UPPER Formula:

    - =UPPER(O1) - Converts the text in cell O1 to uppercase.

16. LOWER Formula:

    - =LOWER(P1) - Converts the text in cell P1 to lowercase.

17. PROPER Formula:

    - =PROPER(Q1) - Capitalizes the first letter of each word in the text in cell Q1.

18. ROUND Formula:

    - =ROUND(R1, 2) - Rounds the value in cell R1 to 2 decimal places.

19. TODAY Formula:

    - =TODAY() - Returns the current date.

20. NOW Formula:

    - =NOW() - Returns the current date and time.

21. DATE Formula:

    - =DATE(2024, 3, 19) - Returns the date March 19, 2024.

22. TIME Formula:

    - =TIME(12, 30, 0) - Returns the time 12:30 PM.

23. INDEX Formula:

    - =INDEX(A1:D10, 3, 2) - Returns the value at the intersection of the 3rd row and 2nd column.

24. MATCH Formula:

    - =MATCH(100, E1:E20, 0) - Returns the position of the value 100 in cells E1 to E20.

25. INDIRECT Formula:

    - =INDIRECT("A1") - Returns the value in cell A1.

26. ROUNDUP Formula:

    - =ROUNDUP(F1, 0) - Rounds up the value in cell F1 to the nearest integer.

27. ROUNDDOWN Formula:

    - =ROUNDDOWN(G1, 0) - Rounds down the value in cell G1 to the nearest integer.

28. SUBSTITUTE Formula:

    - =SUBSTITUTE(H1, "old", "new") - Replaces "old" with "new" in the text in cell H1.

29. TEXT Formula:

    - =TEXT(I1, "dd-mmm-yyyy") - Converts the date in cell I1 to the format "dd-mmm-yyyy".

30. CONCAT Formula:

    - =CONCAT(J1, J2, J3) - Combines the values in cells J1, J2, and J3 without any separator.

Mastering these basic Excel formulas will boost your productivity and efficiency in handling data. Stay tuned for more advanced techniques and tips on My Vidyaarthee!