Excel Formulas list

50 Excel Fourmula List

1. RANDBETWEEN: This formula generates a random number between a specified range.

   • Example: =RANDBETWEEN(1, 100) will give a random number between 1 and 100.

2. ABS: This formula gives the absolute value of a number.

   • Example: =ABS(-5) will give 5.

3. AVERAGE: This formula calculates the average of given numbers.

   • Example: =AVERAGE(10, 20, 30) will give 20.

4. COUNT: This formula counts the number of numeric values in a range.

   • Example: =COUNT(A1:A10) will count the number of numeric values in the range A1 to A10.

5. CONCATENATE: This formula joins multiple text strings.

   • Example: =CONCATENATE("Hello", " ", "World") will give “Hello World”.

6. DAYS360: This formula calculates the number of days between two dates based on a 360-day year.

   • Example: =DAYS360("01/01/2023", "31/12/2023") will give 360.

7. EVEN: This formula rounds a number up to the nearest even number.

   • Example: =EVEN(3) will give 4.

8. ODD: This formula rounds a number up to the nearest odd number.

   • Example: =ODD(4) will give 5.

9. SUM: This formula adds up given numbers.

   • Example: =SUM(10, 20, 30) will give 60.

10. SUMIF: This formula adds numbers based on a condition.

    • Example: =SUMIF(A1:A10, ">10") will sum the numbers in the range A1 to A10 that are greater than 10.

11. INT: This formula returns the integer part of a number.

    • Example: =INT(5.7) will give 5.

12. RIGHT: This formula extracts characters from the right side of a text string.

    • Example: =RIGHT("Excel", 2) will give “el”.

13. LEN: This formula returns the length of a text string.

    • Example: =LEN("Excel") will give 5.

14. LEFT: This formula extracts characters from the left side of a text string.

    • Example: =LEFT("Excel", 2) will give “Ex”.

15. LOWER: This formula converts a text string to lowercase.

    • Example: =LOWER("EXCEL") will give “excel”.

16. UPPER: This formula converts a text string to uppercase.

    • Example: =UPPER("excel") will give “EXCEL”.

17. MAX: This formula returns the maximum value from given values.

    • Example: =MAX(1, 2, 3) will give 3.

18. MINA: This formula returns the minimum value from given values, including text and logical values.

    • Example: =MINA(1, 2, "3", TRUE) will give 1.

19. MOD: This formula returns the remainder of a division.

    • Example: =MOD(10, 3) will give 1.

20. MODE: This formula returns the most frequently occurring value from given values.

    • Example: =MODE(1, 2, 2, 3) will give 2.

21. POWER: This formula returns the result of a number raised to a power.

    • Example: =POWER(2, 3) will give 8.

22. PRODUCT: This formula multiplies given values.

    • Example: =PRODUCT(2, 3, 4) will give 24.

23. SQRT: This formula returns the square root of a number.

    • Example: =SQRT(16) will give 4.

24. LOG: This formula returns the logarithm of a number.

    • Example: =LOG(100, 10) will give 2.

25. COUNTA: This formula counts the number of non-empty cells in a range.

    • Example: =COUNTA(A1:A5) will give 5 if all cells are filled.

26. SUBTOTAL: This formula returns a subtotal in a range.

    • Example: =SUBTOTAL(9, A1:A10) will sum the cells from A1 to A10.

27. TRIM: This formula removes extra spaces from a text string.

    • Example: =TRIM(" Excel ") will give “Excel”.

28. COUNTBLANK: This formula counts the number of empty cells in a range.

    • Example: =COUNTBLANK(A1:A10) will count the number of empty cells from A1 to A10.

29. CODE: This formula returns the ASCII code of the first character in a text string.

    • Example: =CODE("A") will give 65.

30. PMT: This formula calculates the monthly installment (EMI) of a loan.

    • Example: =PMT(0.05/12, 60, -100000) will give the monthly installment.

31. IF: This formula returns a value based on a condition.

    • Example: =IF(A1>10, "Yes", "No") will give “Yes” if A1 is greater than 10, otherwise “No”.

32. DATEDIF: This formula calculates the difference between two dates.

    • Example: =DATEDIF("2023-01-01", "2024-01-01", "Y") will give 1 year.

33. CLEAN: This formula removes non-printable characters from a text string.

    • Example: =CLEAN("Excel" & CHAR(7)) will give “Excel”.

34. CONVERT: This formula converts a measurement from one unit to another.

    • Example: =CONVERT(10, "m", "km") will give 0.01 kilometers.

35. COUNTIF: This formula counts the number of cells that meet a condition.

    • Example: =COUNTIF(A1:A10, ">5") will count the cells in the range A1 to A10 that are greater than 5.

36. DATE: This formula creates a date based on day, month, and year.

    • Example: =DATE(2024, 10, 25) will give 25-10-2024.

37. TIME: This formula creates a time based on hours, minutes, and seconds.

    • Example: =TIME(10, 30, 0) will give 10:30 AM.

38. NOW: This formula returns the current date and time.

    • Example: =NOW() will give the current date and time.

39. VLOOKUP: This formula searches vertically in a table.

    • Example: =VLOOKUP("Apple", A1:B10, 2, FALSE) will give the corresponding value for “Apple”.

40. LOOKUP: This formula searches for a value in a range.

    • Example: =LOOKUP(3, A1:A10, B1:B10) will give the corresponding value for 3 in the range A1:A10.

41. TEXT: This formula converts a number to text.

    • Example: =TEXT(1234.56, "₹#,##0.00") will give ₹1,234.56.

42. DOLLAR: This formula converts a number to currency format.

    • Example: =DOLLAR(1234.56, 2) will give $1,234.56.

43. SECOND: This formula returns the second part of a time.

    • Example: =SECOND("10:30:45") will give 45.

44. AND: This formula checks multiple conditions and returns TRUE if all conditions are met.

    • Example: =AND(A1>5, B1<10) will give TRUE if both conditions are met.

45. OR: This formula checks multiple conditions and returns TRUE if any condition is met.

    • Example: =OR(A1>5, B1<10) will give TRUE if any condition is met.

46. BIN2DEC: This formula converts a binary number to decimal.

    • Example: =BIN2DEC("1010") will give 10.

47. DEC2BIN: This formula converts a decimal number to binary.

    • Example: =DEC2BIN(10) will give 1010.

48. TODAY: This formula returns the current date.

    • Example: =TODAY() will give the current date.

49. ROMAN: This formula converts a number to Roman numerals.

    • Example: =ROMAN(2024) will give MMXXIV.

50.

HLOOKUP function in Excel is used to search for a value in the top row of a table and return a value in the same column from a specified row. Here’s the syntax for the HLOOKUP function: 

Example =HLOOKUP("Feb", A1:D2, 2, FALSE)

 

« Previous Next »