Formulas and Functions in Excel
A formula in Excel can be defined as a mathematical expression that is used to calculate the final value in a cell. Functions are built-in formulas that come with Excel.
The formula in A3 adds the value of A1 and A2.
Cell A3 uses the SUM function to add values in A1 and A2.
Enter a Formula
Use these steps to type a formula.
1. Select a cell.
2. Every formula in Excel always starts with an equal sign. Enter an = (equal sign) first to start typing any formula in Excel.
3. For example, type the formula A1 + A2.
Tip: You can click on cells A1 and A2 instead of entering them manually.
4. Now, enter 3 in cell A1.
Excel automatically recalculates the value of cell A3. This is a key feature in Excel!
Edit a Formula
In Excel, the formula bar shows the value or formula of the selected cell.
1. To start editing the formula in Excel, click on the formula bar.
2. Press Enter.
Operator Precedence
Excel follows a default order for performing calculations. The part of the formula inside parentheses is calculated first. It then performs multiplication or division calculations. After this, Excel will handle the addition and subtraction of the remaining formula. See the example below.
First, Excel performs multiplication (A1 * A2). Then, Excel adds the value of A3 to the result. Another Example,
Excel first adds A2 and A3 together inside the parentheses. Next, the result is multiplied by the value from cell A1.
Copy/Paste a Formula
Excel changes cell references automatically when you copy a formula. Follow these steps to understand it.
1. Enter this formula in cell A4.
2a. Right-click on cell A4 and choose Copy. Or just press Ctrl + C….
…next, Click on cell B4, right-click, and choose “Paste” or press Ctrl + V.
2b. The Second option is to drag the formula to cell B4. Select cell A4, grab the small square in the bottom-right, and pull it to B4. This method is simpler but gives the same result.
Result: For the calculation, the formula in B4 uses values from Column B.
Insert Function
Every function has the same structure. For example, SUM(A1:A4). The name of this function is SUM. The arguments in brackets tell Excel to take the range A1:A4 as input. This function adds the numbers in cells A1, A2, A3, and A4. It’s not always simple to recall the right function and its parameters. Fortunately, Excel’s Insert Function feature can help you do this.
Follow these steps to insert a function.
1. Select a cell.
2. Click the Insert Function button.
The ‘Insert Function’ dialog box appears.
3. Find a function or choose one from a category. For example, go to the Statistical category and choose COUNTIF.
4. Click OK.
The ‘Function Arguments’ dialog box appears.
5. Choose A1:C2 in the Range box.
6. In the Criteria box, enter “>5.”
7. Click OK.
Result: The COUNTIF function counts the number of cells that have values greater than 5.
Note: Rather than using the Insert Function, type =COUNTIF(A1:C2,”>5″) directly. Select A1:C2 to use as a range in =COUNTIF, rather than typing it.
1/11 Completed! Explore formulas and functions ➝
Next Chapter: Ribbon