Definitions - Absolute Vs Relative References

Absolute Cell Reference

Spreadsheets like Excel identify the locations of cells or ranges of cells which are used in formulas, functions, charts, and so forth. Some are general, some are specific; some allow changes without affecting the outcome, while some locations of references are required to remain in place in order to have an accurate result. An absolute cell reference makes sure your result is firmly pointing to the exact place each and every time.

In other words, an absolute cell reference is used when you want a cell reference to stay fixed on a specific cell. This means that as a formula or function is copied and pasted to other locations, the cell references in the formula or function do not change.

Normally, most cell references in a spreadsheet are relative cell references, which change when copied and pasted to other cells.

The dollar sign ($) is the symbol used to denote an absolute cell reference. Here are some examples:

$A$1, $B$15, or $C$12

If you think about it for a moment, the only reason you would want an absolute cell reference is for the times that you need to copy a formula and want the referenced cell to always be calculated.

The best way to understand the difference is to see an example and for you to follow along in your own blank worksheet. Enter the figures in columns A, B, C and D in your worksheet so that they look like the following example. The formula to enter in cell D3 is shown also:



Now, try copying the formula in cell D3 down into cell D4 (click in cell D4 and grab the Fill handle at the lower right hand corner, then drag down one cell). You will get the following result:



This error result (#VALUE!) is because the formula, when copied, continued in its "relative" location, "relative" to where you pointed the formula. Thus, although the formula in cell D3 was correct-- C3*B1, the next relative location was C4*B2, and there is no VALUE in cell B2. More accurately, our intention was to calculate the Agent's Sales times the commission rate in cell B1, 7%.

A WORSE result might show NO ERROR because Excel would multiply the referenced cell by a value that might just be in the next cell referenced, returning a result that MIGHT look right, but is still wrong.

Now, let's make the formula in cell D3 ABSOLUTE, meaning, ALWAYS (or ONLY) pointing at the cell where the commission rate is located. The formula would read =SUM(C3*$B$1). You might read this aloud like this: "The SUM of the value in C3 multiplied by the value that is (ALWAYS or ONLY) in Column B and (ALWAYS or ONLY) in Row 1". The next example shows the correct result when you copy the new formula from cell D3 down to cells D4,D5, and D6:



Note now that this is only a PARTIAL ABSOLUTE reference; that is, when you copy the formula down, one part of the formula is RELATIVE (the values in cells C3:C6) while the REST of the formula is ABSOLUTE ("ALWAYS or ONLY Column B" and "ALWAYS or ONLY Row 1"). This is known as a MIXED ABSOLUTE formula, in that it mixes both RELATIVE and ABSOLUTE locations. When you copy down, the relative reference remains the same as normal; that is, cell C3 is referenced in the formula in D3, cell C4 is referenced in the formula in D4, and cell C5 is referenced in the formula in D5, while at the same time, each formula ONLY calculates that reference against the value in B1.

A FULLY ABSOLUTE reference would make sure, for you, that the value relating to each specific cell is calculated against another specific cell, but you have to be careful. Your intent might be to calculate ONLY one cell against ONLY another cell. That Fully Absolute Reference might look like this:

=SUM($C$3*$B$1)

BUT if you tried copying that formula down, you would get a different result in our example. Here's what would happen:



So, you would have to make sure, in this example, that each cell's formula reflects your intention. That is, what you are trying to do.

Using Excel's cell references are only tricky if you don't stop to think what it is you're trying to accomplish. Think slowly, especially in complicated calculations, and take them step by step. After a while, understanding will come easier and your work will progress faster.

Here's an example of a more complicated worksheet where mixed Absolute references would come in handy:



The agents in this example earn different commissions, and you can tell at a glance what their totals are. There are, of course, other ways to uncomplicate the view and make your worksheet easier to read and understand, such as grouping the commission rates in one location, the sales agents and their sales in another group, and so forth, requiring perhaps a different formula, one that might use what are called IF statements (IF sales exceed X amount, then the COMMISSION is Y).

COLUMN ABSOLUTE

Here is an example using a formula where the references are ABSOLUTE to the column:



Do you see the difference from our previous examples? In our previous example, the formula was a mixed absolute of a relative reference, a column absolute reference, and a row absolute reference (C3*$B$1). The above example shows you what a column absolute by itself would look like. You're able to copy this formula across without errors! Now, here's a Row Absolute Reference:



The Row Absolute example shows you how you can copy down without errors.

Here's another example of Mixed Absolute References:



Now another example of Fully Absolute References:



Can you read the yellow pasted note? It says:

"Note: Excel will cycle through Absolute references if you click F4 when entering a formula. Try it: click on cell B10. Then, in the formula bar above, click again so that your insertion point I-beam is blinking. Then press F4."

This means that Excel allows you to select the correct Absolute reference you're looking for. Click on a cell to get into "Edit Mode" and then press your F4 key on your keyboard. Click the F4 key again, then again, then again--until you come up with the Absolute Reference you're looking for.

Editing A Formula

You can edit your formulas just as you could any other cell. Here are three common ways:

1. Double-click in the cell, which allows direct editing right in the cell.

2. Click in the cell, then press your F2 key on your keyboard.

Note: These two cell-editing techniques can only happen if you have the Edit Directly In Cell option turned on. If not working when you attempt to do either of these, then go to Tools->Options and click on the Edit tab and click on the Edit Directly In Cell option.

3. Click in the cell to select the formula you want edited. Then click in the Formula bar. This lets you edit the formula directly in the Formula bar.

Another AutoSum Tip

Suppose you want to sum data that is not directly above, left, right or below the cell where you want the results to be; here's a way to do it. For our example. let's say you have data in cells A1:C3, and want your calculation to be in cell D4. In the following example, with your active cell as D4, click on the AutoSum tool (the "Sigma" or Greek-looking "E"):



Notice that the cursor is blinking between two enclosing parenthesis. Excel is waiting for you to specify the range you want summed, either by typing in the cell range, or selecting it with your mouse pointer. We're going to select it with the mouse pointer.

Point your mouse to cell A1, then drag to the right and down to cell C3, so that it looks like this:



Note that Excel has recorded the cell range between the parenthesis, and also, note the animated dashed border around your selected range. Now all you have to do is press Enter on your keyboard, or click the green checkmark shown to the left of the Formula Bar. This is the result:

Basics: Enter A Formula

Here is a simple explanation for beginners for entering a basic formula in Excel:

To use this example, open an Excel workbook with a blank worksheet. In cell A1, enter a static number, such as 25. In cell A2, enter another number, such as 50. Now, make cell A3 your active cell by either clicking in it or using your arrow keys to navigate to it. This is the cell where you will enter your formula to add the two numbers you entered together.

In cell A3, your first step is to enter the equal sign =. This is what you will see:



You can now enter the formula by clicking on A1 with the mouse, then typing the + key and then clicking on A2 with the mouse. The screen and formula bar will look like this:



Now you can complete the formula by either hitting your keyboard's Enter key or by clicking on the green checkmark next to the formula bar. Your result will look like this:



Another quick method for entering and/or calculating is by using the Autosum Tool. Here's the example with the cursor pointing at the Autosum tool:




Be sure you're in the cell where you want your result to be (in this case, in cell A3); click the Autosum Tool once and you will see the range selection border as in the following example:



Click the AutoSum Tool again or press your Enter key on your keyboard and the result will then display. Another fast way is to just simply click the AutoSum tool in two distinct clicks, one right after the other.

The only caution here is, make sure that the AutoSum Tool has selected the correct range to calculate.

Excel 2003 Workbook Operators

To perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, use the following arithmetic operators (Click Image To View Detail):




You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value, either TRUE or FALSE.



Text concatenation operator

Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of text.



Combine ranges of cells for calculations with the following operators.



Operator Precedence

If you combine several operators in a single formula, Excel performs the operations in the order shown in the following table.

If a formula contains operators with the same precedence—for example, if a formula contains both a multiplication and division operator—Excel evaluates the operators from left to right.



Use of parentheses

To change the order of calculation, enclose in parentheses the part of the formula to be calculated first.

For example, the following formula produces 12 because Excel calculates multiplication before addition.

The formula multiplies 2 by 4 and then adds 4 to the result, and not 24 as you might expect by calculating left to right as one might do.

=4+2*4 (2 times 4 is eight, plus 4 is 12)

In contrast, if you use parentheses to change the syntax, Excel adds 4 and 2 together and then multiplies the result by 4 to produce 24.

=(4+2)*4 (4 plus 2 is 6, times 4 is 24)

In the example that follows, the parentheses around the first part of the formula force Excel to calculate A5+15 first and then divide the result by the sum of the values in cells D5, E5, and F5.

=(A5+15)/SUM(C5:E5)


So if the sum of cell A5 is 85, and cells C5 through E5 are 10, the result would be 10 (85 plus 15 is 100; divided by 10 is 10.

All PARENTHESES are completed FIRST
A NEGATION (such as -9) precedes any of the following operations. The negative value is used in the remaining calculations.
PERCENT's are calculated next and then used in the remaining calculations.
EXPONENTIATION (cubed numbers) are next calculated, returning the value internally before remaining calculations.
MULTIPLICATION is performed AFTER PARENTHETICAL operations and BEFORE any other calculations that follow.
Next is DIVISION, which follows multiplication.
Next is ADDITION, performed AFTER divisions, and before the final operator:
SUBTRACTION, which follows additions.

Because Excel follows a specific order of operations, you can therefore use parentheses to segment your formula.

This is effective because, remember, Excel performs calculations that are in parentheses first.

The operations in multiple sets of parentheses are performed from inside to outside, then that calculation is used for the second set, following the order of operations.

After that, those results are used for the next level, which are performed from left to right.

Here's an example:
=(2+4)*5+5-3
This results in 32 (2 plus 4 is 6, times 5 is 30, plus five is 35, minus 3 which is 32).

Yet removing the parentheses so that you have =2+4*5+5-3 results in 24 (2 plus 4*5 is 22, plus 5 is 27, minus 3 is 24)!

This could result in dramatic errors, to say the least. Moving the location of the parentheses results in other changes:

=2+4*(5+3)-5 results in 29 (5 plus 3 is 8, times 4 is 32, plus 2 is 34, minus 5 is 29)

=2+4*(5+3-5) results in 14 (5 plus 3 minus 5 is 3, times 4 is 12, plus 2 is 14)

=(2+4)*(5+3)-5 results in 43 (2 plus 4 is 6, 5 plus 3 is 8; 6 times 8 is 48, minus 5 is 43

=(2+4)*(5+3-5) results in 18 (2 plus 4 is 6; 5, plus 3 minus 5, is minus 2 (-2); plus 5 which equals 3 (-2+5=3); 3 times 6, which is 18).

Nesting Parentheses
Just to take this one step further, you can also nest calculations by inserting isolated sets of parentheses within other parentheses. For example:



This calculation results in 72. (4 plus 2 minus 1 is 5; 14 divided by 2 is 7; 7 plus 5 is 12; 12 times the sum of 2 plus 4 (which is 6) is 6 times 12 or 72.
Excel, in other words, calculated the inner-most set of parentheses first (4+2-1) which had been placed inside another set of parentheses (+14/2), then held that calculation (the sum of 5 plus 7) while it next calculated the other set of parentheses (2+4), and then multiplied that sum (6) by the first sum (12) to return 72 (6*12=72).

Are your eyes rolling yet?

Quick Charting

Instead of using the chart wizard to create your chart, select the range of data you want to chart and press F11 on your keyboard. Don’t forget to include the column and row labels in your selection to define your categories and series legend.

This technique produces a standard bar chart on a separate chart sheet. Once the chart is produced, you can modify the chart as you see fit using the commands found on the chart menu and chart toolbar.

Automatic Word Wrap in a Cell

To wrap text in a cell you must set the wrap text control on the alignment page of the format cells dialog box. You can also set the wrap text control automatically by entering a hard carriage return in the cell while entering or editing data.

To enter a hard carriage return, press ALT + Enter at the point where you want the cell to wrap.

Display Formula In Excel Worksheet

Worksheet cells display the results of formula calculations. To see the underlying formula, you click on the cell and the formula is displayed in the formula bar. There are times when you might need to see more than one formula at a time. You can change the worksheet view to display the formulas directly in the cell by pressing the CTRL + ` (accent) keys together. The accent key is located directly above the tab key on the left side of your keyboard.

This keystroke combination is a toggle and you can use it to toggle between the formula view and the results view.

You can also set the formula view by selecting or deselecting the Formula option on the view page of the Options dialog box.