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:
- =SUM
- =AVERAGE
- =MIN
- =MAX
- =COUNT
- =IF
- =VLOOKUP
- =HLOOKUP
- =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+3This 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.