One of the great things about spreadsheet programs is their ability to copy a formula and to intelligently modify that formula when it is pasted back into a cell.

For example, when we created the calculation for margin in the last module the calculation was C2 minus D2. That works great for the first row but it doesn’t work for the second row.

For then next row, the calculation should be C3 minus D3. When the calculation was pasted into the worksheet, Calc correctly changed the cell reference in the calculation based on the relative position of the original cell and the new cell that holds the formula. The type of cell references that intelligently adjust based on their position in the spreadsheet are called relative cell addresses.

Sometimes you don’t want Calc to change the cell addresses. There are times when you always want a calculation to refer to a specific cell.

Let’s look at an example. The price column in our spreadsheet is calculated by multiplying the cost by a multiplier in this case 250% or 2.5.

Calc will let you control whether it changes the relative cell references or not through the use of absolute cell addresses.

To see how this works, let’s recreate the price column calculations. Suppose that list price is derived by multiplying cost by a certain value. In this example the list price is 2 and one-half times cost (or cost times 2.5). But what if the formula changes? What if we wanted to charge the formula from price times 2.5 to price times 3.

The way the formula is set up now, we would have to change the multiplier in each formula form 2.5 to 3. That could be a lot of work.

Rather than do it the hard way. We could change store the multiplier somewhere in the spreadsheet and change the formula so that it references the cell that contains the multiplier, rather than a hard coded number.

So that we can see what’s going on better, we’ll hide a few columns by selecting them, right-clicking, then selecting hide from the popup menu.

Next, let’s create a cell the hold the multiplier value.

Enter the text “cost multiplier” in Cell Q3 then

Enter the value of 3 into cell Q4.

Next lets modify the formula by replacing the static value of 2.5 with the cell reference $Q$4.

The dollar signs in this formula change the reference from a relative cell reference to an absolute cell reference.

Now, when we copy the formula and paste it into the spreadsheet it the formula will always refer to the value in cells Q4.

Let’s try it out. Delete all of the calculations in the column except for the first row. Copy the calculation in the first row. Select the cells that should be filled with the calculation, right-click and select paste.

Let’s see what happened here by highlighting a cell. Notice how part of the formula contains a relative address that changes for each row.
Another part of the formula contains the absolute cell reference we just created. This value doesn’t change from row to row. Each cell always references the cell Q4

Now that the cell address is in place. Let’s see how this approach can save us time. Suppose we wanted to change the multiplier to 3.5. Rather than change all of the calculations, we can just change the value in the multiplier field back to 2.5. Since all of the price references relate to the value in the multiplier cell. They are all updated automatically. Using thus approach, you can change the calculations of hundreds of cells by just making a single change to the spreadsheet.

Whenever you are creating a series of calculations that contain a value that is likely to change, you can place the value in a cell and reference the cell using an absolute cell address like we just did in the example.

This approach makes your spreadsheets much more flexible and easier to maintain.