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.

Read Also: How to Password Protect a Zip File in Windows 11

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:

Read Also: How to Save and Restore Desktop Icon Position in Windows 11

=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.

Read Also: How to Install Nmap with GUI in Windows 11

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.

3] =MIN

=MIN is a function used to calculate the minimum or smallest value of one or more numbers, cells, or ranges of cells. This function belongs to the category of Statistics, and is one of the useful functions for finding the lowest value in a data set.

The syntax of the =MIN function is as follows:

=MIN(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 =MIN function:

•  =MIN(2,3,4) returns the value 2, which is the minimum value of the three numbers entered as arguments.
• =MIN(A1,B1,C1,D1) returns the value 10, which is the minimum of the values in cells A1 through D1.
• =MIN(A1:D2) returns the value 10, which is the minimum value of the values in the cell range A1 through D2.
• =MIN(A1:A3,C1:C3) returns the value 10, which is the minimum value of the values in the cell ranges A1 through A3 and C1 through C3.
• =MIN(A1:D3,5) returns the value 5, which is the minimum value of the values in the cell range A1 through D3 and the number 5 entered as an additional argument.

4] =MAX

=MAX is a function used to calculate the maximum or greatest value of one or more numbers, cells, or ranges of cells. This function belongs to the category of Statistics, and is one of the useful functions for finding the highest value in a data set.

The syntax of the =MAX function is as follows:

=MAX(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 =MAX function:

•  =MAX(2,3,4) returns the value 4, which is the maximum value of the three numbers entered as arguments.
• =MAX(A1,B1,C1,D1) returns the value 40, which is the maximum of the values present in cells A1 through D1.
• =MAX(A1:D2) returns the value 80, which is the maximum value of the values in the cell range A1 through D2.
• =MAX(A1:A3,C1:C3) returns the value 110, which is the maximum value of the values in the range of cells A1 through A3 and C1 through C3.
• =MAX(A1:D3,5) returns the value 120, which is the maximum value of the values present in the cell range A1 through D3 and the number 5 entered as additional arguments.

5] =COUNT

=COUNT is a function used to count the number of cells that contain numbers, either entered directly or generated by other formulas. This function belongs to the category of Statistics, and is one of the useful functions for calculating the frequency or amount of numerical data in a data set.

The syntax of the =COUNT function is as follows:

=COUNT(value1,[value2],...)

Where:

• value1 is a number, cell, or range of cells that must be entered as the first argument.
• [value2],… 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 =COUNT function:

•  =COUNT(2,3,4) returns the value 3, which is the number of cells that contain numbers entered as arguments.
• =COUNT(A1,B1,C1,D1) returns the value 4, which is the number of cells that contain the numbers present in cells A1 through D1.
• =COUNT(A1:D2) returns the value 8, which is the number of cells that contain numbers in the cell range A1 through D2.
• =COUNT(A1:A3,C1:C3) returns the value 6, which is the number of cells that contain numbers in the range of cells A1 through A3 and C1 through C3.
• =COUNT(A1:D3,5) returns the value 13, which is the number of cells containing numbers in the range of cells A1 through D3 and the number 5 entered as additional arguments.

6] =IF

=IF is a function used to test logical conditions and return values based on the result. This function belongs to the Logic category, and is one of the most flexible and useful functions in Excel.

The syntax of the =IF function is as follows:

=IF(logical_test,value_if_true,value_if_false)

Where:

• logical_test is an expression that returns a value of TRUE or FALSE, which usually involves comparison operators, such as =, <, >, <=, >=, or <>.
• value_if_true is the value that will be returned if logical_test is TRUE. This value can be a number, text, date, time, formula, or cell reference.
• value_if_false is the value that will be returned if logical_test is FALSE. This value can be a number, text, date, time, formula, or cell reference.

Here is an example of using the =IF function:

• =IF(2>3,”True”,”False”) returns the text “False”, which is the value returned if condition 2>3 is FALSE.
• =IF(A1>B1,A1,B1) returns the value 20, which is the value returned if condition A1>B1 is TRUE. If the condition is FALSE, then the returned value is B1.
• =IF(A1:D2>50,”Pass”,”Fail”) returns a range of cells that contains the text “Pass”or“Fail”, depending on the result of the comparison between each cell in the range A1:D2 with the number 50. If the cell is greater than 50, then the returned text is “Pass”. If not, then the returned text is “Failed”.
• =IF(A1:A3>C1:C3,”A”,”C”) returns a range of cells that contain the text “A”or“C”, depending on the result of a comparison between each cell in the range A1:A3 and the corresponding cells in the range C1:C3. If a cell in range A1:A3 is larger than a cell in range C1:C3, the returned text is “A”. If not, then the returned text is “C”.
• =IF(A1:D3>100,A1:D3-10,A1:D3+10) returns a range of cells containing values resulting from arithmetic operations that depend on the result of the comparison between each cell in the range A1:D3 with the number 100. If the cell is greater than 100, then the returned value is the cell minus 10. If not, then the returned value is that cell plus 10.

7] =VLOOKUP

=VLOOKUP is a function used to look up values in a table or range of cells based on specific criteria, and return the corresponding values from the specified column. This function belongs to the Search and Reference category, and is one of the most useful functions for combining data from different sources or for searching for specific information in a table.

The syntax of the =VLOOKUP function is as follows:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Where:

