How to Link Cells in Excel (Same Worksheet, Between Worksheets/Workbooks)

Linked cells in Excel refer to the contents of other cells. The other cells may be in the same worksheet, other worksheets in the same workbook, or worksheets in other workbooks. 

This tutorial shows you how to link cells in Excel in the same worksheet, in different worksheets, and in different workbooks.

Benefits of Linking Cells in Excel

The benefits of linking cells in Excel include:

  • Data entry errors are minimized in that you do not have to re-enter the values in the referenced cells. 
  • Data inconsistency is minimized. When the data in the source worksheets are changed, the values in the destination worksheets also change accordingly. 
  • Workbooks are faster to open, calculate and save. When large workbooks are split into linked interdependent workbooks, you can work on the smaller workbooks one at a time without having to open all their related worksheets.    

How to Link Cells in the Same Worksheet

Let’s have a look at some methods to link cells in the same worksheet.

How to link a single cell manually

If we want to link a single cell in the same worksheet, the manual method is much faster.

In this method, we type the cell reference of the source data in the dependent cell.   

Suppose we have the following dataset of a family’s monthly cash flow in an Excel worksheet. 

Dataset

We want to link cell C4, which will contain the opening balance of cash on hand in February to cell B26 which contains the closing balance of the cash on hand in January. 

We follow the steps below:

  1. Select cell C4 and type in the following formula:
=B26
Enter cell reference
  1. Press Enter on the keyboard or click Enter on the formula bar.
click Enter on the formula bar

Cell C4 is now linked to cell B26 and the value in cell B26 is displayed in cell C4.

If the value in cell B26 changes, the value in cell C4 will also change accordingly.

How to link a range of cells using the Paste Special feature

Using the manual method to link a range of cells is inefficient. Using the Paste Special feature in Excel is the efficient way. 

Suppose we have the following dataset.

Dataset

We want to link the range E11:E24 in another location of the worksheet to the range A11:B24 which contains the expenses data. 

We proceed as follows:

  1. Select range A11:B24 and press Ctrl + C to copy it.
Select the cells you want to link
  1. Select cell E11.
Select destination cell
  1. Open the Paste Special dialog box using any of the following techniques:

Press Ctrl + Alt + V.

Or

In the Home tab, select the Paste Special option in the Clipboard group:

Click on paste special
  1. Click the Paste Link button in the bottom left corner of the Paste Special dialog box.
Click on Paste link button

The range E11:F24 is now linked to the range A11:B24. The values in range A11:B24 are now displayed in the range E11:B24. If the values in the source range change, the values in the destination range also change accordingly.

Cells are linked

Link Cells Between Worksheets in the Same Workbook

Let’s now look at some ways you link cells in different worksheets that are in the same Excel workbook.

How to link a single cell using the point-and-click technique

Using the manual method to link cells between worksheets is not recommended because it is slow and error-prone.

The point-and-click technique is fast and accurate. It involves placing the mouse pointer on the source cell, clicking it, and pressing Enter to create the link

Suppose we have the following workbook that has three worksheets. 

Three worksheets

The South Region and East Region worksheets contain sales data. The Summary worksheet will contain a summary of the total sales of the two regions. 

We want to link the two destination cells in the Summary worksheet shown below to the source cells in the South Region and East Region worksheets:

Summary sheet

We use the steps below:

  1. Select cell B2 in the Summary worksheet and type in the equal sign (=).
Enter equal to in a cell
  1. Open the South Region worksheet by clicking its tab.
Click on South Region tab
  1. Select cell D7 which contains the total sales figure for the South Region. Notice the syntax of the formula in the formula bar. The syntax will be described later in this tutorial.
Notice the syntax of the formula in the formula bar
  1. Press Enter.
Press Enter

You are taken back to the Summary worksheet. Cell B3 in the Summary worksheet is now linked to cell D7 in the South Region worksheet.

The value in cell D7 in the South Region worksheet is now displayed in cell B3 of the Summary worksheet.

If the value in the source cell changes, the value in the destination cell changes accordingly.

  1. We repeat steps 1-4 to link cell B4 in the Summary worksheet to cell D7 in the East Region worksheet.
Repeat the same steps

The Regional Sales Summary dataset is now complete.

Summary dataset is now linked

How to link a range of cells using the Paste Special feature

We can use the Paste Special feature to paste several links in the cells in the destination range.

Suppose we want to link the range A9:A13 in the Summary worksheet to range C2:C6 in the East Region worksheet shown below:

Select the range

We use the following steps:

  1. Select range C2:C6 in the East Region worksheet and press Ctrl + C to copy it.
