Blog | Status Updates | About | Contact | Advertise

January 20, 2006 / Filed under: Excel

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:

• Worksheet 1
• Worksheet2
• Worksheet 3

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.