Often, we have to deal with multiple worksheets in Excel and hover over different sheets to find a value. In this article, we demonstrate how to Excel link cell to another sheet.
Let’s say, we have Sale data for December’21 of three different cities, New York, Boston, and Los Angeles. These three Sale data are identical in orientation, so, we show only one worksheet as a dataset.
1. Using Insert Link Option to Link Cell To Another Sheet
In Insert Tab, Excel offers a feature as Insert Link. We can make any cell link using this feature and then insert it in any cell we want. In this case, we can make individual links for a cell from any sheet and then insert them in any worksheet we want. In this way, we can link a cell to another sheet.
Step 1: First, identify the cell that you want to insert the link to. The cell is F13 of the New York sheet. You have to repeat the steps for each cell.
Step 2: Go to another sheet where you want to insert the link to the cell. Place the mouse (i.e., C5) where you want to insert the link. Then, Go to Insert Tab > Select Insert Link (from Link section).
Step 3: The Insert Hyperlink window opens up. In the window,
Select Place in the Document (under Link to options).
Type F13 (in the Type the cell reference option)
Select ‘New York’ (under Or select a place in this document)
Afterward, you see ‘New York’!F13 as Text to display.
⏩ Clicking OK inserts the link to the cell (i.e., C5) in the desired sheet.
⏩ Repeat Steps 2 and 3 to insert respective cell links in another sheet. Repeating Steps 2 and 3 results in something like the image below.
⏩ If you want to check the link, just click on it.
⏩ It’ll take you to the New York sheet where the value sits.
⏩ The Context Menu option can also be used to bring out the Insert Hyperlink window. Also, Keyboard Shortcuts CTRL+K does the job.
2. Using Cell Reference to Link Cell to Another Sheet in Excel
Excel allows us to link a cell to another worksheet using cell reference. For example, we want the city-wise (i.e., New York, Boston, or Los Angeles) monthly Total Sales in other sheets.
Step 2: After typing the Equal Sign (=) in the formula bar, Go to the respective sheet (i.e., New York) you want to reference a cell from then Select Total Sale sum amount cell (i.e., F13) as reference.
Step 3: As you select the reference cell, Hit ENTER. You’ll jump back to the destined worksheet with the sum amount of Total Sale for New York City Sale Dec’21 similar to the image below.
You can link other sum amounts by repeating the same steps (i.e., Steps 1 to 3) mentioned earlier. And you’ll come up with something like the following picture.
In this method, we can use any cell reference to link the cell to another worksheet.
3. Utilizing the Copy Paste Feature to Link a Cell to Another Sheet in Excel
Excel Copy and Paste feature can copy and paste cell links anywhere in Excel. As a result, we can use the Copy and Paste feature to link cells to another sheet.
Step 1: Move to the cell, you want to copy the link. Right-click on the cell (i.e., F13). It brings the Context Menu. Select Copy (from the options).
Step 2: Go to the sheet (i.e., Copy and Paste) where you want to link the cell. Right-click on the destined cell (i.e., C5) in that sheet (i.e., Copy and Paste). The Context Menu appears. From the Context Menu,
Select Paste Special > Select Paste Link (from Other Paste Options).
⏩ The Total Sale value appears in the cell as shown in the following image.
⏩ After repeating Steps 1 and 2 for other entries, you get the values we desire to link the cells in another sheet.
4. Using Name Box to Link Cell To Another Sheet
Excel offers a feature called Name Box. Using the Name Box feature, we can refer to any cell or range in Excel. For this case, we can use the Name Box to name a certain cell in sheets then link it to another sheet. As we want to link the sum amount of each sheet, we have to assign a name to each sum Total Sale cell in the respective sheets.
Step 1: Assign a name (i.e., NY_Total_Sale) for New York to cell F13 using the Name Box. Repeat the step for other sheets such as Boston and Los Angeles.
⏩ You can check whether naming by Name Box successfully does the job or not. To do so, Go to Formulas Tab > Select Name Manager (from the Defined Names section).
⏩ The Name Manager window pops up and you can find all the assigned names in the workbook.
From the above screenshot, you can see the assigned names that we named after certain cells.
Step 2: After assigning names, Go to any worksheet, Type =NY… to insert the sum value from the New York sheet. You see assigned names as selectable options. Select the option.
⏩ As you select the option, the sum of the Total Sale (for New York) value appears in the cell.
⏩ If you repeat the Steps (i.e., Steps 1 and 2) for other cities, you’ll get all the values for respective cities as shown in the following image.
5. Using HYPERLINK Function
As we want to link cells to another sheet, we can use the HYPERLINK function to link cells as clickable hyperlink destinations in another sheet. The HYPERLINK function converts a destination and a given text into a hyperlink. For this purpose, we can instantly move to a cell as per our demand only by clicking on the links existing in another sheet.
The syntax of the HYPERLINK function is
HYPERLINK (link_location, [friendly_name])
In the formula,
link_location; path to the cell you want to jump.
[friendly_name]; display text in the cell where we insert the hyperlink [Optional].
Step 1: Paste the following formula in any cell (i.e., C5).
If we compare the arguments,
Step 2: Press ENTER then Drag the Fill Handle to make the other hyperlinks appear in cells C6 and C7.
You see hyperlinks for Boston and Los Angeles appear as they did for New York.
⏩ You can check whether the hyperlinks work or not, by clicking on any hyperlinks. For this reason, we click on the New York named hyperlink.
⏩ In a moment we jump to the New York sheet’s F13 cell (as directed in the formula) as shown in the image below.
You can test the hyperlinks for each cell and every time you’ll jump to the destination as directed in the formula. For better understanding and brief representation, we use only three worksheets to fetch the data from, you can use as many as you want.
6. Applying the INDIRECT Function
We want to link a cell to another sheet using functions and Excel’s features. This can also be achieved using the INDIRECT function. The INDIRECT function creates a cell reference using a text string. The syntax of the INDIRECT function is
INDIRECT (ref_text, [a1])
The arguments refer,
ref_text; reference in the form of text.
[a1]; a boolean indication for A1 or R1C1 style reference [Optional]. The default option represents TRUE=A1 style.
Step 1: Write the following formula in any blank cell (i.e., C5).
As we know the cell reference for the sum of Total Sale is in F13 for all three sheets and B5 represents the sheet name from where the data will be fetched.
Step 2: Pressing ENTER, drag the Fill Handle to bring out the amount for other sheets. In a moment, you’ll see the sum amounts of Total Sale appear.
Read More: How to Link Sheets in Excel with a Formula
7. Using Drag and Drop Method
We used the HYPERLINK function or Insert Link feature to insert a link of a cell in another sheet in previous methods. Excel offers a manual way to insert a link to a cell in any sheet we want.
Step 1: Place the cursor at the edge of a cell’s (i.e., F13) border and wait until the entire selection icon appears.
Step 2: Right-click on the mouse the Excel shows the cell number under the cursor.
Step 3: Holding the right-click press ALT and Drag the cursor towards the destined sheet (where you want to insert the link). ALT key is used to shift between sheets in Excel. After moving closer to the destined sheet (i.e., Drag and Drop), Excel selects the destined sheet.
Step 4: Place the cursor where you want the link (i.e., C5 in the Drag and Drop Sheet). Then Release the right click holding, a Context Menu appears. Select the Create Hyperlink Here option.
⏩ Selecting Create Hyperlink Here insert the cell’s link in the Drag and Drop Sheet’s C5 cell.
⏩ Repeat the sequences (i.e., Steps 1 to 3) to insert links of all required cells in the sheet.
You can check whether the inserted links work or not by simply clicking on them.
Download Excel Workbook
In the article, we demonstrate multiple ways for to Excel link a cell to another sheet. To do so, we use HYPERLINK and INDIRECT functions as well as multiple Excel features. Some methods create clickable links to the cell and others just fetch the values from the respected cells. You can use any of the methods to link a cell to another sheet according to your dataset demand. Hope the above-described methods do your job. If you have further queries or something to add, feel free to comment in the comment section.