Select the range
  1. Open the Summary worksheet by clicking its tab.
Click on the summary worksheet
  1. Select cell A9 in the Summary worksheet.
Cell cell A9
  1. Open the Paste Special dialog box using any of the following ways:

Press Ctrl + Alt + V (one by one).

Or

In the Home tab, select the Paste Special option in the Clipboard group:

Click on Paste Special
  1. Click the Paste Link button in the bottom left corner of the Paste Special dialog box.
Click on Paste link

The range A9:A13 in the Summary worksheet is now linked to range C2:C6 in the East Region worksheet.

Cells are linked

How to Link Cells Between Workbooks

Sometimes we may need to split a large workbook into smaller workbooks and link cells between them. The workbooks will become faster to open, calculate, and save. 

Suppose we split the workbook that contains the regional sales data into three workbooks: the South Region Sales workbook, the East Region Sales workbook, and the Sales Summary workbook.

The Summary workbook is the destination workbook because it will contain external references or links. The South Region and East Region workbooks are the source workbooks because they contain the data that we want to link to. 

How to link a single cell using the point-and-click technique

We want to link cell B3 in the Sales Summary worksheet of the Sales Summary workbook to cell D7 in the Sales Record worksheet of the South Region Sales workbook

Sales Summary worksheet
South Region Sales workbook

We also want to link cell B4 in the Sales Summary worksheet of the Regional Sales Summary workbook to cell D7 in the Sales Record worksheet of the East Region Sales workbook.

Sales Record worksheet

We follow the steps below:

  1. Open all three workbooks. 
Open three workbooks
  1. Select cell B3 in the Sales Summary worksheet of the Regional Sales Summary workbook and type in the equal sign.
Select the cell
  1. Switch to the South Sales workbook and click the Sales Summary worksheet and select cell D7. Notice the syntax of the link in the formula bar and the absolute reference. The syntax and absolute cell reference will be explained later in this tutorial.
Switch the the next workbook
  1. Press Enter. 

Excel takes you back to the Regional Sales Summary workbook, the destination workbook, and displays the value from the South Region Sales workbook, the source workbook.

Repeat the steps
  1. Repeat steps 1-4 to link cell B4 in the destination workbook to cell D7 in the Sales Summary worksheet of the East Region Sales workbook.
All data linked

How to link a range of cells using the Paste Special feature

We want to link the following range A9:A13 in the Sales Summary worksheet of the Regional Sales Summary workbook, the destination workbook:

Link these cells

To the following range C2:C6 in the Sales Record worksheet of the South Region Sales workbook, the source workbook.

link to these cells

We use the following steps:

  1. Open the destination workbook and the source workbook.
Open both workbooks
  1. Select range C2:C6 in the Sales Record worksheet of the source workbook and press Ctrl + C to copy it.
Select the cells you want to link
  1. Switch back to the destination workbook and select cell A9 in the Sales Summary worksheet.
Select the destination cell
  1. Open the Paste Link dialog box using any of the following techniques.

Press Ctrl + Alt + V.

Or

In the Home tab, select the Paste Special option in the Clipboard group:

Click on Paste Special
  1. Click the Paste Link button in the bottom left corner of the Paste Special dialog box.
Click on Paste Link

The range A9:A13 in the Regional Sales Summary worksheet of the destination workbook is now linked to range C2:C6 in the Sales Record worksheet of the source workbook.

The values in the source range are displayed in the destination range. Any changes in the values in the source range will be reflected in the destination range. 

Cells linked

The syntax of external references

When we linked cell B3 in the Summary worksheet to cell D7 in the South Region worksheet in the same workbook, we noticed the syntax of the link in the formula bar. 

Syntax when cells are linked
  • =’South Region’!D7   The name of the source worksheet is enclosed in single quotes because it includes a space character. If it did not have a space character it would not be enclosed in single quotes. The exclamation point separates the worksheet name from the cell reference.

When we linked a cell between workbooks we also noticed the syntax of the link in the formula bar. For example below:

Syntax when cells are linked
  • ='[South Region Sales.xlsx]Sales Record’!$D$7 – The name of the source workbook is enclosed in square brackets followed by the name of the source worksheet. The names of the source workbook and source worksheet are enclosed in single quotes because the name of the worksheet includes a space character. 
  • The names of the source workbook and source worksheet are separated from the cell reference by an exclamation point. The cell reference is an absolute reference. Excel always returns external links between workbooks with absolute references. You will need to remove the dollar signs if you want to copy the formula to other cells.     

In this tutorial, we have looked at different techniques for linking cells in Excel.

We can link cells manually, use the point-and-click method, or use the Paste Special feature.

Other articles you may also like: