The 10 Most Used Formulas in Excel in 2022

Introduction

According to a recent market share study on office productivity software, Microsoft Excel is one of the most popular software tools in 2022. However, what may be less known is that Excel is not just a program for simple calculations or for creating basic charts and graphs. You can use Excel for more complex tasks, such as data analysis and modeling. Many businesses rely on Excel to make decisions about everything from pricing to inventory levels.

With so much power at its disposal, it's no wonder that Excel has a wide range of formulas that you can use to solve various problems. In this article, we'll look at 10 of the most commonly used formulas in Excel and how you can use them to make your work easier.

Before we start

If you work with many Excel files, you must first find out which of them contains the necessary information to use the formulas.

It's important to note that all of the following formulas can be entered into any cell in an Excel worksheet. Click on the cell where you want the formula to appear and then type = (the equal sign) followed by the formula.

In case your formula is a bit long or complex, you can always use the Formula Bar to enter it. The Formula Bar is located just above the worksheet and can be used to enter or edit formulas.

When the formula is entered incorrectly, Excel will usually show an error message. This is often the case when a formula contains invalid cell references or incorrect syntax.

1. SUM

=SUM(value1;value2)

The SUM function is probably the most commonly used formula in Excel. As its name suggests, it allows you to add up a range of values. For example, if you have a column of numbers, you can use the SUM formula to add them all up.

You can also use the SUM formula to add up multiple ranges of values. For instance, if you have two columns of numbers, you can use the SUM formula to add them both up.

In this example, we decided to sum entire column A and entire column B. The formula for this function is =SUM(A:A;B:B)

2. AVERAGE

=AVERAGE(value1;value2)

AVERAGE function is used to calculate the average of a range of values. For example, if you have a column of numbers, you can use the AVERAGE formula to find their average.

You can also use the AVERAGE formula to find the average of multiple ranges of values. For instance, if you have two columns of numbers, you can use the AVERAGE formula to find their average.

Similar to SUM, in this example, we decided to calculate the average of entire column A and entire column B. The formula for this function is =AVERAGE(A:A;B:B)

3. COUNT

=COUNT(value1;value2)

The COUNT function is used to count the number of cells that contain a number in a range. For example, if you have a column of numbers, you can use the COUNT formula to count the number of cells that contain a number.

It's also good to mention the COUNTIF function at this point - this formula is used to count the number of cells that contain a certain text string. For instance, if you have a column of names, you can use the COUNTIF formula to count the number of cells that contain the text string "John."

The formula would be =COUNTIF(A:A; "John")

4. VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

The VLOOKUP formula is used to lookup and retrieve data from a specific column in a table. The lookup_value argument is the value that you want to look up in the first column of the table. The table_array argument is the range of cells that contains the table. The col_index_num argument is the column number in the table from which the matching value will be returned. The range_lookup argument is a logical value that specifies whether you want VLOOKUP to find an exact or approximate match.

For example, let's say we have a table of data that contains employee names and their respective departments. We can use the following formula to look up an employee's department:

=VLOOKUP(A4;A1:E8; 2;FALSE)

In this formula, A4 is the cell that contains the ID (i.e., the lookup_value). A1:E8 is the range of cells that contains the table (i.e., the table_array). Next, 2 is the column number in the table from which the matching value will be returned (i.e., the col_index_num). FALSE is used to specify that we want an exact match (i.e., the range_lookup).

If the employee's ID is found in the first column of the table, the formula will return the value from the second column of the table, which is the employee's first name.

The VLOOKUP function is not limited to a single workbook - you can also use it to exchange information between multiple Excel files.

5. INDEX/MATCH

=INDEX(array, row_num, column_num)

The INDEX formula is used to look up values in a cell range. This is a very versatile formula that can be used in a variety of ways and with multiple criteria.

For example, let's say we want to look up the last name of the employee who was paid the most in the last month. We can use the following formula:

=INDEX(B:B;(MATCH(MAX(D:D);D:D;0)))

In this formula, B:B is the cell range that contains the last names of the employees, and D:D is the cell range that contains the amount of money they were paid. The row_num argument is MATCH(MAX(D:D);D:D;0), which means that we want to return the row number of the cell that contains the maximum value in column D.

6. MAX

=MAX(value1;value2)

The MAX function can be used to find the largest value in a range of cells.

Similar to other functions, you can also use the MAX function to find the largest value in multiple ranges of values.

In this example, we want to see what was the highest salary paid in the previous month. We use =MAX(B:B), and we see that the highest number in this range is 2800 - this is how much our top performer was paid last month.

7. MIN

=MIN(value1;value2)

The MIN formula is used to find the smallest value in a range of cells. This is especially useful when you have a large data set and want to find the lowest value quickly.

Let's use the same example and find the lowest pay we had last month. We use =MIN(B:B), and the result is 1999.

8. IF

=IF(logical_test, value_if_true, value_if_false)

The IF formula allows you to test a condition and then return one value if the condition is met and another value if the condition is not met.

The IF formula works in the following way:

The logical_test argument is the condition that you want to test.

The value_if_true argument is the value that will be returned if the condition is met.

The value_if_false argument is the value that will be returned if the condition is not met.

For example, let's say we want to find out if Lester worked overtime last month. We can use the following formula:

=IF(C2>40, "Yes", "No")

In this formula, C2 is the cell that contains the number of hours worked. The logical_test is C2>40 (is the number of hours worked greater than 40?). If the condition is met (i.e., the employee worked overtime), the value "Yes" will be returned. If the condition is not met (i.e., the employee did not work overtime), the value "No" will be returned.

9. AND

=AND(logical1, logical2, ...)

The AND formula is used to test multiple conditions at the same time. The formula will return TRUE if all of the conditions are met and FALSE if any of the conditions are not met.

For example, let's say we want to find out if Lester worked overtime last month and if he worked on weekends. We can use the following formula:

=AND(C2>40; OR(D2="Yes"))

In this formula, C2 is the cell that contains the number of hours worked, and D2 is the cell that clarifies if an employee worked on weekends. The logical1 argument is C2>40 (is the number of hours worked greater than 40?), and logical2 argument is OR(D2="Yes") (did the employee work on Saturday or Sunday?). If both conditions are met (i.e., the employee worked overtime and worked on a weekend), the value TRUE will be returned. If either condition is not met (i.e., the employee did not work overtime or did not work on a weekend), the value FALSE will be returned.

10. OR

=OR(logical1, logical2, ...)

The OR formula is used to test multiple conditions at the same time. The formula will return TRUE if any of the conditions are met and FALSE if all of the conditions are not met.

Let's use the same example as before and see if Lester either worked overtime or on weekends last month. We would use the following formula in this case:

=OR(C2>40, OR(D2="Yes"))

This formula is very similar to the AND formula - the only difference is that OR will return TRUE if any of the conditions are met, while AND will return TRUE only if all conditions are met. In our example, OR formula will return TRUE if Lester worked overtime (C2>40 is TRUE) or if he worked on weekends (D2="Yes" is also TRUE).

Conclusion

Excel is a powerful tool that you can use to make your work easier. You can automate repetitive tasks and save time by learning how to use formulas. In this article, we have introduced you to some of the most commonly used formulas in Excel. We hope that this has given you an excellent foundation to choose on which to build your knowledge of Excel formulas.

Do you think that we missed a basic formula? Which of these are you using most commonly? I would love to hear your opinion - leave a comment below.