Top Excel Formulas

Technology has drastically changed the face of work using speed and efficiency in completing tasks. Anyone with knowledge of digital tools has streamlined workflows while maximizing productivity. Microsoft Excel undoubtedly remains the strongest data management tool widely used for analysis, calculations, organizing large datasets, or creating specific reports.

MS Excel is indeed a great spectacle that contains a plethora of built-in formulas and functions that help in automating complex tasks accurately and easily. Anyone willing to be quick in data handling must master some basic Excel formulas.

This guide introduces you to the most commonly used formulas and functions that can be used to simplify your daily activities in Excel. Here’s what we will be covering below:

• What are Excel formulas?
• How to Use Excel Formulas And Functions
• Most Used Excel Formulas And Functions
• Difference Between Formulas And Functions In Excel

What are Excel formulas?

Microsoft Excel has turned out to be one of the very few spreadsheet tools widely used in industries. From financial modelling to time management, invoice creation to inventory tracking, it executes complex data operations with surgical precision. Excel uses built-in formulas and functions that are efficient in handling vast and sensitive calculations.

Excel formulas are mathematical expressions that have been developed to perform calculations automatically. One of the major strengths of the formulas is their dynamic nature: if the source data is modified in any way, the results will automatically be updated, thus eliminating the need to manually recalculate. This guarantees accuracy, efficiency, and the ability to process data in real-time.

How to Use Excel Formulas and Functions

To understand how to use Excel formulas and functions, we have to understand the syntax of it first.

1. Structure of a Function in Excel:

Here is how you can use functions in Excel:

• Always start with an ‘=’ sign.
• Give the name of the function (e.g., SUM, IF, IFS)
• Give open parenthesis.
• Provide all the arguments required by the function.
• Give the closing parenthesis.
• Press Enter to execute.

2. Structure of Formulas in Excel:

Here is how you can use formulas in Excel:

• Always start with an ‘=’ sign.
• Provide with a customized mathematical expression that you want to calculate.
• Press Enter to execute

Most Used Excel Formulas And Functions

Here is an Excel formulas list representing the most used MS Excel formulas and functions that everyone should know.

1. PERCENTAGE Formula in Excel

The calculation of percentages is not directly supported by Excel’s built-in functions, yet it is really simple to derive a percentage by dividing the numbers concerned, and then format the answer using the Percentage option provided within the Number group of the Home tab.

For example, there are columns containing marks for students in math and science. You want to find the percentage of marks scored by each student.

To find the percentage,

• Start with an “=” sign
• Add the cells C3 and D3.
• Assuming total marks are 100 for each subject, divide by 200.
• Click on the percentage symbol in the number group of the home tab.

2. VLOOKUP formula in Excel

VLOOKUP is one of the most popular and frequently used functions in Excel for analyzing data within a worksheet, selected area, or even the whole spreadsheet. Most importantly, it helps find particular values by means of a vertical lookup in the leftmost column of a given dataset and returning data from a predefined column.

Here is the syntax:

=VLOOKUP(lookup_value, table, row_index, range_lookup) to find a value.

For example, you have huge data containing names, marks of students in math and science, and percentages. You are required to lookup the percentage of a student named “Sayan.” You would use VLOOKUP in this case.

Here are the steps to follow:

• Start with an “=” sign
• Give the name of the function VLOOKUP
• Provide the lookup_value, basically what you are looking for (in this case, “Sayan”)
• Provide the table where you want to look for (in this case, B2:E6)
• Provide the row_index (starts from 1), which row data you want to return (here, percentage column row_index is 4)
• Press Enter to Execute

3. SUM Formula in Excel

The SUM function is one of the commonly used functions in Microsoft Excel. Its purpose is to quickly add values from numbers or find their sum in selected cells. Instead of individually summing up multiple cells, use the SUM function and enter the cell range that needs to be calculated.

Here is the syntax:

=SUM(Values)

For example, assume there are columns containing marks for students in math and science. You want to find the total marks scored by each student.

Here are the steps.

• Start with an “=” sign.
• Give the name of the function SUM.
• Provide the parameters, i.e., the marks you want to add.
• Press Enter to execute.

4. SUBTRACTION Formula in Excel

You can actually subtract two numbers in Excel by using the SUM function, only that the second value you are subtracting has to be preceded by a minus (-) sign. This is not a real subtraction function because Excel simply processes the negative value in order to perform the calculation using the SUM formula.

For example, we need to find the difference in marks scored in math by two students.

Here are the steps given:

• Start with an “=” sign.
• Give the name of the function SUM.
• Provide the parameters, i.e., the marks you want to subtract, but make sure the second parameter is prefixed with a ‘-’ sign.
• Press Enter to execute.

5. IF Formula in Excel

The IF function in Microsoft Excel returns one value when a specified condition is met and returns a different value when the condition is not met. This results in a logical decision-making approach within the spreadsheet.

Here is the syntax:

=IF(logical_test, value_if_true, [value_if_false])

For example, columns containing percentages of marks scored by students in the class. You are required to find out if the student has passed or failed the examination. Let’s say a student scoring more than 40% passes and the rest fails.

