Most Formula’s used in Excel

1. SUM: • Argument: IF(logical_test, 14. AVERAGEIFS:

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

corresponding value from 25. TODAY: Use: Returns the remainder after
a specified column. a number is divided by a divisor.
• Argument: • Use: Returns the current
VLOOKUP(lookup_value, date. • Argument: MOD(number,
table_array, • Argument: TODAY() divisor)
col_index_num,
[range_lookup]) 26. NOW: 32. ROUNDUP, ROUNDDOWN:

20. HLOOKUP: • Use: Returns the current • Use: Rounds a number up

date and time. or down to a specified
• Use: Looks up a value in • Argument: NOW() number of decimal places.
the top row of a table and • Arguments:
returns a corresponding 27. TIME: o ROUNDUP(numbe
value from a specified r, num_digits)
row. • Use: Returns a serial o ROUNDDOWN(nu
• Argument: number representing a mber, num_digits)
HLOOKUP(lookup_value, time.
table_array, • Argument: TIME(hour, 33. INT:
row_index_num, minute, second)
[range_lookup]) • Use: Rounds a number
28. DATEDIF: down to the nearest
21. INDEX: integer.
• Use: Calculates the • Argument: INT(number)
• Use: Returns a value from difference between two
a table based on row and dates. 34. CEILING, FLOOR:
column numbers. • Argument:
• Argument: INDEX(array, DATEDIF(start_date, • Use: Rounds a number up
row_num, [column_num]) end_date, interval) or down to the nearest
multiple of a specified
22. MATCH: 29. NETWORKDAYS: number.
• Arguments:
• Use: Returns the relative • Use: Calculates the o CEILING(number,
position of an item in a number of workdays significance)
range that matches a between two dates, o FLOOR(number,
specified value. excluding weekends and significance)
• Argument: holidays.
MATCH(lookup_value, • Argument: 35. RAND:
lookup_array, NETWORKDAYS(start_date
[match_type]) , end_date, [holidays]) • Use: Returns a random
number between 0 and 1.
23. LEN: 30. WORKDAY: • Argument: RAND()

• Use: Returns the length of • Use: Returns a date 36. RANDBETWEEN:

a text string. representing a workday a
• Argument: LEN(text) specified number of days • Use: Returns a random
before or after a starting integer between two
24. DATE: date. specified numbers.
• Argument: • Argument:
• Use: Returns a serial WORKDAY(start_date, RANDBETWEEN(bottom,
number representing a days, [holidays]) top)
date.
• Argument: DATE(year, 31. MOD: 37. COUNTIFS with multiple
month, day) criteria:
 Most Formula’s used in Excel

• Use: Counts cells that 42. CHOOSE: • Use: Transposes a range

meet multiple criteria, of cells, switching rows
including logical operators • Use: Returns a value from and columns.
like AND and OR. a list of values based on an • Argument:
• Argument: index number. TRANSPOSE(array)
COUNTIFS(criteria_range1 • Argument:
, criteria1, criteria_range2, CHOOSE(index_num, 49. XLOOKUP:
criteria2, ...) value1, value2, ...)
• Use: A more versatile
38. SUMIFS with multiple criteria: 43. INDEX and MATCH combined: lookup function that can
search horizontally or
• Use: Sums cells that meet • Use: To perform dynamic vertically, and can return
multiple criteria, including lookups. the closest match if an
logical operators like AND • Example: exact match is not found.
and OR. INDEX(data_range, • Argument:
• Argument: MATCH(lookup_value, XLOOKUP(lookup_value,
SUMIFS(sum_range, lookup_range, 0)) lookup_array,
criteria_range1, criteria1, return_array,
criteria_range2, criteria2, 44. TEXTJOIN: [match_mode],
...) [search_mode])
• Use: Joins text strings
39. AVERAGEIFS with multiple from multiple ranges or 50. TEXT:
criteria: arrays, with a delimiter
between each value. • Use: Formats a number as
• Use: Calculates the • Argument: text.
average of cells that meet TEXTJOIN(delimiter, • Argument: TEXT(value,
multiple criteria, including ignore_empty, text1, format_text)
logical operators like AND [text2], ...)
and OR. 51. SUBSTITUTE:
• Argument: 45. FILTER:
AVERAGEIFS(average_rang • Use: Replaces text within
e, criteria_range1, • Use: Filters a range of cells a text string.
criteria1, criteria_range2, based on one or more • Argument:
criteria2, ...) criteria. SUBSTITUTE(text,
• Argument: FILTER(array, old_text, new_text,
40. IFERROR: include) [instance_num])

• Use: Returns a specified 46. SORT: 52. FIND, SEARCH:

value if a formula results
in an error, otherwise • Use: Sorts a range of cells • Use: Find the position of
returns the formula result. based on one or more one text string within
• Argument: IFERROR(value, columns. another.
value_if_error) • Argument: SORT(array, • Arguments:
[sort_by], [sort_order], o FIND(find_text,
41. IFS: [by_col]) within_text,
[start_num])
• Use: Tests multiple 47. UNIQUE: o SEARCH(find_text,
conditions and returns a within_text,
value corresponding to • Use: Returns a list of [start_num])
the first true condition. unique values from a
• Argument: IFS(condition1, range.
value1, condition2, • Argument: UNIQUE(array)
value2, ...)
48. TRANSPOSE: