Microsoft Excel is a popular tool for handling data. Most people have used Excel because it’s so widely used. Excel is commonly used in businesses to create reports and gain insights. It has many built-in features that make it easy to work with.
One such feature that allows Excel to stand out is – Excel sheet formulas. Here, we will look into the top 25 Excel formulas that one must know while working on Excel. The topics that we will be covering in this article are as follows:
Table of Contents
What is Excel Formula?
In Microsoft Excel, a formula is a way to calculate values based on the data in your cells. Formulas can add, subtract, multiply, and divide numbers. They can also find averages, calculate percentages, handle dates and times, and much more. Formulas give you results, even if there’s an error.
Formulas in Excel: An Overview
- Choose a cell.
- To enter an equal sign, click the cell and type =.
- Enter the address of a cell in the selected cell or select a cell from the list.
- You need to enter an operator.
- Enter the address of the next cell in the selected cell.
- Press Enter
Another term related to Excel formulas is “function.” While “formulas” and “functions” are often used interchangeably, they are slightly different. A formula always starts with an equal sign and can be used for various calculations. Functions, on the other hand, are specific tools within Excel designed to perform more complex calculations automatically. Each function has a name that shows what it does.
The example below shows how we have used the multiplication formula manually with the ‘*’ operator.
This example below shows how we have used the function – ‘PRODUCT’ to perform multiplication. As you can see, we didn’t use the mathematical operator here.
Excel formulas and functions help you perform your tasks efficiently, and it’s time-saving. Let’s proceed and learn the different types of functions available in Excel and use relevant formulas as and when required.
Become our partner & Earn with us
Excel Formulas and Functions
There are many Excel formulas and functions available, each designed for different types of tasks. In this guide, we will explore formulas and functions related to mathematical operations, text handling, date and time management, as well as SUMIF, COUNTIF, and some lookup functions.
We’ll cover the top 25 Excel formulas you should know, organized by their function. Let’s begin with the first formula on our list.
1. SUM
The SUM() function, as the name suggests, gives the total of the selected range of cell values. It performs the mathematical operation which is addition. Here’s an example of it below:
As you can see above, to find the total amount of sales for every unit, we had to simply type in the function “=SUM(C2:C4)”. This automatically adds up 300, 385, and 480. The result is stored in C5.
2. AVERAGE
The AVERAGE() function focuses on calculating the average of the selected range of cell values. As seen from the below example, to find the avg of the total sales, you have to simply type in:
It automatically calculates the average, and you can store the result in your desired location.
3. COUNT
The function COUNT() counts the total number of cells in a range that contains a number. It does not include the cell, which is blank, and the ones that hold data in any other format apart from numeric.
In the example above, we’re counting cells from C1 to C4. The `COUNT` function only includes cells with numbers, so if one cell contains text like “Total Sales,” it will be ignored, resulting in a count of 3.
If you need to count all cells with numbers, text, or any type of data, use the `COUNTA()` function. Note that `COUNTA()` does not count blank cells.
To count blank cells in a range, you should use the `COUNTBLANK()` function.
4. SUBTOTAL
Next, let’s explore how the `SUBTOTAL()` function works. The `SUBTOTAL()` function calculates a subtotal in a dataset and allows you to choose different types of calculations, such as average, count, sum, minimum, maximum, and more. Here are two examples to illustrate how it can be used:
In the example above, we have performed the subtotal calculation on cells ranging from A2 to A4. As you can see, the function used is
In the subtotal list “1” refers to average. Hence, the above function will give the average of A2: A4 and the answer to it is 11, which is stored in C5. Similarly,
This selects the cell with the maximum value from A2 to A4, which is 12. Incorporating “4” in the function provides the maximum result.
5. MODULUS
The MOD() function works on returning the remainder when a particular number is divided by a divisor. Let’s now have a look at the examples below for better understanding.
- In the first example, we have divided 10 by 3. The remainder is calculated using the function
- The result is stored in B2. We can also directly type “=MOD(10,3)” as it will give the same answer.
- Similarly, here, we have divided 12 by 4. The remainder is 0 is, which is stored in B3.
6. POWER
The function “Power()” returns the result of a number raised to a certain power. Let’s have a look at the examples shown below:
As you can see above, to find the power of 10 stored in A2 raised to 3, we have to type:
This is how power function works in Excel.
7. CEILING
Next, we have the ceiling function. The CEILING() function rounds a number up to its nearest multiple of significance.
The nearest highest multiple of 5 for 35.316 is 40.
8. FLOOR
Contrary to the Ceiling function, the floor function rounds a number down to the nearest multiple of significance.
The nearest lowest multiple of 5 for 35.316 is 35.
9. CONCATENATE
This function merges or joins several text strings into one text string. Given below are the different ways to perform this function.
- In this example, we have operated with the syntax:
- In this example, we have operated with the syntax:
Those were the two ways to implement the concatenation operation in Excel.
10. LEN
The function LEN() returns the total number of characters in a string. So, it will count the overall characters, including spaces and special characters. Given below is an example of the Len function.
Let’s now move onto the next Excel function on our list of this article.
- Next, we are replacing A102 with A2102 by typing:
- Finally, we are replacing Adam with Saam by typing:
Let’s now move to our next function.
Amity university: Online mba in data science
12. 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.
Given below are a few examples of this function:
- Here, we are substituting “I like” with “He likes” by typing:
- Next, we are substituting the second 2010 that occurs in the original text in cell A21 with 2016 by typing “=SUBSTITUTE(A21,2010, 2016,2)”.
- Now, we are replacing both the 2010s in the original text with 2016 by typing “=SUBSTITUTE(A22,2010,2016)”.
That was all about the substitute function, let’s now move on to our next function.
13. LEFT, RIGHT, MID
The `LEFT()` function extracts a specified number of characters from the beginning of a text string. The `MID()` function retrieves characters from the middle of a text string, based on a starting position and length. The `RIGHT()` function returns a specified number of characters from the end of a text string.
Let’s understand these functions with a few examples.
- In the example below, we use the function left to obtain the leftmost word on the sentence in cell A5.
Shown below is an example using the mid function.
- Here, we have an example of the right function.
14. UPPER, LOWER, PROPER
The `UPPER()` function changes all characters in a text string to uppercase. The `LOWER()` function changes all characters to lowercase. The `PROPER()` function capitalizes the first letter of each word in a text string, while making all other letters lowercase.
Let’s understand this better with the following examples:
- Here, we have converted the text in A6 to a full uppercase one in A7.
- Now, we have converted the text in A6 to a full lowercase one, as seen in A7.
- Finally, we have converted the improper text in A6 to a clean and proper format in A7.
Now, let us hop on to exploring some date and time functions in Excel.
15. NOW()
The NOW() function in Excel gives the current system date and time.
The result of the NOW() function will change based on your system date and time.
16. TODAY()
The TODAY() function in Excel provides the current system date.
The function DAY() is used to return the day of the month. It will be a number between 1 to 31. 1 is the first day of the month, 31 is the last day of the month.
The MONTH() function returns the month, a number from 1 to 12, where 1 is January and 12 is December.
The YEAR() function, as the name suggests, returns the year from a date value.
17. TIME()
The TIME() function converts hours, minutes, seconds given as numbers to an Excel serial number, formatted with a time format.
18. HOUR, MINUTE, SECOND
The HOUR() function generates the hour from a time value as a number from 0 to 23. Here, 0 means 12 AM and 23 is 11 PM.
The function MINUTE(), returns the minute from a time value as a number from 0 to 59.
The SECOND() function returns the second from a time value as a number from 0 to 59.
19. DATEDIF
The DATEDIF() function provides the difference between two dates in terms of years, months, or days.
Below is an example of a DATEDIF function where we calculate the current age of a person based on two given dates, the date of birth and today’s date.
Now, let’s skin through a few critical advanced functions in Excel that are popularly used to analyze data and create reports.
20. VLOOKUP
Next up in this article is the VLOOKUP() function. This stands for the vertical lookup that is responsible for looking for a particular value in the leftmost column of a table. It then returns a value in the same row from a column you specify.
Below are the arguments for the VLOOKUP function:
lookup_value – This is the value that you have to look for in the first column of a table.
table – This indicates the table from which the value is retrieved.
col_index – The column in the table from the value is to be retrieved.
range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match.
We will use the below table to learn how the VLOOKUP function works.
If you wanted to find the department to which Stuart belongs, you could use the VLOOKUP function as shown below:
Here, A11 cell has the lookup value, A2: E7 is the table array, 3 is the column index number with information about departments, and 0 is the range lookup.
If you hit enter, it will return “Marketing”, indicating that Stuart is from the marketing department.
21. HLOOKUP
Similar to VLOOKUP, we have another function called HLOOKUP() or horizontal lookup. The function HLOOKUP looks for a value in the top row of a table or array of benefits. It gives the value in the same column from a row you specify.
Below are the arguments for the HLOOKUP function:
lookup_value – This indicates the value to lookup.
table – This is the table from which you have to retrieve data.
row_index – This is the row number from which to retrieve data.
range_lookup – [optional] This is a boolean to indicate an exact match or approximate match. The default value is TRUE, meaning an approximate match.
Given the below table, let’s see how you can find the city of Jenson using HLOOKUP.
Here, H23 has the lookup value, i.e., Jenson, G1:M5 is the table array, 4 is the row index number, 0 is for an approximate match.
Once you hit enter, it will return “New York”.
22. IF Formula
The IF() function checks a given condition and returns a particular value if it is TRUE. It will return another value if the condition is FALSE.
In the below example, we want to check if the value in cell A2 is greater than 5. If it’s greater than 5, the function will return “Yes 4 is greater”, else it will return “No”.
In this case, it will return ‘No’ since 4 is not greater than 5.
‘IFERROR’ is another function that is popularly used. This function returns a value if an expression evaluates to an error, or else it will return the value of the expression.
Suppose you want to divide 10 by 0. This is an invalid expression, as you can’t divide a number by zero. It will result in an error.
The above function will return “Cannot divide”.
23. INDEX-MATCH
The `INDEX-MATCH` function is useful for retrieving values from a column to the left of your lookup column. Unlike `VLOOKUP`, which can only return values from columns to the right, `INDEX-MATCH` allows for more flexibility. Additionally, `INDEX-MATCH` is often more efficient than `VLOOKUP` because it only requires Excel to process the lookup and return columns, rather than the entire table array. This can improve performance, especially with large datasets.
Using the below table, let’s see how you can find the city where Jenson resides.
Now, let’s find the department of Zampa.
24. COUNTIF
The function COUNTIF() is used to count the total number of cells within a range that meet the given condition.
Below is a coronavirus sample dataset with information regarding the coronavirus cases and deaths in each country and region.
Let’s find the number of times Afghanistan is present in the table
25. SUMIF
The SUMIF() function adds the cells specified by a given condition or criteria.
Below is the coronavirus dataset using which we will find the total number of cases in India till 3rd Jun 2020. (Our dataset has information from 31st Dec 2020 to 3rd Jun 2020).
The SUMIFS() function adds the cells specified by a given set of conditions or criteria.
Let’s find the total cases in France on those days when the deaths have been less than 100.
26. Goal Seek
Goal Seek is a function in-built in Advanced Excel Functions that allows you to get the desired output by changing the assumptions. The process is dependent on the trial and error method to achieve the desired result.
Let’s look at an example to understand it better.
Example
In this example, we aim to find what will be the rate of interest if the person wants to pay
$5000 per month to settle the loan amount.
PMT function is used when you want to calculate the monthly payment you need to pay to settle the loan amount.
Let’s go through this problem in steps to see how we can calculate the interest rate that will settle a loan of $400,000 by $5,000 a month payment.
- PMT formula should now be entered in the cell that is the Payment cell adjacent. Currently, there is no value in the rate of interest cell, Excel gives us the payment of $3,333.33 because it assumes the rate of interest to be 0%. Ignore it.
- Go to Data > What – If Analysis > Goal Seek
- Set the monthly payment to -5,000. The deduction in amount signifies the negative value.
- Set rate of interest as the changing cell.
- Click OK. You will see the goal seek function automatically gives the interest rate that is required to pay the loan amount.
Go to Home > Number and change the value to Percentage.
Your outcome will look like below:
27. What-If Analysis with Solver
What-If Analysis is the method of changing the values to try out different scenarios for formulas in Advanced excel.
Several different sets of values can be used in one or multiple of these Advanced excel formulas to explore the different results.
The Solver tool in Excel is perfect for “what-if” analysis. It’s an add-in that helps you find the best value for a formula in a specific cell, called the objective cell. However, there are certain constraints or limits that you can apply to other formula cells in your worksheet.
Solver uses decision variables, which are a set of cells involved in calculating the formulas in both the objective and constraint cells. Solver changes the values in these decision variable cells to meet the limits set by the constraints. This process helps you achieve the desired outcome in the objective cell.
Activating Solver Add-in
- On the File tab, click Options.
- Go to Add-ins, select Solver Add-in, and click on the Go button.
- Check Solver Add-in and click OK.
- In the Data tab, in the Analyze group, you can see the Solver option is added.
How to Use Solver in Excel
In this example, we will try to find the solution for a simple optimization problem.
Problem: Suppose you are the business owner and you want your income to be $8000.
Goal: Calculate the units to be sold and price per unit to achieve the target.
For example, we have created the following model:
- On the Data tab, in the Analysis group, click the Solver button.
- In the set objective, select the income cell and set its value to $8000.
- To Change the variable cell, select the C5, C6, and C10 cells
- Click Solve.
Your data model will change according to the conditions.
28. If-Else
IF function is used to test the condition and return a value if the condition is indeed true and a predetermined different value if it turns out to be false.
29. If-Error
The Excel IFERROR function returns an alternative result when a formula generates an error and an expected result when no error is detected.
For example, Excel returns a divide by zero error when a formula tries to divide a number by 0.
By using the IFERROR function, you can add a message if the formula evaluates to an error.
30. Index and Match
This is an Advanced Excel function. MATCH function is designed to return the position of a value in a specified range, while the INDEX function returns a specific value present in a uni-dimensional range.
The MATCH function returns the position of the ID you are looking for. The INDEX function will return the value of the salary corresponding to the position.
31. Offset Function
The OFFSET function returns a reference to a range of cells that is a specified number of rows and columns from a cell or range of cells.
Example:
Consider the following data:
To reference C4 starting at A1, reference is A1, rows is 3 and cols is 2:
SUM Function With OFFSET
In this example we have a monthly sales data of two years. The goal is to find the sum of sales for a specific month.
The OFFSET function returns a 1×2 range, 8 rows below cell A2, and 1 column right of cell A2. The SUM function then calculates the sum of this range.
Conclusion
Excel is a highly powerful tool for data analysis and reporting. After going through this article, you’ve gained knowledge of essential Excel formulas and functions that can help you work more efficiently. We covered formulas and functions related to numbers, text, dates and times, as well as advanced features. Having a strong grasp of Excel can significantly boost your career prospects.
FAQ's - Your Quick Reference Guide
Basic formulas in Excel include arithmetic operations like addition, subtraction, multiplication, and division—for example, SUM, AVERAGE, COUNT, and PRODUCT.
MS Excel formulas and functions are expressions used to perform calculations or manipulate data in Excel. Formulas start with an equal sign (=) and can contain functions, mathematical operations, cell references, and constants.
To write a formula in Excel, start with an equal sign (=), followed by the formula expression. For example, to add two numbers in cells A1 and B1, write "=A1+B1" in another cell.
VLOOKUP is a function in Excel used to search for a value in the first column of a table range and return a related value from a specified column. It's commonly used for data lookup and retrieval.
An Excel formula is a mathematical expression used to work with values in a selected range of cells. These formulas produce a result, even if it's an error. They allow you to perform calculations like addition, subtraction, multiplication, and division in Excel.