Cell Referencing in Excel or Google Sheets

Each cell is referred to as a Column-Row representation such as A1, C10, and so on where the alphabets represent the columns, and the numbers represent the rows.

There are multiple ways in which the cells can be referred. Each type of referencing helps us solve a unique challenge. Let us understand the different types of referencing and when to use these.

Cell Referencing in Excel or Google Sheets

Each cell is referred to as a Column-Row representation such as A1, C10, and so on where the alphabets represent the columns, and the numbers represent the rows.

There are multiple ways in which the cells can be referred. Each type of referencing helps us solve a unique challenge. Let us understand the different types of referencing and when to use these.

A desk with a keyboard, phone, and a chart

The implementation is similar in Excel and Google Sheets. Wherever there is a difference it has been called out specifically.

In the case of a similar implementation, we have used images from Excel.

Referencing a Cell in the Same Worksheet

When you refer to a cell in the same worksheet, the referencing is shown as follows:

=Cell Reference

An example reference to a cell is as below:

Type = sign followed by the cell name to refer a cell in the same worksheet

This is the most common method of referencing and is useful when copying formulas across a table. If you are copying the formulas across rows and the cells referred by the formula move along with the rows, then this method is used.

E.g. if you have data in column C and D spread across rows 1 to 10, and you write a formula in column E which utilizes the values in column C and D of that row. Then you can write the formula in Cell E1 and copy it till E10 using this method.

Referencing a Cell in the Same Workbook

When you refer to a cell in a different worksheet within the same workbook, the referencing is shown as follows:

='Worksheet Name'!Cell Reference

An example reference to a cell in a different worksheet within the same workbook is as below:

Type = sign followed by the sheet name, !, and the cell name to refer a cell in a different worksheet within the same workbook

Relative Referencing

Excel utilizes relative referencing by default when you copy formulas across columns or rows within the same workbook. In the example below for each item, a discount percentage has been identified. We write the formula once in cell D2 and drag it for the remaining items in the table.

Refer to cells without using $ sign before the Column and the Row references

Once you drag the formula from D2 till D7, the formulas in each subsequent row get copied using relative references in such a way that they refer to corresponding cells in the row.

The column and the row references get updated automatically as you copy the formula in the worksheet

Fixed Referencing

In the example let us assume that instead of a product-specific discount, a standard discount of 5% was to be offered as indicated in Cell F1. To apply the discount to the first item we enter the formula in cell C2.

When we copy the formula to other cells, we see that the discount has not been applied. You would notice due to the relative referencing of cells the formulas are referencing to cells F2, F3, and so on. These cells are empty, and therefore no discount has been applied.

However, this is not what we want. We want the formulas to continue referencing cell F1, as we copy the formula down from C2 to C7. We can achieve this by fixing the reference to cell F1 when we write the formula in cell C2. This can be achieved by writing the cell F1 as $F$1.

By adding the $ signs before F and 1, Excel fixes the reference of cell F1 and does not update that to F2, F3 as we copy the formula in the subsequent rows. If you copy the formula now from C2 to C7, you will see that each cell from C2 to C7 refers to F1 for the discount value.

For fixed referencing of cells use a $ sign before the Column and the Row references

To provide a fixed reference for a cell, the $ signs can be added by:.

1. Typing the $ sign manually
2. Selecting the cell reference or bringing the cursor adjacent or in between the cell and pressing F4 on the keyboard

Mixed Referencing

Let us understand what Excel interprets when we refer to a cell $F$1 as in the above example. Excel breaks the above into two parts:

$F: Excel notices that there is a $ before the column reference F. This indicates that when we copy (or drag) the formula to other cells, then it should keep the column referenced fixed to F
$1: Excel notices that there is a $ before the row reference 1. This indicates that when we copy (or drag) the formula to other cells, then it should keep the row referenced fixed to 1

Therefore, in scenarios where you only need to keep the column reference fixed and would want the row references to be relative (move as we copy the formula), you can write it as $F1.

Similarly, if you need to keep the column reference relative and would want the row reference to be fixed, you can write it as F$1.

In the above examples where we offered a standard discount of 5%, we had fixed the reference to cell F1 by writing it as $F$1. In the example, we copied the formula to cells C2 to C3, C4, C5, C6, and C7. We could have also written the formula as F$1 and it would have worked the same.

To provide fixed reference we press F4 on the keyboard. If we press F4 repeatedly it changes the referencing options on each click.

First click of F4: $F$1
Second click of F4: F$1
Third click of F4: $F1
Fourth click of F4: F1

Referencing another Excel file

In your workbook, you can even refer to cells present in a different Excel file. When you provide such a reference, Excel automatically adds the file path before the reference cell. The reference is in the following format:

='Filepath[File Name.xlsx]Worksheet Name'!Cell Reference

i.e. A single quote & file path including the file name & worksheet name & a single Quote & an exclamation mark & the cell reference

Here is an example reference, where a cell A5 present in another file is referred:

='C:Excel Files [File.xlsx]Sheet1'!$A$5

All Excel & Google Sheets Resources

Cell Referencing in Excel or Google Sheets

Cell Referencing in Excel or Google Sheets

Referencing a Cell in the Same Workbook

Relative Referencing

Fixed Referencing

Mixed Referencing

Referencing another Excel file

CONTENT

COMPANY

LEGAL

CONTENT

COMPANY

LEGAL