How To Link Sheets In Excel?

Summary: The purpose of this article is to demonstrate three ways in which you can easily navigate between numerous worksheets using hyperlinks in your Excel workbook. 

If you're an Internet surfer, you know firsthand how valuable hyperlinks can be. Hyperlinks enable you to access other information instantly, no matter where it is located. Excel workbooks can benefit from hyperlinks in spreadsheets, but do you know what they are? Using this great Excel feature has never been easier.

Creating a table of contents for your workbook can be one way to use spreadsheet hyperlinks well. You can quickly jump to the relevant part of the workbook using Excel internal hyperlinks.

What is the purpose of linking spreadsheet data?

It is possible to avoid maintaining the same data on multiple sheets by creating external cell references or links. As a result, data integrity is improved, time is saved, and errors are reduced. Managers can keep detailed spreadsheets for reference and summarized spreadsheets for comparing performances. Other worksheets can be linked to a master sheet containing prices.

Here's how it works: Linking Spreadsheet Data

Excel displays data from another worksheet when we link data. Source worksheets contain data, and destination worksheets need data. A source worksheet contains data, and a destination worksheet wants it. Excel uses a link formula to determine what data to bring forward.

The link formula syntax is =SheetName!CellReference and specifies the worksheet and cell. 

Creating a link to a worksheet

The following three methods will help you create a link formula. This instruction assumes that the source and destination worksheets are in the same workbook. Before beginning the link formula, always format the cell that contains it.

Worksheet Data Linking - Method 1 

1. Work from the destination sheet to the source sheet.
   
2. The destination worksheet will contain the link formula, which you should enter by clicking on the cell and typing an equal sign.
3. You can link the data in the source worksheet by clicking on the cell with the data and pressing Enter. The linked data is displayed in Excel's destination sheet.

Link Worksheet Data - Method Two 

1. Paste the link in the destination worksheet after copying it from the source worksheet.
2. Copy the linked data into the source worksheet.
3. Select the cell where the link formula should appear, and then click Paste ⇒ Paste Link on the Home tab. Alternatively, you can use Edit > Paste Special > Paste Link in older versions of Excel.
4. It is displayed in the formula bar of the destination worksheet and the formula bar of the link formula. Excel creates formulas with absolute cells using this method.
5. To remove the animated border, return to the source worksheet and press ESC.

Writing Formulas Manually - Method 3

1. It is not difficult to enter formulas manually: equal sign, sheet name, exclamation mark, and cell reference: =SheetName! CellReference. You can use Notepad to enter multiple formulas, copy, paste, and modify them if you have multiple formulas to enter.
2. It's not necessary to capitalize worksheet names. If you enter the worksheet name in the formula without the capital letter, it will add the capital letter. In reverse as well.
3. A single quote must be used to surround spaces or special characters in linking formulas.

The Linking of Ranges of Cells

Select the cells you wish to link and click Copy. Then, using the Home tab, click the cell in the destination sheet where the upper-left cell of the range should appear.

Range values will be linked to the destination sheet and appear there. In each range cell, a link formula will reference the corresponding cell on the source sheet.

No doubt that working on excel makes your life easier but we can’t deny the fact that not everyone is an expert in using excel. So, to make it easy for you, Excel-accountant brings you the platform where you can learn more about excel. We are the website for all the professionals who want to become PRO at using excel. Join us today!