How to Provide Table Reference in Another Sheet in Excel

Sometimes, we have data in Excel Tables, and each time we look for any value or items, we have to go to a certain worksheet. Excel reference Table in another sheet is a handy way to deal with existing data in another worksheet. Excel features such as Structured References, Insert Link, and HYPERLINK function can reference Tables from another sheet.

Let’s say we have Sale data for March’22 of three different cities, New York, Boston, and Los Angeles, in Table format. These three Sales data are identical in orientation, so we show only one sales data as a dataset.

In this article, we use Structured References, Insert Link, and HYPERLINK function to Excel reference Table in another sheet.

Provide Table Reference in Another Sheet in Excel: 3 Ways

Before performing any methods, it’s convenient if we assign specific names to our Tables. As a result, we can simply type their names while referencing them.

🔄 Select the entire table or place the cursor in any cell. Excel displays the Table Design tab instantly.

Click on Table Design.

Assign a Table name (i.e., NewYorkSale) under the Table Name dialog box in the Properties section.

Press ENTER. Excel then assigns the name to this Table.

Repeat the Steps for the other 2 Tables (i.e., BostonSale, LosAngelesSale).

🔄 You can check the naming using Formulas > Name Manager (in the Defined Names section). And you see all the assigned Table names in the Name Manager window.

Since we have assigned the Tables specific names to easily refer to them, now we move to reference them in formulas. Follow the below methods to reference Tables in another sheet.

Method 1: Refer a Table in Another Sheet Using Structured Reference

We named Tables specifically depending on their data. Excel offers Structured Reference with Table. Structured Reference means we can reference an entire column by just providing the header name in the formula along with the assigned Table name.

Step 1: Start typing a formula after inserting an Equal Sign (=) in the Formula bar. Then, type a Table name to reference it, as shown in the image below. Excel brings up the Table reference; double-click on it.

Step 2: After referring to the Table, type a third bracket (i.e.,[ ). Excel shows column names to select from. Double-click on Total Sale and close the brackets as pictured below.

🔼 We assign New York Sale Table at first, then one of its columns (i.e., Total Sale) later. We indicate both arguments in colored rectangles.

Step 3: Use the ENTER key to apply the formula in the C5 cell.

🔼 Follow Steps 1, 2, and 3 to reference other Tables in the respective cells. After referencing, Excel shows the sum of the Total Sale columns of respective Tables as depicted in the image below.

You can reference any Table just by assigning its name in the formula along with the column header you want to deal with.

Method 2: Using Insert Link to Provide Table Reference

Excel Insert Link is an effective way to link or reference cells or ranges from other sheets. As we are referencing Table, we need to use the Table range to reference it by link. Prior to the reference, we have to check the range that our Table occupies, similar to the picture below.

Step 1: Place the cursor in the cell (i.e., C5) where you want to insert the reference of the Table. Go to Insert > Link > Insert Link.

Step 2: The Insert Hyperlink dialog box opens. In the dialog box,

Click on Place in this Document as Link to option.

Select the Sheet (i.e., ‘New York’) under Or select a place in this document.

Type cell reference B4:F12 resides in the Table under Type the cell reference.

Edit or keep what Excel takes as Text to display (i.e., ‘New York’!).

Click on OK.

🔼 Clicking OK inserts the link of the Table in the C5 cell.

🔼 You can cross-check the referencing just by clicking on the link as shown below.

🔼 After clicking on the link Excel takes you to the destined worksheet and highlights the entire Table.

🔼 Use Steps 1 and 2 to insert links for other Tables (i.e., Boston Sale and Los Angeles Sale).

Keep in mind that this method would be suitable when you have the table and the sheet name in the same way (same name).

Method 3: Using HYPERLINK Function to Refer Table

As we want to reference Tables from another sheet, we can use the HYPERLINK function. The HYPERLINK function converts a destination and a given text into a hyperlink. For this purpose, we can instantly move to a worksheet as per our demand only by clicking on the links existing in a Worksheet.

The syntax of the HYPERLINK function is

HYPERLINK (link_location, [friendly_name])

In the formula,

link_location; path to the sheet 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 blank cell (i.e., C5).

=HYPERLINK("#'"&B5&"'!NewYorkSale",B5)

In the formula,

#'”&B5&”‘!NewYorkSale = link_location

B5 = [friendly_name]

Step 2: Hit ENTER, then insert the same formula in other cells after replacing it with the respective Table Names as depicted in the following image.

Download Excel Workbook

Reference Table in Another Sheet.xlsx

Conclusion

In this article, we use Structured Reference, Insert Link, and HYPERLINK function to Excel reference Table in another sheet. Structured Reference referencing is the most convenient way to reference a Table. Other methods also work just fine. Hope these above-described methods excel in their purpose in your case. Comment if you have further inquiries or have anything to add.

<< Go Back to Excel Table | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!