See all How-To Articles

How to Cross-Reference in Excel & Google Sheets

This tutorial will demonstrate how to cross-reference other data in Excel & Google Sheets.

crossreference intro

An Excel workbook can be made up of multiple worksheets.   You many 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.

crossreference copy

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

crossreference selectcells

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

crossreference pastelink

OR

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

crossreference pastespecial pastelink

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

Reference Another Sheet Manually

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

crossreference equals

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

crossreference source select

Press enter to complete your formula.

crossreference press enter

When a linked formula is created between 2 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 hypen 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:

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.

crossreference copy

Create a Linked Formula Between Worksheets with a Range Name

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

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

crossreference rangenames source

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

crossreference combinedequals

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

crossreference select rangename

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.

crossreference linked rangename

However, if you click on one of the adjacent cells, you will see that the formula is ‘greyed out’.   This is due to the fact that an array formula has been created.   In older versions of Excel we used to have to press Ctrl+Enter on the keyboard but the IntelliSense in the newer versions of Excel do this for us.  We cannot however delete a formula unless we are on the original cell where the formula was created (in this case C6).

 

crossreference disabled rangename

Reference another File with Paste Special

We 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.

crossreference 2-files

As with linking 2 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.

crossreference sourcecopy

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.

crossreference destinationpaste

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

crossreference linkedformula

 

Reference Another File Manually

As with creating a formula between 2 worksheets, we  can maually create a formula between 2 open files (workbooks).

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

crossreference equals workbook

 

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

crossreference select source

Press enter to complete your formula.

crossreference destination enter

When a linked formula is created beween 2 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 be contained between single quotation marks.  An exclamation mark will then follow with the cell reference following to complete the formula.

for example:

You will notice when a formula is created manually between 2 files (as opposed to using Paste Link) , the cell references in the formula are automtically 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

We  can link between 2 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.

crossreference gs equals

Click on the source sheet and select the cell required.

 

crossreference gs select source

Press enter to complete the formula.

crossreference gs formula

It is possible to link 2 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.

crossreference gs copy url

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

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

crossreference gs allow access

Click Allow access to complete the formula.

crossreference gs importrange