How to Cross-Reference in Excel & Google Sheets

This tutorial will demonstrate 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

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.

 

 

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

 

 

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

 

 

OR

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

 

 

Linked formulas will be created for you linking the destination cell references to the source cell references.

Reference Another Sheet Manually

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.

 

 

Click on the source sheet (the sheet the value is coming from) and click on the source cell required.

 

 

Press ENTER to complete your formula.

 

 

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

If the sheet name contains spaces or any characters that are not alpha-numeric (such as a hyphen in this case), it will be 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

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

 

 

Create a Linked Formula Between Worksheets with a Range Name

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

The worksheet shown below has a range name called “Sedans-North” which refers to cells “B6:D6”

 

In the destination worksheet, click in the cell where you require the formula to go and press the equals (=) key on the keyboard.

 

 

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

 

 

Press Enter to complete your formula. You will notice that the cells adjacent to the cell where you entered the equals sign are also populated with the correct formula.

 

 

However, if you click on one of the adjacent cells, you will see that the formula is grayed out. This is due to the fact that 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

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

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

 

 

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.

 

 

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

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

 

 

A linked formula will be 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

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

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.

 

 

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

 

 

Press ENTER to complete your formula.

 

 

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

The name of the workbook will be 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

You will notice 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.

How to Cross-Reference in Google Sheets

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

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

 

 

Click on the source sheet and select the cell required.

 

 

Press enter to complete the formula.

 

 

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

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

 

 

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/1gTQxVJXErH6mIIr9mGFYKaNCpwwFrdv99NHfgZJEils/edit#gid=0", "B6")

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

 

 

Click Allow access to complete the formula.