Excel: what does the dollar sign do?

Office 365 / SharePoint Blog

Oftentimes when working with Excel, you’ll notice that dollar signs ($) appear in front of, or in the middle of cell references.

For example:
$A1
A$1
$A$1

What does Excel do with this information and why do you need it?

It means that the row or column which comes after the dollar sign is anchored or absolute, and therefore will stay the same as you copy and paste formulae to another cell.

Excel by default uses “relative” reference to a cell, which means that the formula changes as you copy it to another cell.

For example, a formula in column D that states =SUM(A1:A5) will become =SUM(B1:B5) when it is copied into a cell in column E.

By using the dollar sign, you can ensure that either the row stays the same, the column stays the same, or that you want a specific cell referenced.

So using the references from the above example:

$A1 means that the formula will always pull information from column A, regardless of which column it appears, but it can change the row depending on where the formula appears.

A$1 means that the formula will always pull from the first row, regardless of which row it appears, but it can change the column depending on where the formula appears.

$A$1 means that no matter where the formula is placed on the sheet, it will always reference the A1 cell.

Why do we care?

We generally care if we’re trying to reference information in a list. We don’t want the number of items changing in the list, so if you have information in rows 1 through 20, then you don’t want the first row to have a formula looking at rows 1:20, and the second row to have a formula looking at rows 2:21, etc. You would want to make sure your range at least has dollar signs in front of the row numbers, so that A$1:A$20 will always pull rows 1:20 from the column.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s