What is Cell References in Google sheets?
Cell references in Google Sheets tell us which cell to look for a value. These cell references can refer to a single cell (B1) or a range of cells (A3:A5). The cell references may be present in the same sheet, a different sheet, or a sheet in another workbook. Cell references are commonly used when constructing formulas. Let us look at an example.
The formula can refer to a single cell =A1, which would return the data present in A1, as shown below. Otherwise, we can use a reference to a range, for instance, =SUM(B1:B5), to return the sum of the values in column B.
Table of contents
Key Takeaways
- Cell references are very important in any Google sheet when working with formulas.
- There are two types of cell references: relative and absolute. These behave differently when copied and pasted to other cells. The relative reference changes when a formula is copied to another cell. The absolute reference remains constant no matter where they are copied.
- To use the absolute references effectively, ensure that a dollar sign is placed before the cell reference row and column.
- Using relative and absolute cell references in Google Sheets can save you plenty of time and make your work efficient.
- The F4 key helps you toggle through absolute and relative references.
Range Reference
When we must reference a range that is composed of a group of adjacent cells, we must specify the two cells that are at the corners of the diagonal of the range. In general, the cells at the top left and bottom right corner are specified. For instance, look at the image below. To reference the values from cells C5 up to E10, we specify the range reference as C5:E10.
If the group of cells is within a single column or row, you refer to the top and bottom-most cells or the left or right-most cells, respectively. You can see it in the images below.
When you must use these ranges in a function, use the range’s reference.
For instance, to calculate the sum of values in the range A1:A4, use the formula =SUM(A1:A4) in cell A6 and check the result.
Cell Reference Styles
Now that we have seen the basic way to use cell references in place of tedious direct values, let us look at the different cell reference styles that have been adopted.
#1 – A1 reference
Every Google spreadsheet contains many cells, and (each cell is identified by its column alphabet followed by its row number. Such a style of referencing is known as A1-style referencing. For example, C2, B6, and A1 are all A1 styles of referencing.
#2 – R1C1 reference
We have another way of referencing a cell in Google sheets called the R1C1 notation. It refers to a cell or range reference using a row number and column number instead of the letter-number format. Here, R refers to the Row, and C refers to the Column. For instance, instead of cell A1, you refer to that cell as R1C1 (row 1, Column 1). Cell B5 is referred to as R5C2.
R1C1 references can also be absolute or relative. In R1C1, when you refer to a cell, an address is created for the referred cell with its distance from the active cell. In the R1C1 reference, there is no permanent address for a cell (similar to relative reference in excel); its address depends on where you are referring to it.
Here, the reference to the first cell of your sheet (1,1) is R1C1.
When we want to reference it as a Google sheets relative reference, we use the square brackets. For instance, R[1]C[2] is a relative R1C1 reference. It means a cell that is a row down and has two columns right of the current cell.
A value represented as R[-4]C[1] refers to a cell that is behind by four rows and in the next Column.
The R1C1 notation can also reference ranges where two cell references are joined with a “:” colon, like how it is done in the A1 reference.
For example, R[-3]C[0]:R[-1]C[1] refers to the range shown in blue in the figure below if R5C3 is the active cell.
How to Create a Cell Reference in Google sheets?
As we all know, there are two types of cell references in Google sheets: relative and absolute. The relative references change when a formula is copied to another cell, while the absolute references remain constant no matter where they are copied.
Relative References
In the following Google sheets cell reference example, we create a formula that will add the Maths and Science marks of some students. Instead of creating a new formula for each row, we can create a single and copy it to the other rows. In this formula we will use relative references so the formula changes when copied across cells.
Step 1: Select the cell to enter the formula. In this example, we’ll select cell D2. Enter the formula to find the subjects’ total.
=SUM(B2+C2)
Step 2: Press Enter. The result will be displayed in the cell. Now, to copy the formula to the other cells, use the fill handle, which appears in the bottom-right corner of the cell.
Drag the fill handle over the cells you want to fill. In this example, it is cells D3:D7. The formula will be copied to the selected cells using relative references, displaying the result.
Step 3: You will notice that the relative cell references change for each cell depending on their rows. The row number has changed, as shown in the image below, thereby allowing relative references to be modified accordingly.
To create and copy a formula using absolute references
At times, you may not want a cell reference to change when you copy the formula. You can use the absolute reference to keep a row or column intact in the formula. For Google sheets absolute reference, you use a dollar sign ($) to precede the row reference, column reference, or both.
In the Google sheets cell reference example below, we’re going to use cell C1 containing the tips percentage and calculate the total bill paid by the customers at a café. To ensure that the reference to the tip rate stays constant, we must make cell C2 an absolute reference by writing it as $C$1.
Step 1: Enter the following formula in cell C2.
=B2*$C$1.
You can see the tip amount to be paid by the first person.
Step 2: Now, click and drag the fill handle over the cells you want to fill (cells C3:C7 in this example).
The result will be displayed in each cell. The formula will be copied to the selected cells with the absolute reference, and the values will be calculated for each cell. The Google sheets absolute reference should be the same for each cell, while the other references change according to the row.
Without the dollar sign, Google Sheets will interpret it as a relative reference, thereby producing an incorrect result when copied to other cells.
How to Change a cell reference in a formula?
In Google sheets, a cell reference is a relative reference by default. For example, if you refer to cell A3 from cell C3, you are referring to a cell that is two columns to the left in the same row (2). When you copy a formula containing a relative cell reference, that reference in the formula will change.
Look at the image below. You have used the absolute reference in the formula in cell D2.
There may be instances when you want to mix the absolute and relative cell references by preceding either the row or the column with a dollar sign. For example, $E$2 can be written as $E2 or E$2. Here, when we copy the formula to another cell,
- For $E2, the column remains the same while the row will change.
- For E$2, the row remains the same while the column changes.
Now, to toggle between these references, use the following steps.
Step 1: Select the cell containing the formula. We will choose D2 in this case. Go to the formula bar and choose the reference you want to change.
Step 2: Press F4 to switch between the reference types. Each time you press F4, you can see the results changing like the image below.
Cross Reference in Google sheets
Cross referencing in Google Sheets allows you to link data from one cell to another. It helps keep your spreadsheets organized. Cross-referencing ensures accuracy and consistency throughout your entire document. Understanding the cross-reference function and its parameters is essential for efficient cross-referencing.
– From another Sheet
In Google sheets, it is possible to link between two sheets by creating a formula between them. Look at the image below.
Step 1: In the destination file, select the cell to type the formula, and then type (=).
Step 3: Go to the source sheet and select the cell you want.
Step 4: Press Enter to complete the entire formula. You get the price per item multiplied by the number of items sold.
When a linked formula is created between two sheets, the syntax of the linked formula is:
=’Googlesheet’!CellReference
– From another workbook
Step 1: To do this, you must first open both workbooks.
Step 2: Next, copy the URL of the workbook that contains the cell you want to reference.
Step 3: In the workbook where you want the data to appear, type the =IMPORTRANGE function into the cell where you want the data to display. Its syntax is =IMPORTRANGE(“spreadsheet_url”, “range_string”), where “spreadsheet_url” is the URL copied, and “range_string” is the being referenced.
Paste the URL you copied in quotation marks, then a comma and the cell reference in another set of quotation marks. Ensure the cell reference includes the sheet name and cell number. (=Sheet8!B2)
Step 4: Now, Google Sheets will ask permission to access the source workbook. Click “Allow.”
When you grant permission, a link is created between the two workbooks. Now, the data from the source workbook cell will appear in the cell where you entered the formula. You can see that the cost of one coffee has been multiplied by 100.
Types of Cell References
#1 – Relative
A cell reference that is relative to the location of the cell is called a relative reference. This is the default setting for Google sheets. For example, In the example below, you see how the sets of numbers are added with relative reference.
Here, A2 and B2 are the relative references. If you drag the formula from C2 to C5, the formula changes accordingly.
When you copy and paste formulas with relative references, the references will move proportionally. Unless you specify with a $ sign, cell references will be relative (unfrozen) by default.
#2 – Absolute
If the cell references should not change when the formula is moved to another cell, you use the absolute reference. You must use the dollar sign in front of the reference (both row and Column) to freeze your cell reference. You can fix the row reference, column reference, or both.
The below images show how a value changes with change in the cell to which it is copied based on how the absolute reference is set.
When a row has an absolute reference, and the Column has a relative reference
When a row has absolute reference and column has relative reference
When a row has a relative reference and a column has an absolute reference
When row and column have relative reference
Instead of manually adding the $ sign each time, you can select a cell and press F4 to toggle between absolute and relative references as seen in one of the examples earlier.
Let us look at the example of relative references and extend it further. We have added a number in cell F1, which should be added to each of the sums. Let us freeze this reference and use it in the SUM formula.
Enter =SUM(A2,B2, $F$1) in cell E2. Press Enter.
Now, drag the formula till cell E5.
You can observe that the absolute reference remains the same and only the relative reference changes.
#3 – Mixed
A mixed reference is when either a row or column reference is locked.
In a mixed reference, either a row or column is fixed while the other reference changes according to the cell. In the example below, we have the cost and discount percentage of certain Apple products. We apply the formula =PRODUCT(D3*$B2) in D4 to find the discount amount.
Now, copy-paste this formula in cell F5 to find the discount on the keyboard. Here, if you notice the above formula, the column B has been locked in $B2 while the row is a relative value.
You can observe that the mixed reference has its row value changed but not the column value when calculating the discount for the keyboard.
Switch Between Different References Types
As mentioned in the earlier examples, to toggle between the reference types, select the reference to be changed and press F4. Each time you press F4, you can see the results changing like the image below for the formula of the above example.
Circular Reference
A circular cell reference happens when a cell refers back to itself. For instance, if the result of cell D1 is again used as an input to cell D1, we create a circular reference. The cell can either directly refer to itself or there many be many other steps involved.
As an example, we are finding the sum of a few numbers. We also include the final sum in the formula. Look at the image below.
You can observe that we have included B6, the final sum, in the formula as well.
The formula for cell B6 is “=SUM(B1:B6).” It would trigger a circular reference, and the value in B6 is initially set to zero. But before that, Google Sheets requires you to enable iterations as it otherwise throws an error.
Go to File -> Settings. You get a dialog box where you fill in the number of iterations after enabling “Iterative Calculation.”
Now, you get the result of the formula. We get 30, as we have mentioned two iterations.
Download Template
This article must help understand Cell References in Google Sheets with its examples. You can download the template here to use it instantly.
Frequently Asked Questions (FAQs)
To create absolute cell references in Google Sheets, we must add a “$” symbol before the column and/or row reference. This will lock its address. For example, to lock the reference to cell B1, you must enter “$A$1” in the formula.
It is a tedious process to type the dollar signs into formulas in a Google Spreadsheet. So, to change the references between absolute, mixed, and relative, click on a cell, select the cell reference you want to change and press the F4 key. It will create double-dollar signs on that cell reference. Pressing the F4 key again makes the dollar sign appear only on the row. Keep pressing the F4 key to change the reference types.
The dynamic cell reference in Google Sheets uses the INDIRECT function to refer to cells or any ranges that adjust automatically based on the conditions. This technique boosts formulas to adapt to new data or structural changes without manual updates.
Recommended Articles
Guide to Cell References in Google Sheets. Here we learn how to create a Cell Reference in Google sheets with step by step examples. You can learn more from the following articles –
Leave a Reply