Excel Formulas

1. COUNT: Counts the number of cells that contain numerical values within a range.

Example: =COUNT(A1:A10)

2. COUNTA: Counts all nonempty cells in a range, including text, numbers, and errors.

Example: =COUNTA(A1:A10)

3. PROPER: Converts the first letter of each word in a text string to uppercase and the rest to lowercase.

Example: =PROPER("hello world") returns "Hello World"

4. LEN: Returns the length (number of characters) of a text string, including spaces.

Example: =LEN("Hello") returns 5

5. SUMIF: Adds up cells that meet a specific criterion.

Example: =SUMIF(A1:A10, ">5") adds all values in the range A1:A10 that are greater than 5

6. UPPER: Converts all letters in a text string to uppercase.

Example: =UPPER("hello") returns "HELLO"

7. LOWER: Converts all letters in a text string to lowercase.

Example: =LOWER("HELLO") returns "hello"

8. LARGE: Returns the nth largest value from a set of values.

Example: =LARGE(A1:A10, 2) returns the 2nd largest value in the range A1:A10

9. SMALL: Returns the nth smallest value from a set of values.

Example: =SMALL(A1:A10, 3) returns the 3rd smallest value in the range A1:A10
10. RIGHT: Extracts a specified number of characters from the end (right side) of a text string.

Example: =RIGHT("Hello", 2) returns "lo"

11. LEFT: Extracts a specified number of characters from the beginning (left side) of a text string.

Example: =LEFT("Hello", 2) returns "He"

12. COUNTIF: Counts cells in a range that meet a single criterion.

Example: =COUNTIF(A1:A10, "Yes") counts cells in A1:A10 with "Yes"

13. SUM: Adds up all numbers in a range.

Example: =SUM(A1:A10)

14. PRODUCT: Multiplies all numbers in a range together.

Example: =PRODUCT(A1:A10)

15. MAX: Returns the largest number in a range.

Example: =MAX(A1:A10)

16. MIN: Returns the smallest number in a range.

Example: =MIN(A1:A10)

17. COUNTBLANK: Counts the number of empty cells in a range.

Example: =COUNTBLANK(A1:A10)

18. TODAY: Returns the current date.

Example: =TODAY() (no arguments required)

Example: =TIME(14, 30, 0) returns "2:30 PM"

20. CONCATENATE: Combines (joins) two or more text strings into one. In newer versions of Excel,
CONCATENATE has been replaced by the CONCAT and TEXTJOIN functions.

Example: =CONCATENATE("Hello", " ", "World") returns "Hello World"

21. AVERAGE: Calculates the average (arithmetic mean) of a group of numbers.

Example: =AVERAGE(A1:A10) returns the average value of numbers in the range A1:A10

22. COUNTIF: Counts the number of cells in a range that meet a specific condition (criterion).

Example: =COUNTIF(A1:A10, ">10") counts how many cells in A1:A10 have values greater than

23. RANDBETWEEN: Generates a random integer between two specified numbers. This value recalculates
every time the worksheet is recalculated.

Example: =RANDBETWEEN(1, 100) returns a random number between 1 and 100

24. ROMAN: Converts an Arabic numeral (standard number) to Roman numerals.

Example: =ROMAN(2023) returns "MMXXIII"

25. IFERROR: Returns a specified value if a formula evaluates to an error; otherwise, it returns the result
of the formula.

Example: =IFERROR(A1/B1, "Error") returns "Error" if the division results in an error, otherwise it shows
the result.

26. TRANSPOSE: Converts a vertical range of cells to a horizontal range or vice versa.

Example: =TRANSPOSE(A1:A3) changes the vertical range A1:A3 into a horizontal format.
27. INDEX: Returns the value of a cell in a specified row and column within a given range.

Example: =INDEX(A1:C3, 2, 3) returns the value in the second row and third column of the range A1:C3.

28. MATCH: Returns the relative position of an item in an array that matches a specified value in a specified
order.

Example: =MATCH("Apple", A1:A5, 0) returns the position of "Apple" in the range A1:A5.

29. CONCAT: Joins two or more text strings together (similar to CONCATENATE).

Example: =CONCAT(A1, B1) combines the text in cells A1 and B1.

30. TEXT: Formats a number and converts it to text in a specified format.

Example: =TEXT(A1, "0.00") converts the number in A1 to text with two decimal places.

31. DATEDIF: Calculates the difference between two dates in years, months, or days.

Example: =DATEDIF(A1, B1, "Y") returns the number of complete years between dates in A1 and B1.

32. NETWORKDAYS: Returns the number of whole workdays between two dates, excluding weekends and
specified holidays.

Example: =NETWORKDAYS(A1, B1) calculates workdays between dates in A1 and B1.

33. RANDBETWEEN: Returns a random integer between the numbers you specify.

Example: =RANDBETWEEN(1, 100) returns a random number between 1 and 100.

34. ISNUMBER: Checks if a value is a number and returns TRUE or FALSE.

Example: =ISNUMBER(A1) returns TRUE if the value in A1 is a number.

35. ISBLANK: Checks if a cell is empty and returns TRUE or FALSE.

Example: =ISBLANK(B1) returns TRUE if B1 is empty.

Example: =FLOOR(A1, 5) rounds down the value in A1 to the nearest multiple of 5.

37. CEILING: Rounds a number up, away from zero, to the nearest multiple of significance.

Example: =CEILING(B1, 5) rounds up the value in B1 to the nearest multiple of 5.

38. SUBTOTAL: Returns a subtotal in a list or database.

Example: =SUBTOTAL(1, C1:C10) returns the average of the values in C1 to C10, where "1" specifies the
AVERAGE function.

39. CLEAN: Removes all nonprintable characters from text.

Example: =CLEAN(D1) removes nonprintable characters from the text in D1.

40. UNIQUE: Returns a list of unique values from a range or array.

Example: =UNIQUE(E1:E10) returns a list of unique values found in the range E1 to E10.