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.
Creating a Cell Reference
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.
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.
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 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.
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
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.
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
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 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.
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
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
Cell references are typically inside formulas. Now that you understand how to create the different types let’s move on to how formulas work.