Here are the steps given:

• Start with an “=” sign.
• Give the name of the function IF.
• Provide the condition based on which student passes or fails.
• Provide the output to show if the condition is true.
• Provide the output to show if the condition is false.
• Press Enter to execute.

6. AVERAGE Formula in Excel

Microsoft Excel contains the AVERAGE function, which helps calculate the arithmetic mean for selected cells. The AVERAGE function sums up the values located within the specified range and the total is then divided by the number of cells, thus making it useful for the analysis of numerical data.

Here is the syntax:

=AVERAGE(number1, [number2], ...)

For example, a table containing the marks of students with their respective names. You are required to find out the average marks scored by the class.

Here are the steps given:

• Start with an “=” sign.
• Give the name of the function AVERAGE.
• Provide the range for which the average needs to be calculated.
• Press Enter to execute.

7. COUNTIF Function in Excel

The COUNTIF function in Microsoft Excel counts the cells in a defined range that meets a condition within those cells. If there are values that do not match, they will be ignored. Thus, one can filter out data, know how many times occurrences took place, and report them.

Here is the syntax:

=COUNTIF(range, criteria)

For example, a table containing the marks of students with their respective names. You are required to find out the count of students with marks > 70.

Here are the steps given:

• Start with an “=” sign.
• Give the name of the function COUNTIF.
• Provide the entire range from the marks column
• Provide the condition that “marks > 70”
• Press Enter to execute.

8. Excel RANK Function

The RANK function of Microsoft Excel is employed to assign a number to a given value depending on how high or low that value is when compared to other numbers in a specified column.

Here is the syntax:

=RANK(number,ref,[order])

For example, a table containing the marks of students with their respective names. You are required to find out the rank of students in the class.

Here are the steps given:

• Start with an “=” sign.
• Give the name of the function RANK.
• Provide the cell address for which the rank is to be calculated.
• Provide the columns where the marks will be compared and the rank will be declared.
• Provide in which order you want the rank to be 0 for descending and 1 for ascending.
• Press Enter to execute.

9. INDEX Function Excel

In general, the INDEX function has a valuable use for sophisticated lookup functions within Microsoft Excel. More specifically, it fetches a value for a particular cell within a selected range, using its row and column number as references.

Here is the syntax:

=INDEX(array, row_num, [column_num])

For example, a table containing the marks of students with their respective names. You are required to find out the marks of Ankit.

Here are the steps given:

• Start with an “=” sign.
• Give the name of the function INDEX.
• Provide the row number where the name is present
• Provide the column index, i.e., the value that you want.
• Press Enter to execute.

10. LOOKUP Function Excel

LOOKUP provides access to a specific value from a row or column to use as a reference point from which to return a corresponding value from another row or column in Microsoft Excel.

Here is the syntax:

=LOOKUP(lookup_value, lookup_vector, [result_vector])

For example, a table containing the marks of students with their respective names. You are required to find out the marks of Ankit.

Here are the steps given:

• Start with an “=” sign.
• Give the name of the function LOOKUP
• Provide the key that you want to search for.
• Provide the range where the key should be searched
• Provide the range from which the value should be returned.
• Press Enter to execute.

11. HLOOKUP in Excel

The HLOOKUP function is used in Microsoft Excel to find a designated value within the topmost row of a table and return a value from the same column of a user-specified row.

Here is the syntax:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

For example, a table containing the names of the employees and the same column containing their basic information. You are given a search term, let’s say a name, and you are required to find the gender and employee ID of that employee.

Here are the steps given:

• Start with an “=” sign.
• Give the name of the function HLOOKUP
• Provide the key that you want to search for.
• Provide the range where the key should be searched
• Provide the range from which the value should be returned.
• Press Enter to execute.

12. MATCH Function Excel

The match function is used to retrieve the positional index of the value based on the search key. It is often clubbed with the INDEX function for better and easier lookup activities.

Here is the syntax:

=MATCH(lookup_value, lookup_array, [match_type])

For example, a table containing the marks of students with their respective names. You are required to find out the positional index of Sayan which will be further used in the INDEX function.

Here are the steps given:

• Start with an “=” sign.
• Give the name of the function MATCH
• Provide the key that you want to search for.
• Provide the range where the key should be searched
• Provide the match type (0 for exact match, 1 for closest value less than, -1 for smallest value greater than). Here 1 is the default value.
• Press Enter to execute.

13. SUMIF Function in Excel

This function in Microsoft Excel is used to add numbers or to find out the total value of selected cells based on a certain condition.

Here is the syntax:

=SUMIF(range, criteria, [sum_range])

For example, there are columns containing percentages scored by students. You want to find the total marks scored by the class, but with students who have passed, i.e., marks > 40.

Here are the steps.

• Start with an “=” sign.
• Give the name of the function SUMIF.
• Provide the parameters, i.e., the marks you want to add.
• Provide the condition “> 40.”
• Press Enter to execute.

Master Business Analytics & Elevate Your Career!

Gain in-demand analytics skills, hands-on experience, and industry-recognized certification. Enroll today!

