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".
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.
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.
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.
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.
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.
3. Copy and paste the product's names from product's price.
4. Click under Total (1), type =B5+C5 (2), then press enter.
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.
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).
7. Go to the other document.
8. Right click on the cell under Price (1), and choose paste special (2).
9. Click paste link Important
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.
Click Paste or Paste Link.
(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.
You can also insert an object to your spreadsheet.
To do that just click on insert (1) and then on shapes (2).
Choose a drawing object.
Press and hold the left button on the mouse while moving the mouse to draw the object.
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.
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.
- Lets us start by doing a find by clicking ctrl + F.
- Type .xl in the find box.
- In the Within box, click Workbook.
- In the Look in box, click Formulas.
- Click Find All.
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:
- Use Ctrl+F to display the Find dialog (Figure 1).
- Type a [ in the Find what box.
- Click the Options<< button to expand the dialog.
- Make sure the Look in dropdown says Formulas.
- Click Find All. It can give the results as follows:
Another way is to click the edit links in the Data tab as shown below:
Then you can click the break links button to break the links to the external sources.
Now you know really a lot about linking Excel spreadsheets and related tricks.