Using Cell References in Google Sheets

Cell references tell Google Sheets where to look for a value. Cell references can point to a single cell (A1), a range of cells (D3:D6). These cells can be in the same sheet, a different sheet, or a sheet in another file. We will cover all of these scenarios below.

You use cell references when constructing formulas. The formula can be as simple as =A1, which would return the string Sample Data to =AVERAGE(D3:D6) to return the average of the values in column D. Get a copy of the spreadsheet used in the examples to follow along.

Video

Video thumbnail
Video Tutorial on Cell References

Creating a Cell Reference

Typing

It may seem old-fashioned, but the easiest way to create a cell reference is just by typing it in. If your formula is short and you know what to type, keeping both hands on the keyboard and typing is the fastest.

Mouse

You can also select a cell reference with your mouse.

When creating a formula, you always start with an equals sign. After typing an equals sign, you can input a simple reference to cell C4 by typing =C4. See how formulas work to learn more about building formulas.

As you can see in the animation, a formula suggestion often pops up. If it guesses wrong, as it did in this case, ignore it, continue to write your formula, and select the cell with your mouse.

Formula Suggestion

Google’s AI tries to guess your formula. As you type, it narrows the choices. Often it guesses correctly, making the formula suggestion a good choice.

Formula suggestion getting it close enough
Formula Suggestion

Here we see the suggestion of =SUM(D2:D6). The cell reference of D2:D6 is close enough, but it contains the text string of Total from cell D2. While this string is not a number, and you shouldn’t include it in your formula, it does not change the result of the SUM function and therefore produces an accurate result.

Types of Cell References

Now that we have covered how to enter a cell reference let’s dive into the different types. These types can be mixed and matched. For example, you could have a relative cell reference, a fixed cell reference to another sheet, a relative cell reference to another file, etc.

Relative Cell References

Relative cell references are the most common type of cell reference. They consist of only letters and numbers such as A1, the first cell in the upper left can corner of the grid, or the A1:C1, a horizontal range consisting of three adjacent cells as shown in the second image below.

Cell reference of A1
Cell A1
Cell range of A1:C1
Cell Range A1:A3

You can also see the active cell ranges in the name box in the upper left of these images. The name box can help show the location of the active cell if it is not visible. Also, you can use the name box for naming ranges.

Relative cell references get their name from being relative to their position. If you copied the reference of A1 and pasted it one cell to the right, it would become B1 just as it would become A2 if you pasted it one row below.

Relative Cell References Changing When Copied

This example shows the cell references that would result if you entered a formula into cell D3 and copied it through D6.

Fixed Cell References

Fixed cell references are handy if you do not want a column or row reference to change when your formula moves. You can fix the row, column, or both by adding a dollar sign. An example would be multiplying items in a column by a fixed tax rate, as shown below.

Fixing a Cell Reference

Rows 3 through 6 need to be multiplied by the tax rate in cell B1. The formula =A3*B1 in cell B3 would shift to =A4*B2 when copied to cell B4. The row number does not change after fixing the reference to cell B$1 with a dollar sign, . After being copied to cell B4, the formula would become =A4*B$1.

You can also fix column references to stop formulas from shifting left or right. In the example above, you could reference B1 as $B$1. However, the result would be the same.

Pro Tip: F4 can be used as a shortcut to fix references. Highlight the cell reference to fix, and press F4 until the dollar sign is where you want it.

References To Another Sheet

Each spreadsheet can contain more than one sheet. These sheets are shown as Sheet Tabs at the bottom left of the spreadsheet and are essential to more extensive spreadsheets.

Different tabs with sheet names
Sheet Tabs Showing Sheet Names

Multiple sheets are optional, but it is common to have a spreadsheet with multiple sheets.

The easiest way to create a reference to a cell in another sheet is to type an equals sign, go to the sheet you want to reference, and click on the desired cell. Clicking on the cell will build the cell reference for you.

Reference to Another Sheet

Alternatively, you can type the reference with the following syntax. ='[Name of sheet]'!Cell. You only need to surround the sheet name with single quotes if there is a space in the sheet name. Therefore, a best practice is not to use spaces when naming your sheets.

References to Cells in Another File

Referencing a cell in another sheet is more involved than a cell in the same file. To do this, you need to use the IMPORTRANGE function. The syntax is =IMPORTRANGE(spreadsheet_url, range_string). To get the spreadsheet_url, go to the spreadsheet you are referencing and copy the sharing link. The value for spreadsheet_url must either be enclosed in quotation marks or reference a cell containing the URL of a spreadsheet. The range_string is text in the format “[sheet_name!]range” (e.g. "Sheet1!A2:B6" or "A2:B6"). The sheet_name part of range_string is optional. By default, IMPORTRANGE will import the given range of the first sheet in the file.

The value for range_string must either be in quotation marks or be a reference to a cell containing the text. The first time the destination sheet pulls data from a new source sheet, Sheets will ask for permission. Once access is granted, any editor on the destination spreadsheet can use IMPORTRANGE to pull from any part of the source spreadsheet.

Bonus Tip – Expanding Ranges

Your cell references may need to be updated if you add data to your spreadsheet. To avoid this problem, you can drop the row numbers from your references. For example, if you wanted to reference column A starting at row 2 and going to the bottom of the spreadsheet, you can use a cell reference of A2:A. This same methodology applies to a rectangular range where B4:C would reference starting in B4 and go to the last row in column C.

Similarly, if picking up the entire columns is desirable, both the beginning and ending rows can be omitted such as A:A for the entire column A.

Formulas

Cell references are typically inside formulas. Now that you understand how to create the different types let’s move on to how formulas work.

Leave a Comment