What’s with the dollar signs in Excel formulas?

dollar signEver noticed cell identifiers with dollar signs in them? Things like $A$1. Sometimes they’re in formulas. Sometimes you select a cell for a dialog box and it shows you something with dollar signs. What’s that all about?

You might try the formulas =A1, =$A1, =A$1, and =$A$1 and find they all give you the same answer, the contents of cell A1. Where you see the difference is when you copy or AutoFill the formula. Excel is smart enough to adjust your formula to its new location, a very convenient feature. But sometimes you don’t want it to do that. That’s what the $ does. It tells Excel “Don’t change the following letter or number when you copy this formula.” So, for example, $A1 will always keep the A, but the 1 may change when copied somewhere else.

Let’s take an example. Here’s a spreadsheet, dollar signs, that calculates total cost of some items, including sales tax.

dollar signs spreadsheet

Everything in columns C through F starting in row 4 is calculated using a formula. In fact, formulas were entered only in row 4, and the rest were copied down the columns using AutoFill. The formula in D4 is =B4+C4. Add the price and the tax to get the total. No dollar signs, so when it gets copied to the other rows it will change accordingly. In D5, for example, the formula is =B5+C5, which is just what you want. This is what Excel does normally, and usually it gives you exactly what you want and saves you a ton of work.

Now look at C4. The formula is =B4*B1, multiply the price by the tax rate. That works here, but it we try to copy it down the column, we’ll get garbage (try it), since it will try to use B2, B3, and so on, instead of B1. We want to make sure we always use the tax rate that is in B1, so we write it as $B$1, and the formula that works for copying is =B4*$B$1.

Check out the formula in E4, that multiplies the total cost of one item by the quantity in E3. It’s =E$3*$D4. See if you can figure out why this works not only when you copy it down the column, but also when you copy it across to column F.

By the way, the references that change (without the $) are called relative references and the ones that don’t change (with the $) are called absolute references. Knowing when to use them will really help you create some big, useful spreadsheets from just a few formulas.

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