Explore Program

14. ROUND Function in Excel

The round function will round off the decimal number to the required decimal place.

Here is the syntax:

=ROUND(number,num_digits)

For example, assume you have a dataset containing percentages of marks and the name of the students. The percentage contains 5 decimal places. You are required to round off the percentages to 2 decimal places.

Here are the steps.

• Start with an “=” sign.
• Give the name of the function ROUND.
• Provide the number to be rounded off.
• Provide the number of decimal places required.
• Press Enter to execute.

15. CEILING Function in Excel

The CEILING function in Excel is used to return the rounded-off number to the nearest largest multiple of a value provided in the parameter.

Here is the syntax:

=CEILING(number, significance)

• number – Value to be rounded up.
• significance – The factor to be used to round off data.

For example, Consider a dataset having the prices of products, and the prices are inconsistent amounts, far from being round about ten. Nevertheless, you must set the prices to a unit of ten to standardize them.

Here are the steps

• Start with an “=” sign.
• Name the function CEILING.
• Specify the number to be rounded up (243 in this case).
• Specify the significance (10 in this case)
• Press Enter to execute.

This formula rounds 243 to the nearest multiple of 10 which corresponds to 250.

16. FLOOR Function in Excel

The FLOOR function is just the opposite of the ceiling function. It is used to return the rounded number to the nearest smallest multiple of the value provided in the parameter.

Here is the syntax:

=FLOOR(number, significance)

• number – Value to be rounded up.
• significance – The factor to be used to round off data.

For example, you already have a dataset containing prices at irregular amounts for various products. It becomes important to standardize the pricing by rounding down to the nearest 10:

Here are the steps

• Start with an “=” sign.
• Name the function FLOOR.
• Specify the number to be rounded down (243 in this case).
• Specify the significance (10 in this case)
• Press Enter to execute.

This formula rounds 243 down to the nearest multiple of 10 which corresponds to 250.

17. LEN Function in Excel

The LEN function in Excel calculates the total number of characters in a text; space is included.

Here is the syntax:

=LEN(text)

• text – The string whose length (number of characters) you want to count.

For example, you have a data source with customers’ names and you are interested in finding out how many characters are in each name, counting spaces:

Here are the steps

• Start with an “=” sign.
• Name the function LEN.
• Specify the cell reference or text string for character counting (A2 in this case).
• Press Enter to execute.

Output for “text string” John Doe will be 8.

18. CONCAT in Excel

The CONCAT function is used to combine multiple texts together without a delimiter. If you intend to use a delimiter, you can use the join function.

Here is the syntax:

=CONCAT(text1, [text2],…)

For example, you have a dataset containing the first names and last names of students in a class. You are required to join the first and last names and create a column named names.

Here are the steps.

• Start with an “=” sign.
• Give the name of the function CONCAT.
• Specify the first text to join.
• Specify the second text to join.
• Press Enter to execute.

NOTE: There is no provision to add a delimiter while using the CONCAT function.

19. UPPER Function in Excel

All lowercase letters are converted to uppercase within a given text string by using the UPPER function in Excel.

Here is the syntax:

=UPPER(text)

• text – The string or cell reference which has to be converted to uppercase.

For example, you were given a dataset containing names of customers in mixed case, and you want to flip them to uppercase:

Here are the steps

• Start with an “=” sign.
• Name the function UPPER.
• Specify the cell reference or text string to convert (A2 in this case).
• Press Enter to execute.

The output for “original text” John Doe is JOHN DOE.

20. LOWER Formula in Excel

All uppercase letters are converted to lowercase within a given text string by using the UPPER function in Excel.

Here is the syntax:

=LOWER(text)

• text – The string or cell reference which has to be converted to uppercase.

For example, you were given a dataset containing names of customers in mixed case, and you want to flip them to lowercase:

Here are the steps

• Start with an “=” sign.
• Name the function LOWER.
• Specify the cell reference or text string to convert (A2 in this case).
• Press Enter to execute.

The output for “original text” John Doe is john doe.

21. PROPER Formula in Excel

The PROPER function in Excel serves the goal of converting your words into the proper case, meaning it converts the first letter of each word into uppercase while keeping the rest in lowercase.

Here is the syntax:

=PROPER(text)

• text – The string or cell reference which has to be converted to proper case.

For example, you have a dataset with customer names in all uppercase or lowercase format, and you need to format it in the proper case.

Here are the steps

• Start with an “=” sign.
• Name the function PROPER.
• Specify the cell reference or text string to convert (A4 in this case).
• Press Enter to execute.

The output for “original text” excel formulas is now Excel Formulas.

22. NOW Function in Excel

The NOW function in Excel returns the date and time, adaptable to change each and every moment the worksheet is recalculated or reopened.

Here is the syntax:

=NOW()

No arguments needed.

For example, you wanted to display the current date and time in a cell so that you can track easily using them.

Here are the steps

• Start with an “=” sign.
• Name the function NOW.
• Add parenthesis.
• Press Enter to execute.

The output was 13-02-2025 14:43 which captured the current time.

23. TIME Formula in Excel

