How to Cross-Reference in Excel & Google Sheets

This tutorial demonstrates how to cross-reference other data in Excel and Google Sheets.

 

An Excel workbook can be made up of multiple worksheets. You may wish to create a summary worksheet that refers to other worksheets in your file. You can do this by creating formulas that refer to or cross-reference to the other sheets. You can also create formulas that refer to or cross-reference to other Excel files (workbooks).

The worksheet supplying the data is called the source worksheet while the worksheet containing the reference is termed the dependent worksheet.

Reference Another Sheet With Paste Special

1. Select the cells in the source worksheet that you wish to link to the destination sheet, and then in the Ribbon, select Home > Clipboard > Copy, or press CTRL + C on the keyboard.

 

 

2. Click in the destination sheet and select the cells where you wish to insert the linked data.

3. In the Ribbon, select Home > Clipboard > Paste > Other paste Options > Paste Link.

 

OR

1. In the Ribbon, select Home > Clipboard > Paste > Paste Special, and then select Paste Link.

 

 

2. Linked formulas are created for you linking the destination cell references to the source cell references.

Reference Another Sheet Manually

1. Click in the cell in your destination sheet where you wish the linked formula to be placed, and then press equals (=) on the keyboard to begin your formula.

 

 

2. Select the source sheet (the sheet the value is coming from), and then click on the source cell.

 

 

3. Press ENTER to complete your formula.

 

 

4. When a linked formula is created between two sheets (either by Paste Special or manually) the syntax of the linked formula will always be as follows:

='Worksheet'!CellReference

5. If the sheet name contains spaces or any characters that are not alphanumeric (such as a hyphen, in this case), it is contained between single quotation marks. It will then always have an exclamation mark after it, and then the cell reference will follow to complete the formula.

For example:

=CarSales-North!B6

6. Once you have created a linked formula, you can copy the formula down and across, as long as the formula does not contain any absolute (anchored) references.

 

 

Create a Linked Formula With Range Name

1. If you have range names in your source worksheet, you can create linked formulas to these range names.

2. The worksheet shown below has a range name called Sedans-North, which refers to cells B6:D6.

 

3. In the destination worksheet, click in the cell where you need the formula to go and press the equal sign (=).

 

4. Switch to the worksheet that contains the range name you want, and then select the range name.

 

5. Press ENTER to complete your formula. Notice that the cells adjacent to the cell where you entered the equal sign are also populated with the correct formula.

 

 

6. However, if you click on one of the adjacent cells, you will see that the formula is grayed out. This is because an array formula has been created. In older versions of Excel, you had to press CTRL + ENTER on the keyboard but the IntelliSense in the newer versions of Excel do this for you. You cannot, however, delete a formula unless you are on the original cell where the formula was created (in this case C6).

 

 

Reference Another File With Paste Special

1. You can also link two separate Excel files (workbooks) together using linked formulas.

2. Before you link two files together, make sure that both files are open, and have been saved with any recent changes.

 

 

3. As with linking two worksheets together, in the source workbook, select the cell references to copy, and then in the Ribbon, select Home > Clipboard > Copy, or press CTRL + C on the keyboard.

 

 

4. Click back in the destination workbook and select the cell references where you wish the linked data to go.

5. In the Ribbon, select Home > Clipboard > Paste > Other paste Options > Paste Link.

 

6. A linked formula is created in each of the selected cells. This formula differs slightly to the linked formula when you are linking worksheets together as the formula will contain the name of the file as well as the name of the sheet.

 

 

Reference Another File Manually

1. As with creating a formula between two worksheets, you can manually create a formula between two open files (workbooks).

2. Click in the cell in your destination workbook where you wish the linked formula to be placed, and then press equals (=) on the keyboard to begin your formula.

 

 

3. Click on the source workbook and the sheet the value is coming from and click on the source cell.

 

4. Press ENTER to complete your formula.

 

5. When a linked formula is created between two files (either by Paste Special or manually) the syntax of the linked formula will always be as follows:

='[FileName] WorksheetName'!Reference

6. The name of the workbook is enclosed in square brackets while the file name and the worksheet name will then be contained between single quotation marks. An exclamation mark will then follow with the cell reference following to complete the formula.

For example:

='[NorthernBranch.xlsm]CarSales-North'!B6

7. Notice that, when a formula is created manually between two files (as opposed to using Paste Link), the cell references in the formula are automatically put in as absolute (or anchored) references. This means that you are unable to copy the formula across or down unless you amend your formula to remove the absolute (dollar) signs.

Cross-Reference in Google Sheets

You can link between two sheets in a Google Sheets file by manually creating a formula between them.

1. In the destination file, select where you want the formula to go, and then press equals (=) on the keyboard.

 

2. Click on the source sheet and select the cell you want.

 

3. Press ENTER to complete the formula.

4. It is possible to link two separate Google Sheets files together using a function called IMPORTRANGE.

5. Make sure both the destination and source files are open, and then copy the URL of the source file.

 

 

6. Switch back to the destination file, and in the cell where you want the link to go, enter the following formula:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1xWqFy2UgYpicRItMvjBYA4f6n9-5M8iBYi4n7O9ddNc/edit?pli=1#gid=0", "C7")

7. You may find that you need to give your file access to the source file.

 

8. Click Allow access to complete the formula.