Excel Formulas List With Examples | Comprehensive Guide
Table of Contents
- Jaro Education
- 8, March 2025
- 4:00 pm
Excel is a highly capable piece of software, far more than mere data storage, as it allows for much more efficient data exploration, manipulation, and visualization. At the core of these capabilities lies Excel formulas. But what are they? Simply speaking, Excel formulas are expressions performing calculations or operations on your data. They can perform from a basic arithmetic function to a complex statistical analysis, thanks to which you draw insights for informed decisions.
Be it a fellow student, a working professional, or an individual sitting at home looking to organize finances, learning Excel formulas can be a productivity booster. In this guide, we will cover tons of Excel formulas with examples and their real-time usefulness. By the end of this journey, you should have the resources to: work with these formulas into your workflow and further develop your ability to analyze data. So, let’s get started and fully utilize Excel!
Understanding Excel Formulas
Before we jump into the various types of Excel formulas, it’s essential to understand the components that make up an Excel formula:
1. Operators: These are symbols that specify the type of calculation you want to perform. Common operators include:
- Addition: +
- Subtraction: –
- Multiplication: *
- Division: /
- Exponentiation: ^
2. Functions: These are predefined formulas in Excel that perform specific calculations. For example, the SUM() function adds a range of numbers.
3. References: These refer to the cells that contain the data you want to use in your calculations. Cell references can be relative (e.g., A1) or absolute (e.g., $A$1), affecting how they behave when copied to other cells.
4. Constants: These are fixed values that you can include in your formulas, such as numbers or text strings.
Basic Excel Formulas
Let’s start with some basic formulas that form the foundation of more complex calculations.
1. SUM()
The SUM() function adds a range of numbers.
Formula: =SUM(A1:A5)
Example:
This formula calculates the total of the values in cells A1 through A5.
2. AVERAGE()
The AVERAGE() function calculates the mean of a set of numbers.
Formula: =AVERAGE(B1:B10)
Example:
This formula computes the average of the values in cells B1 through B10.
3. COUNT()
The COUNT() function counts the number of cells that contain numerical data.
Formula: =COUNT(C1:C20)
Examples:
This formula counts how many cells in the range C1 to C20 have numbers.
4. MAX() and MIN()
These functions find the largest and smallest numbers in a range, respectively.
Formula:
=MAX(F2:F7)
=MIN(F2:F7)
Examples:
The first formula returns the highest value in the range D1 to D10, while the second returns the lowest.
Intermediate Excel Formulas
Once you’re comfortable with basic functions, you can explore intermediate formulas that provide more analytical capabilities.
5. IF()
The IF() function allows you to perform conditional logic in your calculations.
Formula:
=IF(logical_test, [value_if_true], [value_if_false])
Example:
=IF(C2<D3,“TRUE”,”FALSE”) – This formula checks whether the value in cell C2 is less than the value in D3. If this condition is met, the cell will display TRUE; otherwise, it will display FALSE.
=IF(SUM(C1:C10) > SUM(D1:D10), SUM(C1:C10), SUM(D1:D10)) – This is a more complex IF statement. It calculates the sum of the values from C1 to C10 and D1 to D10, then compares the two sums. If the sum of C1 to C10 exceeds the sum of D1 to D10, the cell will show the sum of C1 to C10; otherwise, it will show the sum of D1 to D10.
6. VLOOKUP()
The VLOOKUP() function searches for a value in the first column of a range and returns a value in the same row from a specified column.
Example:
=VLOOKUP(F1, A1:B10, 2, FALSE)
In this formula, Excel looks for the value in F1 within the range A1 to B10 and returns the corresponding value from the second column.
7. CONCATENATE()
This function joins two or more text strings into one.
Example:
Suppose you have the following data in cells A1 and B1:
Cell A1: “Hello”
Cell B1: “world”
To combine these two strings with a space in between, you can use the CONCAT function like this:
=CONCAT(A1, " ", B1)
8. TRIM()
The TRIM() function removes extra spaces from text.
Example:
Copy the example data from the table below and paste it into cell A1 of a new Excel worksheet. To display the results of the formulas, select each formula, press F2, and then hit Enter. If necessary, you can adjust the column widths to view all the data.
| Formula | Description | Result |
|---|---|---|
| =TRIM(" First Quarter Earnings ") | Removes leading and trailing spaces from the text in the formula (First Quarter Earnings) | First Quarter Earnings |
Advanced Excel Formulas
For those looking to take their Excel skills to the next level, the following advanced formulas will enhance your data-handling capabilities.
9. INDEX() and MATCH()
These two functions are often used together to look up values dynamically.
Example:
This example illustrates a small list where the value we want to search for, Chicago, is not located in the leftmost column, making VLOOKUP unsuitable. Instead, we will use the MATCH function to locate Chicago in the range B1:B11, where it is found in row 4. Then, the INDEX function uses this row number to retrieve the population of Chicago from the 4th column (column D). The formula is displayed in cell A14.
10. SUMIF()
The SUMIF() function adds up values based on a specified condition.
Formula:
=SUMIF(K1:K10, ">100", L1:L10)
This formula sums the values in L1 to L10 where the corresponding values in K1 to K10 are greater than 100.
Example SUMIF function
Find the sum of Total stats for Grass-type Pokemon:
The condition is that the type is “Grass”.
11. COUNTIF()
Similar to SUMIF(), the COUNTIF() function counts cells that meet a certain criterion.
Example:
=COUNTIF(M1:M20, "Yes")
This formula counts how many cells in the range M1 to M20 contain the word "Yes".
12. TODAY() and NOW()
These functions return the current date and time.
Examples:
=TODAY()
=NOW()
TODAY() returns the current date, while NOW() returns the current date and time.
Text Functions
Excel also provides a variety of functions specifically designed for manipulating text.
13. LEFT() and RIGHT()
These functions extract a specified number of characters from the left or right side of a string.
Examples:
=LEFT(N1, 5)
=RIGHT(N1, 3)
The first formula returns the first five characters of the text in N1, while the second returns the last three characters.
14. MID()
The MID() function extracts a substring from a string based on a starting position and length.
Example:
=MID(O1, 3, 4)
This formula retrieves four characters from the text in O1, starting from the third character.
15. LEN()
The LEN() function returns the number of characters in a string.
Example:
=LEN(P1)
This formula counts how many characters are in the text in P1.
Date and Time Functions
Date and time calculations are crucial for many business applications. Excel provides several functions to handle these tasks efficiently.
16. DATEDIF()
This function calculates the difference between two dates.
Example:
=DATEDIF(Q1, R1, "D")
This formula computes the number of days between the dates in Q1 and R1.
17. EDATE()
The EDATE() function returns a date that is a specified number of months before or after a given date.
Example:
=EDATE(S1, 3)
This formula adds three months to the date in S1.
18. YEAR() and MONTH()
These functions extract the year or month from a date.
=YEAR(T1)
=MONTH(T1)
The first formula returns the year from the date in T1, while the second returns the month.
Logical Functions
Logical functions allow you to perform tests and return different values based on the outcome.
19. AND() and OR()
These functions evaluate multiple conditions and return TRUE or FALSE.
Examples:
=AND(U1>50, V1<100)
=OR(W1="Yes", X1="No")
The first formula checks if U1 is greater than 50 and V1 is less than 100. The second checks if W1 is "Yes" or X1 is "No".
20. NOT()
The NOT() function reverses the logical value of its argument.
Example:
=NOT(Y1="Complete")
This formula returns TRUE if Y1 is not "Complete".
21. REPLACE
REPLACE stands for standing in for some text that is substituted for part of another text string. The syntax is, “=REPLACE(old_text, start_num, num_chars, new_text)” where start_num requires the number index position that shall indicate the starting position of the substitutions. Next follows num_chars; it is a required argument defining the number of characters that shall be traced back to substitutes in a string.
Let’s have a look at the ways we can use this function.
Here, we are replacing A101 with B101 by typing,
=REPLACE(A15,1,1,”B”)
22. SUBSTITUTE
The SUBSTITUTE() function replaces the existing text with a new text in a text string.
The syntax is “=SUBSTITUTE(text, old_text, new_text, [instance_num])”.
Here, [instance_num] refers to the index position of the present texts more than once.
23. OFFSET combined with SUM or AVERAGE
Formula: =SUM(B4:OFFSET(B4,0,E2-1))
There is no big deal about the OFFSET function itself; hopefully, when packaged with other functions like SUM or AVERAGE, it will bring out a more sophisticated formula. Suppose you want a function that allows for dynamic summing of several cells. Using a regular SUM formula, you can only calculate statically, while with OFFSET included, you give the required freedom of how to go about reference changing within cells.
In this formula, instead of the reference cell at the end of the SUM function, we employ the OFFSET function in its place. This makes the formula dynamic, and it is in cell E2 where you tell Excel how many consecutive cells you would like to add up. Advanced Excel formulas have just arrived on board
*corporatefinanceinstitute.com
24. DATEDIF()
This function calculates the difference between two dates.
Example:
=DATEDIF(R1, S1, "D")
This formula computes the number of days between the dates in R1 and S1.
25. EDATE()
The EDATE() function returns a date that is a specified number of months before or after a given date.
Example:
=EDATE(T1, 3)
This formula adds three months to the date in T1.
26. YEAR() and MONTH()
These functions extract the year or month from a date.
Examples:
=YEAR(U1)
=MONTH(U1)
The first formula returns the year from the date in U1, while the second returns the month.
Additional Functions
27. AND() and OR()
These functions evaluate multiple conditions and return TRUE or FALSE.
Examples:
=AND(V1>50, W1<100)
=OR(X1="Yes", Y1="No")
The first formula checks if V1 is greater than 50 and W1 is less than 100. The second checks if X1 is "Yes" or Y1 is "No".
28. NOT()
The NOT() function reverses the logical value of its argument.
Example:
=NOT(Z1="Complete")
This formula returns TRUE if Z1 is not "Complete".
Practical Applications of Excel Formulas
Now that we’ve covered a wide variety of MS Excel formulas, let’s explore their practical applications in real-world scenarios.
1. Budgeting and Financial Analysis
Excel formulas are invaluable for personal and business budgeting. You can easily calculate totals, averages, and variances to keep track of expenses and incomes.
Example: Using formulas like SUM() and AVERAGE() to analyze monthly expenditures.
2. Data Analysis and Reporting
For data analysts, Excel formulas help in summarizing large datasets. Functions such as VLOOKUP(), SUMIF(), and COUNTIF() allow for detailed reporting and insights.
Example: Analyzing sales data to determine trends and performance metrics.
3. Project Management
Excel formulas can track project timelines, budgets, and resources. Using functions like IF() and TODAY(), project managers can monitor deadlines and task completion.
Example: Calculating the number of days until project deadlines.
4. Inventory Management
Business owners can manage inventory levels using formulas to track stock counts, reorder levels, and sales data.
Example: Using SUMIF() to calculate the total sales of a specific product.
5. Performance Tracking
Whether in education or business, tracking performance metrics can be done efficiently with Excel. The use of formulas allows for quick assessments and comparisons.
Example: Analyzing student grades using AVERAGE() and COUNTIF() to determine passing rates.
Tips for Mastering Excel Formulas
Mastering Excel formulas is essential for enhancing your data analysis skills and improving efficiency. Here are some valuable tips to help you become proficient:
1. Practice Regularly
The best way to get comfortable with Excel formulas is through consistent practice. The more you use Excel, the more familiar you will become with its functionalities. Try to incorporate formulas into your daily tasks, whether it’s for budgeting, data analysis, or project management.
2. Use the Formula Bar
Familiarize yourself with the formula bar, which is located at the top of the Excel window. This tool allows you to enter and edit formulas directly. Using the formula bar can help you manage complex formulas more easily, as it provides a clear view of the entire expression.
3. Explore the Function Library
Excel boasts a vast library of functions that cater to various tasks, from simple calculations to complex data analysis. Spend time exploring this library to understand the functions available and how they can be applied. This knowledge will empower you to use the right formula for your specific needs.
4. Watch Tutorials
Online tutorials offer a wealth of visual guidance and tips for mastering Excel formulas. Platforms like YouTube and educational websites provide step-by-step demonstrations that can enhance your learning experience. These resources can clarify complex concepts and provide practical examples.
5. Join Excel Communities
Engaging with online forums or local Excel user groups can significantly enhance your learning journey. These communities allow you to share knowledge, ask questions, and solve problems collaboratively. Learning from others’ experiences can provide insights that you might not find in tutorials or manuals.
Conclusion
Mastering Excel formulas is a journey that pays off in improved efficiency and enhanced data analysis capabilities. From basic calculations to complex logical operations, these formulas empower you to make data-driven decisions effectively.
As you continue to explore and practice, you’ll discover even more ways to leverage Excel’s power in your personal and professional life. Embrace the challenge, and soon you’ll find that Excel formulas are not just tools—they’re essential skills that open up a world of possibilities.
By incorporating the knowledge and examples provided in this guide, you’re well on your way to becoming an Excel expert. So, roll up your sleeves and start experimenting with these formulas today!
Also, to enhance your knowledge and gain skills in any field, you can visit Jaro Education’s website. We are a leading online higher education and upskilling company, providing academic guidance and career counseling to individuals. We collaborate with many prestigious universities and colleges to offer industry-standard online certification and degree courses that help participants excel in their careers.
Frequently Asked Questions
![Hospital Manager Salary in India 2025 [For Freshers & Experienced]](https://jaro-website.s3.ap-south-1.amazonaws.com/2025/03/Hospital-Manager-Salary-in-India-2025-For-Freshers-Experienced-1024x576.jpg)
Trending Blogs