The TIME function in Excel combines hours, minutes, and seconds to create a time value.

Here is the syntax:

=TIME(hour, minute, second)

• hour – Hour value (0 to 23)
• minute – Minute value (0 to 59)
• second – Second value (0 to 59)

For example, you have to create a time entry of 9:30:45 AM in Excel.

Here are the steps:

• Start with an “=” sign.
• Name the function TIME.
• Specify the values for hour, minute and second.
• Press Enter to execute.

The output is 9:30:45 AM

24. TODAY Function in Excel

The TODAY function returns only the current date without any aspect of time. It may automatically track due dates, automate the manual job of date tracking, generate any kind of scheduling, and create any report as is required.

Here is the syntax:

=TODAY()

Doesn’t take any arguments.

For example, you are willing to show the current date to track your work in one cell:

Here are the steps:

• Start with an “=” sign.
• Name the function TODAY.
• Add parentheses().
• Press Enter to execute.

The output was 13-02-2025 which captured the current date.

Now if we want to granularize the date, we can use the DAY, MONTH and YEAR functions as shown below:

25. TRIM Function in Excel

The TRIM function available in Excel plays the part of removing all the unnecessary or additional spaced areas in a text string, while retaining only single spaces between the words.

Here is the syntax:

=TRIM(text)

• text – The text string or cell reference from which the extra spaces must be removed.

For example, we own a dataset where names have several irrelevant spaces that need to be removed:

Here are the steps

• Start with an “=” sign.
• Name the function TRIM.
• Specify the cell reference or text string to clean (A2 in this case).
• Press Enter to execute.

Upon using the formula, the output is returned as “John Doe” removing the extra spaces in “ John Doe “.

26. COUNTA Function in Excel

The COUNTA function in Excel is used to count the number of cells in the selected range, which is not empty.

Here is the syntax:

=COUNTA(value1, [value2], ...)

• value1, value2, … – The range or values to be counted.

For example, you already have a dataset with names and scores; however, in some cases, cells are not filled. The task at hand is to figure out – how many filled cells there are.

Here are the steps.

• Start with an “=” sign.
• Name the function COUNTA.
• Provide the range of cells that require counting.
• Press Enter to execute.

Since one cell is empty, the output is 5.

27. COUNT Formula in Excel

The count function in Excel is used to count the number of cells in the selected range, which is not empty and also contains a numeric value.

Here is the syntax:

=COUNT(value1, [value2], ...)

For example, you have a dataset containing the first names and last names of students in a class along with their marks in English. You are required to count the number of students who attended the exam.

Here are the steps.

• Start with an “=” sign.
• Give the name of the function COUNT.
• Provide the range of cells.
• Press Enter to execute.

28. MIN and MAX in Excel

The MIN function is used to find the smallest number from the selected range of cells.

Here is the syntax:

=MAX(number1, [number2], ...)

For example, you have a dataset containing the first names and last names of students in a class along with their marks in English. You are required to find the topper (student with maximum marks) and lowest scorer (student with minimum marks) in the class.

Here are the steps.

To find the student with maximum marks

• Start with an “=” sign.
• Give the name of the function MAX.
• Provide the range of cells.
• Press Enter to execute.

To find the student with minimum marks

• Start with an “=” sign.
• Give the name of the function MIN.
• Provide the range of cells.
• Press Enter to execute.

29. SMALL Function in Excel

The SMALL function is used to return the k-th smallest value in a dataset. It helps to locate the lowest, second lowest, the third lowest and so on, in a range.

Here is the syntax:

=SMALL(array, k)

• array: The range of numeric values
• k: The position in the array/rank of the element(from the smallest)

For example, if you have to find the third lowest score from the list found in cells A1:A10:

Here are the steps

• Start with an “=” sign.
• Name the function SMALL
• Provide the range of numbers A1:A10
• Provide the value of k (3 for the third smallest)
• Press Enter to execute

30. LARGE Function in Excel

The LARGE function is nothing but the opposite of SMALL. This returns the k-th-largest value from a dataset, which can be used for ranking purposes and to determine top values in the dataset.

Here is the syntax:

=LARGE(array, k)

• array: The range of numeric values
• k: The position in the array/rank of the element(from the smallest)

For example, if you want to find the 2nd highest score in a dataset that is located in A1:A10:

Here are the steps

• Start with an “=” sign
• Name the function LARGE
• Provide the range of numbers A1:A10
• Provide the value of k (2 for the second largest)
• Press Enter to execute

31. REPLACE Function in Excel

Usually, the REPLACE function replaces the given string with another string and specifies the position and length at which this replacement should occur.

Here is the syntax:

=REPLACE(old_text, start_num, num_chars, new_text)

• old_text: The original text.
• start_num: The position from where the replacement must start.
• num_chars: Number of characters to be replaced.
• new_text: The text that shall replace the original chunk.

For example, in case cell A1 reads “Excel2024”, and you want to replace “2024” with “2025”, use:

Here are the steps

