Everything about linking in Excel

Everything about linking in Excel what you did not know and was too afraid too ask.

First things first. Let's start from something easy.

How to insert a hyperlink?

Below are the steps to create a hyperlink in Microsoft Excel.

Launch Microsoft Excel.

Click on the "INSERT" tab and then click on "Hyperlink".

add hyperlink

There are two ways you can use the hyperlink:

1. If you want to link a website than just write the name of the website in the Address bar and click ok.

insert hyperlink

2. You can also create a link to a document that in your computer. To do this use the "look in" to search for the folder and select the document that you want to be linked to your Excel file. Click ok.

address

Note: Beside document you can also link images, songs and videos to your Excel file using the Hyperlink. The links will be in BLUE color.

google hyperlink

How to Link two Documents to Each Other?

Linking different Excel files to each other is what we will be doing together.

1. Create, and save two different Excel files.

two files

 

Note: Skip this step, if you already have two files available. You should also have these two files open.

2. Open the document, which you want to link another one to. In our case, the Shipping Price.

open first file

3. Copy and paste the product's names from product's price.

copy and paste

4. Click under Total (1), type =B5+C5 (2), then press enter.

click under total

Note: If instead you have quantities instead of Shipping fees in column C, then use multiple, rather than plus.

5. Double click on the small square of the result gotten from previous step.

double click small square

6. Go to product Price document, click on first cell under Product’s price, and press CTRL + C on your computer (alt. ⌘ + c, if you are using Mac).

go to second file

7. Go to the other document.

other document

8. Right click on the cell under Price (1), and choose paste special (2).

paste special

9. Click paste link Important

paste link

Note: You must repeat copy and paste special steps on all the cells in price column.

In conclusion, when the other document changes, this would update this document. For instance, if the price in product price.xlxs changes, it would instantly update the cell in shipping price.xlxs.

Linking documents is often used in business for files which let managers to prepare raports. That's why they don't have to go to files which employees use.

How to link objects?

Follow the steps below to learn how to create linked objects in Excel.

Open both Word and Excel document hat contains the data that you want to create a linked object.

In Excel select the range of cells (containing data) or the charts that you want and copy them (CTRL + C)

Next go to Word and then click where you want the information to appear. On the Home tab click on Paste and select Paste Special. In the As list select Microsoft Office Excel Object.

paste special

Click Paste or Paste Link.

paste object

(Optional) To create a new worksheet within a document. Click on the Insert tab and then click on Table and then click on Excel Spreadsheet. Fill in the worksheet with the information you want.

Excel spreadsheet

You can also insert an object to your spreadsheet.

To do that just click on insert (1) and then on shapes (2).

insert ribbon objects

Choose a drawing object.

choose ribbon object

Press and hold the left button on the mouse while moving the mouse to draw the object.

drawing object in Excel

Linking Text Box to a specific cell

Here's how to link a TextBox to a specific cell.

1. Go to the ribbon to the Insert tab.

2. Click a Text Box button.

3. Draw a text box into your worksheet.

4. Click your text box.

5. Go to Formula Bar and type an address of cell eg. B2.

6. In your text box you'll see a content of the cell.

B2 cell content

How to check if an workbook is linked to another?

And here is how to check if an Excel file is linked to any other External workbook or references.

  1. Lets us start by doing a find by clicking ctrl + F.
  2. Type .xl in the find box.
  3. In the Within box, click Workbook.
  4. In the Look in box, click Formulas.
  5. Click Find All.

find all xl

If there are any external links it will show up like above.

From the location you can go into the respective cells to remove the links. Sometimes the links are in the formulas used in those cells.

Another way to do is by using [ bracket instead of the .xl in the find as follows:

  1. Use Ctrl+F to display the Find dialog (Figure 1).
  2. Type a [ in the Find what box.
  3. Click the Options<< button to expand the dialog.
  4. Make sure the Look in dropdown says Formulas.
  5. Click Find All. It can give the results as follows:

find and replace bracket

Another way is to click the edit links in the Data tab as shown below:

edit links

Then you can click the break links button to break the links to the external sources.

break link

Now you know really a lot about linking Excel spreadsheets and related tricks.