Excel absolute cell reference Jan20 '06

I always seem to forget how to reference cells in Microsoft Excel as an absolute reference, so I’m jotting it down here, for future reference.

Targeting cells in Excel is crucial for interacting with your data.

The normal way to target a cell is to simply use the column letter and the row number:

B2

This is called a relative reference.

However, there are cases where it’s better to use an absolute reference:

$B$2

The dollar signs ($) make this an aboslute reference. This is particularly useful when targeting cells across worksheets.

Let’s say you have three worksheets open. They are named, respectively:

Notice, in particular, that Worksheet2 is all one word, and the other worksheets have a space before the number.

In Worksheet 3, you’d like to target cell B2, which is located in Worksheet2. Into any cell in Worksheet 3, you could type the following:

=Worksheet2!$B$2

Notice how we precede the cell reference with a worksheet name.

Now... let’s say we wanted to target Worksheet 1, from Worksheet 3. Would we do this:

=Worksheet 1!$B$2 ?

No. You have to be careful, if your worksheet name is multiple words. In other words - if it has any spaces in it, you have to wrap your worksheet reference in quotes:

='Worksheet 1'!$B$2

Notice how Worksheet 1 is now wrapped in single quotes. This is the proper way to reference cells from other worksheets.

Categories: Excel

Add Feedback (view all)

Leave feedback

Feedback

Input format: The editor controls below will assist with Markdown syntax.

Status

Sub-status

Your info

THANK YOU! You are my hero. I have been looking for this for days and I had eventually given up! Thanks for sharing this with all of us. ... Read more.

Thanks - you saved me!!! ... Read more.

matthom is published and produced by Matt Thommes - an independent publishing enthusiast, mobile blogger, content creator, informative writer, web developer from Chicago. Never one to conform, Matt intends to promote the effect the web has on our lives, in an effort to intensify, instruct, and clarify all that is happening around us.

Contact Matt

Similar Entries

Stats

945 unique visits since August 2008

Syndicate

Advertisements