• Start with an “=” sign.
• Name the function REPLACE.
• Provide the original text cell (A1 in this case).
• Specify the starting position of the replacement (6 in this case).
• Mention the number of characters to replace (4 in this case ).
• Enter new text (“2025”).
• Press Enter to execute.

32. SUBSTITUTE Function in Excel

The SUBSTITUTE function works to substitute specific text in a certain string, engaging the idea of replacing selected occurrences of a substring.

Here is the syntax:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

• text: The full text.
• old_text: Text to replace.
• new_text: The new text.
• instance_num(Optional): the occurrence to be replaced.

For example, if A1 has “apple, banana, apple”, and you feel like replacing “apple” with “grape” instead, use:

Here are the steps

• Start with an “=” sign.
• Name the function SUBSTITUTE.
• Provide the original text cell (A1 in this case ).
• Specify the text to be replaced (“apple” in this case).
• Enter new text (“grape” in this case ).
• Press Enter to execute.

33. LEFT Function in Excel

The LEFT function extracts a certain number of characters from the left side of a string (from the beginning).

Here is the syntax:

=LEFT(text, num_chars)

• text: The full text.
• num_chars: Number of characters to be extracted.

For example, if A1 has “ExcelFormula”, and you would like to use the first 5 characters, use:

Here are the steps

• Start with an “=” sign.
• Name the function LEFT.
• Provide the original text cell (A1 in this case ).
• Specify the number of characters to be extracted (“5” in this case).
• Press Enter to execute.

34. RIGHT Function in Excel

The RIGHT function extracts a certain number of characters from the right side of a string (from the end).

Here is the syntax:

=RIGHT(text, num_chars)

• text: The full text.
• num_chars: Number of characters to be extracted.

For example, if A1 has “ExcelFormula”, and you would like to use the last 7 characters, use:

Here are the steps

• Start with an “=” sign.
• Name the function RIGHT.
• Provide the original text cell (A1 in this case ).
• Specify the number of characters to be extracted (“7” in this case).
• Press Enter to execute.

34. MID Function in Excel

The MID function extracts a substring from a text string and begins parsing the text string from the position you specify.

Here is the syntax:

=MID(text, start_num, num_chars)

• text: The full text.
• start_num: Starting position of the extraction.
• num_chars: Number of characters to be extracted.

For example, if “ExcelFormula” is stored in A1, and you want “Form” extracted from it, then use:

Here are the steps

• Start with an “=” sign.
• Name the function MID.
• Provide the original text cell (A1 in this case ).
• Specify the starting position (let’s say 6)
• Specify the number of characters to be extracted (let’s say 4).
• Press Enter to execute.

35. SUBTOTAL Function in Excel

The SUBTOTAL function in Excel can be used to perform any of the calculations such as SUM, AVERAGE, COUNT, MAX, MIN, etc., on a dataset while allowing inclusion or exclusion of filtered values.

Here is the syntax:

=SUBTOTAL(function_num, range)

• function_num: The number which represents the operation (eg, 9 for SUM, 1 for AVERAGE)
• range: The dataset where the function will be implemented.

For example, to find the sum of the values in A1:A10 for visible (non-filtered) rows, use:

Here are the steps

• Start with an “=” sign.
• Name the function SUBTOTAL.
• Specify the function number (let’s say 9 for SUM)
• Specify the range of numbers (A1:A10 in this case).
• Press Enter to execute.

36. HOUR Function in Excel

The HOUR function returns the hour from a specific time value.

Here is the syntax:

=HOUR(serial_number)

• serial_number: The time value from which the hour is to be extracted.

For example, if A1 stores a time of 14:45:25 and you wish to extract the hour:

Here are the steps

• Start with an “=” sign.
• Name the function HOUR.
• Specify the time cell (A1 in this case)
• Press Enter to execute.

Upon pressing enter it should return “14”.

37. MINUTE Function in Excel

The MINUTE function returns the minute value from a time reference.

Here is the syntax:

=MINUTE(serial_number)

• serial_number: The time value from which minute is to be extracted.

For example, if A1 stores a time of 14:45:30 and you want to extract the minutes:

Here are the steps

• Start with an “=” sign.
• Name the function MINUTE.
• Specify the time cell (A1 in this case)
• Press Enter to execute.

Upon pressing enter it should return “45”.

38. SECOND Function in Excel

The SECOND function returns the seconds value from a time reference.

Here is the syntax:

=SECOND(serial_number)

• serial_number: The time value from which seconds is to be extracted.

For example, if A1 stores a time of 14:45:30 and you want to extract the seconds:

Here are the steps

• Start with an “=” sign.
• Name the function SECOND.
• Specify the time cell (A1 in this case)
• Press Enter to execute.

Upon pressing enter it should return “30”.

39. DATEDIF Function in Excel

The DATEDIF function happens to calculate the time difference between two dates in days, months and years.

Here is the syntax:

=DATEDIF(start_date, end_date, unit)

• start_date: The beginning date.
• end_date: The ending date.
• unit: The unit of time (Y for years, M for months, D for days)

For example, If A1 contains the date 01/01/2020 and B1 contains the date 01/01/2025, and you then want to calculate the difference in years:

