» Changing an Absolute Reference to a Relative Reference or Vice Versa
CATEGORY - Working with Formulas
VERSION - All Microsoft Excel Versions
When a formula is copied, a Relative reference is used. A Relative reference is the distance, in rows and columns, between the reference and the cell containing the formula.
For example: In cell A1, type the number 100, and in cell B1, type the formula =A1. Cell B1 is one column to the right of cell A1. When the formula is copied from cell B1 to cell B10, the distance between the reference and the cell containing the formula remains one column, and the formula in cell B10 is =A10.
Absolute Reference
Select cell B1 from the previous example. In the Formula Bar, select A1, and then press F4. The result is =$A$1.
Copy the contents of cell B1 to cell B10. Notice that the formula does not change – the formula reference remains constant as =$A$1.
The F4 Key
The F4 keyboard shortcut has four states:
State 1: Absolute reference to the column and row, =$A$1
State 2: Relative reference (column) and Absolute reference (row), =A$1
State 3: Absolute reference (column) and Relative reference (row), =$A1
State 4: Relative reference to the column and row, =A1

Book Store:
Recommended Books:
- Investing for Dummies, Third Edition
- The Essential 55: An Award-Winning Educator's Rules for Discovering the Successful Student in Every Child
- What the IRS Doesn't Want You to Know: A Cpa Reveals the Tricks of the Trade
- Cashflow Quadrant: Rich Dad's Guide to Financial Freedom
- H&R Block's Just Plain Smart(tm) Tax Planning Advisor: A year-round approach to lowering your taxes this year, next year and beyond
- Getting to Yes: Negotiating Agreement Without Giving In
R1C1 Relative References
Dennis Adams Posted on: 31-12-1969
Can you discuss how a formula is written using real relative references, such as the R1C1 designations. Using =AVERAGE(M$90:M$100) will not calculate the average of the last ten rows if the user inserts new data rows between row 90 and 10>


