Essential Excel Formulas Everyone Should Be Familiar With

Microsoft Excel offers a wide range of formulas and functions that can save us time by performing calculations automatically. Depending on the task, different formulas and functions can be used. For example, instead of manually adding values in cells, we can use the =SUM() function, which takes the cell range or specific cell numbers as arguments and returns the sum. In addition to basic arithmetic operations, Excel offers many other commonly used formulas and functions for mathematical, conditional, and time-based calculations. These tools can significantly simplify complex tasks, and it is essential to understand how to use them effectively.

Multiplication

Performing multiplication operations in Microsoft Excel is simple, but it requires creating a formula. As mentioned earlier, formulas should always start with an equal sign, followed by the formula itself. To multiply the values of any two or more cells, we can use the ‘*’ operator. For example, to multiply the values in cells A1 and B1, the formula would be:

= A1 * B1

In case we want to use the Multiplication Function, we can use the PRODUCT function. The syntax for using PRODUCT function is:

= PRODUCT(value1, value2)

In this case, we can replace “value1” and “value2” with the cell references or the values themselves. The PRODUCT function will multiply the numbers or values that are inside the parentheses.

Division

In Microsoft Excel, we can easily perform division operations by using the forward-slash (/) symbol in our formula. After typing the equal sign, we need to mention two cell references or values in the formula bar. Then, we add the forward-slash (/) symbol to divide the first value by the second value.

For example:

=A1/B1 or =8/4 will give a result of 2.

In the above example, the formula will divide the value of cell A1 by the value of cell B1. It’s worth noting that there’s no DIVIDE function in Microsoft Excel

SUM

The SUM function in Excel is used to add the values of cells, a range of cells, or a mix of both. It returns the sum of all the provided values. To use this function, we can add individual cell references, values, or a range of cells.

The syntax of the SUM function in Excel is as follows:

“=SUM(num-1, num-2, ……, num-n)”

Here, num-1 and num-2 are the first and second numbers to be added, and we can add up to n numbers, where num-n can be any positive number. The first argument, num-1, is mandatory and the rest are optional.

For example:

=SUM(A2:A10)

The above example will return the sum of cell values from A2 to A10, where SUM is the function used.

Subtraction

In Excel, subtraction can be performed by either directly entering values or by using cell references. The subtraction formula syntax requires the use of the ‘-‘ sign between two or more values or cell references, and it is as follows:

=number1-number2

Note that the equal sign is always necessary when declaring any function or formula. The first number is the number from which you want to subtract any value, while the second number is the value that you want to subtract from the first number. Both numbers are mandatory.

For example, to subtract 7 from 10 directly, use the formula:

=10-7

This will return the result as 3.

To subtract values using cell references, use a formula like this:

=A3-D3

This will subtract the value of cell D3 from the value of cell A3 and return the result in the desired cell where the formula is entered.

IF

The IF function is a widely used and powerful function in Excel that allows users to make logical comparisons between a value and an expected value. The result of an IF statement can either be True or False, depending on whether the comparison is true or false.

To use the IF function, follow this syntax:

=IF(logical_condition, statement_if_true, statement_if_false)

In the above syntax, the logical statement is a required argument that needs to be checked in the worksheet or table. The second argument is the value that is returned if the condition is true, which is also a required argument. However, the statement for the false condition is optional.

For example, let’s consider the following formula:

=IF(C2=”Yes”, 1, 2)

In this formula, if the value of cell C2 is “Yes”, it will return 1; otherwise, it will return 2.

DATE

To create a date in Excel by combining separate values of year, month, and day, we use the DATE function. This function returns a serial number that represents a date.

Here is the syntax for the DATE function:

=DATE(year, month, day)

All three arguments, year, month, and day are mandatory. You can either directly enter the values or use cell references for each parameter inside the parentheses. The function will then combine these values and return a date.

For example, if you want to create a date for April 5, 2023, you can use the following formula:

=DATE(2023, 4, 5)

This will return the serial number for April 5, 2023, which you can format as a date.

ARRAY

Array formulas are powerful tools in Excel that allow for complex calculations that cannot be performed using simple worksheet functions. There are two types of array formulas in Excel: single-cell array formulas and multi-cell array formulas.

For example, a single-cell array formula can be written as {=B2:B8C2:C8}. The curly brackets indicate that this is an array formula. To create this type of formula, write the formula normally (B2:B8C2:C8), and then press CTRL + SHIFT + ENTER to add the curly brackets. This type of formula generates a single value as the result.

On the other hand, multi-cell array formulas require an array of values as an argument. These formulas can be used to perform calculations on a range of cells and return an array of results.