What is Microsoft Excel
Microsoft Excel is a powerful spreadsheet software developed by Microsoft, widely used for storing, organizing, and analyzing data. It offers rows and columns where you can input information and perform calculations with ease. From simple addition to complex data analysis, Excel makes handling large amounts of data quick and efficient.
One of the most important features of Excel is its formulas. They allow you to perform calculations, automate tasks, and analyze data without manual effort. Whether you need to sum values, find averages, or create logical comparisons, formulas make it possible in just a few clicks. In this guide, we will explore the Excel formulas list that every beginner and professional should know to work smarter and faster.
Why Excel Formulas Are Important?
Excel is one of the most used tools in offices, schools, businesses, and even by bloggers. What makes Excel truly powerful is its ability to calculate, filter, and process data automatically using formulas.
Whether you’re adding numbers, checking conditions, or finding information — Excel formulas make your work faster and smarter.
Excel Formulas List
| No. | Function Name | Formula Example | Description (Elaborated) |
|---|---|---|---|
| 1 | SUM – Add Numbers | =SUM(A1:A10) | Adds up all numbers in a given range quickly, saving time compared to manual addition. |
| 2 | AVERAGE – Calculate Mean | =AVERAGE(A1:A10) | Finds the arithmetic mean by adding values and dividing by their count. |
| 3 | COUNT – Count Numbers | =COUNT(A1:A10) | Counts only numeric entries in a range, ignoring text and blanks. |
| 4 | COUNTA – Count Non-Empty Cells | =COUNTA(A1:A10) | Counts all non-blank cells, including text, numbers, and symbols. |
| 5 | COUNTIF – Conditional Counting | =COUNTIF(A1:A10, ">50") | Counts cells in a range that meet a specific condition. |
| 6 | IF – Logical Test | =IF(A1>50, "Pass", "Fail") | Returns one value if the condition is true, another if false. |
| 7 | VLOOKUP – Vertical Lookup | =VLOOKUP("Tom", A2:C10, 2, FALSE) | Looks for a value in the first column and returns data from another column in the same row. |
| 8 | HLOOKUP – Horizontal Lookup | =HLOOKUP(90, A1:D2, 2, FALSE) | Similar to VLOOKUP but searches in the first row across columns. |
| 9 | INDEX – Retrieve Value | =INDEX(A1:C10, 3, 2) | Returns the value from a specified row and column in a range. |
| 10 | MATCH – Find Position | =MATCH(50, A1:A10, 0) | Returns the position of a value in a range; often paired with INDEX. |
| 11 | CONCATENATE / CONCAT – Join Text | =CONCAT(A1, " ", B1) | Joins text from multiple cells into one. |
| 12 | LEFT – Extract Left Characters | =LEFT(A1, 4) | Extracts a set number of characters from the start of a text string. |
| 13 | RIGHT – Extract Right Characters | =RIGHT(A1, 3) | Extracts characters from the end of a text string. |
| 14 | MID – Extract Middle Characters | =MID(A1, 2, 4) | Returns a specific number of characters from the middle of a text string. |
| 15 | TRIM – Remove Extra Spaces | =TRIM(A1) | Removes all extra spaces, leaving only single spaces between words. |
| 16 | CLEAN – Remove Unwanted Characters | =CLEAN(A1) | Removes non-printable characters from text. |
| 17 | TODAY – Display Current Date | =TODAY() | Shows today’s date, updating automatically each day. |
| 18 | NOW – Display Date & Time | =NOW() | Displays the current date and time, auto-updates in real-time. |
| 19 | ROUND – Round Numbers | =ROUND(A1, 2) | Rounds a number to a specified number of decimal places. |
| 20 | IFERROR – Handle Errors | =IFERROR(A1/B1, "Error") | Displays a custom message instead of an error if a formula fails. |
| 21 | LEN – Count Characters | =LEN(A1) | Counts the total number of characters in a cell, including spaces. |
| 22 | TEXT – Format Numbers/Text | =TEXT(A1, "MM/DD/YYYY") | Converts numbers into formatted text. |
| 23 | REPT – Repeat Text | =REPT("*", 10) | Repeats text or symbols a specified number of times. |
| 24 | PROPER – Capitalize Each Word | =PROPER(A1) | Capitalizes the first letter of each word in text. |
| 25 | UPPER – Convert to Uppercase | =UPPER(A1) | Converts all letters in text to uppercase. |
| 26 | LOWER – Convert to Lowercase | =LOWER(A1) | Converts all letters in text to lowercase. |
| 27 | REPLACE – Replace by Position | =REPLACE(A1, 2, 3, "ABC") | Replaces part of a text string with another based on position. |
| 28 | SUBSTITUTE – Replace Specific Text | =SUBSTITUTE(A1, "old", "new") | Replaces all instances of a specific word or text in a cell. |
| 29 | SEARCH – Find Text (Case-Insensitive) | =SEARCH("cat", A1) | Finds position of a substring, ignoring case sensitivity. |
| 30 | FIND – Find Text (Case-Sensitive) | =FIND("Cat", A1) | Finds position of a substring, considering case sensitivity. |
Also Visit:-
- The Complete ASP.NET Guide: Introduction, Features & Controls
- MS Word: The Complete, Expanded Guide — Features, Functions, Advantages, and Practical Use
- search more
- Microsoft Excel Mastery 2025: Definitive Guide from Basics to Advanced
Let’s explore the top Excel formulas with explanation every beginner should know
1. SUM – Add Values
=SUM(A1:A5)
The SUM function is used to add all numeric values within a specified range of cells. It saves time by avoiding manual addition of each cell individually.
Example: If cells A1 to A5 contain the numbers 10, 20, 30, 40, and 50, the formula =SUM(A1:A5) will calculate the total of these numbers and return 150. This function works for both continuous ranges (A1:A5) and multiple ranges separated by commas, such as =SUM(A1:A3, C1:C3).
2. AVERAGE – Calculate Mean
=AVERAGE(A1:A5)
The AVERAGE function calculates the arithmetic mean of the selected cells by adding all the numbers together and then dividing the total by the number of values. This is especially useful for finding the central value of a data set without manually performing the calculation.
Example: If cells A1 to A5 contain the numbers 10, 20, 30, 40, and 50, the formula =AVERAGE(A1:A5) will first sum these values (150) and then divide by the count of numbers (5), giving a result of 30. This function ignores empty cells and cells with text, making it accurate even when the range is not fully filled with numbers.
3. COUNT – Count Numeric Values
=COUNT(A1:A10)
The COUNT function is used to count how many cells in a given range contain numeric values. It automatically ignores text, logical values (TRUE/FALSE), and blank cells, ensuring only numbers are counted. This is useful when you need to quickly determine the number of entries or data points in a list without including empty cells or labels.
Example: If cells A1 to A10 contain 10, 25, “Hello”, 50, blank, 40, 70, TRUE, 90, and blank, the formula =COUNT(A1:A10) will only count the numeric values (10, 25, 50, 40, 70, 90) and return 6 as the result. This helps maintain accuracy when working with mixed data types in a spreadsheet.
4. COUNTA – Count Non-Empty Cells
=COUNTA(A1:A10)
The COUNTA function counts all non-empty cells within a given range. Unlike the COUNT function, which only counts numeric values, COUNTA includes numbers, text, dates, logical values (TRUE/FALSE), symbols, and even error values. This makes it useful for knowing how many total entries exist in a dataset, regardless of their data type.
Example: If cells A1 to A10 contain 10, “Apple”, 25, blank, TRUE, 45, “Excel”, blank, 60, and “#N/A”, the formula =COUNTA(A1:A10) will count all the non-empty cells — including text, numbers, logical values, and errors — and return 8. This is especially helpful when you want a complete count of filled cells in a table, not just the numbers.
5. COUNTIF – Conditional Counting
=COUNTIF(A1:A10, ">50")
The COUNTIF function counts the number of cells in a range that meet a specific condition or criteria. This criteria can be a number, text, date, or even a logical expression. It is very useful for filtering and summarizing data without manually checking each cell.
Example: If cells A1 to A10 contain the values 40, 55, 60, 30, 75, 50, 90, blank, 20, and 100, the formula =COUNTIF(A1:A10, ">50") will count all the cells with values greater than 50. In this case, it will return 5 because the numbers 55, 60, 75, 90, and 100 meet the condition. You can also use text criteria, like =COUNTIF(B1:B10, "Apple"), to count how many times “Apple” appears in a list.
6. IF – Logical Test
=IF(A1>50, "Pass", "Fail")
The IF function is used to perform a logical test and return one value if the condition is TRUE, and another value if the condition is FALSE. It is one of the most commonly used formulas for decision-making in Excel. You can use it for numbers, text comparisons, dates, or even nested inside other formulas for more complex logic.
Example: If cell A1 contains 65, the formula =IF(A1>50, "Pass", "Fail") will check whether the value in A1 is greater than 50. Since 65 is greater than 50, the result will be “Pass”. If the value was 40, the result would be “Fail”. You can also combine IF with other functions like AND, OR, and ISERROR to create advanced conditions.
7. VLOOKUP – Vertical Lookup
=VLOOKUP("Tom", A2:C10, 2, FALSE)
The VLOOKUP function searches for a value in the first column of a table or range and returns a value from another column in the same row. It works by scanning vertically from top to bottom until it finds the match. This formula is ideal for retrieving related information, such as prices, employee details, or product codes, from large datasets.
Example: If cells A2:C10 contain a list of employee names in column A and their respective departments in column B, the formula =VLOOKUP("Tom", A2:C10, 2, FALSE) will search for “Tom” in column A and return the corresponding department from column B. The 2 in the formula indicates the column number (from the selected range) from which the value should be returned, and FALSE ensures an exact match is found.
8. HLOOKUP – Horizontal Lookup
=HLOOKUP(90, A1:D2, 2, FALSE)
The HLOOKUP function works similarly to VLOOKUP, but instead of searching vertically down a column, it searches horizontally across the first row of a table or range. Once it finds a match, it returns a value from the specified row number within the range. This function is useful when your data is arranged in rows instead of columns.
Example: If cells A1:D1 contain scores like 70, 80, 90, and 100, and A2:D2 contain corresponding grades like C, B, A, and A+, the formula =HLOOKUP(90, A1:D2, 2, FALSE) will search for 90 in the first row and return the value from the second row of that column, which is A. The FALSE argument ensures it looks for an exact match.
9. INDEX – Retrieve Value from Range
=INDEX(A1:C10, 3, 2)
The INDEX function returns the value of a cell located at the intersection of a specific row and column within a given range. It is a highly versatile function often used in combination with MATCH to perform advanced lookups. Unlike VLOOKUP and HLOOKUP, INDEX does not require the lookup value to be in the first row or column, making it more flexible for retrieving data from any position.
Example: If range A1:C10 contains a dataset of products (column A), prices (column B), and quantities (column C), the formula =INDEX(A1:C10, 3, 2) will return the value found in the 3rd row and 2nd column of that range. If the value in that position is 150, the result will be 150.
10. MATCH – Find Position of Value
=MATCH(50, A1:A10, 0)
The MATCH function returns the relative position of a specific value within a range, rather than the value itself. It is especially useful when you want to know the location of an item in a list. MATCH can be combined with INDEX for more advanced and flexible lookups, allowing you to retrieve data from anywhere in a table.
Example: If cells A1 to A10 contain the numbers 10, 20, 30, 40, 50, 60, 70, 80, 90, and 100, the formula =MATCH(50, A1:A10, 0) will return 5, because 50 is located in the 5th position within the range. The last argument (0) specifies that the match should be exact. You can also use 1 or -1 for approximate matches depending on your sorting order.
11. CONCATENATE / CONCAT – Join Text
=CONCATENATE(A1, " ", B1) (Old method)=CONCAT(A1, " ", B1) (New method)
This formula joins the contents of two or more cells into a single cell. In the old Excel versions, CONCATENATE was used, but in modern versions, CONCAT is the preferred function. You can add spaces, commas, or other characters between values by placing them inside quotation marks.
Example: If cell A1 contains “John” and cell B1 contains “Doe”, the formula =CONCAT(A1, " ", B1) will return John Doe. This is especially useful when combining first names, last names, or merging information from multiple columns.
12. LEFT – Extract Left Characters
=LEFT(A1, 4)
This formula returns the first specified number of characters from the left-hand side of a text string. It is especially useful when you need to extract codes, abbreviations, or fixed-length segments from data entries.
Example: If cell A1 contains "ExcelTips", the formula =LEFT(A1, 4) will return “Exce” because it extracts the first 4 characters starting from the left.
Tip: You can combine LEFT with other functions like FIND or SEARCH to dynamically determine the number of characters to extract based on the position of a specific character, such as a space or dash.
13. RIGHT – Extract Right Characters
=RIGHT(A1, 3)
The RIGHT function returns the last specified number of characters from the right-hand side of a text string. It is useful when you want to extract suffixes, codes, or fixed-length endings from data values.
Example: If cell A1 contains "Excel2025", the formula =RIGHT(A1, 3) will return “025” because it extracts the last 3 characters from the right.
Tip: RIGHT can be combined with functions like LEN and FIND to dynamically determine how many characters to pull, especially when the length of the text varies.
14. MID – Extract Middle Characters
=MID(A1, 2, 4)
The MID function extracts a specific number of characters from any position within a text string, starting from a defined point. You specify three arguments: the text, the starting position, and the number of characters to return.
Example: If cell A1 contains "ExcelTips", the formula =MID(A1, 2, 4) will return “xcel”, because it starts at the 2nd character and extracts 4 characters.
Tip: MID is especially useful for breaking apart structured text like codes, IDs, or names. You can combine it with FIND or SEARCH to dynamically locate where to start the extraction, even if the text length varies.
15. TRIM – Remove Extra Spaces
=TRIM(A1)
The TRIM function removes all unnecessary spaces from a text string, leaving only single spaces between words and no leading or trailing spaces. This is particularly useful when working with imported data that may contain irregular spacing.
Example: If cell A1 contains " Excel Tips ", the formula =TRIM(A1) will return “Excel Tips”, with clean, evenly spaced text.
Tip: TRIM is often paired with functions like CLEAN to remove both extra spaces and non-printable characters, ensuring data is ready for analysis or presentation.
16. CLEAN – Remove Unwanted Characters
=CLEAN(A1)
The CLEAN function removes non-printable characters from text, which often appear when data is copied from external sources such as web pages, PDFs, or other software. These hidden characters can interfere with formulas, sorting, or filtering.
Example: If cell A1 contains "Excel Tips" with invisible special characters, =CLEAN(A1) will return a clean version of the text without those unwanted elements.
Tip: For best results, CLEAN can be combined with TRIM to remove both non-printable characters and unnecessary spaces, ensuring your text is perfectly formatted.
17. TODAY – Display Current Date
=TODAY()
The TODAY function displays the current system date and updates automatically each day when the worksheet is opened or recalculated. It’s ideal for creating dynamic reports, logs, or trackers that always reflect the present date without manual updates.
Example: If today’s date is 10-Aug-2025, the formula =TODAY() will return 10-Aug-2025.
Tip: You can combine TODAY with other functions for advanced calculations, such as finding the number of days between two dates:=TODAY() - A1 (returns the days elapsed since the date in cell A1).
18. NOW – Display Current Date and Time
=NOW()
The NOW function displays the current system date and exact time, updating automatically each time the worksheet is opened or recalculated. It’s useful for time-stamped records, live dashboards, or any situation where both date and time need to be tracked dynamically.
Example: If the current date and time is 10-Aug-2025 5:45 PM, the formula =NOW() will return 10-Aug-2025 17:45 (depending on your cell formatting).
Tip: You can format the result using Format Cells → Date or Custom to show only the date, only the time, or both. For example:
Time only: =NOW()-TODAY()
Date only: =TODAY()
=NOW()
Shows the current date and exact time. Updates automatically.
19. ROUND – Round Numbers
=ROUND(A1, 2)
The ROUND function adjusts a number to a specified number of decimal places, either increasing or decreasing precision as needed. It follows normal rounding rules—values ending in 5 or more round up, while values less than 5 round down. This is especially useful for financial data, percentages, or any calculation where clean, readable numbers are required.
Example: If A1 contains 3.14159, the formula =ROUND(A1, 2) will return 3.14.
Tip:
Negative decimal values (e.g., ROUND(A1, -2)) round to the nearest hundred, thousand, etc.
Use ROUND(A1, 0) to round to the nearest whole number.
20. IFERROR – Handle Errors Gracefully
=IFERROR(A1/B1, "Error")
The IFERROR function is used to catch and handle errors in formulas, allowing you to replace them with a custom value, message, or alternative calculation. It’s especially useful for preventing distracting error codes like #DIV/0!, #N/A, or #VALUE! from appearing in your worksheet.
Example: If A1 = 10 and B1 = 0, the formula =A1/B1 would normally show #DIV/0!. But using =IFERROR(A1/B1, "Error") will display Error instead.
Tip:
Ideal for creating professional reports and dashboards where you want clean outputs without technical error codes.
You can replace "Error" with another formula, a blank (""), or a more descriptive message like "Check Input".
21. TEXT – Format Numbers as Text
=TEXT(1234.567, "₹#,##0.00")
The TEXT function converts a number into text in a specific format. It’s ideal for displaying numbers as dates, currency, percentages, or custom patterns without changing the underlying value.
Example:
If cell A1 = 1234.567
Formula: =TEXT(A1, "₹#,##0.00") → Result: ₹1,234.57
Tip:
- Use
"DD-MMM-YYYY"for date formatting. - Helps in creating dynamic reports where numbers need to be displayed in a reader-friendly format.
22. CONCATENATE / CONCAT – Join Text
=CONCATENATE(A1, " ", B1) (Older)=CONCAT(A1, " ", B1) (Newer)
This function merges multiple text strings into one. It’s commonly used for joining first and last names, combining addresses, or creating custom labels.
Example:
If A1 = “John” and B1 = “Doe”
Formula: =CONCAT(A1, " ", B1) → Result: John Doe
Tip:
- Use
TEXTJOIN()for more flexibility, especially when joining with delimiters like commas or spaces. - CONCAT is the updated version and works better with modern Excel versions.
23. LEN – Count Characters
=LEN(A1)
The LEN function returns the number of characters in a cell, including spaces, punctuation, and numbers. It’s useful for data validation, cleaning, and text analysis.
Example:
If A1 = "Excel Rocks"
Formula: =LEN(A1) → Result: 11
Tip:
- Use in combination with TRIM to get accurate counts without extra spaces.
- Helpful in preparing data for import/export where character limits apply.
FAQs
1. What is the Excel Formulas List?
The Excel Formulas List is a collection of essential formulas like SUM, COUNT, and VLOOKUP that help simplify data analysis and calculations.
2. Why is the Excel Formulas List important for beginners?
Using the Excel Formulas List helps beginners perform calculations, analyze data, and automate tasks without errors or delays.
3. Can the Excel Formulas List improve work efficiency?
Yes, the Excel Formulas List allows users to handle large datasets faster and more accurately by automating repetitive calculations.
4. Does the Excel Formulas List include logical functions?
Absolutely, the Excel Formulas List contains logical formulas like IF and IFERROR which help make decisions based on data conditions.
5. How can I use the Excel Formulas List for data lookup?
Formulas like VLOOKUP and HLOOKUP from the Excel Formulas List allow you to quickly find data across tables or ranges.
6. Are text manipulation formulas part of the Excel Formulas List?
Yes, functions such as CONCAT, LEFT, RIGHT, and MID in the Excel Formulas List help in managing and formatting text data efficiently.(Excel Formulas List)
7. What is the difference between COUNT and COUNTA in the Excel Formulas List?
COUNT counts only numeric cells, while COUNTA counts all non-empty cells, including text and symbols in the Excel Formulas List.(Excel Formulas List)
8. Can the Excel Formulas List handle date and time calculations?
Definitely, formulas like TODAY and NOW in the Excel Formulas List enable dynamic date and time tracking.(Excel Formulas List)
9. Is the Excel Formulas List useful for error handling?
Yes, the IFERROR formula from the Excel Formulas List helps replace errors with custom messages or alternate calculations.(Excel Formulas List )
10. How do I learn the Excel Formulas List quickly?
Start by practicing the most common formulas in the Excel Formulas List like SUM, COUNT, IF, and VLOOKUP to build a strong foundation.(Excel Formulas List )