100 Microsoft Excel Formulas with Examples

Artificial Intelligence Generated Content MS Excel Formula
  1. SUM: Adds up a range of numbers.
    Example: =SUM(A1:A5) adds numbers in cells A1 to A5.
  2. AVERAGE: Calculates the average of a range of numbers.
    Example: =AVERAGE(B1:B4) calculates the average of numbers in cells B1 to B4.
  3. COUNT: Counts the number of cells in a range containing numbers.
    Example: =COUNT(C1:C6) counts cells with numbers in cells C1 to C6.
  4. MAX: Returns the largest number in a range.
    Example: =MAX(D1:D8) finds the maximum value in cells D1 to D8.
  5. MIN: Returns the smallest number in a range.
    Example: =MIN(E1:E7) finds the minimum value in cells E1 to E7.
  6. IF: Performs conditional calculations.
    Example: =IF(F1>10, "Yes", "No") returns “Yes” if F1 is greater than 10; otherwise, it returns “No.”
  7. VLOOKUP: Searches for a value in a table and returns a corresponding value from another column.
    Example: =VLOOKUP("Apple", G1:H5, 2, FALSE) searches for “Apple” in column G and returns the value from column H.
  8. CONCATENATE: Combines text from multiple cells into one.
    Example: =CONCATENATE("Hello", " ", "World") combines text into “Hello World.”
  9. LEFT: Extracts text from the left side of a cell.
    Example: =LEFT("Excel", 3) extracts “Exc” from “Excel.”
  10. RIGHT: Extracts text from the right side of a cell.
    Example: =RIGHT("Excel", 2) extracts “el” from “Excel.”
  11. MID: Extracts text from the middle of a cell.
    Example: =MID("Excel Formulas", 7, 8) extracts “Formulas” from “Excel Formulas.”
  12. SUMIF: Adds up values in a range based on a specified condition.
    Example: =SUMIF(I1:I5, ">5") adds values in I1 to I5 greater than 5.
  13. COUNTIF: Counts cells based on a specified condition.
    Example: =COUNTIF(J1:J6, "Apples") counts “Apples” in J1 to J6.
  14. AVERAGEIF: Calculates the average of cells based on a specified condition.
    Example: =AVERAGEIF(K1:K4, "<>0") averages non-zero values in K1 to K4.
  15. IFERROR: Returns a specified value if a formula results in an error.
    Example: =IFERROR(L1/M1, "Error") returns “Error” if there’s a division error.
  16. ROUND: Rounds a number to a specified number of decimal places.
    Example: =ROUND(N1, 2) rounds N1 to two decimal places.
  17. DATE: Creates a date value.
    Example: =DATE(2023, 10, 15) creates the date “October 15, 2023.”
  18. TODAY: Returns the current date.
    Example: =TODAY() returns today’s date.
  19. NOW: Returns the current date and time.
    Example: =NOW() returns the current date and time.
  20. TEXT: Converts a value to text with a specified format.
    Example: =TEXT(O1, "mm/dd/yyyy") converts the date in O1 to “mm/dd/yyyy” format.
  21. LEN: Counts the number of characters in a cell.
    Example: =LEN(P1) counts characters in cell P1.
  22. TRIM: Removes extra spaces from text.
    Example: =TRIM(Q1) removes extra spaces from cell Q1.
  23. UPPER: Converts text to uppercase.
    Example: =UPPER(R1) converts text in R1 to uppercase.
  24. LOWER: Converts text to lowercase.
    Example: =LOWER(S1) converts text in S1 to lowercase.
  25. PROPER: Capitalizes the first letter of each word.
    Example: =PROPER(T1) capitalizes each word in T1.
  26. CONVERT: Converts a measurement from one unit to another.
    Example: =CONVERT(U1, "m", "ft") converts meters to feet.
  27. FV (Future Value): Calculates the future value of an investment.
    Example: =FV(0.05, 10, -1000) calculates future value with a 5% annual interest rate over 10 years.
  28. PV (Present Value): Calculates the present value of an investment.
    Example: =PV(0.05, 10, -1000) calculates present value with a 5% annual interest rate over 10 years.
  29. IRR (Internal Rate of Return): Calculates the internal rate of return for a series of cash flows.
    Example: =IRR(V1:V5) calculates the internal rate of return for cash flows in cells V1 to V5.
  30. NPV (Net Present Value): Calculates the net present value of an investment based on a discount rate.
    Example: =NPV(0.1, W1:W5) calculates the net present value with a 10% discount rate for cash flows in cells W1 to W5.
  31. COUNTBLANK: Counts the number of blank cells in a range.
    Example: =COUNTBLANK(X1:X10) counts the number of blank cells in X1 to X10.
  32. RANK: Returns the rank of a number in a list.
    Example: =RANK(Y1, Y1:Y10) calculates the rank of the value in Y1 within the range Y1 to Y10.
  33. SUMPRODUCT: Calculates the sum of products of corresponding values in arrays.
    Example: =SUMPRODUCT(A1:A5, B1:B5) calculates the sum of products of corresponding values in arrays A1:A5 and B1:B5.
  34. INDEX: Returns the value of a cell in a specific row and column of a range.
    Example: =INDEX(C1:E5, 3, 2) returns the value in the third row and second column of the range C1:E5.
  35. MATCH: Searches for a value in a range and returns its relative position.
    Example: =MATCH("SearchValue", F1:F10, 0) finds “SearchValue” in F1:F10 and returns its position.
  36. HLOOKUP: Searches for a value in the first row of a table and returns a corresponding value from a specified row.
    Example: =HLOOKUP("ProductX", A1:D4, 3, FALSE) looks up “ProductX” in row 1 and returns the value from row 3.
  37. DATEVALUE: Converts a text date into a serial number.
    Example: =DATEVALUE("2023-10-15") converts the text “2023-10-15” to a date serial number.
  38. TIMEVALUE: Converts a text time into a serial number.
    Example: =TIMEVALUE("15:30:00") converts the text “15:30:00” to a time serial number.
  39. DAYS: Calculates the number of days between two dates.
    Example: =DAYS("2023-10-15", "2023-10-20") calculates the days between October 15, 2023, and October 20, 2023.
  40. DATEDIF: Calculates the difference between two dates in various units (e.g., days, months, years).
    Example: =DATEDIF("2023-01-01", "2023-12-31", "d") calculates the number of days in the year 2023.
  41. EOMONTH: Returns the last day of the month before or after a specified number of months.
    Example: =EOMONTH("2023-10-15", 3) returns the last day of January 2024 (3 months after October 15, 2023).
  42. NOW: Returns the current date and time.
    Example: =NOW() returns the current date and time.
  43. TODAY: Returns the current date.
    Example: =TODAY() returns the current date.
  44. WEEKDAY: Returns the day of the week for a specified date.
    Example: =WEEKDAY("2023-10-15") returns 1 (Sunday) for October 15, 2023.
  45. NETWORKDAYS: Calculates the number of working days between two dates, excluding weekends.
    Example: =NETWORKDAYS("2023-10-15", "2023-10-20") calculates the number of working days between October 15, 2023, and October 20, 2023, excluding weekends.
  46. FLOOR: Rounds a number down to the nearest multiple of significance.
    Example: =FLOOR(17.8, 5) rounds 17.8 down to the nearest multiple of 5, which is 15.
  47. CEILING: Rounds a number up to the nearest multiple of significance.
    Example: =CEILING(17.8, 5) rounds 17.8 up to the nearest multiple of 5, which is 20.
  48. MOD: Returns the remainder when one number is divided by another.
    Example: =MOD(10, 3) returns 1 because 10 divided by 3 has a remainder of 1.
  49. RAND: Generates a random decimal number between 0 and 1.
    Example: =RAND() generates a random decimal number between 0 and 1.
  50. RANDBETWEEN: Generates a random integer between specified minimum and maximum values.
    Example: =RANDBETWEEN(1, 100) generates a random integer between 1 and 100.
  51. COUNTA: Counts the number of non-empty cells in a range.
    Example: =COUNTA(Z1:Z10) counts the number of non-empty cells in Z1 to Z10.
  52. MATCH with Wildcards: Searches for a value in a range using wildcards (* and ?) and returns its position.
    Example: =MATCH("App*", AA1:AA10, 0) finds “App*” in AA1:AA10 using a wildcard and returns its position.
  53. INDEX/MATCH (Two-Way Lookup): Combines INDEX and MATCH to perform a two-way lookup.
    Example: =INDEX(C2:E5, MATCH("ProductX", B2:B5, 0), MATCH("RegionA", C1:E1, 0)) performs a two-way lookup in a table.
  54. IFNA: Returns a specified value if a formula results in a #N/A error.
    Example: =IFNA(VLOOKUP("Apple", G1:H5, 2, FALSE), "Not Found") returns “Not Found” if “Apple” is not found.
  55. DATEDIF (Age Calculation): Calculates a person’s age using DATEDIF.
    Example: =DATEDIF("1990-01-01", TODAY(), "y") calculates a person’s age from their birthdate to today in years.
  56. ROUNDUP: Rounds a number up to a specified number of decimal places.
    Example: =ROUNDUP(17.3, 0) rounds 17.3 up to the nearest whole number, which is 18.
  57. ROUNDDOWN: Rounds a number down to a specified number of decimal places.
    Example: =ROUNDDOWN(17.9, 0) rounds 17.9 down to the nearest whole number, which is 17.
  58. SUBSTITUTE: Replaces occurrences of a specified text within a cell with new text.
    Example: =SUBSTITUTE("Excel is great.", "is", "can be") replaces “is” with “can be.”
  59. SEARCH: Searches for a specified text within another text and returns the starting position.
    Example: =SEARCH("Excel", "Microsoft Excel is powerful.") returns 10 (position of “Excel”).
  60. REPLACE: Replaces a specified number of characters within a text string, starting at a specified position.
    Example: =REPLACE("Hello, World!", 7, 5, "Universe") replaces “World” with “Universe.”
  61. CONVERT (Temperature Conversion): Converts temperatures from Fahrenheit to Celsius or vice versa.
    Example: =CONVERT(98.6, "F", "C") converts 98.6 degrees Fahrenheit to Celsius.
  62. FIND: Finds the position of a specified text within another text.
    Example: =FIND("Excel", "Microsoft Excel is great.") returns 11 (position of “Excel”).
  63. AVERAGEA: Calculates the average of a range that includes numbers and text.
    Example: =AVERAGEA(A1:A5) calculates the average of a range with mixed numbers and text.
  64. DATEDIF (Elapsed Months): Calculates the number of elapsed months between two dates.
    Example: =DATEDIF("2022-01-01", "2023-10-01", "ym") calculates the elapsed months between the two dates.
  65. HYPERLINK: Creates a clickable hyperlink within a cell.
    Example: =HYPERLINK("https://www.example.com", "Visit Example") creates a clickable link.
  66. ABS: Returns the absolute value of a number.
    Example: =ABS(-5) returns 5, which is the absolute value of -5.
  67. EXP: Returns the result of e raised to the power of a number.
    Example: =EXP(2) returns the value of e^2, which is approximately 7.389.
  68. LOG: Returns the logarithm of a number to a specified base.
    Example: =LOG(100, 10) returns 2 because 10^2 = 100.
  69. SQRT: Calculates the square root of a number.
    Example: =SQRT(25) returns 5 because the square root of 25 is 5.
  70. COUNTIFS: Counts cells based on multiple criteria.
    Example: =COUNTIFS(A1:A10, "Apples", B1:B10, "Red") counts cells where A column has “Apples” and B column has “Red.”
  71. SUMIFS: Adds up values based on multiple criteria.
    Example: =SUMIFS(C1:C10, A1:A10, "Apples", B1:B10, "Red") sums C column where A column has “Apples” and B column has “Red.”
  72. AVERAGEIFS: Calculates the average based on multiple criteria.
    Example: =AVERAGEIFS(D1:D10, A1:A10, "Apples", B1:B10, "Red") averages D column where A column has “Apples” and B column has “Red.”
  73. TEXTJOIN: Joins text from multiple cells with a specified delimiter.
    Example: =TEXTJOIN(", ", TRUE, E1:E5) joins text in E1:E5 with a comma and space as the delimiter.
  74. IFNA: Returns a specified value if a formula results in a #N/A error.
    Example: =IFNA(VLOOKUP("Apple", G1:H5, 2, FALSE), "Not Found") returns “Not Found” if “Apple” is not found.
  75. NPER (Number of Periods): Calculates the number of periods for an investment to reach a certain value.
    Example: =NPER(0.05, -500, 1000) calculates the number of periods needed to reach $1000 from an initial investment of -$500 at a 5% interest rate.
  76. IPMT (Interest Payment): Calculates the interest payment for a specific period of a loan or investment.
    Example: =IPMT(0.06, 2, 5, 10000) calculates the interest payment for the second period of a $10,000 loan with a 6% interest rate over 5 periods.
  77. PPMT (Principal Payment): Calculates the principal payment for a specific period of a loan or investment.
    Example: =PPMT(0.06, 2, 5, 10000) calculates the principal payment for the second period of a $10,000 loan with a 6% interest rate over 5 periods.
  78. NORM.DIST (Normal Distribution): Calculates the cumulative probability of a value in a normal distribution.
    Example: =NORM.DIST(80, 70, 10, TRUE) calculates the probability that a value in a normal distribution with a mean of 70 and standard deviation of 10 is less than or equal to 80.
  79. NORM.INV (Inverse Normal Distribution): Returns the value in a normal distribution corresponding to a given probability.
    Example: =NORM.INV(0.85, 70, 10) calculates the value in a normal distribution with a mean of 70 and standard deviation of 10 corresponding to a cumulative probability of 0.85.
  80. RANDARRAY: Generates an array of random numbers.
    Example: =RANDARRAY(3, 3) generates a 3×3 array of random numbers between 0 and 1.
  81. RANK.EQ: Returns the rank of a number in a list with ties ranked by the average.
    Example: =RANK.EQ(Y1, Y1:Y10, 0) ranks the value in Y1 within the range Y1:Y10 with ties ranked by the average.
  82. COUNTIFS with Wildcards: Counts cells based on multiple criteria with wildcards (* and ?).
    Example: =COUNTIFS(A1:A10, "App*", B1:B10, "R?d") counts cells where A column starts with “App” and B column has a three-letter word starting with “R” and ending with “d.”
  83. LOOKUP (Vector): Searches for a value in a vector and returns a corresponding value from another vector.
    Example: =LOOKUP("Apples", G1:G5, H1:H5) searches for “Apples” in G1:G5 and returns the corresponding value from H1:H5.
  84. RANK.AVG: Returns the rank of a number in a list with ties ranked by the average.
    Example: =RANK.AVG(Y1, Y1:Y10, 0) ranks the value in Y1 within the range Y1:Y10 with ties ranked by the average.
  85. ROUND (to Nearest 10): Rounds a number to the nearest multiple of 10.
    Example: =ROUND(37, -1) rounds 37 to the nearest multiple of 10, which is 40.
  86. QUOTIENT: Returns the integer part of a division result.
    Example: =QUOTIENT(10, 3) returns 3, which is the integer part of 10 divided by 3.
  87. SIGN: Returns the sign of a number (positive, negative, or zero).
    Example: =SIGN(-5) returns -1 because -5 is negative.
  88. INT: Rounds a number down to the nearest integer.
    Example: =INT(5.8) returns 5, which is the integer part of 5.8.
  89. LCM (Least Common Multiple): Returns the least common multiple of two or more numbers.
    Example: =LCM(12, 18) returns 36, which is the least common multiple of 12 and 18.
  90. GCD (Greatest Common Divisor): Returns the greatest common divisor of two or more numbers.
    Example: =GCD(24, 36) returns 12, which is the greatest common divisor of 24 and 36.
  91. MODE.SNGL: Returns the most frequently occurring number in a range, excluding duplicates.
    Example: =MODE.SNGL(A1:A10) returns the mode (most frequently occurring value) in the range A1:A10.
  92. MODE.MULT: Returns an array of the most frequently occurring numbers in a range, including duplicates.
    Example: =MODE.MULT(A1:A10) returns an array of all modes in the range A1:A10.
  93. STDEV.P (Population Standard Deviation): Calculates the standard deviation of a population.
    Example: =STDEV.P(A1:A10) calculates the population standard deviation of values in the range A1:A10.
  94. STDEV.S (Sample Standard Deviation): Calculates the standard deviation of a sample.
    Example: =STDEV.S(A1:A10) calculates the sample standard deviation of values in the range A1:A10.
  95. PERCENTILE.EXC: Returns the exclusive k-th percentile of a data set.
    Example: =PERCENTILE.EXC(B1:B10, 0.75) returns the 75th percentile (exclusive) of the data in B1:B10.
  96. PERCENTILE.INC: Returns the inclusive k-th percentile of a data set.
    Example: =PERCENTILE.INC(B1:B10, 0.75) returns the 75th percentile (inclusive) of the data in B1:B10.
  97. RANK.PERCENTILE: Returns the rank of a value in a list as a percentile (inclusive).
    Example: =RANK.PERCENTILE(Y1, Y1:Y10, 0) ranks the value in Y1 within the range Y1:Y10 as a percentile.
  98. ISNUMBER: Checks if a cell contains a numeric value.
    Example: =ISNUMBER(Z1) returns TRUE if Z1 contains a number.
  99. ISBLANK: Checks if a cell is blank.
    Example: =ISBLANK(A1) returns TRUE if A1 is blank.
  100. CONCAT: Combines text or values from multiple cells or ranges.
    Example: =CONCAT(A1, " ", B1) combines the text or values in A1 and B1 with a space in between.

Artificial Intelligence Generated Content

Welcome to Ourtaxpartner.com, where the future of content creation meets the present. Embracing the advances of artificial intelligence, we now feature articles crafted by state-of-the-art AI models, ensuring rapid, diverse, and comprehensive insights. While AI begins the content creation process, human oversight guarantees its relevance and quality. Every AI-generated article is transparently marked, blending the best of technology with the trusted human touch that our readers value.   Disclaimer for AI-Generated Content on Ourtaxpartner.com : The content marked as "AI-Generated" on Ourtaxpartner.com is produced using advanced artificial intelligence models. While we strive to ensure the accuracy and relevance of this content, it may not always reflect the nuances and judgment of human-authored articles. [Your Website Name] and its team do not guarantee the completeness or reliability of AI-generated content and advise readers to use it as a supplementary resource. We encourage feedback and will continue to refine the integration of AI to better serve our readership.

Leave a Reply

Your email address will not be published. Required fields are marked *