Creating Excel Spreadsheet Formula Tutorial

Formula Basicsposition in the worksheet. An absolute reference to
Formulas in Microsoft Excel begin with an equal sign.cell A1 looks like this: =$A$1.
The equal sign tells Excel that the succeedingA mixed reference contains a relative reference and
characters constitute a formula. If you don't enteran absolute reference. A mixed reference to cell A1,
the equal sign, Excel will treat your entry as text andfor example, looks like this: =$A1 or =A$1.
the calculation will fail.If the dollar sign precedes only the letter such as
$A1, the column A is absolute, and the row 1 is
Notice the formula bar shows the formula you justrelative. If the dollar sign precedes only the number
typed. What appears in the cell is the result; whatsuch as A$1, the column A is relative, and the row 1
appears in the formula bar is the underlying value,is absolute.
which is a formula in this case.Absolute and mixed references are important when
Excel Order of Calculationsyou begin copying formulas from one location to
When performing calculations in a formula, Excelanother. When you copy and paste, relative
follows certain rules of precedence:references adjust automatically, while absolute
Excel calculates expressions within parentheses first.references do not. This means if you copy this
Excel calculates multiplication and division beforeformula =B$1+$B2 from cell A1 to B2. In cell B2, the
addition and subtraction.formula would adjust to =B$1+$B3.
Excel calculates consecutive operators with the sameYou can change reference types by pressing F4. The
level of precedence from left to right.following steps show how:
For example, the formula = 10+10*2 gives a result of1. Select cell A1, and type =B1+B2 (but do not press
30 as Excel multiplies 10 by 2 and then adds 10.Enter).
However, the formula =(10+10)*2 produces a result2. Move the cursor near B1 and press F4 once. The
of 40. This is because Excel calculates the expressionformula becomes =$B$1+B2. Move the cursor near
(10+10) within the parentheses first. It then multipliesB2 and press F4 once. The formula becomes
by 2.=B1+$B$2.
If you are unsure of the order in which Excel3. Press F4 again on either B1 or B2 to change the
calculates, use parentheses - even if the parenthesesreference to mixed; relative column and absolute row.
aren't necessary. Parentheses also make your4. Press F4 again to reverse the mixed reference;
formulas easier to read.absolute column and relative row.
Referencing Cells in Formulas5. Press F4 again to return to the original relative
You can include or reference other cells in a formula.reference.
When you do so, the result of the formula dependsRelative reference is the default. If you want to
on the values in the referenced cells and changesmake a reference mixed or absolute, use F4 to do
automatically when the values in the referenced cellsso.
change. This is extremely powerful in what-ifReferences to Other Worksheets
scenarios.You can refer to cells in other worksheets within the
To see how this works, enter 10 in cell A1. Nowsame workbook just as easily as you refer to cells in
select cell A2 and type =A1*2. The value in cell A2 isthe same worksheet. For example, to enter a
20. If you change the value in cell A1 from 10 to anyreference to cell A2 in Sheet2 into cell A1 in Sheet1,
value, the value in cell A2 will also change. Celldo this:
references are especially helpful when you create1. Select cell A1 in Sheet1, and type an equal sign.
complex formulas, or conduct what-if analysis.2. Click the Sheet2 tab.
To reference cells in your formula you can select3. Click cell A2, and then press Enter.
them with your pointer rather than having to type.After you press Enter, Sheet1 is now active. Select
For example, to enter a formula in cell A1 thatcell A2, and you will see that it contains the formula
references cells A2 and A3, do the following:=Sheet2!A2.
- Select cell A1, and type an equal sign.The exclamation point separates the worksheet
- Click cell A2, and type a plus sign.portion of the reference from the cell portion.
- Click cell A3, and press Enter.References to Worksheets in Other Workbooks
The active cell does not have to be visible in theYou can refer to cells in worksheets in other
current window for you to enter a value in that cell.workbooks in the same way you refer to cells in
You can reference cells any where; in existingother worksheets within the same workbook. These
worksheet, another worksheet or even cells in otherreferences are called external references. For
workbooks. You simply scroll through the worksheetexample, to enter a reference to Book2 in Book1,
without changing the active cell and click cells infollow these steps:
remote areas of your worksheet, in other1. Create 2 workbooks; Book1 and Book2
worksheets, or in other workbooks, as you build a2. Select cell A1 in Sheet1 of Book1, and type an
formula. The formula bar displays the contents of theequal sign.
active cell, no matter which area of the worksheet is3. Switch to Book2. Click to select A2.
currently visible.4. Press Enter
Relative, Absolute, and Mixed ReferencesAfter you press enter, your formula should be
Relative references refer to cells by their position in=[Book2]Sheet1!$A$2. This reference has 3 parts:
relation to the cell that contains the formula. AThe Workbook Book2 in square brackets, the
relative reference to cell A1, for example, looks likeworksheet and the cell. So referencing cells in
this: =A1.external workbooks by selecting the workbook, then
Absolute references refer to cells by their fixedworksheet, and then the cell you want to reference.