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.
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:
- Select cell C4 and type in the following formula:
=B26
- Press Enter on the keyboard or 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.
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:
- Select range A11:B24 and press Ctrl + C to copy it.
- Select cell E11.
- 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 the Paste Link button in the bottom left corner of the Paste Special dialog box.
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.
Also read: Excel Hyperlink Cannot Open the Specified File – Fix!
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.
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:
We use the steps below:
- Select cell B2 in the Summary worksheet and type in the equal sign (=).
- Open the South Region worksheet by clicking its tab.
- 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.
- 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.
- We repeat steps 1-4 to link cell B4 in the Summary worksheet to cell D7 in the East Region worksheet.
The Regional Sales Summary dataset is now complete.
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:
We use the following steps:
- Select range C2:C6 in the East Region worksheet and press Ctrl + C to copy it.
- Open the Summary worksheet by clicking its tab.
- Select cell A9 in the Summary worksheet.
- 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 the Paste Link button in the bottom left corner of the Paste Special dialog box.
The range A9:A13 in the Summary worksheet is now linked to range C2:C6 in the East Region worksheet.
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
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.
We follow the steps below:
- Open all three workbooks.
- Select cell B3 in the Sales Summary worksheet of the Regional Sales Summary workbook and type in the equal sign.
- 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.
- 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 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.
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:
To the following range C2:C6 in the Sales Record worksheet of the South Region Sales workbook, the source workbook.
We use the following steps:
- Open the destination workbook and the source workbook.
- Select range C2:C6 in the Sales Record worksheet of the source workbook and press Ctrl + C to copy it.
- Switch back to the destination workbook and select cell A9 in the Sales Summary worksheet.
- 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 the Paste Link button in the bottom left corner of the Paste Special dialog box.
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.
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.
- =’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:
- ='[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:
- How to Break Links To External References in Excel? (2 Easy Ways)
- How to Remove Hyperlinks in Excel (Easy Step-by-Step)
- How to Extract URLs from Hyperlinks in Excel (Using VBA Formula)
- Switch Between Tabs/Worksheets in Excel [Keyboard Shortcut]
- How to Copy Multiple Sheets to a New Workbook in Excel
- How to Merge Two Excel Files? 3 Easy Ways!
- Insert Hyperlink in Excel [Keyboard Shortcut]