• lookup_value is the value that you want to look up in a table or range of cells. This value can be a number, text, date, time, or cell reference.
• table_array is the table or range of cells that contains the data you want to search. This table or range of cells must have at least two columns, and the value you want to look up must be in the first column.
• col_index_num is the column number in the table or range of cells that contains the value that you want to return. This column number must be a positive number, starting at 1 for the first column, 2 for the second column, and so on.
• [range_lookup] is an optional argument that specifies whether you want to look up an exact value or one that is approximate. This value can be TRUE or FALSE, or 1 or 0. If you enter TRUE or 1, or leave these arguments blank, the =VLOOKUP function looks for values that come close to, that is, values less than or equal to lookup_value, assuming that the first column in the table or range of cells is already sorted ascendingly. If you enter FALSE or 0, then the =VLOOKUP function will look up the exact value, that is, the exact same value as lookup_value, regardless of the order of the first column in the table or range of cells.

Here is an example of using the =VLOOKUP function:

•  =VLOOKUP(“Mind “,A1:D5,2,FALSE) returns the value 70, which is the value returned from the second column in the table or cell range A1:D5, based on the “Bob”value searched in the first column, by using the exact value lookup.
• =VLOOKUP(“Cici”,A1:D5,3,TRUE) returns the text“Pass”, which is the value returned from the third column in the table or cell range A1:D5, based on the “Cici”value searched in the first column, by using an approximate value lookup.
• =VLOOKUP(A2,A1:D5,4,FALSE) returns the text“A001″, which is the value returned from the fourth column in the table or cell range A1:D5, based on the values in cell A2 that were searched in the first column, by using the exact value lookup.
• =VLOOKUP(45,A1:D5,1,TRUE) returns the text “Dedi”, which is the value returned from the first column in the table or cell range A1:D5, based on the value 45 searched in the first column, by using an approximate value lookup.

8] =HLOOKUP

=HLOOKUP is a function used to look up values in a table or range of cells based on specific criteria, and return the corresponding value from the specified row. This function belongs to the Search and Reference category, and is a function similar to the VLOOKUP function, but different in terms of data orientation. The HLOOKUP function looks up the value in the first row of the table or range of cells, and returns the value from the specified row, while the VLOOKUP function looks up the value in the first column of the table or range of cells, and returns the value from the specified column.

The syntax of the =HLOOKUP function is as follows:

=HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

Where:

• lookup_value is the value that you want to look up in a table or range of cells. This value can be a number, text, date, time, or cell reference.
• table_array is the table or range of cells that contains the data you want to search. This table or range of cells must have at least two rows, and the value you want to look up must be in the first row.
• row_index_num is the row number in a table or range of cells that contains the values that you want to return. This line number must be a positive number, starting at 1 for the first row, 2 for the second line, and so on.
• [range_lookup] is an optional argument that specifies whether you want to look up an exact value or one that is approximate. This value can be TRUE or FALSE, or 1 or 0. If you enter TRUE or 1, or leave these arguments blank, the =HLOOKUP function looks for values that come close to, that is, values less than or equal to lookup_value, assuming that the first row in the table or range of cells is already sorted ascendingly. If you enter FALSE or 0, then the =HLOOKUP function looks up the exact value, which is the exact same value as lookup_value, regardless of the order of the first row in the table or range of cells.

Here is an example of using the =HLOOKUP function:

•  =HLOOKUP(“Value “,A1:D5,2,FALSE) returns the value 80, which is the value returned from the second row in the table or cell range A1:D5, based on the “Value”value searched in the first row, by using the exact value lookup.
• =HLOOKUP(“Information “,A1:D5,3,TRUE) returns the text“Pass”, which is the value returned from the third row in the table or cell range A1:D5, based on the “Description”value searched in the first row, by using an approximate value lookup.
• =HLOOKUP(B1,A1:D5,4,FALSE) returns the text“A001″, which is the value returned from the fourth row in the table or cell range A1:D5, based on the value that is in cell B1 that was searched in the first row, by using the exact value lookup.
• =HLOOKUP(45,A1:D5,1,TRUE) returns the text “Value”, which is the value returned from the first row in the table or cell range A1:D5, based on the value 45 searched in the first row, by using an approximate value lookup.

9] =INDEX

=INDEX is a function used to return the value of a specific cell in a table or range of cells, based on the specified row and column numbers. This function belongs to the Lookup and Reference category, and is one of the useful functions for retrieving data from different sources or for generating dynamic cell references.

The syntax of the =INDEX function is as follows:

=INDEX(array,row_num,[column_num])

Where:

• an array is a table or range of cells that contains the data you want to return. This table or range of cells must have at least one row and one column.
• row_num is the row number in a table or range of cells that contains the value you want to return. This line number must be a positive number, starting at 1 for the first row, 2 for the second line, and so on.
• [column_num] is the column number in the table or range of cells that contains the value you want to return. This column number must be a positive number, starting at 1 for the first column, 2 for the second column, and so on. If you leave this argument blank, the =INDEX function returns the entire row specified by the row_num.

Here is an example of using the =INDEX function:

•  =INDEX(A1:D5,2,3) returns the text “Pass”, which is the returned value from cells that are in the second row and third column in the table or cell range A1:D5.
• =INDEX(A1:D5,4,0) returns cell range D4:D5, which is the entire row returned from the table or cell range A1:D5, based on the specified row number 4.
• =INDEX(A1:D5,0,4) returns cell range D1:D5, which is the entire column returned from the table or cell range A1:D5, based on the specified column number 4.
• =INDEX(A1:D5,5,2) returns the value 40, which is the returned value from cells that are in the fifth row and second column in the table or cell range A1:D5.