Referencing Cells in Google Sheets

If your formula is to work properly, it is vital to grasp what happens to cell references as you copy a formula to new cells in your worksheet. By default, Google Sheets treats cell references as relative references. When these are copied, they change: If you copy a formula from a cell in row 1 (such as B1) into cell C8, all of the relative cell references in the formula will change (in this case by adding one column and seven rows, so if your formula includes cell D4, in the copied formula it will contain E11 instead).

Usually this is fine. If you want to show the sum of column B (tax) to column A (price), for example, by placing a formula in column C, and then copy this formula to multiple lines, then you’d want the relative references to change; on row 10, you’d want cell C10 to contain:

=(A10 + B10)

There will be times, however, when you don’t want a cell reference to change when copying a formula to other cells. In this case you need to use an absolute reference to keep a row and/or column constant in the formula, regardless of where that formula might be copied to. Absolute references do not change when they are copied – they still reference the exact same cell that they did in the original.

An absolute reference is designated by the addition of the dollar sign $. It can be added before the column letter, the row number, or both, but normally you’ll find yourself using both.

Take a look at this snippet of spreadsheet. Here we’ve grabbed the current euro/pound exchange rate from the web (this post explains how), and we want to apply it to each of our bank accounts:

Click image to enlarge

Click image to enlarge

The value in C4 is calculated by multiplying the bank balance in B4 by the exchange rate in B1. We want to copy the formula to cells C5, C6 and C7 to show equivalent values for the other accounts. However, in each row the address of cell B1 must remain fixed; we must use an absolute reference for cell B1 and a relative reference for cell B4. Here’s the formula prior to the copy and paste:

Click image to enlarge

Click image to enlarge

After we make the copy and paste to cells C5, C6 and C7, the value of B1 has been retained on each occasion, but the value of the bank balance term has incremented. Let’s see what formula has been applied to cell C7:

Click image to enlarge

Click image to enlarge

As you can see, the formula has modified the ‘bank balance’ cell to B7, but is still multiplying that cell’s value by the figure in cell B1.


2 thoughts on “Referencing Cells in Google Sheets

  1. Pingback: Referencing Cells in Google Sheets | Instructio...

  2. Pingback: Using VLOOKUP in Google Sheets | Drive Bunny

Leave a Reply

Your email address will not be published. Required fields are marked *