Cell references tell Google Sheets where to look for a value. Cell references can point to a single cell (A1
) or 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.
Contents
Video
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 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 shown 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.
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 the correct result of 1,907.92
.
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 corner of the grid, or the A1:C1
, a horizontal range comprised of three adjacent cells, as shown in the second image below.
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.
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.
You must multiply rows 3
through 6
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.
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.
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 spreadsheet involves more 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
, copy the sharing link to the spreadsheet you are referencing. The value for spreadsheet_url
must either be enclosed in quotation marks or be a reference to 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.
Table References
If your data is in a Google Sheets table, you can reference the entire table or individual columns with a unique syntax called a Table Reference. These references need a table and column name as such: (Table Name[Column Name])
.
If you want to reference the Total
column in a table called Sales_History
, use the syntax =SUM(Sales_History[Total])
. See more examples in our table references page.
Bonus Tip – Expanding Ranges
Your cell references may need to be updated if you add data to your spreadsheet. You can drop the row numbers from your references to avoid this problem. For example, if you want 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, the beginning and ending rows can be omitted, such as A:A
for the whole 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.