Excel and you: Dollar signs, stop the presses!

Last week we discussed how to drag a formula down a column, using the previous cell as a reference.

This week, we’ll talk about the all-important dollar sign.

Now, when you drag a cell down one row, the cell references update to one row down of what they were previously.  For example, if you had =D16 in a cell, if you were to drag it down one row, that cell would say =D17.  The cells references are dynamic.

HOWEVER, if you put a dollar sign in the formula, the reference directly following that dollar sign becomes static. So, in the previous example, if your cell were to say =D$16, when you drag it into the next cell, that cell would also say =D$16.

Practical applications for this are nearly endless.

A relatively easy example follows:

Say you have a combined bill for multiple locations or people, with static usage(in my work, an example would be the insurance, which we break out per house based on number of beds.)

Here are the numbers:

Total cost of insurance: $1500

House 1: 4 beds

House 2: 6 beds

House 3: 4 beds

In my actual spreadsheet, there are 52 houses, but I want to keep this relatively simple :)

Here is what my (very) simplified spreadsheet looks like:

The formula on the far right is for illustrative purposes only!

As you can see, I employed the dollar sign on two different cell references.

 At the end of the formula, I used the dollar sign to hold the 3 in place.  Because the formula was already in column C, I didn’t have to use a dollar sign to hold it in place, though I could have if I wanted to.

In the middle of the formula, you can see I used the dollar sign to hold both B and 7 in place.

Now, when I wrote this formula, I only had to type in the first cell’s formula long hand.  Essentially what it is doing is taking the number of beds in the home(B4), dividing it by the total number of beds(B7), and then multiplying the whole thing by $1500, thereby giving me a proportional amount of the cost of insurance.   

After that was finished, I simply dragged the formula down through the other two cells, and got the totals for all houses

Because I wanted the numerator to be the number of beds in the house, which changed per house, that cell reference got no dollar signs.

To divide that number by the number of beds total in the house, I wanted to use cell B7 in all cells, so I put dollar signs in front of both cell coordinates.

Finally, to multiply the whole thing by cell C3, I needed a dollar sign in front of the 3, and viola! I have a formula that can be dragged and will copy correctly!

There are many uses for the dollar sign in all sorts of different types of worksheets.

Let me know some of your uses in the comments!