Most Popular Excel Formulas and How to Use Them

Microsoft Excel is one of the most widely used number processing applications in the world, both for personal and professional purposes. Excel has many features and functions that can help you with a variety of tasks, such as creating tables, graphs, reports, data analysis, and more. One of the most useful and versatile features is the formula.

A formula is a statement or expression that instructs Excel to perform a specific calculation or operation on data that is in a cell or range of cells. Formulas can return values, text, dates, times, or logical results, depending on the type and purpose of the formula. Formulas can also refer to other cells, either on the same or different worksheets, or even in different workbooks.

Excel has more than 400 pre-made formulas, called functions, that you can use for various purposes. These functions are categorized by topic or field, such as mathematics, statistics, finance, logic, text, date and time, and others. You can also create your own formulas by using arithmetic, comparison, and logic operators, and combine multiple functions in a single formula.

In this article, we will discuss the 9 most popular Excel formulas and how to use them. These formulas are:

1. =SUM
2. =AVERAGE
3. =MIN
4. =MAX
5. =COUNT
6. =IF
7. =VLOOKUP
8. =HLOOKUP
9. =INDEX

We will give a detailed description of what formulas are, how to write and enter formulas, as well as examples of the use of formulas in tables and figures.

What are Formulas and How to Write and Enter Formulas

A formula is a statement or expression that instructs Excel to perform a specific calculation or operation on data that is in a cell or range of cells. Formulas usually begin with an equal sign (=), followed by the elements that make up the formula, such as functions, operators, constants, or cell references.

Here’s an example of a simple formula that counts the sum of two numbers:

=2+3

This formula returns the value 5, which will be displayed in the cell where the formula is entered.

Here’s an example formula that uses the SUM function to count the sum of a range of cells:

=SUM(A1:A10)

This formula returns a value equal to the sum of all values in cells A1 through A10, which will be displayed in the cell where the formula is entered.

Here’s an example formula that uses the IF function to test logical conditions and returns a value based on the result:

=IF(B1>10,”Larger","Smaller or equal to“)

This formula tests whether the value in cell B1 is greater than 10 or not. If yes, then the formula returns the text “Larger”. Otherwise, the formula returns the text “Smaller or equal to”. The returned text will be displayed in the cell where the formula is entered.

To write and enter formulas, you can use one of two ways:

• The first way is to manually type the formula in the formula bar, located at the top of the worksheet, to the left of the Enter key. You can type formulas by using the keyboard, or by using the keys in the formula bar, such as equal signs (=), parentheses (()), and commas (,). You can also use the Insert Function (fx) key to open the Function Library dialog box, which lets you select and insert the functions you want. After you finish typing the formula, press the Enter key to enter the formula into the active cell.
• The second way is to use the AutoSum feature, which is located in the Home tab, the Editing group, or in the Formulas tab, the Function Library group. AutoSum is a button that has a sigma (∑) icon, which you can use to enter the most commonly used formulas, such as =SUM, =AVERAGE, =COUNT, =MIN, and =MAX. To use AutoSum, select the cell where you want to enter the formula, then click the AutoSum button. Excel will automatically detect the range of cells you want to count, and display the formula in the formula bar. You can change or adjust the range of cells if necessary, and then press the Enter key to enter a formula into the active cell.

1] =SUM

=SUM is a function used to count the sum of one or more numbers, cells, or ranges of cells. This function belongs to the category of Mathematics and Trigonometry, and is one of the most used functions in Excel.

The syntax of the =SUM function is as follows:

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

Where:

• number1 is a number, cell, or range of cells that must be entered as the first argument.
• [number2],… is an optional additional number, cell, or range of cells, which can be entered up to 255 arguments.

Here is an example of using the =SUM function:

•  =SUM(2,3,4) returns the value 9, which is the sum of the three numbers entered as arguments.
• =SUM(A1,B1,C1,D1) returns the value 100, which is the sum of the values present in cells A1 through D1.
• =SUM(A1:D2) returns the value 350, which is the sum of the values in the cell range A1 through D2.
• =SUM(A1:A3,C1:C3) returns the value 330, which is the sum of the values in the cell range A1 through A3 and C1 through C3.
• =SUM(A1:D3,5) returns the value 815, which is the sum of the values present in the range of cells A1 through D3 and the number 5 entered as additional arguments.

2] =AVERAGE

=AVERAGE is a function used to calculate the arithmetic mean of one or more numbers, cells, or ranges of cells. This function belongs to the category of Statistics, and is one of the most frequently used functions for analyzing data in Excel.

The syntax of the =AVERAGE function is as follows:

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

Where:

• number1 is a number, cell, or range of cells that must be entered as the first argument.
• [number2],… is an optional additional number, cell, or range of cells, which can be entered up to 255 arguments.

Here is an example of using the =AVERAGE function:

•  =AVERAGE(2,3,4) returns the value 3, which is the arithmetic mean of the three numbers entered as arguments.
• =AVERAGE(A1,B1,C1,D1) returns the value 25, which is the arithmetic mean of the values in cells A1 through D1.
• =AVERAGE(A1:D2) returns a value of 43.75, which is the arithmetic mean of the values in the cell range A1 through D2.
• =AVERAGE(A1:A3,C1:C3) returns the value 55, which is the arithmetic mean of the values in the cell ranges A1 through A3 and C1 through C3.
• =AVERAGE(A1:D3,5) returns the value 67.5, which is the arithmetic mean of the values in the cell range A1 through D3 and the number 5 entered as additional arguments.