Using Absolute and Relative Referencing ($ signs)

Please purchase the course before starting the lesson.

You may wonder why, when you look at some spreadsheets, the calculations often refer to cells but there are $ signs in front of the letter and number. So instead of =A1 you will see =$A$1.

This is Excel’s way of addressing one of the problems you may have already experienced when copying and pasting calculations.

Copy/ Paste once or Retype the formula a thousand times?

As shown below, in order to calculate the tax amount we need to multiply the Net Profit (C19) with the tax rate (B21). We enter the formula as

=C19*B21.

Why Dollar Signs

This will work fine. But when you want to copy this cell to the cell to the right you hit a problem.

As shown below, what Excel will do is move  where it looks based on how you moved it. So when we copied cell C23 and pasted it into D23 (a shift of one column to the right), the cells within the formula also moved one column to the right. So where the formula was.

=C19*B21 it now becomes

=D19*C21

The move from C19 to D19 is ok because we need to look at the next month’s profit. But the tax rate is in cell B21. Looking for it one column to the right is not going to give you the correct answer.

Why Dollar Signs

So this means that you can’t do a copy and paste on the formula as is. You will need to manually build each formula if you want to keep it as is.

This is where the $ signs come into play.

Most people don’t like to hear this, but understanding $ signs is very important. It is the difference between typing a formula 100 times or typing it once and copying it once.

To see the full course contents click here.

Back to: Advanced Excel Course > Function and Formula Basics