Creating Excel Spreadsheet Formula Tutorial
Formula Basics
Formulas in Microsoft Excel begin with an equal sign. The equal sign tells Excel that the succeeding characters sentence a formula. If you do not enter the equal sign, Excel will treat your entry as text and the calculation will fail.
To show how formulas work, we'll begin with a simple exercise by selecting blank cell A1. Then type = 5 + 5, and press Enter. Excel fulfills the calculation and produces a result of 10 in cell A1.
Notice the formula bar shows the formula you just typed. What appears in the cell is the result; what appears in the formula bar is the undering value, which is a formula in this case.
Excel Order of Calculations
When performing calculations in a formula, Excel follows certain rules of precedence:
Excel calculates expressions within parentheses first.
Excel calculates multiplication and division before addition and subtraction.
Excel calculates consecutive operators with the same level of precedent from left to right.
For example, the formula = 10 + 10 * 2 gives a result of 30 as Excel multiplies 10 by 2 and then adds 10. However, the formula = (10 + 10) * 2 results a result of 40. This is because Excel calculates the expression (10 + 10) within the parentheses first. It then multiplies by 2.
If you are unsure of the order in which Excel calculates, use parentheses - even if the parentheses are not necessary. Parentheses also make your formulas easier to read.
Referencing Cells in Formulas
You can include or reference other cells in a formula. When you do so, the result of the formula depends on the values in the referred cells and changes automatically when the values in the refereed cells change. This is extremely powerful in what-if scenarios.
To see how this works, enter 10 in cell A1. Now select cell A2 and type = A1 * 2. The value in cell A2 is 20. If you change the value in cell A1 from 10 to any value, the value in cell A2 will also change. Cell references are especially helpful when you create complex formulas, or conduct what-if analysis.
To reference cells in your formula you can select them with your pointer rather than having to type. For example, to enter a formula in cell A1 that references cells A2 and A3, do the following:
- Select cell A1, and type an equal sign.
- Click cell A2, and type a plus sign.
- Click cell A3, and press Enter.
The active cell does not have to be visible in the current window for you to enter a value in that cell. You can reference cells any where; in existing worksheet, another worksheet or even cells in other workbooks. You simply scroll through the worksheet without changing the active cell and click cells in remote areas of your worksheet, in other worksheets, or in other workbooks, as you build a formula. The formula bar displays the contents of the active cell, no matter which area of the worksheet is currently visible.
Relative, Absolute, and Mixed References
Relative references refer to cells by their position in relation to the cell that contains the formula. A relative reference to cell A1, for example, looks like this: = A1.
Absolute references refer to cells by their fixed position in the worksheet. An absolute reference to cell A1 looks like this: = $ A $ 1.
A mixed reference contains a relative reference and an absolute reference. A mixed reference to cell A1, for example, looks like this: = $ A1 or = A $ 1.
If the dollar sign precedes only the letter such as $ A1, the column A is absolute, and the row 1 is relative. If the dollar sign precedes only the number such as A $ 1, the column A is relative, and the row 1 is absolute.
Absolute and mixed references are important when you begin copying formulas from one location to another. When you copy and paste, relative references adjust automatically, while absolute references do not. This means if you copy this formula = B $ 1 + $ B2 from cell A1 to B2. In cell B2, the formula would adjust to = B $ 1 + $ B3.
You can change reference types by pressing F4. The following steps show how:
1. Select cell A1, and type = B1 + B2 (but do not press Enter).
2. Move the cursor near B1 and press F4 once. The formula becomes = $ B $ 1 + B2. Move the cursor near B2 and press F4 once. The formula becomes = B1 + $ B $ 2.
3. Press F4 again on either B1 or B2 to change the reference to mixed; relative column and absolute row.
4. Press F4 again to reverse the mixed reference; absolute column and relative row.
5. Press F4 again to return to the original relative reference.
Relative reference is the default. If you want to make a reference mixed or absolute, use F4 to do so.
References to Other Worksheets
You can refer to cells in other worksheets within the same workbook just as easily as you refer to cells in the same worksheet. For example, to enter a reference to cell A2 in Sheet2 into cell A1 in Sheet1, do this:
1. Select cell A1 in Sheet1, and type an equal sign.
2. Click the Sheet2 tab.
3. Click cell A2, and then press Enter.
After you press Enter, Sheet1 is now active. Select cell A2, and you will see that it contains the formula = Sheet2! A2.
The exclusion point separates the worksheet portion of the reference from the cell portion.
References to Worksheets in Other Workbooks
You can refer to cells in worksheets in other workbooks in the same way you refer to cells in other worksheets within the same workbook. These references are called external references. For example, to enter a reference to Book2 in Book1, follow these steps:
1. Create 2 books; Book1 and Book2
2. Select cell A1 in Sheet1 of Book1, and type an equal sign.
3. Switch to Book2. Click to select A2.
4. Press Enter
After you press enter, your formula should be = [Book2] Sheet1! $ A $ 2. This reference has 3 parts: The Workbook Book2 in square brackets, the worksheet and the cell. So referencing cells in external books by selecting the workbook, then worksheet, and then the cell you want to reference.
Learn more about Excel spreadsheet at [http://www.tayop.net.au/ExcelTraining/tabid/59/Default.aspx]
