# 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`) 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.

## 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`, 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 be a 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. 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.