Using Formulas and Functions in Excel
A formula in Excel is an expression of an equation to calculate a value whereas a function is a pre-defined formula. One good thing is that we can use functions in our formula. To be specific, Excel formula can be custom made and the Excel function is pre-defined. Below are examples of both formulas and functions in Excel.
Cell B4 has a formula that adds two previous numbers in cells B2 and B3 and divides the sum by 2.
Cell B4 has an AVERAGE function that calculates the average of two numbers in cells B2 and B3. Note that, in both approaches, we get the same results.
Enter a Formula
To enter a formula in a cell, do the following.
1. Select a cell that you want to write a formula in. In our example, we select cell B4.
2. Type in an equal sign (=), your formula, and then press Enter.
Note: In Excel, always write an equal sign before writing a formula or function. Typing something in a cell without starting with an equal sign does not let Excel know that you are trying to do a mathematical operation or invoke any pre-defined Excel function.
3. Once you are done typing the formula, any change in cell B2 or B3 will lead Excel to automatically recalculate the vale in cell B4.
Edit a Formula
Here is how you can edit a formula.
1. Select the cell that contains the formula. Excel will show the associated formula in the formula bar.
2. click in the formula bar and change it the way you want.
3. Press Enter
Copy and Paste a Formula
1. Type a formula in a cell or select the cell containing a formula that you want to copy. We select B4.
2. Righ-click and select copy from the menu. Keyboard shortcut: CTRL+c
3a. Select cell D4 or your target cell, right-click, and select Paste. Keyboard shortcut: CTRL+v
3b. Alternatively, select cell B4 and then drag the fill handle to the right to cell D4.
Result: The formula from cell B4 is copied to cell D4 and the value is calculated automatically. Note that the formula is also copied to cell C4. As there is no value in cells C2 and C3, the result shows as 0.
Insert a Function
In Excel, the structure of the function is more or less the same. If you know the function already, you can start with an equal sign (=) and write the function. Excel will auto-suggest you what function you are trying to write.
However, to insert using the Excel built-in button, follow the steps below.
1. Select cell B4.
2. Click the Insert Function button.
3. From the Insert Function dialog box, search for your function. We will find the “AVERAGE” function.
4. Click OK.
5. Click in the “Number1” box and select your range of cells using your mouse or manually type it in. For instance, we select B2:B3.
6. Click OK.
| 1 of 10 finished! Recommending more on Formulas and Functions: Next Example >> |
| << Previous Example | Skip to Next Chapter 07: Find and Select |