Excel formulas are flexible tools that help users build interconnected spreadsheets, making it easy to perform complex calculations, apply conditional formatting, and organize data accurately and effortlessly. This comprehensive guide features the top 35+ essential Excel formulas along with examples, that are categorized into basic and advanced levels. It will help you start your Excel journey right from scratch!
Table of Contents
Learn everything about Microsoft Excel from scratch from our Tutorial video:
Microsoft Excel is a powerful spreadsheet software by Microsoft that enables users to create, organize, and analyze data in tabular form. Excel’s formula capabilities allow users to manipulate data and generate results, facilitating efficient data management and analysis.
It utilizes formulas, which are expressions comprising numbers, cell references, functions, and operators, to perform calculations and automate tasks within spreadsheets.
Looking for a career in Data Analytics? Enroll in this professional Data Analytics Course and learn from experts.
Excel has a set of formulas and functions that you can use depending on what kind of operation you want to perform on the dataset. Let’s explore some basic Excel formulas that we have categorized in different sections to get started.
Below are some arithmetic formulas, such as SUM, AVERAGE, PRODUCT, QUOTIENT, and POWER, with examples that will enable you to perform many operations within spreadsheets.
1. SUM
The “SUM” function is one of the most fundamental and frequently used Excel formulas. It adds a range of numbers to a worksheet, making it a crucial tool for performing various mathematical and analytical tasks in Excel. The SUM function allows you to quickly calculate the total of a series of values without requiring manual addition.
The basic syntax of the SUM function is:
=SUM(number1, [number2], [number3], ...)
Here is an example of how to use the SUM function in Excel:
Suppose you have the following data in an Excel worksheet–
To calculate the total, you can use the SUM function as follows:
The formula “=SUM(B1:B5)” adds up these values and returns a total of 263.
2. AVERAGE
The “AVERAGE” function is a fundamental and widely used Excel formula that calculates the arithmetic mean of a range of numbers. It helps you find the average value of a set of data points, making it essential for various analytical and statistical tasks in Excel.
The basic syntax of the AVERAGE function is as follows:
=AVERAGE(number1, [number2], [number3], ...)
Here is an example of how to use the AVERAGE function in Excel:
Suppose you have a list of data in the Excel sheet-
To calculate the average of the data, you can use the AVERAGE function as follows:
The formula “=AVERAGE(B1:B5)” calculates the average of these given data. So, the average test score for the given data is 52.6.
3. PRODUCT
The “PRODUCT” function in Excel is a fundamental formula that calculates the product of a range of numbers. It is especially useful when you need to multiply multiple values, making it an essential tool for various mathematical and analytical tasks in Excel.
The basic syntax of the PRODUCT function is as follows:
=PRODUCT(number1, [number2], [number3], ...)
Below is an example of how to use the PRODUCT function in Excel:
Suppose you have a list of amounts in an Excel worksheet like this-
To calculate the total amount, you can use the PRODUCT function as follows:
The formula “=PRODUCT(B1:B5)” multiplies these amounts together and returns the result, 228428640.
4. QUOTIENT
The “QUOTIENT” function in Excel is a formula that calculates the integer quotient of two numbers. It’s particularly useful when you want to find out how many times one number can be evenly divided by another, discarding any remainder. This function returns the largest integer that is less than or equal to the result of dividing two numbers.
The basic syntax of the QUOTIENT function is as follows:
=QUOTIENT(numerator, denominator)
- ‘numerator’ represents the dividend, the number you want to divide.
- ‘denominator’ represents the divisor, the number by which you want to divide the numerator.
Here is an example of how to use the QUOTIENT function in Excel:
Suppose you want to calculate how many times the product of some data can be evenly divided by the sum of that particular data. You can use the QUOTIENT function like this:
The formula “=QUOTIENT(B6, B7)” calculates the amount and returns the result 868549.
5. POWER
The “POWER” function in Excel is a useful formula that calculates a number raised to a specified power. It is commonly used for performing exponential calculations, such as exponentiation and root extraction.
This function is essential for various mathematical and scientific calculations in Excel.
The basic syntax of the POWER function is as follows:
=POWER(number, power)
- ‘number’ is the base number you want to raise to a power.
- ‘power’ is the exponent or the power to which you want to raise the base number.
Below is an example of how to use the POWER function in Excel:
Suppose you want to calculate 5 raised to the power of 2. You can use the POWER function like this:
The formula “=POWER(5, 2)” calculates 5^2 and returns the result 25.
You can also use the POWER function for other exponentiation and root extraction calculations. For instance,
- To calculate the square root of a number, you can use a power of 0.5. For example, to find the square root of 64:
The formula will return the result as 8.
- To calculate the cube root of a number, you can use a power of 1/3. For example, to find the cube root of 27:
The formula will return the result, which is 3.
Check out our blog on Data Analytics tutorial to learn more about Data Analytics!
Here are some logical formulas like IF, AND, OR, and NOT along with examples. These will help you execute various operations within your spreadsheets based on specific conditions or logical evaluations.
6. IF
The “IF” function in Excel is a fundamental logical formula that allows you to perform conditional calculations based on specified criteria. It is a versatile tool for making decisions and performing different calculations depending on whether a given condition is met or not.
The basic syntax of the IF function is as follows:
=IF(logical_test, value_if_true, value_if_false)
- ‘logical_test’ is the condition you want to evaluate. It can be any expression that results in either TRUE or FALSE.
- ‘value_if_true’ is the value or calculation returned if the logical_test evaluates to TRUE.
- ‘value_if_false’ is the value or calculation that is returned if the logical_test evaluates to FALSE.
Below is a simple table with an example of using the IF function in Excel:
In this table, we want to assign grades to students based on their marks. Let’s use the IF function to calculate the grades.
In cell E2, you can enter the following formula to assign grades based on the scores:
This formula checks the score in cell E2 and assigns an appropriate grade based on the following criteria:
If the score is 90 or higher, assign an “A.”
If the score is between 80 and 89, assign a “B.”
If the score is between 70 and 79, assign a “C.”
If the score is between 60 and 69, assign a “D.”
If the score is below 60, assign an “F.”
Drag this formula down from cell E2 to E6 to calculate grades for all the students.
The table will now look like this:
7. AND
The “AND” function in Excel is a logical formula that tests whether all of the specified conditions are true. It returns TRUE if all conditions are met and FALSE if at least one condition is not met. The “AND” function is commonly used in Excel to perform complex logical tests and make decisions based on multiple criteria.
The basic syntax of the “AND” function is as follows:
=AND(condition1, condition2, ...)
- condition1, condition2, and so on are the conditions or expressions that you want to test.
Here is an example of how to use the “AND” function in Excel with a table:
Suppose you have a table of products with the following data:
To identify the products that are both in stock (In Stock is “Yes”) and priced under 50, you can use the “AND” function in Excel.
Here is the formula you can use in a cell:
This formula checks two conditions for each product:
- H2 = “Yes” checks if the “In Stock” status in cell H2 is “Yes.”
- G2 < 50 checks if the price in cell G2 is less than 50.
The “AND” function combines these conditions and returns TRUE if both conditions are met and FALSE if at least one condition is not met.
Now, you can drag this formula to apply the logical test to all the products in your table.
This is how your table will look with the “AND” function applied:
8. OR
The “OR” function in Excel checks whether at least one of the specified conditions is true. It returns TRUE if at least one condition is met, and FALSE if none of the conditions are met. The “OR” function is valuable for performing logical tests and making decisions based on multiple criteria.
The basic syntax of the “OR” function is as follows:
=OR(condition1, condition2, ...)
- condition1, condition2, and so on are the conditions or expressions that you want to test.
Here is an example of how to use the “OR” function in Excel with a table:
Suppose you have a table of job applicants with the following data:
Now, let’s say you want to identify job applicants who either have at least 5 years of experience or have a relevant certification (or both). You can use the “OR” function to create a logical test.
In cell D2, you can enter the following formula:
This formula checks two conditions for each applicant:
- B2 >= 5 checks if the applicant’s years of experience in cell B2 are greater than or equal to 5.
- C2 = TRUE checks if the applicant has a relevant certification in cell C2.
The “OR” function combines these conditions and returns TRUE if at least one condition is met and FALSE if none of the conditions are met.
You can drag this formula to apply the logical test to all the applicants in your table.
Here is how your revised table will look with the “OR” function applied:
9. NOT
The “NOT” function in Excel negates a given condition or expression. It returns TRUE if the condition is FALSE, and it returns FALSE if the condition is TRUE. The “NOT” function is a valuable tool for reversing logical values and performing logical tests where you need to check the opposite of a condition.
The basic syntax of the “NOT” function is as follows:
=NOT(logical_value)
- logical_value is the condition or expression that you want to negate. It can be any expression that results in either TRUE or FALSE.
Here is an example of how to use the “NOT” function in Excel with a table:
Suppose you have a table of students with the following data:
Now, let’s say you want to identify the students who are not enrolled (Is Enrolled is FALSE). You can use the “NOT” function to create a logical test.
In cell C2, you can enter the following formula:
This formula reverses the condition in cell B2, which checks if the student is enrolled. The “NOT” function returns TRUE if the student is not enrolled (B2 is FALSE) and FALSE if the student is enrolled (B2 is TRUE).
You can drag this formula down to apply the logical test to all the students in your table.
With the “NOT” function applied, your table will now look like this:
Add a certificate of Advanced Microsoft Excel to your resume if you want to stand out from the rest!
Below are some lookup and reference formulas like VLOOKUP, HLOOKUP, INDEX, and MATCH, with examples. They can help you find and match data in your spreadsheet, making it easier to locate and use specific information.
10. VLOOKUP
The “VLOOKUP” function in Excel is a powerful and commonly used lookup and reference formula. It allows you to search for a specific value in a table, and when found, retrieve related information from that table. “VLOOKUP” stands for “Vertical Lookup,” as it searches for values in the leftmost (first) column of a table and returns corresponding values from a specified column.
The basic syntax of the “VLOOKUP” function is as follows:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value is the value you want to find in the first column of the table.
- table_array is the range of cells that represents the table containing the data.
- col_index_num is the column number from which you want to retrieve data when a match is found in the first column of the table.
- [range_lookup] is an optional argument that determines whether you want an exact match (FALSE) or an approximate match (TRUE or omitted). Use FALSE for exact matches and TRUE for approximate matches (e.g., when working with sorted data).
Here is an example of how to use the “VLOOKUP” function in Excel:
Suppose you have a table that contains information about employees and their departments, like this:
Now, let’s say you want to find the department of a specific employee based on their Employee ID. You can use the “VLOOKUP” function to do this.
Assuming the Employee ID you want to look up is in cell E2, you can use the following formula in cell F2:
In this formula:
- E2 contains the Employee ID you want to look up.
- A2:C6 is the range that represents the entire table, with the Employee ID in the leftmost column.
- 3 specifies that you want to retrieve data from the third column (Department) when a match is found.
- FALSE is used to indicate that you want an exact match.
- The “VLOOKUP” function will search for the Employee ID in the first column of the table, and when it finds a match, it will return the corresponding department.
Below is how the table might look with the “VLOOKUP” function applied:
11. HLOOKUP
The “HLOOKUP” function in Excel is similar to “VLOOKUP,” but it searches for a specific value in the first row of a table and retrieves related information from a specified row.
“HLOOKUP” stands for “Horizontal Lookup,” as it searches for values horizontally across the first row of a table and returns corresponding values from a specified row.
The basic syntax of the “HLOOKUP” function is as follows:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- lookup_value is the value you want to find in the first row of the table.
- table_array is the range of cells that represents the table containing the data.
- row_index_num is the row number from which you want to retrieve data when a match is found in the first row of the table.
- [range_lookup] is an optional argument that determines whether you want an exact match (FALSE) or an approximate match (TRUE or omitted). Use FALSE for exact matches and TRUE for approximate matches (e.g., when working with sorted data).
Here is an example of how to use the “HLOOKUP” function in Excel with a table:
Suppose you have a table that contains information about products and their prices, like this-
Now, let’s say you want to find the price of a specific product based on its name. You can use the “HLOOKUP” function to do this.
Assuming the product name you want to look up is in cell G2, and you want to retrieve the price from the table, you can use the following formula in cell H2:
In this formula:
- G2 contains the product name you want to look up.
- A1:E2 is the range that represents the entire table, with product names in the first row.
- 2 specifies that you want to retrieve data from the second row (Price) when a match is found.
- FALSE is used to indicate that you want an exact match.
- The “HLOOKUP” function will search for the product name in the first row of the table, and when it finds a match, it will return the corresponding price.
Here is how the table will look with the “HLOOKUP” function applied:
12. INDEX
The “INDEX” function in Excel allows you to retrieve data from a specific cell within a table or range based on row and column numbers. It provides a flexible way to access data from different locations within a dataset. The “INDEX” function is particularly useful when you need to extract information from a multi-dimensional table or array.
The basic syntax of the “INDEX” function is as follows:
=INDEX(array, row_num, [column_num])
- array is the range or array from which you want to retrieve data.
- row_num is the row number within the array from which you want to retrieve data.
- [column_num] (optional) is the column number within the array from which you want to retrieve data. If omitted, Excel assumes that you want to retrieve data from the specified row only.
Here is an example of how to use the “INDEX” function in Excel with a table:
Suppose you have a table of products and their corresponding prices, like this-
Now, let’s say you want to use the “INDEX” function to retrieve the price of “C.”
You can use the following formula in a cell:
In this formula:
- B2:B5 is the range that contains the prices of the products.
- MATCH(“C”, A2:A5, 0) searches for “C” in the range A2:A5 (which contains the product names) and returns the position of the match.
The “INDEX” function takes the position (row number) returned by the “MATCH” function and retrieves the corresponding price from the range B2:B5.
As a result, the formula returns the price of “C,” which is 12, as shown in the given table.
Get 100% Hike!
Master Most in Demand Skills Now !
13. MATCH
The “MATCH” function in Excel is a powerful lookup and reference formula that allows you to find the relative position of a specific value within a range or an array. It returns the position (row number or column number) of the first occurrence of the lookup value.
The “MATCH” function is particularly useful when you need to locate items within lists or arrays and use their positions for various calculations or lookups.
The basic syntax of the “MATCH” function is as follows:
=MATCH(lookup_value, lookup_array, [match_type])
- lookup_value is the value you want to find within the lookup array.
- lookup_array is the range or array in which you want to search for the lookup value.
Below is an example of how to use the “MATCH” function in Excel with a table:
Suppose you have a list of product names and their corresponding prices, like this-
Now, if you want to use the “MATCH” function to find the position (row number) of “Product D” in the list of product names.
You can use the following formula in a cell:
In this formula:
- “Product D” is the lookup value you want to find.
- A2:A5 is the range (lookup_array) where you want to search for the product “Product D”
- 0 is specified as the match_type to find an exact match (since the data is not sorted).
- The “MATCH” function will search for “Product D” in the range A2:A5 and return the position (row number) of the first occurrence of “Product D,” which is 4, as shown below.
Check out Intellipaat’s guide on MS Excel Interview Questions to crack good interviews for data analysis jobs.
Text Manipulation Formulas
Below we will discuss some text manipulation formulas, including LEFT, LEN, MID, and CONCATENATE, along with examples. These formulas can assist you in manipulating and extracting specific text from your spreadsheet, simplifying the process of working with textual information.
14. LEFT
The “LEFT” function in Excel is a text manipulation formula that allows you to extract a specified number of characters from the beginning (left-hand side) of a text string. It is useful when you need to isolate a portion of text from a larger cell or when you want to split a text string based on a delimiter.
The basic syntax of the “LEFT” function is as follows:
=LEFT(text, num_chars)
- text is the text string from which you want to extract characters.
- num_chars is the number of characters you want to extract from the left side of the text string.
Below is an example of how to use the “LEFT” function in Excel with a table:
Suppose you have a list of email addresses in a column, and you want to extract the first 5 characters (the username) from each email address:
You can use the “LEFT” function to extract the usernames. In cell B2, you can enter the following formula:
In this formula:
- A2 contains the email address from which you want to extract the username.
- 5 specifies that you want to extract the first 5 characters.
- The “LEFT” function will take the text from cell A2 and extract the first 5 characters (the username) from the left side.
As a result, the formula returns “Ravin.” Then drag the formula down to apply it to the other email addresses in the list.
The final table will look like this:
15. LEN
The “LEN” function in Excel allows you to calculate the length (the number of characters) of a text string. It’s a straightforward and useful function when you need to determine the length of text data, such as strings, sentences, or cell contents. The “LEN” function can be especially valuable for tasks like validating data length or extracting portions of text based on their length.
The basic syntax of the “LEN” function is as follows:
=LEN(text)
text is the text string for which you want to calculate the length.
Here’s an example of how to use the “LEN” function in Excel with a table:
Suppose you have a list of Intellipaat’s benefits in a column, and you want to determine the length (number of characters) of each description:
You can use the “LEN” function to calculate the length of each product description. In cell B2, you can enter the following formula:
In this formula:
- A2 contains the benefits of Intellipaat, for which you want to calculate the length.
- The “LEN” function will take the text from cell A2 and calculate the length of the text string, including spaces and characters.
As a result, the formula returns the length of the benefits in cell B2. Now drag the value to find the result of each row.
Below is the result of the table with the “LEN” function applied:
16. MID
The “MID” function in Excel allows you to extract a specified number of characters from a text string, starting from a specific position within the string.
MID is useful when you need to isolate a portion of text from a larger cell, especially when the position and length of the desired text segment are known.
The basic syntax of the “MID” function is as follows:
=MID(text, start_num, num_chars)
- text is the text string from which you want to extract characters.
- start_num is the position within the text string where you want to start the extraction.
- num_chars is the number of characters you want to extract starting from the specified position.
Here is an example of how to use the “MID” function in Excel with a table:
Suppose you have a list of phone numbers in a column, and you want to extract the area codes from each phone number:
You can use the “MID” function to extract the area codes from the phone numbers. In cell B2, you can enter the following formula:
In this formula:
- A2 contains the phone number from which you want to extract the area code.
- 1 specifies that you want to start the extraction from the 1st character (the opening parenthesis).
- 3 specifies that you want to extract 3 characters (the area code).
As a result, the formula returns the area code in cell B2, and here is the table on how it will look:
17. CONCATENATE
The “CONCATENATE” function in Excel allows you to combine or join multiple text strings into a single text string.
It is helpful when you need to merge data from different cells or add separators between text elements to create a coherent text output.
The basic syntax of the “CONCATENATE” function is as follows:
=CONCATENATE(text1, [text2], ...)
text1, text2, and so on are the text strings you want to concatenate. You can provide multiple text strings as arguments, separated by commas.
Below is an example of how to use the “CONCATENATE” function in Excel with a table:
Suppose you have a table of names, and you want to create a full name by concatenating the first name and last name-
You can use the “CONCATENATE” function to create the full names. In cell C2, you can enter the following formula:
In this formula:
- A2 contains the first name.
- ” ” (a space within double quotes) is used as a separator between the first name and last name.
- B2 contains the last name.
The formula returns the full name in cell C2, and below is how the table will look with the “CONCATENATE” function applied:
Want to know more about the field of data analytics? Enroll in the professional Data Analytics Course in Bangalore and become an expert!
Now we will explore the date and time formulas- TODAY, DATE, TIME, MONTH, YEAR, and DATEVALUE, along with practical examples. These functions can help in handling date-related data within spreadsheets. It simplifies tasks such as extracting specific time-related details or performing calculations based on dates.
18. TODAY
The “TODAY” function in Excel is a Date and Time formula that allows you to insert the current date into a cell.
This function is useful for tasks that require you to work with the current date, such as tracking deadlines or managing schedules.
The basic syntax of the “TODAY” function is as follows:
=TODAY()
Below is an example of how to use the “TODAY” function in Excel with a table:
Suppose you have a table where you want to track the tasks that are due for today-
You can use the “TODAY” function to automatically insert the current date as the due date for each task. In cell B2, you can enter the following formula:
The formula returns the due date in cell B2, and below is how the table will look with the “TODAY” function applied:
19. DATE
The “DATE” function in Excel allows you to create a valid date by specifying the year, month, and day as arguments. This function is useful when you need to work with specific dates, calculate time intervals, or perform date-related calculations in Excel.
The basic syntax of the “DATE” function is as follows:
=DATE(year, month, day)
Here is an example of how to use the “DATE” function in Excel with a table:
Suppose you have a table where you want to calculate the date for specific events based on the year, month, and day components:
Now, In cell E2, you can enter the following formula:
The formula returns the calculated date in cell E2. Drag down and select the result to find out the remaining results.
Here is how the table might look with the “DATE” function applied:
20. TIME
The “TIME” function in Excel allows you to create a valid time value by specifying the hours, minutes, and seconds as arguments.
This function is useful when you need to work with specific times, calculate time intervals, or perform time-related calculations in Excel.
The basic syntax of the “TIME” function is as follows:
=TIME(hour, minute, second)
Below is an example of how to use the “TIME” function in Excel with a table:
Assume you have a table where you want to calculate the time for specific events based on the hour, minute, and second components:
You can use the “TIME” function to calculate the times for these activities based on the provided hour, minute, and second components.
In cell E2, you need to enter the following formula for identifying the time:
The “TIME” function will take the values from cells B2, C2, and D2 and create a valid time value based on the given data in the table.
The formula returns the calculated time in cell E2, and then drags for the remaining values.
Here is how the table will look with the “TIME” function applied:
21. MONTH
The “MONTH” function in Excel is a Date and Time formula that allows you to extract the month component from a date.
It is helpful when you need to work with dates and want to retrieve the month part for analysis, calculations, or formatting.
The basic syntax of the “MONTH” function is as follows:
=MONTH(serial_number)
Here is an example of how to use the “MONTH” function in Excel with a table:
Suppose you have a table with dates, and you want to extract the month from each date:
You can use the “MONTH” function to extract the month from each date. In cell B2, you can enter the following formula:
The “MONTH” function will take the date from cell A2 and extract the month component.
Below is the result that the formula will return in cell B2:
22. YEAR
The “YEAR” function in Excel allows you to extract the year component from a date. It’s useful when you need to work with dates and want to retrieve the year part for analysis, calculations, or formatting.
The basic syntax of the “YEAR” function is as follows:
=YEAR(serial_number)
Here is an example of how to use the “YEAR” function in Excel with a table:
Suppose you have a table with dates, and you want to extract the year from each date:
You can use the “YEAR” function to extract the year from each date. In cell B2, you can enter the following formula:
The “YEAR” function will take the date from cell A2 and extract the year component. As a result, the formula returns the year as a number in cell B2.
Now drag the result, and here is how the table will look with the “YEAR” function applied:
23. DATEVALUE
The “DATEVALUE” function in Excel allows you to convert a text representation of a date into a valid Excel date value.
It is useful when you have date information stored as text in your Excel worksheet and need to convert it into proper date values for calculations, sorting, and other date-related operations.
The basic syntax of the “DATEVALUE” function is as follows:
=DATEVALUE(date_text)
Below is an example of how to use the “DATEVALUE” function in Excel with a table:
Suppose you have a table with dates stored as text, and you want to convert these text-based dates into valid Excel date values:
You can use the “DATEVALUE” function to convert the text-based dates into Excel date values. In cell B2, you can enter the following formula:
In this formula:
- A2 contains the text-based date you want to convert.
- The “DATEVALUE” function will take the text from cell A2 and convert it into a valid Excel date value.
- Since Microsoft Excel stores dates since January 1, 1900, as a result, the formula returns the date as a serial number in cell B2.
Here is how the table will look after dragging the result throughout the cell, with the “DATEVALUE” function applied:
Here are some financial formulas like PV, FV, PMT, RATE, and NPER, along with examples. They help with different money calculations in spreadsheets, like figuring out present or future values, payments, interest rates, and periods.
24. PV
The “PV” function in Excel calculates the present value of an investment or a series of future cash flows.
This function is commonly used in finance to evaluate the value of investments, loans, and other financial transactions.
The basic syntax of the “PV” function is as follows:
=PV(rate, nper, pmt, [fv], [type])
Below is an example of how to use the “PV” function in Excel with a table:
Suppose you want to calculate the present value of receiving 1000 per year for 5 years at an annual interest rate of 5%. The future payments are due at the end of each year.
You can use the “PV” function to calculate the present value. In cell D2, you can enter the following formula:
The “PV” function will calculate the present value based on the provided interest rate, number of years, and payment per year. As a result, the formula returns the present value in cell D2.
With the “PV” function applied, the table will display the following result:
25. FV
The “FV” function in Excel is a financial formula that calculates the future value of an investment or a series of future cash flows.
This function is commonly used in finance to assess the future worth of investments, savings, or loans.
The basic syntax of the “FV” function is as follows:
=FV(rate, nper, pmt, [pv], [type])
Below is an example of how to use the “FV” function in Excel with a table:
Suppose you want to calculate the future value of an investment where you deposit 1000 into a savings account with an annual interest rate of 5%, and you plan to make an additional 500 deposit at the end of each year for 5 years.
You can use the “FV” function to calculate the future value. In cell E2, you can enter the following formula:
The “FV” function will calculate the future value based on the provided interest rate, number of years, payment per year, and initial deposit.
So, the formula returns the future value in cell E2, and here is how the table will look:
26. PMT
The “PMT” function in Excel calculates the periodic payment amount required to pay off a loan or reach a specific savings goal.
PMT is commonly used in finance to determine how much you need to pay or save regularly to achieve a financial objective.
The basic syntax of the “PMT” function is as follows:
=PMT(rate, nper, pv, [fv], [type])
Below is an example of how to use the “PMT” function in Excel with a table:
Suppose you want to calculate the monthly payment amount for a 3-year loan of $20,000 with an annual interest rate of 5%. The future value is 0 because you want to fully pay off the loan.
You can use the “PMT” function to calculate the monthly payment amount. In cell D2, you can enter the following formula:
The “PMT” function will calculate the monthly payment amount based on the provided interest rate, loan term, and loan amount.
Here is what the formula returns in cell D2 about the result of monthly payment:
27. RATE
The “RATE” function in Excel calculates the interest rate for a loan or investment based on a series of periodic payments, the present value, and the future value. It is commonly used in finance to determine the interest rate associated with a loan or the rate of return on an investment.
The basic syntax of the “RATE” function is as follows:
=RATE(nper, pmt, pv, [fv], [type], [guess])
Here is an example of how to use the “RATE” function in Excel with a table:
Suppose you want to find out the interest rate associated with a loan of $15,000 that will be paid off in 3 years with monthly payments of $500 each.
You can use the “RATE” function to calculate the interest rate. In cell D2, you can enter the following formula:
=RATE(C2*12, -B2, A2)
The “RATE” function will calculate the interest rate based on the provided number of payment periods, monthly payment amount, and loan amount. As a result, the formula returns the interest rate in cell D2.
Here’s how the table might look with the “RATE” function applied:
28. NPER
The “NPER” function in Excel is a financial formula that helps you calculate the number of payment periods required to pay off a loan or reach a savings goal.
It is commonly used in finance to determine how long it will take to repay a loan or achieve a specific savings target based on regular payments and interest rates.
The basic syntax of the “NPER” function is as follows:
=NPER(rate, pmt, pv, [fv], [type])
Here’s an example of how to use the “NPER” function in Excel with a table:
Suppose you want to find out how long it will take to pay off a $10,000 loan with an annual interest rate of 6% by making monthly payments of $200 each.
You can use the “NPER” function to calculate the number of months required to pay off the loan. In cell D2, you can enter the following formula:
=NPER(B2/12, -C2, A2)
The “NPER” function will calculate the number of months required to pay off the loan based on the provided interest rate, monthly payment, and loan amount. As a result, the formula returns the number of months in cell D2.
Prepare yourself for the industry by going through these Data Analyst Interview Questions now!
Statistical formulas like COUNT, MIN, MAX, and STDDEV offer crucial functions in spreadsheets. They help in counting, finding the smallest or largest values, and calculating the standard deviation, enhancing data analysis capabilities.
29. COUNT
The “COUNT” function in Excel is a statistical formula that allows you to count the number of cells within a specified range that contains numeric values or numbers.
This function is often used for data analysis, especially when working with large datasets.
The basic syntax of the “COUNT” function is as follows:
=COUNT(range)
Below is an example of how to use the “COUNT” function in Excel with a table:
Suppose you have a table that represents the scores of students on a test, and you want to count the number of students who scored above 70.
Here is a table with student names in column A and their test scores in column B:
To count the number of students who scored above 70, you can use the COUNT function. In an empty cell, you can enter the following formula:
Below is the table in which the “COUNT” function will return the result:
30. MIN
The “MIN” function in Excel allows you to find the minimum value within a specified range of numeric values.
It is a fundamental function for data analysis and is often used to identify the lowest value in a dataset.
The basic syntax of the “MIN” function is as follows:
=MIN(range)
Below is an example of how to use the “MIN” function in Excel with a table:
Suppose you have a table that represents the prices of products in a store, and you want to find the product with the lowest price.
Here is a table with product names in column A and their corresponding prices in column B:
To find the product with the lowest price, you can use the MIN function. In an empty cell, you can enter the following formula:
Below is the result that the formula will display:
31. MAX
The “MAX” function in Excel is a statistical formula that allows you to find the maximum value within a specified range of numeric values.
It is a fundamental function for data analysis and is often used to identify the highest value in a dataset.
The basic syntax of the “MAX” function is as follows:
=MAX(range)
Below is an example of how to use the “MAX” function in Excel with a table:
Suppose you have a table that represents the prices of products in a store, and you want to find the product with the highest revenue price.
Here is a table with product names in column A and their corresponding prices in column B:
To find the product with the higher price, you can use the MAX function. In an empty cell, you can enter the following formula:
So, below is the result of the table with the MAX function applied:
32. STDDEV
The “STDEV” (Standard Deviation) function in Excel is a statistical formula that calculates the standard deviation of a dataset.
It provides valuable insights into the variability or dispersion of data points within a dataset.
The basic syntax of the “STDEV” function is as follows:
=STDEV(range)
Here is an example of how to use the “STDEV” function in Excel with a table:
Suppose you have a table that represents the daily temperatures recorded over a month, and you want to calculate the standard deviation of these temperatures to understand how much they vary from the average temperature.
Below is a table with the dates of the whole month of October 2023, in column A and the corresponding temperatures in column B:
To calculate the standard deviation of the temperatures, you can use the “STDEV” function. You need to enter the following formula in an empty cell:
When you press Enter, the STDEV function will calculate the standard deviation of the temperatures in the specified range and return the result. The result represents the degree of variation or dispersion in the daily temperatures.
Array formulas like SUMPRODUCT, TRANSPOSE, FREQUENCY, and TREND offer diverse functionalities within spreadsheets. They allow users to perform advanced operations, such as calculating sums of products, flipping array orientations, generating frequency distributions, and fitting data to linear trends.
33. SUMPRODUCT
The “SUMPRODUCT” function in Excel is a powerful array formula that allows you to multiply corresponding elements in multiple arrays, sum the products, and return the result.
It is commonly used for a variety of tasks, including calculating weighted sums, performing conditional calculations, and summarizing data from multiple arrays.
The basic syntax of the “SUMPRODUCT” function is as follows:
=SUMPRODUCT(array1, [array2], [array3], ...)
Below is an example of how to use the “SUMPRODUCT” function in Excel with a table:
Suppose you have a table that represents the sales of different products in two different regions, and you want to calculate the total sales for each region.
Here is a table with product names in column A, sales in Region 1 in column B, and sales in Region 2 in column C:
To calculate the total sales for each region, you can use the SUMPRODUCT function. In an empty cell, you can enter the following formula for Region 1:
And for Region 2:
So now, the SUMPRODUCT function will multiply the corresponding sales values in the specified range and return the sum of the products. This gives you the total sales for each region.
Below is the result of the formula that we applied to find the total sales for both regions:
34. TRANSPOSE
The “TRANSPOSE” function in Excel is an array formula that allows you to change the orientation of a range or an array of values. It effectively flips rows into columns and columns into rows, making it useful for reorganizing data and creating summary tables.
The basic syntax of the “TRANSPOSE” function is as follows:
=TRANSPOSE(original_range)
Below are the steps on how to use the “TRANSPOSE” function:
Suppose you have a table with sales data organized by products in rows and months in columns, and you want to transpose it to have months in rows and products in columns for easier analysis.
Original table:
To transpose this table, you just need to:
- Select a range that has the same number of cells as the original data but in the opposite orientation. In this case, you would select a 3×3 range to accommodate the three months and three products.
Enter the following formula:
Instead of pressing Enter, press Ctrl+Shift+Enter. Excel will transpose the data, and you will see the months in rows and products in columns:
After applying the “TRANSPOSE” function, the table will display:
35. FREQUENCY
The “FREQUENCY” function in Excel is an array formula that allows you to calculate the frequency distribution of a set of values. It helps you determine how many times values within a specified range occur within certain bins or intervals.
This function is beneficial when you want to analyze data distribution, create histograms, or segment data into categories.
Following are the key steps on how to use the “FREQUENCY” function:
- Start by setting up bins or intervals in which you want to count the occurrences of values. These bins should be in ascending order.
- Select a range of cells where you want to display the frequency distribution results. Ensure that this range has one more cell than the number of bins, as Excel will include a bin for values greater than the last specified bin.
The basic syntax of the “FREQUENCY” function is as follows:
=FREQUENCY(data_range, bins_range)
Here is an example of how to use the “FREQUENCY” function with a table:
Suppose you have a table that represents the test scores of students, and you want to calculate the frequency distribution of scores in specific score ranges or bins.
Below is the table with student and test scores in columns A and B respectively:
Now, you want to create bins for test scores in the following ranges: 0-49, 50-59, 60-69, 70-79, 80-89, and 90-100.
Set up the bins in a separate column, for example, in column C:
Enter the following formula as an array formula in cell D2:
- Instead of pressing Enter, press Ctrl+Shift+Enter.
Excel will calculate the frequency distribution of test scores based on the specified bins and populate the selected range with the results:
Frequency distribution table:
36. TREND
The “TREND” function in Excel is an array formula used to predict future values based on a linear trend of existing data points.
It is particularly useful when you have a series of data points and want to extend the trend to forecast future values or interpolate missing data points.
The basic syntax of the “TREND” function is as follows:
=TREND(known_y’s, [known_x’s], [new_x’s], [const])
- known_y’s is an array or range containing the dependent variable data points (the y-values).
- [known_x’s] is an optional array or range containing the independent variable data points (the x-values). This is used when you have specific x-values corresponding to the known y-values.
- [new_x’s] is an optional array or range containing the x-values for which you want to predict y-values (future or missing data points).
- [const] is an optional argument that, when set to TRUE or omitted, forces the intercept of the trend line to be zero. When set to FALSE, it allows for a non-zero intercept.
Below is an example of how to use the “TREND” function in Excel with a table:
Suppose you have a table that represents the sales of a product over several months, and you want to predict the sales for the next three months based on the existing data.
Suppose the month and sales data in the table are like this:
You want to predict the sales for July, August, and September.
- Set up a range of x-values for the known months (Jan to Jun) and a range for the known sales figures (B2:B7).
In a new cell, enter the following formula to predict the sales for the next three months (Jul to Sep):
=TREND(B2:B7, A2:A7, {“Jul”,”Aug”,”Sep”})
Excel will calculate the linear trend and provide the predicted sales values for the months of July, August, and September based on the existing data.
The result will look like this:
Conclusion
Excel is a powerful tool for data analysis and reporting. It offers a wide range of formulas and functions to streamline tasks. From basic arithmetic and logical operations to advanced financial and statistical analyses, Excel provides professionals with essential skills for various industries. Proficiency in these formulas enhances productivity and supports informed decision-making.
Having good knowledge is not only advantageous but also a valuable career asset. These Excel formulas and functions that we covered in this blog can definitely empower you to shape and advance your professional journey.
Visit Intellipaat’s community page to post any queries!