Using $ in excel formulas- Tips and Tricks

How to apply this to your spreadsheets?

Below are some tips and tricks to build formula with $ signs.

Create 2 formula and identify the similarities

One way to create these formula is to create the same function twice in different cells and identify the similarities. This is particularly useful when the copy you are going to do is down and across.

Watch the video clip and/or read the text to understand how to use the Function Wizard

In the example below we want to multiple the selling price by the number of units sold to get a matrix of possible revenue at all prices and volumes.

The formula itself is easy.

In cell C4 it would be =C3*B4. But you don’t want to do this for each cell. You want to create it once and then copy it to the rest.

One way to do this is to first manually create the formula in two cells that are diagonally opposite each other. Note below that I captured the formula in cell C4 and D5 (the second one is 1 to the right AND 1 down).

Now I can look at each formula and see what is common with the cell references i.e. look at the letter and number in each cell reference (click in the cells or see the trick below to see the formula at the same time).

Notice that the first part of the formula is C3 in the one cell and D3 in the other cell. The letters are changing (C to a D), but the numbers are not (both have a 3). This indicates that we can use a $ sign on the number 3. In Excel’s terms it must always look at row 3 which makes sense because we want it to use the selling prices.

For the second part of the formula (the B4 in the first cell and B5 in the second cell) notice that the numbers are changing (from 4 to 5) but the letters are staying the same (both are B’s). A $ sign in front of the letter will achieve the same thing (Excel will always look at column B for the number of units sold).

Now you can go to the first formula, put a $ sign in front of the 3 and the B so that is reads =C$3*$B4, copy it across and down and check that it is doing what you want (use the Formula Auditing Toolbar and common sense).

In a similar way you can spend the time to create a complex formula twice but save yourself lots of time with the rest of the formula.

Build the formula and work through the thought process

Another way is to think through the process in a single cell. It is the same concept as above but requires you to do the thinking.

The first thing you should always do is just build the formula in a single cell. Get the syntax right and see if you get the correct result. For example, below the formula in F9 is checking whether the month it is in, is an escalation month. If it is, then it applies an escalation to the prior months rental collection. If it is not, then it just uses last months rental.

If we copied the formula one to the right, as it is at the moment, it would result in errors. This is because, when you move it to the right, Excel will change all the letters to be one higher. So where it used to refer to E8 (the escalation month), it will now refer to F8 which is a blank cell. The formula won’t work.

It is at this point that you can now consider where you want the $ signs to go.

Using the function wizard we can decide where the dollar signs should be.

Let’s start with the first referent to F7 (in the logical test). F7 is the cell which tells us what month we are in. If we copy the cell to the right, it will move to the right. So when we are in August it will look at F7. If it moves to the right we are happy if it now looks at G8 (September). In this case a movement to the right needs to look at the cell to the right. We are therefore happy that the F will change to a G, so we leave it as is.

You must remember that you may want to copy it downwards as well. In this case, if we copied the cell down, Excel will want to change the reference from F7 to F8. If this happens, then Excel will be comparing a number to a word which will not give the correct result. So we need to tell Excel that it cannot allow the row number to change. To do this we put a $ sign in front the 7.

We can now move onto the next part of the logical test, the reference to E3. E3 is the cell which tells us what month is the escalation month. If we leave it as is, then a copy to the right would result in Excel changing the E to a F. If this happens, the formula will look at a blank cell which is incorrect. We can see that we want the formula to always look at column E. To do this we can put a $ in front of the E.

But keep in mind that we will also be copying it down. If we copy it down, the 3 will change to a 4. So when it is in a cell that relates to Lease 2, it will look at the escalation month for Lease 2. This is acceptable and therefore we are happy for the numbers to change. As a result we will leave the 3 as is.

Below is what the formula should look like. Work through each reference and see if you agree.

Excel $ Sign Tips and Tricks

Some useful tips and tricks for $ sign work

• If you highlight a cell reference e.g. the A4 in the formula =A4+A5 and click the F4 key it will toggle between the possible combinations
• Use the auditing toolbar to figure out if you have got the correct $ signs. Once you have made your paste, click on trace precedent and see if it is looking at the correct cells
• You can use the Show Formula button (in the FORMULA tab by the Formula Auditing Toolbar) to easily see the formula in many cells at the same time.