Master Your Excel Worksheets

This is a must join course for accounts and Commerce, Science and Engineering students. Join our Elearning course for 3 months and excel in your Excel Worksheets. Courses are conducted by Subject Matter Experts with demo videos.

To join the elearning course in Excel please click https://saitechinfo.com/stores/product/elearning-courses/

Mostly used Formula’s in Excel: You can learn all these with live demo videos. You can also clarify your doubts through Telegram Doubt Clinic Chat Channel.

1. SUM:
   • Use: Adds a range of cells.
   • Argument: SUM(number1, number2, ...)
2. AVERAGE:
   • Use: Calculates the average of a range of cells.
   • Argument: AVERAGE(num1, num2, ...)
3. COUNT:
   • Use: Counts the number of cells containing numbers in a range.
   • Argument: COUNT(value1, value2, ...)
4. COUNTA:
   • Use: Counts the number of cells containing any value (text, numbers, or logical values) in a range.
   • Argument: COUNTA(value1, value2, ...)
5. MAX:
   • Use: Returns the largest value in a range of cells.
   • Argument: MAX(number1, number2, ...)
6. MIN:
   • Use: Returns the smallest value in a range of cells.
   • Argument: MIN(number1, number2, ...)
7. IF:
   • Use: Performs a logical test.
   • Argument: IF(logical_test, value_if_true, value_if_false)
8. CONCATENATE:
   • Use: Joins text strings into one text string.
   • Argument: CONCATENATE(text1, text2, ...)
9. SUMIF:
   • Use: Sums cells that meet a specified criteria.
   • Argument: SUMIF(range, criteria, [sum_range])
10. SUMIFS:
    • Use: Sums cells that meet multiple criteria.
    • Argument: SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
11. COUNTIF:
    • Use: Counts cells that meet a specified criteria.
    • Argument: COUNTIF(range, criteria)
12. COUNTIFS:
    • Use: Counts cells that meet multiple criteria.
    • Argument: COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)
13. AVERAGEIF:
    • Use: Calculates the average of cells that meet a specified criteria.
    • Argument: AVERAGEIF(range, criteria, [average_range])
14. AVERAGEIFS:
    • Use: Calculates the average of cells that meet multiple criteria.
    • Argument: AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
15. INDEX:
    • Use: Returns a value from a table based on row and column numbers.
    • Argument: INDEX(array, row_num, [column_num])
16. LEFT, RIGHT, MID:
    • Use: Extract text from a string.
      • LEFT(text, num_chars)
      • RIGHT(text, num_chars)
      • MID(text, start_num, num_chars)
17. TRIM:
    • Use: Removes extra spaces from a text string.
    • Argument: TRIM(text)
18. UPPER, LOWER, PROPER:
    • Use: Convert text to uppercase, lowercase, or proper case.
    • Arguments:
      • UPPER(text)
      • LOWER(text)
      • PROPER(text)
19. VLOOKUP:
    • Use: Looks up a value in the leftmost column of a table.

Mostly used Formula’s in Excel:

20. HLOOKUP:

• Use: Looks up a value in the top row of a table and returns a corresponding value from a specified row.
• Argument: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

21. INDEX:

• Use: Returns a value from a table based on row and column numbers.
• Argument: INDEX(array, row_num, [column_num])

22. MATCH:

• Use: Returns the relative position of an item in a range that matches a specified value.
• Argument: MATCH(lookup_value, lookup_array, [match_type])

23. LEN:

• Use: Returns the length of a text string.
• Argument: LEN(text)

24. DATE:

• Use: Returns a serial number representing a date.
• Argument: DATE(year, month, day)

25. TODAY:

• Use: Returns the current date.
• Argument: TODAY()

26. NOW:

• Use: Returns the current date and time.
• Argument: NOW()

27. TIME:

• Use: Returns a serial number representing a time.
• Argument: TIME(hour, minute, second)

28. DATEDIF:

• Use: Calculates the difference between two dates.
• Argument: DATEDIF(start_date, end_date, interval)

29. NETWORKDAYS:

• Use: Calculates the number of workdays between two dates, excluding weekends and holidays.
• Argument: NETWORKDAYS(start_date, end_date, [holidays])

30. WORKDAY:

• Use: Returns a date representing a workday a specified number of days before or after a starting date.
• Argument: WORKDAY(start_date, days, [holidays])

31. MOD:

• Use: Returns the remainder after a number is divided by a divisor.
• Argument: MOD(number, divisor)

32. ROUND, ROUNDUP, ROUNDDOWN:

• Use: Rounds a number up or down to a specified number of decimal places.
• Arguments:
  • ROUND(number, num_digits)
  • ROUNDUP(number, num_digits)
  • ROUNDDOWN(number, num_digits)

33. INT:

• Use: Rounds a number down to the nearest integer.
• Argument: INT(number)

34. CEILING, FLOOR:

• Use: Rounds a number up or down to the nearest multiple of a specified number.
• Arguments:
  • CEILING(number, significance)
  • FLOOR(number, significance)

35. RAND:

• Use: Returns a random number between 0 and 1.
• Argument: RAND()

36. RANDBETWEEN:

• Use: Returns a random integer between two specified numbers.
• Argument: RANDBETWEEN(bottom, top)

37. COUNTIFS with multiple criteria:

• Use: Counts cells that meet multiple criteria, including logical conditions like AND and OR.
• Argument: COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)

Mostly used Formula’s in Excel:

38. SUMIFS with multiple criteria:

• Use: Sums cells that meet multiple criteria, including logical operators like AND and OR.
• Argument: SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

39. AVERAGEIFS with multiple criteria:

• Use: Calculates the average of cells that meet multiple criteria, including logical operators like AND and OR.
• Argument: AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

40. IFERROR:

• Use: Returns a specified value if a formula results in an error; otherwise, returns the formula result.
• Argument: IFERROR(value, value_if_error)

41. IFS:

• Use: Tests multiple conditions and returns a value corresponding to the first condition.
• Argument: IFS(condition1, value1, condition2, value2, ...)

42. CHOOSE:

• Use: Returns a value from a list of values based on an index number.
• Argument: CHOOSE(index_num, value1, value2, ...)

43. INDEX and MATCH combined:

• Use: To perform dynamic lookups.
• Example: INDEX(data_range, MATCH(lookup_value, lookup_range, 0))

44. TEXTJOIN:

• Use: Joins text strings from multiple ranges or arrays, with a delimiter between each value.
• Argument: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

45. FILTER:

• Use: Filters a range of cells based on one or more criteria.
• Argument: FILTER(array, include)

46. SORT:

• Use: Sorts a range of cells based on one or more columns.
• Argument: SORT(array, [sort_by], [sort_order], [by_col])

47. UNIQUE:

• Use: Returns a list of unique values from a range.
• Argument: UNIQUE(array)

48. TRANSPOSE:

• Use: Transposes a range of cells, switching rows and columns.
• Argument: TRANSPOSE(array)

49. XLOOKUP:

• Use: A more versatile lookup function that can search horizontally or vertically, and can return the closest match if an exact match is not found.
• Argument: XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])

50. TEXT:

• Use: Formats a number as text.
• Argument: TEXT(value, format_text)

51. SUBSTITUTE:

• Use: Replaces text within a text string.
• Argument: SUBSTITUTE(text, old_text, new_text, [instance_num])

52. FIND, SEARCH:

• Use: Find the position of one text string within another.
• Arguments:
  • FIND(find_text, within_text, [start_num])
  • SEARCH(find_text, within_text, [start_num])

eLearning Classes