Microsoft Excel is a very powerful all-in-one tool that anyone can use. But if you’re someone who works with spreadsheets on a daily basis, you may need to know more than the basics of how to use Excel. Knowing a few simple tricks can help you succeed with Excel. A good example is knowing how to link cells in Excel between a worksheet and a workbook.
Learning this will save a lot of time and confusion in the long run.
Table of Contents
Why link cell data in Excel
Being able to reference data across different sheets is a valuable skill for a few reasons.
First, it will make it easier for you to organize your spreadsheets. For example, you can use a worksheet or workbook to collect raw data, and then create a new tab or workbook for reports and/or summaries.
When you link cells between two cells, you simply change or enter new data in one of them and the results will automatically change in the other. All without having to move back and forth between different spreadsheets.
Second, this trick will avoid duplicating the same numbers in multiple worksheets. This will reduce your work time and the possibility of calculation errors.
In the following article, you will learn how to link single cells in other worksheets, link a range of cells, and how to link cells from different Excel documents.
How to link two single cells
Let’s start by linking two cells that are in different worksheets (or tabs) but in the same Excel file. To do that, follow these steps.
- In Sheet2 type a equal symbol (=) into a cell.
- Go to another tab (Sheet1) and click the cell you want to link to.
- Press enter to complete the formula.
Now, if you click on the box in Sheet2you will see that Excel will write the path for you in the formula bar.
For example, = Page1! C3where Sheet1 is the name of the sheet, C3 is the cell you are linking to and exclamation mark (!) is used as a separator between the two.
Using this approach, you can manually link without leaving the original sheet. Just enter the formula that references the cell directly.
Note: If the sheet name contains spaces (eg: Sheet 1), then you need to enclose the name in quotes when entering a reference in a cell. Alike = ‘Page 1 ′! C3. That’s why it’s sometimes easier and more reliable to let Excel write your reference formulas for you.
How to link a range of cells
Another way you can link cells in Excel is to link an entire range of cells from different Excel tabs. This is useful when you need to store the same data in different sheets without having to edit both sheets.
To link multiple cells in Excel, follow these steps.
- In the root tab there is data (Sheet1), highlight the cells that you want to reference.
- Copy cells (Control/Request + OLDor right click and select Copy).
- Go to another tab (Sheet2) and click on the cell (or cells) where you want to place the links.
- Right click on the cell (-s) and select Special stickers…
- In the bottom left corner of the menu, select Paste the link.
When you click the newly linked cells in Sheet2, you can see references to cells from Sheet1 in the formulas tab. Now, whenever you change the data in the selected cells in Sheet1, it will automatically change the data in the linked cells in Sheet2.
How to link a cell to a function
Linking to a group of cells can be useful when you do summaries and want to keep them on a separate sheet from the original raw data.
Let’s say you need to write a SUM function in Sheet2 that will link to some cells from Sheet1. To do that, visit Sheet2 and click in the cell where you want to place the function. Write the function as you normally would, but when you need to select a range of cells, switch to another sheet and highlight them as described above.
You will have =SUM(Sheet1!C3:C7), where the SUM function sums the contents from cells C3:C7 in Sheet1. Press enter to complete the formula.
How to link cells from different Excel files
The process of linking between different Excel files (or workbooks) is almost the same as above. Except, when you paste cells, paste them in another worksheet instead of another tab. Here’s how in 4 easy steps.
- Open both Excel documents.
- In the second file (Geek Help Desk), select a cell and enter equal symbol (=).
- Switch to the original file (Online Tech Tips), and click the cell that you want to link to.
- Press enter to complete the formula.
Now the formula for the linked cell also has the other workbook name in square brackets.
If you close the original Excel file and look at the formula again, you’ll see that it now also has the location of the entire document. This means that if you move the original file you linked to somewhere else or rename it, the links will stop working. That’s why it’s more reliable to keep all the important data in the same Excel file.
Become a Microsoft Excel Pro user
Linking cells between worksheets is just one example of how you can filter data in Excel and keep your spreadsheets organized. Check out another one Excel Tips and Tricks that we put together to help you become an advanced user.
What other neat Excel support tools do you know and use? Do you know of any other creative ways to link cells in Excel? Share them with us in the comments section below.