Here are the steps

• Start with an “=” sign.
• Name the function DATEDIF.
• Specify the start date (A1 in this case)
• Specify the end date (B1 in this case).
• Specify the unit (“Y’ for years)
• Press Enter to execute.

Upon pressing enter it should return “5”.

40. IF-ELSE Condition in Excel

The IF function assesses if certain criteria are met and returns one value if TRUE and another if FALSE.

Here is the syntax:

=IF(condition, value_if_true, value_if_false)

• condition: The logical test to evaluate.
• value_if_true: The output if the given condition is satisfying.
• value_if_false: The output if the given condition is false.

For example, A1 has a score value and you want to evaluate if it is higher than 50.

Here are the steps

• Start with an “=” sign.
• Name the function IF.
• Specify the condition (A1>50 in this case)
• Specify the value if true (“Pass” in this case).
• Specify the value if false (“Fail” in this case)
• Press Enter to execute.

Upon pressing enter it returns “Pass” and “Fail” based on the score.

41. OFFSET Function in Excel

The OFFSET function returns a reference to a certain cell or range with an initial reference.

Here is the syntax:

=OFFSET(reference, rows, cols, [height], [width])

• reference: The starting cell.
• rows: Number of rows to move.
• cols: Number of columns to move.
• height (optional): Indicates the number of rows in a range.
• width (optional): Indicates the number of rows in a range.

For example, In cell A1, if the variable is equal to 100, and you want a value two rows down, use:

Here are the steps:

• Start with an “=” sign.
• Name the function OFFSET.
• Specify the reference cell (A1 in this case).
• Specify how many rows to move (2 in this case).
• Specify how many columns to move (0 in this case).
• Press Enter to execute.

The value contained in the cell A3 will be displayed as soon as you press enter.

42. ABS Function in Excel

The ABS function takes a number as an input and returns its absolute value without its negative sign.

Here is the syntax:

=ABS(number)

• number: The number whose absolute value you’re looking for.

For example, if A1 contains the value -50, to return the value 50 use.

Here are the steps:

• Start with an “=” sign.
• Name the function ABS.
• Press Enter to execute.

Upon pressing enter, it will return 50.

43. AND Function in Excel

The AND function checks two or more conditions in a formula and will return TRUE when all conditions are TRUE. If any condition is FALSE, the function returns FALSE.

Here is the syntax:

=AND(condition1, condition2, …)

• condition1, condition2, … : Logical conditions that need evaluation.

For example, perhaps if you want to check if student scored more than 50 in both A1 and B1:

Here are the steps:

• Start with an “=” sign.
• Name the function AND.
• Specify condition 1 (A1>50).
• Specify condition 2 (B1>50).
• Press Enter to execute.

Upon pressing enter, the function will return ‘TRUE’ if two scores surpass 50, otherwise it will return ‘FALSE’.

44. OR Function in Excel

The OR function checks a series of conditions and returns the result TRUE automatically if at least one of them is satisfied; otherwise, it returns FALSE.

Here is the syntax

• condition1, condition2, … : Logical conditions that need evaluation.

For example, in order to check if a student has scored more than 50 in either A1 or B1:

Here are the steps:

• Start with an “=” sign.
• Name the function OR.
• Specify condition 1 (A1>50).
• Specify condition 2 (B1>50).
• Press Enter to execute.

Upon pressing enter, it returns TRUE if at least one condition is/conditions are TRUE.

45. NOT Function in Excel

The NOT function provides a way to reverse the outcome of a particular condition.

Here is the syntax:

=NOT(condition)

• condition: Logical condition that needs evaluation.

For example, if you want to find out if the number in cell A1 is not greater than 50:

Here are the steps:

• Start with an “=” sign.
• Name the function NOT.
• Specify condition (A1>50).
• Press Enter to execute.

Upon pressing enter, if cell A1 is 50 or below, TRUE is returned; otherwise FALSE.

46. IFS Function in Excel

The “IFS” function is useful for testing multiple conditions at once and returning the result for the initial condition that is “TRUE”.

Here is the syntax:

=IFS(condition1, value1, condition2, value2, …)

• condition1, condition2, … : Logical conditions that need evaluation.
• value1, value2, … : Values returned if the condition is fulfilled.

For example, if grades are to be assigned based on the marks obtained in A1:

Here are the steps:

• Start with an “=” sign.
• Name the function IFS.
• Specify condition 1 (A1>=90) and value (“A”)
• Specify condition 2 (A1>=80) and value (“B”)
• Specify condition 3 (A1>=70) and value (“C”)
• Press Enter to execute.

Upon pressing the Enter key, one might see “A,” “B,” “C,” or “F” depending upon the grade received.

47. FILTER Function in Excel

The FILTER Function in excel returns an array of values satisfying specific criteria.

Here is the syntax:

=FILTER(array, include, [if_empty])

• array: The range of numeric values.
• include: Condition to be applied.
• if_empty (optional): The value which will be returned if a match is not found.

For example, If you want to filter sales above 5000 appearing in A2:A10, with corresponding names in B2:B10:

Here are the steps:

