Microsoft Excel is a powerful tool for data analysis, finance, and many other fields, and mastering its formulas can significantly enhance productivity. In this article, we’ll dive deep into the top 98 Excel formulas, provide practice examples, and offer insights into how you can apply them to improve your Excel skills. Whether you are an Excel beginner or a power user, this guide will help you streamline your work and manage data more effectively.
Table of Contents
1. SUM Formula in Excel
The SUM function adds values together. You can sum individual values, cell references, or ranges.
Syntax:
=SUM(number1, [number2], …)
Example:
=SUM(A1:A5) // Adds the values in cells A1 through A5
2. AVERAGE Formula in Excel
The AVERAGE function calculates the average (arithmetic mean) of the numbers in a specified range of cells.
Syntax:
=AVERAGE(number1, [number2], …)
Example:
=AVERAGE(B1:B10) // This formula computes the average of the values in the range B1 to B10.
3. IF Formula in Excel
The IF function runs a logical test, returning one result in the event that it is TRUE and a different value in the event that it is FALSE.
Syntax:
=IF(logical_test, value_if_true, value_if_false)
Example:
=IF(C1>10, “Above 10”, “Below or Equal to 10”) // If C1 is greater than 10, return “Above 10”, else return “Below or Equal to 10”
4. VLOOKUP Formula in Excel
The VLOOKUP method retrieves a value from another column in the same row after searching for a value in a table’s first column.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example:
=VLOOKUP(123, A2:D10, 3, FALSE) // Searches for 123 in the first column of A2:D10 and returns the value from the 3rd column in the same row
5. COUNT Formula in Excel
The number of cells containing numbers is counted by the COUNT function.
Syntax:
=COUNT(value1, [value2], …)
Example:
=COUNT(D1:D20) // Counts the number of numeric values in cells D1 through D20
6. COUNTA Formula in Excel
To find the number of cells in a range that are not empty, use the COUNTA function. It includes cells with any form of data, such as numbers, text, or dates.
Syntax:
=COUNTA(value1, [value2], …)
Example:
=COUNTA(E1:E30) // Counts the number of non-empty cells in the range E1:E30
7. CONCATENATE Formula in Excel
Two or more strings can be joined into one using the CONCATENATE function.
Syntax:
=CONCATENATE(text1, [text2], …)
Example:
=CONCATENATE(H1, ” “, I1) // Joins the content of H1 and I1 with a space in between
8. NOW Formula in Excel
The current date and time are returned by the NOW function.
Syntax:
=NOW()
Example:
=NOW() // The current date and time are returned by this formula.
9. IFERROR Formula in Excel
If a formula yields an error, the IFERROR function gives a value; if not, it returns the formula’s result.
Syntax:
=IFERROR(value, value_if_error)
Example:
=IFERROR(A1/B1, “Error in division”) // If the division results in an error, “Error in division” is returned
10. AND & OR Formulas in Excel
The AND & OR functions are logical operators used to evaluate multiple conditions. AND returns TRUE if all conditions are met, while OR returns TRUE if any of the conditions are met.
Syntax for AND:
=AND(logical1, [logical2], …)
Syntax for OR:
=OR(logical1, [logical2], …)
Example:
=AND(A1>10, B1<5) // Returns TRUE if A1 is greater than 10 AND B1 is less than 5
=OR(A1>10, B1<5) // Returns TRUE if either A1 is greater than 10 OR B1 is less than 5
11. LEN Formula in Excel
The number of characters in a text string is returned by the LEN function.
Syntax:
=LEN(text)
Example:
=LEN(“Excel Mastery”) // Yields 13 since the string “Excel Mastery” contains 13 characters.
Example: =LEN(BD1)
This formula returns the number of characters in cell BD1, including spaces.
12. SUMIF Formula in Excel
The numbers in a range that satisfy a particular criteria are added using the SUMIF function.
Syntax:
=SUMIF(range, criteria, [sum_range])
Example:
=SUMIF(A1:A10, “>5”) // Adds all values in A1:A10 that are greater than 5
13. COUNTIF Formula in Excel
The COUNTIF function keeps track of how many cells satisfy a given requirement.
Syntax:
=COUNTIF(range, criteria)
Example:
=COUNTIF(B1:B10, “Apple”) // Counts the number of cells in B1:B10 that contain the word “Apple”
14. TRIM Formula in Excel
A text string’s superfluous spaces are eliminated by the TRIM function, leaving only single spaces between words.
Syntax:
=TRIM(text)
Example:
=TRIM(” Excel Mastery “) // Returns “Excel Mastery” by removing extra spaces
Example: =TRIM(BG1)
This formula removes any extra spaces from the text in cell BG1.
15. PROPER Formula in Excel
In a text string, the PROPER function capitalizes the first letter of each word.
Syntax:
=PROPER(text)
Example:
=PROPER(“excel mastery”) // Returns “Excel Mastery”
Example: =PROPER(BH1)
This formula capitalizes the first letter of each word in the text in cell BH1.
16. RANK Formula in Excel
The rank of a number in a list of numbers is returned by the RANK function.
Syntax:
=RANK(number, ref, [order])
Example:
=RANK(5, A1:A10) // Returns the rank of 5 in the range A1:A10
17. HLOOKUP Formula in Excel
The HLOOKUP function retrieves a value from a different row in the same column after searching the top row of a table for a value.
Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Example:
=HLOOKUP(“January”, A1:D5, 3, FALSE) // Looks for “January” in the top row of A1:D5 and returns the value from the 3rd row in the same column
18. CHOOSE Formula in Excel
Based on an index number, the CHOOSE function selects a value from a list of values.
Syntax:
=CHOOSE(index_num, value1, [value2], …)
Example:
=CHOOSE(2, “Red”, “Green”, “Blue”) // Returns “Green”, the 2nd value in the list
19. TEXTJOIN Formula in Excel
The TEXTJOIN function combines the contents of multiple cells, with a specified delimiter separating each value. It’s more flexible than the CONCATENATE function because it allows you to ignore empty cells.
Syntax:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
Example:
=TEXTJOIN(“, “, TRUE, A1:A5) // Joins the values in A1:A5, separated by commas
20. TRANSPOSE Formula in Excel
A vertical range of cells can be changed to a horizontal range, or vice versa, using the TRANSPOSE function.
Syntax:
=TRANSPOSE(array)
Example:
=TRANSPOSE(A1:A5) // Converts the vertical range A1:A5 into a horizontal range
21. MOD Formula in Excel
The remainder obtained from dividing a number by a divisor is returned by the MOD function.
Syntax:
=MOD(number, divisor)
Example:
=MOD(10, 3) // Returns 1, as 10 divided by 3 leaves a remainder of 1
22. SUBTOTAL Formula in Excel
The SUBTOTAL function is used to calculate subtotals in a list or range of data. It can perform various operations, such as SUM, AVERAGE, COUNT, etc.
Syntax:
=SUBTOTAL(function_num, ref1, [ref2], …)
Example:
=SUBTOTAL(9, B1:B10) // This formula calculates the sum of the values in the range AS1 to AS10, where “9” specifies the SUM function.
23. ISBLANK Formula in Excel
The ISBLANK function determines if a cell contains nothing.
Syntax:
=ISBLANK(value)
Example:
=ISBLANK(C1) // Returns TRUE if C1 is empty, otherwise FALSE
24. OFFSET Formula in Excel
A reference to a range that is offset from a beginning cell or range is returned by the OFFSET function.
Syntax:
=OFFSET(reference, rows, cols, [height], [width])
Example:
=OFFSET(A1, 2, 3) // Returns a reference to the cell 2 rows down and 3 columns to the right of A1
Example: =OFFSET(AR1, 2, 3, 1, 1)
This formula returns a reference to the cell that is 2 rows down and 3 columns to the right of cell AR1.
25. MATCH Formula in Excel
The MATCH function searches for a value in a range and returns the relative position of that value.
Syntax:
=MATCH(lookup_value, lookup_array, [match_type])
Example:
=MATCH(“Orange”, A1:A10, 0) // This formula searches for the value in cell AP1 in the range AQ1 to AQ10 and returns its position.
26. UNIQUE Formula in Excel
A list of distinct values from a range is what the UNIQUE function returns.
Syntax:
=UNIQUE(array)
Example:
=UNIQUE(B1:B10) // Returns unique values from the range B1:B10
27. ARRAY Formula in Excel
The ARRAYFORMULA function allows you to apply a formula to a range of cells at once, rather than applying it individually to each cell.
Syntax:
=ARRAYFORMULA(array_formula)
Example:
=ARRAYFORMULA(A1:A10 * B1:B10) // Multiplies each value in A1:A10 by the corresponding value in B1:B10
28. TEXT Formula in Excel
The TEXT function allows you to format numbers, dates, or other values as text in a specific format.
Syntax:
=TEXT(value, format_text)
Example:
=TEXT(TODAY(), “MM/DD/YYYY”) // Formats the current date in the specified format (e.g., “09/19/2024”)
29. INDIRECT Formula in Excel
The reference given by a text string is returned by the INDIRECT function. It allows you to dynamically change the reference to a cell or range.
Syntax:
=INDIRECT(ref_text, [a1])
Example:
=INDIRECT(“A1”) // This formula returns the value in cell A1, based on the text string “A1”.
30. VSTACK and HSTACK Formulas in Excel
The VSTACK function vertically stacks multiple ranges of values, while HSTACK stacks them horizontally.
Syntax for VSTACK:
=VSTACK(range1, range2, …)
Syntax for HSTACK:
=HSTACK(range1, range2, …)
Example:
=VSTACK(A1:A3, B1:B3) // Vertically stacks values in A1:A3 and B1:B3
=HSTACK(A1:A3, B1:B3) // Horizontally stacks values in A1:A3 and B1:B3
31. FILTER Formula in Excel
The FILTER function is an advanced formula that allows you to extract values from a range based on specified criteria. It’s quite helpful when making dynamic reports.
Syntax:
=FILTER(array, include, [if_empty])
Example:
=FILTER(A1:A10, B1:B10 > 5) // Returns all values in A1:A10 where the corresponding value in B1:B10 is greater than 5
32. SPLIT Formula in Excel
The SPLIT function divides a text string into separate parts based on a delimiter.
Syntax:
=SPLIT(text, delimiter)
Example:
=SPLIT(“Apple, Orange, Banana”, “, “) // Splits the text into “Apple”, “Orange”, and “Banana”
33. GOOGLEFINANCE Formula in Excel
The GOOGLEFINANCE function retrieves real-time financial data for a specified stock symbol.
Syntax:
=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date], [interval])
Example:
=GOOGLEFINANCE(“AAPL”, “price”) // Returns the current stock price of Apple Inc.
34. MMULT Formula in Excel
The matrix product of two arrays is returned by the MMULT function. This is a useful tool in advanced data analysis.
Syntax:
=MMULT(array1, array2)
Example:
=MMULT(A1:B2, C1:D2) // Returns the matrix product of the two arrays A1:B2 and C1:D2
35. XLOOKUP Formula in Excel
The XLOOKUP function is a more powerful and flexible alternative to VLOOKUP and HLOOKUP, offering greater ease of use.
Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Example:
=XLOOKUP(“Apple”, A1:A10, B1:B10) // Searches for “Apple” in A1:A10 and returns the corresponding value from B1:B10
36. DAYS Formula in Excel
The number of days between two dates can be computed using the DAYS function.
Syntax:
=DAYS(end_date, start_date)
Example:
=DAYS(TODAY(), “01/01/2023”) // determines how many days are left before January 1, 2023.
37. ISEVEN and ISODD Formulas in Excel
The ISEVEN and ISODD functions are used to check if a number is even or odd, respectively. They return TRUE if the condition is met, and FALSE otherwise.
Syntax for ISEVEN:
=ISEVEN(number)
Syntax for ISODD:
=ISODD(number)
Example:
=ISEVEN(4) // Returns TRUE because 4 is an even number
=ISODD(3) // Returns TRUE because 3 is an odd number
38. CODE Formula in Excel
The first character in a text string’s numeric code is returned by the CODE function.
Syntax:
=CODE(text)
Example:
=CODE(“A”) // This formula returns the ASCII code for the character “A”, which is 65.
39. FORMULATEXT Formula in Excel
The FORMULATEXT function displays the formula in a cell as text. It’s a helpful tool for reviewing or sharing the formulas used in a worksheet.
Syntax:
=FORMULATEXT(reference)
Example:
=FORMULATEXT(A1) // Displays the formula used in cell A1 as text
40. SEQUENCE Formula in Excel
The SEQUENCE function generates a sequence of numbers in a grid format.
Syntax:
=SEQUENCE(rows, [columns], [start], [step])
Example:
=SEQUENCE(5, 1, 1, 1) // Generates a vertical sequence of numbers from 1 to 5
41. HYPERLINK Formula in Excel
A clickable hyperlink is created in a cell using the HYPERLINK function.
Syntax:
=HYPERLINK(link_location, [friendly_name])
Example:
=HYPERLINK(“https://www.example.com”, “Click here”) // Creates a hyperlink that displays “Click here” and links to “https://www.example.com”
42. SORT Formula in Excel
The SORT function sorts the contents of a range based on a given column and direction.
Syntax:
=SORT(array, [sort_index], [sort_order])
Example:
=SORT(A1:A10, 1, 1) // Sorts the values in A1:A10 in ascending order
43. SORTBY Formula in Excel
The SORTBY function sorts a range based on the values in another range.
Syntax:
=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], …)
Example:
=SORTBY(A1:A10, B1:B10, 1) // Sorts the range A1:A10 based on the values in B1:B10 in ascending order
44. LAMBDA Formula in Excel
The LAMBDA function allows you to create reusable custom functions in Excel without needing VBA (Visual Basic for Applications). You define the logic and then apply the function throughout your workbook.
Syntax:
=LAMBDA(parameter, calculation)
Example:
=LAMBDA(x, x+2)(5) // Returns 7, as it adds 2 to the parameter passed (5)
45. XMATCH Formula in Excel
The XMATCH function is similar to MATCH, but it offers enhanced flexibility in terms of search modes and match types. It gives back an item’s relative location inside a range.
Syntax:
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
Example:
=XMATCH(“Apple”, A1:A10) // Returns the position of “Apple” within the range A1:A10
46. IFS Formula in Excel
The IFS function evaluates multiple conditions and returns a corresponding value for the first condition that is true. It simplifies the use of nested IF statements.
Syntax:
=IFS(condition1, value1, condition2, value2, …)
Example:
=IFS(A1 > 90, “A”, A1 > 80, “B”, A1 > 70, “C”) // Returns a grade based on the value in A1
47. SWITCH Formula in Excel
The SWITCH function compares an expression against a list of values and returns the result corresponding to the first match. It simplifies the logic of multiple IF statements.
Syntax:
=SWITCH(expression, value1, result1, [value2, result2], …)
Example:
=SWITCH(A1, 1, “One”, 2, “Two”, 3, “Three”) // Returns the text corresponding to the value in A1
48. MINIFS and MAXIFS Formulas in Excel
These functions return the smallest (MINIFS) or largest (MAXIFS) value in a range that meets specified criteria.
Syntax for MINIFS:
=MINIFS(range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Syntax for MAXIFS:
=MAXIFS(range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Example:
=MINIFS(A1:A10, B1:B10, “>50”) // Returns the smallest value in A1:A10 where the corresponding value in B1:B10 is greater than 50
=MAXIFS(A1:A10, B1:B10, “<30”) // Returns the largest value in A1:A10 where the corresponding value in B1:B10 is less than 30
49. ARRAYTOTEXT Formula in Excel
The ARRAYTOTEXT function returns the text form of an array.
Syntax:
=ARRAYTOTEXT(array, [format])
Example:
=ARRAYTOTEXT(A1:A5) // Returns the text representation of the array in A1:A5
50. TEXTSPLIT Formula in Excel
The TEXTSPLIT function splits text into rows or columns based on specified delimiters.
Syntax:
=TEXTSPLIT(text, col_delimiter, [row_delimiter])
Example:
=TEXTSPLIT(“apple,banana,grape”, “,”) // Splits the text into individual words separated by commas
51. LET Formula in Excel
The LET function allows you to assign names to calculations and values inside a formula. It improves performance by enabling the reuse of calculated values.
Syntax:
=LET(name1, value1, calculation)
Example:
=LET(x, A1*2, x+5) // Assigns the result of A1*2 to x, then adds 5 to x
52. NETWORKDAYS ARRAY Formula in Excel
The RANDARRAY function returns an array of random numbers between 0 and 1 or between a specified range.
Syntax:
=RANDARRAY([rows], [columns], [min], [max], [whole_number])
Example:
=RANDARRAY(5, 1, 1, 100, TRUE) // Generates a vertical array of 5 random integers between 1 and 100
53. SPILL Formula in Excel
The SPILL function refers to the behaviour when a formula that returns multiple values “spills” the results into adjacent cells. This occurs with dynamic array functions such as FILTER, SORT, SEQUENCE, etc.
Syntax:
There is no specific syntax for SPILL, but it occurs with dynamic arrays.
Example:
=FILTER(A1:A10, B1:B10 > 50) // If this returns multiple values, they “spill” into the adjacent cells below
54. MAX Formula in Excel
The MAX function returns the highest number from a specified range of cells.
Syntax: =MAX(number1, [number2], …)
Example: =MAX(D1:D6)
This formula finds the maximum value in the range D1 to D6.
52. MIN Formula in Excel
The MIN function returns the lowest number from a specified range of cells.
Syntax: =MIN(number1, [number2], …)
Example: =MIN(E1:E4)
This formula determines the lowest value between E1 and E4.
56. RAND Formula in Excel
A random decimal number between 0 and 1 is produced using the RAND function.
Syntax: =RAND()
Example: =RAND()
This formula generates a random decimal number between 0 and 1.
57. LEFT Formula in Excel (Extracting Variable Number of Characters)
The LEFT function takes a text string’s beginning and extracts a predetermined amount of characters from it.
Syntax: =LEFT(text, [num_chars])
Example: =LEFT(AN1, SEARCH(” “, AN1)-1)
This formula extracts characters from the beginning of the text in cell AN1 until the first space.
58. RIGHT Formula in Excel (Extracting Variable Number of Characters)
A text string’s ending is stripped of a predetermined amount of characters using the RIGHT function.
Syntax: =RIGHT(text, [num_chars])
Example: =RIGHT(AO1, LEN(AO1)-SEARCH(” “, AO1))
This formula extracts characters from the first space to the end of the text in cell AO1.
59. MID Formula in Excel (Extracting Variable Number of Characters)
The MID function can begin at any point in a text string and extract a given number of characters from it.
Syntax: =MID(text, start_num, num_chars)
Example: =MID(AP1, SEARCH(” “, AP1)+1, LEN(AP1)-SEARCH(” “, AP1))
This formula extracts characters after the first space to the end of the text in cell AP1.
60. AVERAGEIFS Formula in Excel
The average of a range of values is determined by the AVERAGEIFS function using a number of different factors.
Syntax: =AVERAGEIFS(avg_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Example: =AVERAGEIFS(AH1:AH10, AI1:AI10, “Red”, AJ1:AJ10, “<>0”)
This formula averages the values in AH1 to AH10 where the corresponding cells in AI1 to AJ10 meet specific criteria.
61. SUMIFS Formula in Excel
The SUMIFS function adds up the values in a range based on multiple criteria.
Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Example: =SUMIFS(AE1:AE10, AF1:AF10, “Apples”, AG1:AG10, “>50”)
This formula sums the values in AE1 to AE10 where the corresponding cells in AF1 to AG10 meet specific criteria.
62. INDEX Formula in Excel
The value of a cell in a given row and column within a range is returned by the INDEX function.
Syntax: =INDEX(array, row_num, [column_num])
Example: =INDEX(X1:Y10, 3, 2)
The value in the third row and second column of the range X1 to Y10 is returned by this formula.
63. AVERAGEIF Formula in Excel
The AVERAGEIF function calculates the average of numbers in a range based on a single criterion.
Syntax: =AVERAGEIF(range, criteria, [average_range])
Example: =AVERAGEIF(T1:T10, “Red”, U1:U10)
This formula averages the values in U1 to U10 where the corresponding cell in T1 to T10 is “Red”.
64. TODAY Formula in Excel
The TODAY function returns the current date.
Syntax: =TODAY()
Example: =TODAY()
This formula returns the current date.
65. RANDBETWEEN Formula in Excel
A random integer is produced by the RANDBETWEEN function between two given integers.
Syntax: =RANDBETWEEN(bottom, top)
Example: =RANDBETWEEN(1, 100)
Using this formula, a random whole number between 1 and 100 is produced.
66. TODAY Formula in Excel (with Custom Formatting)
The TEXT function formats the TODAY function’s output as text with a specific format.
Syntax: =TEXT(TODAY(), “format”)
Example: =TEXT(TODAY(), “dd-mmm-yyyy”)
This formula returns the current date in the “day-month-year” format.
67. DATE Formula in Excel
The DATE function creates a date based on the specified year, month, and day.
Syntax: =DATE(year, month, day)
Example: =DATE(2024, 09, 18)
This formula creates a date for September 18, 2024.
68. TIME Formula in Excel
The TIME function creates a time value based on the specified hour, minute, and second.
Syntax: =TIME(hour, minute, second)
Example: =TIME(9, 30, 0)
This formula creates a time value for 9:30 AM.
69. WEEKDAY Formula
The day of the week for a given date is returned by the WEEKDAY function.
Syntax: =WEEKDAY(serial_number, [return_type])
Example: =WEEKDAY(AS1)
This formula returns the day of the week for the date in cell AS1.
70. EOMONTH Formula
The last day of the month, along with the number of months that have passed before or after a particular date, is returned by the EOMONTH function.
Syntax: =EOMONTH(start_date, months)
Example: =EOMONTH(AT1, 3)
Three months after the date in cell AT1, this formula yields the last day of the month.
71. DATEDIF Formula
The DATEDIF function calculates the difference between two dates based on a specified time unit.
Syntax: =DATEDIF(start_date, end_date, unit)
Example: =DATEDIF(AU1, AU10, “y”)
The number of full years between the dates in cells AU1 and AU10 is determined by this formula.
72. PMT Formula
The PMT function uses constant interest rates and payments to determine a loan’s monthly payment.
Syntax: =PMT(rate, nper, pv, [fv], [type])
Example: =PMT(0.05/12, 12*5, -20000)
This formula calculates the monthly payment for a loan with a 5% annual interest rate, 60 payments, and a $20,000 principal.
73. FV Formula
The future value of an investment is determined using the FV function using constant monthly payments and a constant interest rate.
Syntax: =FV(rate, nper, pmt, [pv], [type])
Example: =FV(0.04, 10, -200, -10000)
This formula calculates the future value of an investment with a 4% annual interest rate, 10 periods, and $200 payments.
74. NPV Formula
The net present value (NPV) of an investment is determined by the NPV function using a discount rate and a sequence of periodic cash flows.
Syntax: =NPV(rate, value1, [value2], …)
Example: =NPV(0.08, 1000, 1200, 1500)
This formula calculates the net present value of future cash flows of $1000, $1200, and $1500 at an 8% discount rate.
75. IRR Formula
The internal rate of return for a sequence of cash flows is computed using the IRR function.
Syntax: =IRR(values, [guess])
Example: =IRR(AJ1:AJ10)
This formula calculates the internal rate of return for the series of cash flows in the range AJ1 to AJ10.
76. REPLACE Formula
A text string can have portions replaced with another text string using the REPLACE function.
Syntax: =REPLACE(old_text, start_num, num_chars, new_text)
Example: =REPLACE(AW1, 1, 5, “Replaced”)
This formula replaces the first 5 characters in cell AW1 with “Replaced”.
77. SUBSTITUTE Formula
The SUBSTITUTE function replaces occurrences of a specified text string with another text string.
Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])
Example: =SUBSTITUTE(AX1, “Apple”, “Orange”)
This formula replaces every instance of “Apple” in cell AX1 with “Orange”.
Example: =SUBSTITUTE(BK1, “cat”, “dog”)
This formula replaces every instance of “cat” in cell BK1 with “dog”.
78. REPT Formula
A text string is repeated a certain number of times using the REPT function.
Syntax: =REPT(text, number_times)
Example: =REPT(“A”, 5)
This formula repeats the character “A” five times.
79. ROUND Formula
A number is rounded to a predetermined number of digits using the ROUND function.
Syntax: =ROUND(number, num_digits)
Example: =ROUND(AY1, 2)
This formula rounds the value in cell AY1 to 2 decimal places.
80. ROUNDUP Formula
A number is rounded up to a predetermined number of digits using the ROUNDUP function, away from zero.
Syntax: =ROUNDUP(number, num_digits)
Example: =ROUNDUP(AZ1, 2)
This formula rounds the value in cell AZ1 up to 2 decimal places.
81. ROUNDDOWN Formula
The ROUNDDOWN function rounds a number down, toward zero, to a specified number of digits.
Syntax: =ROUNDDOWN(number, num_digits)
Example: =ROUNDDOWN(BA1, 2)
This formula rounds the value in cell BA1 down to 2 decimal places.
82. CEILING Formula
A number is rounded up to the closest multiple of a given value using the CEILING function.
Syntax: =CEILING(number, significance)
Example: =CEILING(BB1, 5)
This formula rounds the value in cell BB1 up to the nearest multiple of 5.
83. FLOOR Formula
A number is rounded by the FLOOR function to the closest multiple of a given value.
Syntax: =FLOOR(number, significance)
Example: =FLOOR(BC1, 5)
This formula rounds the value in cell BC1 down to the nearest multiple of 5.
84. FIND Formula
The FIND function returns the starting position of a text string within another text string, case-sensitive.
Syntax: =FIND(find_text, within_text, [start_num])
Example: =FIND(“e”, BE1)
This formula finds the position of the first occurrence of the character “e” in cell BE1.
85. SEARCH Formula
The SEARCH function returns the starting position of a text string within another text string, not case-sensitive.
Syntax: =SEARCH(find_text, within_text, [start_num])
Example: =SEARCH(“e”, BF1)
This formula finds the position of the first occurrence of the character “e” in cell BF1, regardless of case.
86. UPPER Formula
A text string’s letters can all be changed to uppercase using the UPPER function.
Syntax: =UPPER(text)
Example: =UPPER(BI1)
This formula converts all letters in the text in cell BI1 to uppercase.
87. LOWER Formula
A text string’s letters are all changed to lowercase using the LOWER function.
Syntax: =LOWER(text)
Example: =LOWER(BJ1)
This formula converts all letters in the text in cell BJ1 to lowercase.
88. VALUE Formula
The VALUE function converts text that appears in a recognized format (like numbers) into a numeric value.
Syntax: =VALUE(text)
Example: =VALUE(BL1)
This formula converts the text in cell BL1 to a numeric value.
89. NETWORKDAYS Formula
The number of working days between two dates is determined by the NETWORKDAYS function.
Syntax: =NETWORKDAYS(start_date, end_date, [weekend], [holidays])
Example: =NETWORKDAYS(BM1, BN1)
This formula calculates the number of working days between the dates in cells BM1 and BN1.
90. WORKDAY Formula
The WORKDAY function returns the date after a specified number of working days.
Syntax: =WORKDAY(start_date, days, [weekend], [holidays])
Example: =WORKDAY(BO1, 10)
This formula returns the date that is 10 working days after the date in cell BO1.
91. MIN and MAX Formulas
The MIN function returns the smallest number in a set, while MAX returns the largest.
Syntax for MIN:
=MIN(number1, [number2], …)
Syntax for MAX:
=MAX(number1, [number2], …)
Example:
=MIN(F1:F15) // Returns the smallest number in the range F1:F15
=MAX(G1:G15) // Returns the largest number in the range G1:G15
92. LEFT, RIGHT, and MID Formulas
The LEFT, RIGHT, and MID functions extract parts of a text string.
Syntax for LEFT:
=LEFT(text, num_chars)
Syntax for RIGHT:
=RIGHT(text, num_chars)
Syntax for MID:
=MID(text, start_num, num_chars)
Example:
=LEFT(“Excel Mastery”, 5) // Returns “Excel”
=RIGHT(“Excel Mastery”, 7) // Returns “Mastery”
=MID(“Excel Mastery”, 7, 3) // Returns “Mas”
93. LOWER and UPPER Formulas
The LOWER function converts all text to lowercase, while UPPER converts all text to uppercase.
Syntax for LOWER:
=LOWER(text)
Syntax for UPPER:
=UPPER(text)
Example:
=LOWER(“EXCEL MASTERY”) // Returns “excel mastery”
=UPPER(“excel mastery”) // Returns “EXCEL MASTERY”
94. ISEVEN and ISODD Formulas
The ISEVEN and ISODD functions are used to check if a number is even or odd, respectively. They return TRUE if the condition is met, and FALSE otherwise.
Syntax for ISEVEN:
=ISEVEN(number)
Syntax for ISODD:
=ISODD(number)
Example:
=ISEVEN(4) // Returns TRUE because 4 is an even number
=ISODD(3) // Returns TRUE because 3 is an odd number
95. MINIFS and MAXIFS Formulas
These functions return the smallest (MINIFS) or largest (MAXIFS) value in a range that meets specified criteria.
Syntax for MINIFS:
=MINIFS(range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Syntax for MAXIFS:
=MAXIFS(range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Example:
=MINIFS(A1:A10, B1:B10, “>50”) // Returns the smallest value in A1:A10 where the corresponding value in B1:B10 is greater than 50
=MAXIFS(A1:A10, B1:B10, “<30”) // Returns the largest value in A1:A10 where the corresponding value in B1:B10 is less than 30
96. ARRAY Formula
Array formulas allow us to perform multiple calculations on multiple data sets simultaneously. They are highly useful when we want to return multiple results or perform operations on entire data ranges.
Syntax for Array:
{=SUM(A1:A10*B1:B10)}
Example:
This formula multiplies the values in ranges A1 and B1 and then returns the sum of these products. The curly brackets {} indicate that this is an array formula and must be entered with Ctrl + Shift + Enter.
97. Creating Dynamic Data with Pivot Tables and Macros
Creating a Pivot Table
Pivot tables summarize large data sets quickly. They help us manipulate data by creating customizable summaries without altering the underlying dataset.
Steps to Create a Pivot Table:
- Select the dataset.
- Use the shortcut Alt + N + V to open the PivotTable Wizard.
- Select the location for the Pivot Table (new or old worksheet).
98. Using Macros for Automation
Automating repeated processes with macros reduces the chance of error and saves time. A macro is a set of recorded instructions that Excel can execute automatically.
Shortcut to Record a Macro:
Alt + T + M + R opens the Record Macro dialog.
Once the macro is recorded, you can assign a shortcut key (such as Ctrl + Shift + M) to run the macro with a single keystroke.
Conclusion
Mastering Excel formulas is a critical skill for anyone dealing with data analysis, reporting, or financial modeling. These formulas can significantly improve productivity and streamline tasks. By learning and utilizing various formulas such as XLOOKUP, FILTER, LAMBDA, and more, you will be well-equipped to tackle any data-related challenges in Excel.
This comprehensive guide covers some of the most essential and advanced formulas used in Excel today, ensuring that you have the tools necessary to manipulate, analyze, and present data efficiently. From simple arithmetic calculations to complex array operations, Excel formulas provide powerful capabilities that can enhance any workflow. Keep this guide handy as you work through your Excel tasks to make the most of what this powerful software has to offer.
