Using links to other worksheets in Excel makes it possible to link sheets to each other. This allows you to create complex projects within a single workbook, where many sheets borrow data from each other. In this tutorial, you will learn how to create a link within an Excel worksheet, as well as between workbooks.
Excel allows you to refer to cells in any worksheet in the current workbook, which is especially useful when you need to use a specific value from another worksheet. To do this, the link must start with the sheet name followed by an exclamation point (!). For example, if you want to refer to a cell A1 on the sheet Sheet1, the link will look like this: Sheet1!A1.
Please note that if the sheet name contains spaces, then it must be enclosed in single quotes (‘ ‘). For example, if you want to create a link to cell A1, which is on a worksheet called July budget. The link will look like this: ‘July Budget’!A1.
Create a link in Excel to another sheet
In the following example, we will refer from one Excel sheet to a value that refers to another worksheet. This will allow us to use the same value on two different sheets.
- Find the cell you want to link to and remember where it is. In our example, this is cell E14 on the worksheet. Menu order.
- Go to the required sheet. In our example, we will select the sheet Food service bill.
- The selected sheet will open.
- Find and select the cell where you want the value to appear. In our example, we will select cell B2.
- Enter an equal sign (=), a sheet name with an exclamation point (!) and a cell address. In our example, we will enter =’Order Menu’!E14.
- Press Enter on keyboard. The referenced value will appear. If the value of cell E14 on the worksheet Menu order change then and the value on the sheet Food service bill will automatically update.
If you later rename the sheet, the link will automatically update to display the new sheet name.
If you enter the sheet name incorrectly, an error will appear in the cell #LINK! In the following example, we made a typo in the title. An exclamation point smart tag has appeared next to the cell that contains the error. Click on it and select the option you need from the drop-down list: edit or ignore the error.
How to create a link to another Excel workbook
In addition to creating links to other worksheets, Excel also allows you to create links to other workbooks. To do this, before the link to the worksheet, you must substitute the name of the workbook in square brackets. For example, if the book name is Book 1, list – Sheet1, then cell reference A1 will look like this: =[Book1.xlsx]List1!A1
To use the above construct, it is necessary that the Excel workbook we are referring to be open.