• Start with an “=” sign.
• Name the function FILTER.
• Specify array (B2:B10).
• Specify condition (A2:A10>5000).
• Press Enter to execute.

Upon pressing enter, it will give back the names of all those having sales figures above 5000.

48. INDIRECT Function in Excel

When an INDIRECT function is used, it is with the aim of returning a reference that is defined by a text string.

Here is the syntax:

=INDIRECT(ref_text, [a1])

• Ref_text: Reference as a string.
• A1 (optional): If TRUE (default), it uses A1-style referencing.

For example, if A1 contains the term B2, and you want to obtain the value in B2, then:

Here are the steps:

• Start with an “=” sign.
• Name the function INDIRECT.
• Specify reference (A1).
• Press Enter to execute.

Upon pressing enter, the value in B2 will be returned.

49. RANDBETWEEN Function in Excel

RANDBETWEEN is a function that generates a random number, which is constrained by the upper and lower limits.

Here is the syntax

=RANDBETWEEN(bottom, top)

• bottom: The smallest value
• top: The largest value

For example, if you are looking for a random number between 1 and 100.

Here are the steps:

• Start with an “=” sign.
• Name the function RANDBETWEEN.
• Specify bottom value (1 in this case).
• Specify top value (100 in this case).
• Press Enter to execute.

Upon pressing enter, it will return a value between 1 and 100.

50. VALUE Function in Excel

The VALUE function helps to convert the text appearing in a number format into a numeric value. This is applicable especially for numbers which Excel sometimes stores as text, so that calculations are not carried out on it.

Here is the syntax:

=VALUE(text)

• text: The text string that represents a number

For example, If the text ‘100’ is stored in A1 and you want to convert it into a numeric value:

Here are the steps:

• Start with an “=” sign.
• Name the function VALUE.
• Specify cell reference (A1 in this case).
• Press Enter to execute.

Upon pressing enter, 100 will come up as a number, enabling you to perform calculations.

51. WEEKDAY Function in Excel

The WEEKDAY function returns an integer value representing the day of the week for a specific date (1 for Sunday, 2 for Monday, 3 for Tuesday, and so on).

Here is the syntax:

=WEEKDAY(serial_number, [return_type])

● Serial_number: The date you’re looking to evaluate.
● return_type: Defines the number for each day (1 for Sunday, 2 for Monday, etc.)

For example, if A1 is coded as 2025-02-07 (February 7, 2025) and you would like to find out what day of the week this is:

Here are the steps:

• Start with an “=” sign.
• Name the function WEEKDAY.
• Specify the date (A1 in this case).
• Press Enter to execute.

Upon pressing enter, it will display the weekday, such as 6 for Friday (given Sunday = 1).

52. WEEKNUM Function in Excel

The function WEEKNUM is used to identify the number of the week on the basis of the given date from a date of reference.

Here is the syntax:

=WEEKNUM(serial_number, [return_type])

● Serial_number: The date you’re looking to evaluate.
● return_type: Defines the first day of the week (Sunday being a default)

For example, if A1 holds the date 2025-02-07 and you would like to calculate the week number:

Here are the steps:

• Start with an “=” sign.
• Name the function WEEKNUM.
• Specify the date (A1 in this case).
• Press Enter to execute.

Upon pressing enter, the output will read ‘6’ informing you of the week number which denotes that the date 2025-02-07 falls in the 6th week of the year.

53. TODAY Function in Excel

The TODAY function updates the date, showing the current one that would be calculated every time the worksheet is opened.

Here is the syntax

=TODAY()

No arguments present.

For example, if you are looking to display today’s date in cell B1:

Here are the steps:

• Start with an “=” sign.
• Name the function TODAY.
• Add parentheses ().
• Press Enter to execute.

Upon pressing enter, the current date will be returned (e.g., if today’s date is February 7, 2025 then it will return 2025-02-07)

54. TEXT Function in Excel

In a given format, the function TEXT converts numbers and dates into text.

Here is the syntax:

=TEXT(value, format_text)

• value: The number or date which needs to be formatted.
• format_text: The desired format in quotes

For example, if A1 has 45000.678 and the value is to be formatted in currency:

Here are the steps:

