Excel is one of the most powerful and widely used tools for data analysis, reporting, and business decision-making. Whether you are a student, job seeker, freelancer, or working professional, mastering Excel formulas can dramatically improve your productivity.
In this Excel tutorial, you will learn 30 must-know Excel formulas explained in simple and descriptive language, along with sample datasets and example explanations so that anyone can understand them easily.
Sample Dataset Used in This Article
1. SUM
Purpose: The SUM function in Excel is used to add numeric values from selected cells or ranges and return the total. It is one of the most commonly used formulas for calculating totals in sales, expenses, marks, or financial data. The syntax of the SUM function is simple and flexible, allowing users to add individual cells, multiple ranges, or a combination of both. Excel automatically ignores text and empty cells while performing the calculation. The SUM function helps save time and reduces manual calculation errors, making it essential for daily Excel work.
=SUM(C2:C6)
Explanation: This formula calculates the total sales of all employees.
Example Screenshot Description: Column C selected → Formula bar showing =SUM(C2:C6) → Result cell displays total sales.
2. AVERAGE
Purpose: AVERAGE is an Excel function used to calculate the mean value of a group of numbers. It adds all the numeric values in the selected range and then divides the total by the count of numbers. Text, empty cells, and logical values are automatically ignored by the AVERAGE function. This function is commonly used to analyze performance, such as average sales, marks, or expenses. It helps in understanding overall trends rather than individual values. The AVERAGE function is widely used in reports, dashboards, and data analysis.
Formula:
=AVERAGE(C2:C6)
Explanation: Finds the average sales value from the dataset.
3. COUNT
Purpose: COUNT is an Excel function used to count the number of cells that contain numeric values in a selected range. It includes numbers such as integers, decimals, dates, and times, but ignores text, blank cells, and logical values. This function is commonly used to identify how many valid numeric entries exist in a dataset. The basic syntax of COUNT is =COUNT(range). COUNT is especially useful in data analysis, reporting, and validation where only numerical data needs to be evaluated.
Formula:
=COUNT(C2:C6)
Explanation: Counts how many sales entries are numeric.
4. COUNTA
Purpose: COUNTA is an Excel function used to count the number of non-empty cells in a selected range. It counts cells that contain text, numbers, dates, formulas, or symbols. Unlike the COUNT function, COUNTA does not ignore text values. This function is very useful when you want to know how many entries are filled in a dataset. COUNTA is commonly used in reports to count total records or active rows.
Formula:
=COUNTA(A2:A6)
Explanation: Counts total employees listed.
5. MAX
Purpose: MAX Function in Excel is used to find the largest (highest) numeric value from a selected range of cells. It automatically ignores text, blank cells, and logical values. The syntax of the MAX function is =MAX(number1, [number2], …). This function is commonly used to identify top sales, highest marks, or maximum revenue in a dataset. For example, =MAX(B2:B10) returns the highest value from cells B2 to B10. MAX is widely used in reports, dashboards, and data analysis to quickly highlight peak values.
Formula:
=MAX(C2:C6)
Explanation: Identifies the highest sales figure.
6. MIN
Purpose: The MIN function in Excel is used to find the smallest numeric value from a selected range of cells. It automatically ignores text, blank cells, and logical values, considering only numbers. This function is commonly used to identify the lowest sales, minimum score, or least expense in a dataset. The syntax of the MIN function is simple and easy to apply. It is especially helpful in data analysis, reporting, and comparison tasks.
Formula:
=MIN(C2:C6)
Explanation: Identifies the lowest sales value.
7. IF
Purpose: IF Function in Excel is a logical function used to test a condition and return different results based on whether the condition is TRUE or FALSE. It helps in decision-making by allowing Excel to act differently under different scenarios. The IF function compares a value with a condition and returns one result if the condition is met, and another if it is not. It is commonly used for performance evaluation, pass/fail results, and target analysis. IF can also be combined with other functions like AND, OR, and nested IF for more complex logic.
Formula:
=IF(C2>=D2,"Target Achieved","Target Not Achieved")
Explanation: Checks whether sales target is achieved or not.
8. AND
Purpose: The AND function in Excel is a logical function used to test multiple conditions at the same time. It returns TRUE only when all the given conditions are satisfied; if even one condition is FALSE, the result will be FALSE. The AND function is commonly used with the IF function to apply complex decision logic. It is widely used in performance evaluation, eligibility checks, and validation rules. AND helps make formulas more accurate when decisions depend on more than one condition.
Formula:
=AND(C2>=40000,B2="Sales")
Explanation: Returns TRUE if both conditions are met.
9. OR
Purpose: OR Function in Excel is a logical function used to test multiple conditions at the same time. It returns TRUE if any one of the given conditions is satisfied, and FALSE only when all conditions fail. The OR function is commonly used with the IF function to apply flexible decision-making logic. It helps in scenarios like eligibility checks, approval rules, or performance evaluation. OR improves data analysis by allowing multiple logical comparisons in a single formula.
Formula:
=OR(C2>=50000,D2>=50000)
10. IF with AND
Purpose: IF with AND in Excel is used when you want to test multiple conditions at the same time and return a result based on whether all conditions are true. The AND function checks two or more logical conditions and returns TRUE only if every condition is met. The IF function then uses this TRUE or FALSE result to display a specific output. This combination is commonly used in business scenarios like bonus eligibility, pass/fail status, or performance evaluation. If even one condition fails, Excel returns the value specified for FALSE. It helps in making accurate, rule-based decisions within a worksheet.
Formula:
=IF(AND(C2>=D2,B2="Sales"),"Bonus","No Bonus")
Explanation: Grants bonus only if multiple conditions match.
11. SUMIF
Purpose: SUMIF is an Excel function used to add values in a range only when a specific condition is met. It is especially useful for calculating totals based on categories, such as department-wise sales or region-wise expenses. The function evaluates a criteria range and sums the corresponding values from the sum range. SUMIF helps in quick conditional analysis without using filters or pivot tables. It is commonly used in financial reports, sales analysis, and performance tracking.
Formula:
=SUMIF(B2:B6,"Sales",C2:C6)
Explanation: Sums sales for Sales department only.
12. COUNTIF
Purpose: COUNTIF is an Excel function used to count the number of cells that meet a specific condition within a selected range. It is especially useful when you want to analyze data based on criteria such as text, numbers, or logical conditions. The function checks each cell in the range and counts only those that match the given condition. COUNTIF is commonly used in reports to count entries like total salespeople, completed tasks, or specific categories. It supports criteria such as text values, comparison operators (>, <, =), and wildcards. This makes COUNTIF an essential tool for quick data analysis and summaries.
Formula:
=COUNTIF(B2:B6,"Sales")
13. AVERAGEIF
Purpose: AVERAGEIF is an Excel function used to calculate the average of numbers that meet a specific condition. It allows you to apply a single criterion while averaging values from a selected range. This function is especially useful in sales, finance, and performance analysis where filtered averages are required. For example, you can find the average sales of only one department or average marks above a certain score. AVERAGEIF ignores text and empty cells automatically, ensuring accurate results. It simplifies data analysis by combining filtering and averaging in one formula.
Formula:
=AVERAGEIF(B2:B6,"Sales",C2:C6)
14. VLOOKUP
Purpose: VLOOKUP is an Excel function used to search for a value in the first column of a table and return a related value from another column in the same row. It works vertically, meaning Excel looks top to bottom in the selected table range. VLOOKUP is commonly used to retrieve data such as prices, names, or sales figures from large datasets. The function requires an exact or approximate match based on user selection. Although widely used, VLOOKUP has limitations, such as not being able to look left. In modern Excel, it is often replaced by XLOOKUP for more flexibility.
Formula:
VLOOKUP("Ali",A2:C6,3,FALSE)
Explanation: Fetches Ali’s sales.
15. HLOOKUP
Purpose: HLOOKUP (Horizontal Lookup) is an Excel function used to search for a value in the first row of a table and return a corresponding value from a specified row below it. It works horizontally, unlike VLOOKUP which works vertically. HLOOKUP is commonly used when data is arranged in rows instead of columns. The function allows both exact and approximate matches. It is useful for retrieving values like sales, marks, or prices from horizontally structured datasets. However, for flexible and modern use cases, XLOOKUP is often preferred today.
Formula:
=HLOOKUP("Sales",A1:E2,2,FALSE)
16. XLOOKUP
Purpose: XLOOKUP is a modern Excel function used to search for a value in one range and return a corresponding result from another range. It works both vertically and horizontally, making it more flexible than VLOOKUP or HLOOKUP. XLOOKUP does not require the lookup column to be on the left, which removes a major limitation of older functions. It also returns an exact match by default and can handle missing values gracefully. This function is faster, more accurate, and easier to use for dynamic data analysis in Excel.
Formula:
=XLOOKUP("Ali",A2:A6,C2:C6)
17. INDEX
Purpose: Returns value from position.
Formula:
=INDEX(C2:C6,3)
18. MATCH
Purpose: MATCH is an Excel function used to find the position (row or column number) of a specific value within a selected range. It does not return the value itself, but the location where the value appears. MATCH is commonly used with INDEX to perform advanced and flexible lookups. The function supports exact match and approximate match options, making it useful for sorted and unsorted data. MATCH is faster and more dynamic than traditional lookup methods.
Formula:
=MATCH("John",A2:A6,0)
19. INDEX + MATCH
Purpose: INDEX + MATCH is a powerful Excel combination used to look up and return values from a table based on a matching condition. The MATCH function first finds the position (row number) of a lookup value in a column. The INDEX function then uses that position to fetch the corresponding value from another column. Unlike VLOOKUP, INDEX + MATCH works in any direction (left or right) and is more flexible. It is also faster and more reliable for large datasets. Because of these advantages, INDEX + MATCH is widely used in advanced Excel and data analysis.
Formula:
=INDEX(C2:C6,MATCH("John",A2:A6,0))
20. LEFT
Purpose: The LEFT function in Excel is used to extract a specified number of characters from the beginning (left side) of a text string. It is commonly used when you need to separate codes, initials, or prefixes from text data. The function works only with text values and counts spaces as characters. LEFT is especially useful in data cleaning and formatting tasks. It helps analysts quickly derive meaningful information from structured text.
Formula:
=LEFT(A2,2)
21. RIGHT
Purpose: The RIGHT function in Excel is used to extract a specified number of characters from the end (right side) of a text string. It is commonly used when you need to separate codes, last digits, or suffixes from data. The function works only with text but can also be applied to numbers by treating them as text. RIGHT is especially useful in data cleaning and formatting tasks. It helps analysts quickly isolate meaningful information without manually editing the data.
Formula:
=RIGHT(A2,2)
22. MID
Purpose: MID is an Excel text function used to extract a specific number of characters from the middle of a text string. It works based on a starting position and the number of characters you want to return. The syntax is =MID(text, start_num, num_chars), where text is the original data. This function is very useful when working with codes, IDs, or formatted text like phone numbers. For example, =MID(A2,2,3) extracts three characters starting from the second position in cell A2. MID is commonly used in data cleaning and text analysis tasks.
Formula:
=MID(A2,2,2)
23. LEN
Purpose: LEN Function in Excel is used to count the total number of characters in a cell, including letters, numbers, spaces, and special symbols. It helps users understand the exact length of text data. The syntax of the LEN function is =LEN(text), where text can be a cell reference or direct text. For example, =LEN("Excel Formula") returns 13 because it counts every character, including the space. LEN is commonly used in data cleaning, validation, and text analysis tasks. It is especially useful when checking ID lengths, passwords, or formatting consistency in datasets.
Formula:
=LEN(A2)
24. CONCAT / CONCATENATE
Purpose: CONCAT / CONCATENATE functions in Excel are used to join or combine text from multiple cells into a single cell. These functions are commonly used to merge names, create full addresses, or format reports. CONCATENATE is the older function, while CONCAT is the newer and recommended version in modern Excel. Both functions allow you to combine text, numbers, and cell values with spaces or symbols. CONCAT can also work with ranges of cells, making it more flexible and efficient. These functions are very useful in data cleaning and report preparation.
Formula:
=CONCAT(A2," - ",B2)
25. TRIM
Purpose: TRIM is an Excel function used to remove extra spaces from text. It deletes all leading and trailing spaces and also removes extra spaces between words, leaving only a single space. This function is especially useful when working with imported or manually entered data that contains unwanted spacing. TRIM helps clean text data for accurate analysis, matching, and reporting. It does not remove single spaces between words or non-breaking spaces.
Formula:
=TRIM(A2)
26. UPPER
Purpose: UPPER is an Excel text function used to convert all letters in a text string into uppercase (capital letters). It is especially useful for standardizing data, such as names, codes, or headings, to maintain consistency. The syntax of the UPPER function is =UPPER(text), where text can be a cell reference or a typed value. For example, if cell A2 contains “excel skills”, the formula =UPPER(A2) will return “EXCEL SKILLS”. UPPER does not affect numbers or special characters in the text. This function is commonly used in data cleaning and formatting tasks in Excel.
Formula:
=UPPER(A2)
27. LOWER
Purpose: LOWER is an Excel text function used to convert all uppercase letters in a cell into lowercase. It is especially useful when cleaning and standardizing text data such as names, email IDs, or product codes. The syntax of the function is =LOWER(text), where text can be a cell reference or a text string. For example, =LOWER(A2) will convert “EXCEL Formula” into “excel formula”. The LOWER function does not affect numbers, spaces, or special characters. It is commonly used in data preparation to maintain consistency across large datasets.
Formula:
=LOWER(A2)
28. TODAY
Purpose: The TODAY() function in Excel is used to return the current date based on your computer’s system clock. It does not require any arguments, so you simply write =TODAY(). This function automatically updates every day, making it perfect for tracking deadlines, calculating age, or generating dynamic reports. For example, if you enter =TODAY() in a cell, it might show 15-Jan-2026 (today’s date). You can also use it in formulas, like =TODAY()-A2, to calculate the number of days between today and a past date.
It’s widely used in reporting, dashboards, and automated date calculations in Excel.
Formula:
=TODAY()
29. NOW
Purpose: The NOW function in Excel returns the current date and time based on your computer’s system clock. It is a dynamic function, which means it updates automatically every time the worksheet recalculates.
Formula:
=NOW()
You can use it to track timestamps, calculate deadlines, or measure time differences in reports. Unlike the TODAY() function, which only gives the date, NOW() includes both date and exact time. It is widely used in financial, project management, and daily tracking tasks.
30. ROUND
Purpose: The ROUND function in Excel is used to round a number to a specified number of digits. It can round numbers up or down depending on the value of the digit after the rounding point.
Formula:
=ROUND(C2,0)
