Excel 365 Office Top Powerful formulas (Part1)
Excel 365 is a powerful tool for data analysis and manipulation, offering a wide range of formulas that can simplify complex calculations and automate repetitive tasks.
In this blog post, we will explore some of the best formulas available in Excel 365, along with practical examples to help you understand their application and potential.
Whether you’re a beginner or an experienced Excel user, this comprehensive guide will enhance your knowledge and make you more efficient in Excel 365.
VLOOKUP Formula:
Example: Using VLOOKUP to retrieve data from a different sheet in Excel 365.
IF Formula:
Example: Utilizing the IF function to apply conditional formatting based on specific criteria in Excel 365.
SUMIF Formula:
Example: Calculating the sum of values in a range based on a given condition using the SUMIF formula in Excel 365.
INDEX and MATCH Formula:
Example: Using INDEX and MATCH together to perform advanced lookup operations in Excel 365.
CONCATENATE Formula:
Example: Concatenating text and values from different cells using the CONCATENATE formula in Excel 365.
AVERAGE Formula:
Example: Finding the average of a range of values using the AVERAGE formula in Excel 365.
COUNTIF Formula:
Example: Counting the number of cells that meet specific criteria using the COUNTIF formula in Excel 365.
LEFT, RIGHT, and MID Formulas:
Example: Extracting text from cells using LEFT, RIGHT, and MID formulas in Excel 365.
SUMPRODUCT Formula:
Example: Performing complex calculations involving multiple arrays using the SUMPRODUCT formula in Excel 365.
NETWORKDAYS Formula:
Example: Calculating the number of working days between two dates using the NETWORKDAYS formula in Excel 365.
VLOOKUP Formula: with Example
The VLOOKUP formula is a powerful function in spreadsheet programs like Microsoft Excel and Google Sheets. It allows you to search for a specific value in a range of cells and retrieve a corresponding value from another column. The syntax for the VLOOKUP function is as follows:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Let’s break down each part of the formula:
lookup_value: This is the value you want to search for in the first column of the range.
table_array: This is the range of cells that contains the data you want to search in. The first column of the range should contain the lookup values, and the value you want to retrieve should be in a column to the right of the lookup column.
col_index_num: This is the column number within the table_array from which you want to retrieve the corresponding value. The first column is 1, the second column is 2, and so on.
range_lookup: (optional) This parameter determines whether you want an exact match or an approximate match. If set to TRUE or omitted, an approximate match will be performed (the data in the first column of the table_array should be sorted in ascending order). If set to FALSE, an exact match will be performed.
Now, let’s illustrate the VLOOKUP formula with an example. Suppose we have the following table:
A B
1 Fruit Price
2 Apple $1.00
3 Banana $0.50
4 Orange $0.75
5 Pear $1.25
We want to find the price of a banana. We can use the VLOOKUP function to achieve this. Assuming the table is in cells A1:B5, the formula would be:
=VLOOKUP(“Banana”, A1:B5, 2, FALSE)
In this example, the lookup value is “Banana,” the table array is A1:B5, we want to retrieve the value from the second column (which contains prices), and we set range_lookup to FALSE for an exact match. The formula will return “$0.50” as the result, which is the price of a banana.
IF Formula: with example
The IF formula is a conditional function used in spreadsheet programs, such as Microsoft Excel or Google Sheets, to perform different calculations or actions based on a specified condition. Its basic syntax is as follows:
=IF(condition, value_if_true, value_if_false)
Here’s an example to demonstrate how the IF formula works. Let’s say we have a column of numbers in column A, and we want to calculate a corresponding column B that indicates whether each number is greater than 5. If the number is greater than 5, we want to display “Yes” in column B; otherwise, we want to display “No”.
A B
3 No
8 Yes
6 Yes
2 No
9 Yes
To achieve this, we can use the IF formula in cell B1 and then drag it down to fill the rest of column B. In cell B1, we would write the following formula:
=IF(A1>5, “Yes”, “No”)
This formula checks the condition A1>5. If it is true (i.e., the number in cell A1 is greater than 5), it returns the value “Yes”; otherwise, it returns the value “No”. By dragging the formula down, it adjusts the cell references accordingly, and we get the desired output in column B.
Note that the IF formula can be nested to handle more complex conditions and multiple possible outcomes.
SUMIF Formula: with Example
The SUMIF formula is a powerful function in spreadsheet software, such as Microsoft Excel or Google Sheets, that allows you to sum values based on specific criteria. The syntax for the SUMIF formula is as follows:
SUMIF(range, criteria, [sum_range])
Here’s a breakdown of each parameter:
range: This is the range of cells that you want to evaluate against the given criteria. It could be a single column or row, or a range of cells.
criteria: This is the condition or criteria that you want to apply to the range. It can be a number, text, logical expression, or cell reference.
sum_range (optional): This is the range of cells that you want to sum if the corresponding cells in the range meet the criteria. If omitted, the range is used as the sum_range (i.e., the values in the range are summed).
Now, let’s look at an example to illustrate the usage of the SUMIF formula. Suppose you have a spreadsheet with the following data:
A B
Apples 10
Oranges 15
Apples 20
Bananas 5
Oranges 10
If you want to sum the values in column B for all the rows where the corresponding value in column A is “Apples,” you can use the SUMIF formula:
=SUMIF(A:A, “Apples”, B:B)
The formula above will sum all the values in column B where the corresponding cells in column A contain the text “Apples.” In this case, it will return the result of 30 (10 + 20).
You can also use cell references for the criteria. For example, if you have the value “Apples” in cell D1, you can modify the formula as follows:
=SUMIF(A:A, D1, B:B)
The formula above will sum all the values in column B where the corresponding cells in column A contain the text “Apples.” In this case, it will return the result of 30 (10 + 20).
You can also use cell references for the criteria. For example, if you have the value “Apples” in cell D1, you can modify the formula as follows:
=SUMIF(A:A, D1, B:B)
The formula above will sum all the values in column B where the corresponding cells in column A contain the text “Apples.” In this case, it will return the result of 30 (10 + 20).
You can also use cell references for the criteria. For example, if you have the value “Apples” in cell D1, you can modify the formula as follows:
=SUMIF(A:A, D1, B:B)
Make sure to adjust the ranges (A:A and B:B) based on the location of your actual data.
That’s how you can use the SUMIF formula to sum values based on specific criteria in a spreadsheet application.
INDEX and MATCH Formula: with example?
The INDEX and MATCH functions are commonly used together in Excel to perform advanced lookups. The INDEX function returns a value from a specific cell within a given range, and the MATCH function searches for a specified value within a range and returns its relative position.
Here’s an example to illustrate how the INDEX and MATCH formula works:
Let’s say you have a table that contains student names in column A, their corresponding student IDs in column B, and their test scores in column C. You want to find the test score of a specific student based on their name.
Here’s how you can use the INDEX and MATCH formula to achieve this:
Set up your data in columns A, B, and C
A B C
1 Student Name Student ID Test Score
2 John 001 85
3 Sarah 002 92
4 Michael 003 78
5 Emily 004 90
- In another cell, let’s say cell E2, enter the student name for whom you want to retrieve the test score. For example, “Sarah.”
- In a separate cell, let’s say cell F2, use the INDEX and MATCH formula to find the test score of the student.
INDEX(C2:C5, MATCH(E2, A2:A5, 0))
The MATCH function searches for the value in cell E2 (the student name “Sarah”) within the range A2:A5 (the student names) and returns the relative position of the match. The 0 at the end indicates an exact match.
The INDEX function then uses the returned position from the MATCH function to retrieve the corresponding test score from the range C2:C5.
- After entering the formula in cell F2, you should see the test score for the student “Sarah” displayed.
Sarah’s test score: 92
The combination of the INDEX and MATCH formula allows you to dynamically search and retrieve data from a table based on specific criteria. You can modify the formula as needed for different lookup scenarios.
CONCATENATE Formula: with example
The CONCATENATE function in Excel is used to combine multiple strings into one string. It allows you to join text from different cells or add custom text within a formula. Here’s an example to demonstrate how the CONCATENATE formula works:
Let’s say you have a table with employee information, including their first name in column A and last name in column B. You want to create a formula to concatenate the first name and last name into a single cell.
Here’s how you can use the CONCATENATE formula to achieve this:
Set up your data in columns A and B
A B
1 First Name Last Name
2 John Smith
3 Sarah Johnson
4 Michael Brown
- In a separate cell, let’s say cell C2, enter the CONCATENATE formula to combine the first name and last name.
=CONCATENATE(A2, ” “, B2)
The CONCATENATE function joins the values in cell A2 (the first name) and cell B2 (the last name) together. The space within quotation marks ” ” is added to separate the first name and last name with a space.
- After entering the formula in cell C2, you should see the concatenated full name displayed.
Full Name: John Smith
The CONCATENATE function can be used to combine text from multiple cells or even include additional custom text. For example, you can add a comma or a hyphen between the first name and last name. Here’s an example of modifying the CONCATENATE formula:
=CONCATENATE(A2, “, “, B2)
This formula will result in the full name with a comma and space:
Full Name: John, Smith
In addition to the CONCATENATE function, Excel also provides an alternative concatenation operator, the ampersand (&). You can achieve the same result using the ampersand operator instead of the CONCATENATE function:
=A2 & ” ” & B2
This formula will give you the same output as the previous CONCATENATE formula:
Both the CONCATENATE function and the ampersand operator allow you to combine text in Excel based on your specific requirements.
AVERAGE Formula: with example
The AVERAGE formula is a mathematical function commonly used in spreadsheets and programming languages to calculate the average value of a set of numbers. It adds up all the numbers in the set and divides the sum by the count of numbers.
The general syntax of the AVERAGE formula is as follows:
AVERAGE(number1, number2, …)
Here’s an example to illustrate how the AVERAGE formula works. Let’s say we have a set of numbers: 5, 10, 15, 20, and 25. We want to calculate the average of these numbers using the AVERAGE formula.
Using the AVERAGE formula, we would write:
AVERAGE(5, 10, 15, 20, 25)
The formula would then calculate the average by adding up all the numbers:
5 + 10 + 15 + 20 + 25 = 75
Next, it divides the sum by the count of numbers in the set, which in this case is 5:
75 / 5 = 15
Therefore, the average of the set of numbers 5, 10, 15, 20, and 25 is 15.
You can use the AVERAGE formula in various scenarios, such as calculating the average of test scores, sales figures, or any other set of numerical data.
COUNTIF Formula: with example
The COUNTIF formula is a function used in spreadsheets and programming languages to count the number of cells in a range that meet specific criteria. It allows you to specify a condition or criteria, and it counts the cells that satisfy that condition.
The general syntax of the COUNTIF formula is as follows:
COUNTIF(range, criteria)
Here’s an example to demonstrate how the COUNTIF formula works. Let’s say we have a list of students’ test scores in cells A1 to A10, and we want to count the number of scores that are greater than or equal to 80.
Using the COUNTIF formula, we would write:
COUNTIF(A1:A10, “>=80”)
In this case, the range is specified as A1 to A10, and the criteria is set as “>=80” to count scores that are greater than or equal to 80.
The formula would then evaluate the range and count the number of cells that meet the specified criteria. Let’s assume that there are four scores greater than or equal to 80 in cells A2, A4, A7, and A9.
The COUNTIF formula would return:
COUNTIF(A1:A10, “>=80”) = 4
Therefore, the result of the COUNTIF formula in this example is 4, indicating that there are four scores that are greater than or equal to 80 in the specified range.
You can use the COUNTIF formula to count cells based on various conditions, such as equal to a specific value, less than a certain value, containing a certain text, or meeting any other criteria you define.
LEFT, RIGHT, and MID Formulas: with example
The LEFT, RIGHT, and MID formulas are commonly used in spreadsheet applications, such as Microsoft Excel or Google Sheets, to extract specific portions of text from a cell. Here’s an explanation of each formula with examples:
LEFT:
The LEFT formula allows you to extract a specified number of characters from the left side of a text string. The syntax of the LEFT formula is as follows:
LEFT(text, num_characters)
“text” refers to the cell or text string from which you want to extract characters.
“num_characters” indicates the number of characters you want to extract from the left side of the text string.
Example:
Let’s say cell A1 contains the text string “Hello, World!” and you want to extract the first 5 characters (“Hello”) using the LEFT formula. You would enter the following formula in another cell:
=LEFT(A1, 5)
The result would be “Hello.”
RIGHT:
The RIGHT formula is similar to the LEFT formula but extracts characters from the right side of a text string. The syntax is as follows:
RIGHT(text, num_characters)
“text” represents the cell or text string you want to extract characters from.
“num_characters” specifies the number of characters to extract from the right side of the text string.
Example:
Suppose cell A1 contains the text string “Hello, World!” and you want to extract the last 6 characters (“World!”) using the RIGHT formula. You would enter the following formula in another cell:
=RIGHT(A1, 6)
The result would be “World!”
MID:
The MID formula allows you to extract a specific number of characters from the middle of a text string. The syntax is as follows:
MID(text, start_position, num_characters)
“text” refers to the cell or text string from which you want to extract characters.
“start_position” indicates the position within the text string where you want to begin extraction.
“num_characters” specifies the number of characters to extract from the starting position.
Example:
Let’s assume cell A1 contains the text string “Hello, World!” and you want to extract “World” (characters 8 to 12) using the MID formula. You would enter the following formula in another cell:
=MID(A1, 8, 5)
The result would be “World.”
These formulas are handy when you need to manipulate and extract specific portions of text within a larger string in spreadsheet applications.
SUMPRODUCT Formula: with example
The SUMPRODUCT formula in Excel is a versatile function that allows you to multiply corresponding elements in multiple arrays or ranges and then sum the products. Its syntax is as follows:
SUMPRODUCT(array1, array2, …)
Each array argument represents a range of cells or arrays that you want to multiply and sum. The arrays must have the same dimensions, meaning they should have the same number of rows and columns.
Here’s an example to illustrate the usage of the SUMPRODUCT formula:
Let’s say we have the following data in an Excel spreadsheet:
A B C
1 Quantity Price Total
2 5 10 Formula
3 3 15
4 2 20
In cell C2, we want to calculate the total cost by multiplying the quantity and price for each item and summing them up. We can use the SUMPRODUCT formula to achieve this.
In cell C2, we would enter the following formula:
=SUMPRODUCT(A3:A5, B3:B5)
The formula takes the range A3:A5 (quantity) and multiplies it element-wise with the range B3:B5 (price). It then sums up the resulting products.
After entering the formula, the result in cell C2 would be:
=SUMPRODUCT(A3:A5, B3:B5)
=SUMPRODUCT({3, 2, 1}, {15, 20, 25})
=3*15 + 2*20 + 1*25
=45 + 40 + 25
=110
So, the total cost would be 110.
The SUMPRODUCT formula is particularly useful when you need to perform calculations that involve multiple arrays or ranges in Excel. It allows you to perform complex calculations efficiently and easily.
NETWORKDAYS Formula: with example
The NETWORKDAYS formula is a useful function in spreadsheet software such as Microsoft Excel or Google Sheets. It allows you to calculate the number of working days (excluding weekends and optionally specified holidays) between two dates. The syntax for the NETWORKDAYS formula is as follows:
NETWORKDAYS(start_date, end_date, [holidays])
start_date: The starting date of the period you want to calculate the working days for.
end_date: The ending date of the period you want to calculate the working days for.
[holidays] (optional): A range or list of dates representing holidays to exclude from the calculation. This argument is not required if you don’t have any holidays to consider.
Now, let’s go through an example to illustrate how to use the NETWORKDAYS formula:
Suppose you want to calculate the number of working days between January 1, 2023, and January 31, 2023, excluding weekends (Saturdays and Sundays) and the holiday on January 15, 2023.
In Excel or Google Sheets, you can use the following formula:
=NETWORKDAYS(“2023-01-01”, “2023-01-31”, {“2023-01-15”})
The formula takes the start date as “2023-01-01”, the end date as “2023-01-31”, and the holiday as {“2023-01-15”} (enclosed in curly braces to create an array). The result of this formula will be the number of working days between the specified dates, excluding weekends and the specified holiday.
Assuming there are no additional holidays in that period, the result will be 21 (working days) in this case, as weekends and the specified holiday are excluded from the calculation.
Note: Make sure the date format in your spreadsheet matches the format used in the formula (YYYY-MM-DD in this example).