The whole thing about referencing in Excel is that you want to make sure that the software only replaces only those cell adresses that you want to. This comes in very handy if you copy formulas or enter massive amounts of them at once (read this post, if you want to know how to do that).
To force a reference to stay in a row, a column or both, you have one new best friend on your keyboard:
Yes, that's the F4 key. Give it a hug! (No, don't pull it of your keyboard!!! 
)
Currently there are three modes to fix a cell address:
- Column and row absolute - press F4 once
- Row absolute - press F4 twice
- Column absolute - press F4 three times
There is a fourth state which is no fixing at all. If you press F4 four times (a lot of 4s, huh? 
) you will have the normal cell adress again. So pressing the F4 key "scrolls" through the several states, the fixation can have.
This was the theoretical part, let's get our hands dirty:
Set up a table like this:
| A | B | C | D | |
| 1 | Net costs | Tax rate full | Tax rate reduced | Final price |
| 2 | 38 | 19% | 45.22 | |
| 3 | 46 | 19% | 54.74 | |
| 4 | 68 | 7% | 72.76 | |
| 5 | 37 | 19% | 44.03 | |
| 6 | 78 | 7% | 83.46 | |
| 7 | 39 | 7% | 41.73 | |
| 8 | 51 | 19% | 60.69 |
| Formulas in the table |
||||||||||||||||
|
The formula used in this little example is the following one:
=IF(B2<>"",$A2+($A2*B2),$A2+($A2*C2))
This formula consists of the following elements:
- =IF(B2<>"", - Dear Excel, please check, if cell B2 is empty...
- $A2+($A2+($A2*B2) - ... then please add the calculated full tax (net cost x full tax rate) to the net costs ...
- $A2+($A2+($A2*C2) - ... otherwise add the reduced tax (net cost x reduced tax rate) to the net costs.
The $-sign (or string) in front of A2 tells Excel only to use values in column A as net costs. Of course, for this simple example it would not have been necessary, to make the reference A2 column absolute, but using it whenever possible is a good habit to get used to it.
| A | B | C | D | E | F | G | |
| 1 | 01.01.2010 | 08.01.2010 | 15.01.2010 | 22.01.2010 | 29.01.2010 | 05.02.2010 | 12.02.2010 |
| 2 | Week 1 | Week 2 | Week 3 | Week 4 | Week 5 | Week 6 | Week 7 |
| Formulas in the table | ||||||||||||||||
|
The formula
="Week "&WEEKNUM(A$1)
tells our favorite Microsoft product
- ="Week " - Take the word week (don't forget the space behind it!) and ....
- &WEEKNUM(A$1) - ... add the week number of the date in cell A1 to it
In this example we want Excel to fix the reference to row 1.
Here comes our last example:
Sheet3
| A | B | C | |
| 1 | Gross margin | 25% | |
| 2 | |||
| 3 | Net price | Margin value | Resale price |
| 4 | 44.00 | 11.00 | 55.00 |
| 5 | 83.00 | 20.75 | 103.75 |
| 6 | 90.00 | 22.50 | 112.50 |
| 7 | 68.00 | 17.00 | 85.00 |
| 8 | 35.00 | 8.75 | 43.75 |
| 9 | 102.00 | 25.50 | 127.50 |
| Formulas in the table | ||||||||||||||||||||||||||
|
Display Excel tables online >> Excel Jeanie HTML 4
In this example we use the interesting formula is in column B and it goes like
=+A4*$B$1
This tells Excel to multiply the net price with gross margin percentage which can be found in one cell (and this cell only): B1. Therefore we need a column and row absolute reference.
If this article helps you to wrok with Excel (and fixing references ) please leave a comment.
Discuss this blog post in the forums (click the blue button)
Comments
RSS feed for comments to this post.