Link data and spreadsheets – Creating references

Link data and spreadsheets – Creating references, Relative and absolute hyperlinks allows working with multiple worksheets and connects worksheet together using link building or creating references of specific cells. 

Read this also: Setting up Multiple sheets

Link data and spreadsheets – Creating references

For linking data between spreadsheets you need to understand the concept of references.

What is a cell reference ?

A cell reference refers to the particular address of a cell of a worksheet in spreadsheet software. This cell contains data or value in the worksheet. Sometimes it can be a range of cells.

For example, A1, B2, C3 or A1:C5 etc.

This cell reference can be used for the following:

  1. In different formulas or function to take the values for computation or calculation
  2. To display the value of particular cells
  3. To carry forward data from one worksheet to another

Link data and spreadsheets – Creating references to other sheets

Sometimes when you are working with spreadsheet software you need to repeat or use same data values frequently in worksheet. So you can insert value in worksheet and then use that value by a reference in other worksheets.

There are two ways to creating references:

  1. Using keyboard
  2. Using mouse

Using keyboard

You can do this by following these simple steps:

1 Enter the data into the worksheet for the master sheet.

Data in Mastersheet for creating references
Data in Mastersheet for creating references

2 Insert new worksheet by pressing sheet + F11. Move your worksheet to next to the mastersheet rename the worksheet as Data Entry. In this worksheet we will carry forward Roll No and Names from the mastersheet.

3 Now point the cursor in the cell where you need the cell value. Type = in the cell.

Creating references using keyboard
Creating references using keyboard

4 Now switch to the master sheet to take the reference. (Press Ctrl + Page Up key) and select the cell for the Roll No column. Observe the following screenshot.

Selecting the References in MS Excel
Selecting the References in MS Excel

5 Now switch to the data entry worksheet by pressing the Ctrl + Page Down Key. Place the cursor where you want to display the referenced value. You will get something like this:

Switching to the data entry sheet
Switching to the data entry sheet

6 Press enter when you finished. Repeat the similar steps for the names. Observe the following screen shot.

Referenced worksheet
Referenced worksheet

That’s it!

Using mouse

When you want to create reference using mouse follow these steps:

1 Prepare your mastersheet by entering the data.

2 Click on + symbol in the sheet tab to insert new worksheet.

3 Place the cursor where you want to create a reference.

4 Type = and click on mastersheet to switch the worksheet.

5 Place the cursor which reference you want to take.

6 Switch back to data entry worksheet.

7 Click and drag the data as per the requirement.

Hyperlinks in Spreadsheet

As you are aware with links generated from google meet or other web applications to connect different online text based web pages.

Hyperlinks are text based links that allows to redirect or open another file or website in MS Excel.

Create Hyperlink in Spreadsheet

To create a hyperlink follow the given steps:

1 Place the cursor where you wan to display the link text.

2 Click on Insert –> hyperlink option from Links group. Look at this screenshot.

Inserting hyperlink in spreadsheet
Inserting hyperlink in spreadsheet

3 Insert hyperlink dialog box will appear. Select appropriate option from dialog box and click on OK button.

Link data and spreadsheets - Creating references
Insert Hyperlink Dialog box in MS Excel

Options

Existing File or web page: You can select a file from your computer or web page from internet.

Place in this document: This option allows to select a place like cell references or defined names or ranges.

Create New Document: It allows to create a new document which can be edited now or later.

Edit Hyperlink in Spreadsheet

To edit hyperlink follow the similar steps as insert hyperlink and do the changes.

Remove the hyperlink from Spreadsheet

Follow these steps to remove link from the excel worksheet.

1 Click on the cell where the link is created.

Select the hyperlink cell
Select the hyperlink cell

2 Click on Insert –> Hyperlink option from Links group.

3 The Edit Hyperlink dialog box will appear.

remove link button
remove link button

4 Find the Remove Link button in the dialog box.

5 Click on Remove Link button.

Thank you for reading this article. Feel free to your views on this article in the comment section. Any queries regarding this topic? Ask in the comment section.

FAQs

How can I link data from one Excel spreadsheet to another?

Open both worksheet, place your cursor in the cell where you want to link data. Type = and switch to the worksheet which you want to link.

What is a linked spreadsheet?

A linked spreadsheet contains cell of another worksheets as data with sheetname and cell address like Sheet1!A3.

What is the advantage of linking workbooks?

You can use master data from other worksheet into multiple sheets.

Can you link two Excel documents?

Yes, of course you can link by using keyboard and mouse.

What are some of the possible drawbacks of linking workbooks?

It is very difficult to eliminate links after using them once. Because it changed formulas and data

Leave a Reply