Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Understanding Relative and Absolute Addressing

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Understanding Relative and Absolute Addressing.

You already know that one of the powerful features of spreadsheets is that you can refer to the contents of other cells within a formula. In Excel, cells are referred to by a combination of their column letter and row number. Thus, the cell at the intersection of column D and row 15 is known as cell D15.

When you copy a formula that contains a cell reference, Excel automatically assumes that you want to cell reference modified to reflect the cell into which you are pasting the formula. For example, suppose that the cell at B1 contains the simplest of formulas, as follows:

=A1

This simply means that B1 will contain the same value as in A1. Now suppose you copy cell B1 and paste it into B2 through B5. As Excel pastes each cell, it modifies the formula so the cell reference is the same, relative to the new location, as it was to the old. In the original formula, Excel knows that the cell being referenced was one cell to the left of the cell containing the formula. Thus, every cell into which the formula is pasted will contain a formula that has a cell reference one cell to the left of the target cell. For example, cell B2 will contain the formula =A2 and cell B5 will contain the formula =A5.

If you don't want Excel to modify the row or column designator in your cell references, then you must use absolute cell references. You designate a reference as absolute (unchangeable) by preceding it with a dollar sign ($). You can precede either the column letter or row number with the dollar sign. When you later copy and paste the formula containing the absolute reference, Excel will not modify that portion of the reference, but will paste it unchanged in the target.

Normally you use absolute referencing when you want to refer to a non-changing position in a formula. For instance, if the cell at A7 contains an interest rate, and you want that interest rate referred to specifically, without it being modified by Excel, then you would use the following cell reference:

=$A$7

Another way to ensure that Excel does not modify your cell reference is to name the references and use the names in your formulas. Defining cell names is covered elsewhere in ExcelTips.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2062) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Understanding Relative and Absolute Addressing.

Related Tips:

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

dilawarabbasawan    03 Jun 2015, 13:14
please make it more easy through examples as i dont have mathematical background but i am interested to know such kind of things
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.