Apr12

Tagged in:, , Comments: YourThoughts?

Use The Dollar Sign To Assign Absolute Reference

Don't want Excel to adjust references when you copy a formula to a different cell? Use an absolute reference. Absolute reference in Excel is handy when you want to re-use a formula across a spreadsheet and need it's calculation to stay fixed in a constant fashion.

Perhaps you need to build a multiplication table that will multiply row 1 times it's counterpart in column A. If you copy your formula to the other cells in your table, the formula will move "relative" to the cell. We get around this by using the dollar sign ($) in front of the value you wish to remain constant.

You can mix absolute references with relative references in the same cell. For example, look at the table below.

 

 

D

E

F

5

= B$1+$C2

 

= D$1+$C2

6

 

 

 

7

= B$1+$C4

 

= D$1+$C4


 

In the above example, look at row 5. Do you see how the "B" has no dollar sign in front of it? That allows excel to float that value in a relative sense, but notice how the "1" has the dollar sign ($) in front of it which anchors that value firmly in row 1 and will not float around.

The syntax for using the dollar sign ($) for absolute cell reference is:

  • absolute column and absolute row (for example, $C$1)
  • relative column and absolute row (C$1)
  • absolute column and relative row ($C1)
  • relative column and relative row (C1)

The data you enter into your spreadsheet will change if you only use relative references in your formulas. Using the dollar sign to assign absolute reference to your data is a nice option to have.

Share

No comments

This article has no comments. Be the first and add your own comment!

Leave a comment

(required)

(required)

 me

Help

Some things to think about...

  • Please keep comments related to topic. Please be nice. Please don't spam!
  • Basic HTML tags are allowed:
    <a href> <abbr> <acronym> <blockquote> <code> <em> <strike> <strong>
  • Add your comment and click "Preview". If you're satisfied, click "Submit"
  • Just A Note: un-related or spam comments will be deleted.
  • Thanks!

Close

Back To Top

Download What?

Download Microsoft Excel of course! And all the stuff you need like worksheets and templates. How about discussion on formulas and cell tagging? Tutorials and lists of "how-to" for everything Excel. Oh yeah, there are Excel downloads too.

About