The most important concept that you must master in Excel is Relative and Absolute Addressing. This is the basic foundation of Excel. Without understanding this, you will not work with excel with ease.

Formula Philosophy:

- Excel is defaulted to Relative Addressing!
- Relative Addressing is when you copy a formula across and it increments the letters in the formula.
- Relative Addressing is when you copy a formula down and it increments the numbers in the formula.
- The formula does change but it does not have to do what column or row you are sitting at. It is irrelevant.
- Excel’s job is to zoom into the formula based on how you are copying/filling (across or down), it will do exactly what is told to do. Letters across and numbers down.

For example: =Sum(F20:F40) Copying across will increment the letter “F” to “G”.

For example: =Sum (B2:F2) Coping down will increment the row “2” to “3”. - Absolute Addressing is opposite of Relative Addressing. You want to lock in the cell in a formula so that when you copy across or down the particular cell does not change. It is absolutely locked. Place the Insert beam in the formula before, in the middle or after the cell that needs to be locked. Hit F4 key. This will place a $ signs next to the letter and row number. For example , if you need the value in G2 and it must be used for all the rows, then you must lock it. Excel does not tell you that it has to be locked. For example, G2 is the cell I want to use, it should look like this: $G$2.

- F4 has four different absolute set up.
- Hitting F4 the 1
^{st}time Excel places both dollar signs – $G$2 - Hitting F4 a 2
^{nd}times Excel places a dollar sign in front of the number – G$2 - Hitting F4 a 3
^{rd}time Excel places a dollar sign in front of the letter – $G2 - Hitting F4 a 4
^{th}time Excel keeps the original letter and number – G2

- Hitting F4 the 1

- Why would I want to use Absolute? The reason is that if you have a bonus of 15% in a cell and the salaries are going to be multiplied by 15%, then I will write a formula that uses the salaries times the bonus rate that is located in the cell. I would not type the following formulas:
- =B2*15%
- =B3*15%
- =B4*15% and so on.

This does not work because if you decide to change the 15% to 20% then you have to retype the formula and copy it down. If the formula is very large, you may make other typing mistakes. - Remember, Excel works for you!

Here is an example of an Excel sheet with name, salary, bonus amount, and total salary. 15% is in G2

- Place 15% percent in the cell G2. Then type in C3:
- =B2*G2 and while the insert beam is blinking, hit F4 the formula now become

=B2*$G$2 hit Enter - Copy the formula and the rest is typed in for you leaving G2 intact.
- =B3*$G$2

=B4*$G$2

For your information, if you did not place the absolute in G2, when you copy down, Excel zooms into the formula and it is told to increment the row numbers. Therefore, B3 becomes B4 which then becomes B5 and G2 will become G3 which then becomes G4. The last 2 formulas will produce a zero values become something times nothing gives you ZERO!

Pressing Ctrl+` keys (` is located to the left of the number 1 key) shows you the formulas. Pressing those two keys will return back to normal view. It is an On/Off switch.

Practice makes perfect and Absolute is used everywhere in Excel. You can not hide from this concept!

**Click on the example to see it better.

john bishop

Jun 26, 2013@ 16:24:46i am still confused

MOSexpert

Jun 27, 2013@ 19:41:31John

Take a piece of paper and create the four columns. The columns are names, salary, bonus amount and total salary (salary plus bonus). Use small numbers rather than large ones like in the example xl sheet. So use 100 200 300. Take out a calculator and you the calculations. You will see that you are multipling the 15% to each salary. The 15% is the constant cell and you are ALWAYS using that number. In xl that ALWAYS cell is the ABSOLUTE cell (each of these words begin with the letter A). Everyhting is RELATIVE.

Roland

Feb 19, 2015@ 18:11:46I want some cells to contain what’s in the cell to its left. A1=cvx, A2=utx. B1 contains “=a1” and I get cvx, so far so good. But when I put “=a1” in B2 I get cvx, not utx. “=a1” is relative addressing pointing to the cell immediately to its left, correct?

MOSexpert

Feb 20, 2015@ 19:03:56all you have to do is to autofill down what you have in b1 which is =a1. In relative addressing a1 will become a2 and so forth as you autofill down.

vincent

Feb 21, 2017@ 10:36:02difference between relative and absolute cell addressing?

Are you confused about this concept? What is your question in more detail.