Microsoft Excel: Basic to Advance Formulas to Learn

Microsoft Excel is a powerful tool used by millions worldwide for data analysis, financial modeling, and much more. Mastering Excel formulas can significantly enhance your productivity and efficiency. This article will guide you through some of the top Microsoft Excel formulas that you should learn to maximize your potential with this versatile software.

Understanding Excel Formulas

Excel formulas are essential for performing calculations and data analysis. They can range from simple arithmetic to complex functions that analyze and manipulate data. Learning these formulas can help you automate tasks, streamline workflows, and make data-driven decisions.

Essential and Advance Excel Formulas

1. SUM Function

The SUM function is one of the most commonly used formulas in Excel. It adds up a range of cells and is perfect for quickly totaling values.

Syntax: =SUM(number1, [number2], ...)

Example:

In the below example we totaled Ranges "C3:C9", refer the image.

2. AVERAGE Function

The AVERAGE function calculates the mean of a set of numbers. It's useful for finding the central tendency of your data.

Syntax: =AVERAGE(number1, [number2], ...)

Similiar to the above example, if we need to know the average salary of the below employees, we can use this formula, =AVERAGE(C3:C9) and it results in $1,07,963.

3. IF Function

The IF function is used for conditional evaluations. It returns one value if a condition is true and another value if it's false.

Syntax: =IF(logical_test, value_if_true, value_if_false)

Example:

You can replace the text in quotes, the first text is for TRUE value and second text is for FALSE, i.e. =IF(TODAY() - C3=>3650 "Decade", "Yet to Completed")

4. COUNT, COUNTA, COUNTIF Function

The COUNT function counts the number of cells that contain numbers within a specified range. It will not consider any text in the cells, so if you specifically deal with text and number both use COUNTA.

Syntax: =COUNTA(value1, [value2], ...)

COUNTIF Function

The COUNTIF function counts the number of cells that meet a specified condition.

Syntax: =COUNTIF(range, criteria)

Example: In the above image, we counted the number of times India has appeared in Between Ranges C4:C10, it results in 2.

7. CONCATENATE Vs TEXTJOIN Functions

The CONCATENATE and TEXTJOIN functions both help join two or more text strings into one. However, the TEXTJOIN function offers a significant advantage: it combines text from multiple ranges and strings, including a specified delimiter between each text value.

Syntax: =CONCATENATE(text1, [text2], ...)

Example:

What if I need all the names separated by Commas in one cell ?

Using TEXTJOIN Formula

Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

When working with multiple delimiters, using CONCATENATE can result in a lengthy and cumbersome formula. In contrast, TEXTJOIN simplifies the process by allowing you to specify the delimiter once, and it automatically includes it between all data points. This makes TEXTJOIN a more efficient and streamlined option for joining text with consistent delimiters.

Refer the below example for more clarity

8. PMT Function

The PMT function calculates the payment for a loan based on constant payments and a constant interest rate.

Syntax: =PMT(rate, nper, pv, [fv], [type])

Example:

Principal - 10000

Rate - 5%

Term - 60 Months

Function =PMT(0.05/12, 60, -10000)