• Start with an “=” sign.
• Name the function TEXT.
• Specify the value (A1 in this case).
• Specify the format (“$#,##0.00).
• Press Enter to execute.

Upon pressing enter, it will return $45,000.68.

55. UNIQUE Function in Excel

UNIQUE is a function that extracts unique values from a range and removes duplicates dynamically.

Here is the syntax:

=UNIQUE(array, [by_col], [exactly_once])

• array: The range from where unique values will be extracted from.
• [by_col]: This function returns TRUE for unique values obtained through columns and to FALSE for similar ones under rows.
• [exactly_once]: FALSE will return all values while TRUE only displays unique results.

For example, if cells A1 to A10 both contain duplicate names and if you wanted to list all the unique names:

Here are the steps:

• Start with an “=” sign.
• Name the function UNIQUE.
• Specify the range (A1:A10 in this case).
• Press Enter to execute.

Upon pressing enter, a list of names selected dynamically will be returned.

56. SORT Function in Excel

The SORT function dynamically sequences values in ascending or descending order.

Here is the syntax:

=SORT(array, [sort_index], [sort_order], [by_col])

• array: The range of values to be sorted.
• [sort_index]: Column or row number which will be used for sorting (default=1).
• [sort_order]: 1 for ascending and -1 for descending order.
• [by_col]: Columns sort TRUE, rows sort FALSE.

For example, if you have a range of numbers in A1:A10 that you wish to put in ascending order.

Here are the steps:

• Start with an “=” sign.
• Name the function SORT.
• Specify the range (A1:A10 in this case).
• Press Enter to execute.

Upon pressing enter, the numbers will be shown in ascending order dynamically.

57. SORTBY Function in Excel

The SORTBY function dynamically sorts data based on another column.

Here is the syntax:

=SORTBY(array, by_array, [sort_order])

• array: The range of values to be sorted.
• by_array: Column or row number which will be used for sorting.
• [sort_order]: 1 for ascending and -1 for descending order.

For example, if A1:A10 contains students’ names and B1:B10 contains test scores and if you want to arrange the names according to the grades:

Here are the steps:

• Start with an “=” sign.
• Name the function SORTBY.
• Specify the range (A1:A10 in this case).
• Specify the column to sort by (B1:B10 in this case).
• Press Enter to execute.

Upon pressing enter, you could see a list of students and their scores in descending order.

58. XLOOKUP Function in Excel

The function XLOOKUP is used for value lookup in different specified ranges. It has the potential to replace VLOOKUP and HLOOKUP together.

Here is the syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value: The value to search for.
• lookup_array: The array which contains the search value.
• return_array: The array from which the value is returned.
• [if_not_found]: Value to be returned if lookup value is not found.
• [match_mode]: 0 will be the exact match (default), -1 calls for the exact match or the next lower one, 1 calls for the exact match or the next higher one.
• [search_mode]: 1 for top-to-bottom search, and -1 for a bottom-up one.

For example, if “Laptop” (the product name) is stored within A1:A10 and the corresponding price data is stored in B1:B10, and you are looking for the price of the laptop:

Here are the steps:

• Start with an “=” sign.
• Name the function XLOOKUP.
• Specify the lookup value (“Laptop” in this case).
• Specify the lookup range (A1:A10 in this case).
• Specify the return range (B1:B10 in this case).
• Press Enter to execute.

Upon pressing enter, you’ll get the price of the laptop or “Not Found” if it’s absent.

59. TEXTJOIN Function in Excel

In Excel, the TEXTJOIN function is useful for combining all sorts of text values with a delimiter between them.

Here is the syntax:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

• delimiter: The separators between values are given, for example, “, ” signifies a comma plus one blank space.
• ignore_empty: TRUE will ignore empty cells while FALSE will include them.
• text1, text2, …: Text strings or range to be combined.

For example, A1:A6 includes names and one wants to join them using a comma:

Here are the steps:

• Start with an “=” sign.
• Name the function TEXTJOIN.
• Specify the delimiter (“, ” in this case).
• Specify if you want to ignore empty cells (TRUE in this case).
• Specify the return range (A1:A6 in this case).
• Press Enter to execute.

Upon pressing enter, it will return 5 names which are in A1:A6.

60. CHOOSE Function in Excel

CHOOSE finds a value in a list on the basis of an index number.

Here is the syntax:

=CHOOSE(index_num, value1, value2, …)

• index_num: The position of the value to be returned.
• value1, value2, …: A list of values to pick from.

For example, if you need to extract the word “March” as the third month from the list:

Here are the steps:

• Start with an “=” sign.
• Name the function CHOOSE.
• Specify the index number (3).
• Specify the list of values (“January”, “February”, “March” in this case).
• Press Enter to execute.

Upon pressing enter, it must return “March”.

61. NOW Function in Excel

NOW function returns the present day and time that constantly updates.

Here is the syntax:

=NOW()

For example, if one wants to display the current date and time in B1, then:

Here are the steps:

• Start with an “=” sign.
• Name the function NOW.
• Add parentheses ().
• Press Enter to execute.

Upon pressing enter, it returns the current date and time (eg. 2025-02-12 12:42)

Difference Between Formulas And Functions In Excel

Formula and function are the two major words associated with Microsoft Excel, which are most often used interchangeably but have their own distinct differences from each other in terms of how they operate and how they are used. Here is the list of their key differences:

To learn more on Microsoft Excel, check out this video by Intellipaat:

Conclusion

We trust that this blog has aided you to a fair extent in appreciating the very essential Excel formulas and functions used on a day-to-day basis. Excel is a powerful tool used all over the world in various industries, and it is a must-have skill in any profession. Comparing two columns in Excel is a key technique that helps users quickly analyze data, making Excel even more valuable. Mastering it will be advantageous, especially for those who aspire for managerial and leadership roles.

If you want to have your hands on Excel and master it, consider enrolling in our Advanced Certification in Microsoft Excel training program to get exposure to certification-oriented courses that can endorse your value.