Microsoft Excel is a very powerful multipurpose tool that anyone can use. But if you are someone who works with spreadsheets every day, you may need to know more than just the basics of working with Excel. Knowing a few simple tricks can go a long way with Excel. A good example is the ability to link cells in Excel between sheets and workbooks.
Knowing this will save you a lot of time and savings in the long run.
Why is cell data linked in Excel
The ability to reference data on different sheets is a valuable skill for several reasons.
First, it will make it easier to organize your tables. For example, you can use one sheet or workbook to collect raw data and then create a new tab or new workbook for reports and / or summaries.
After you link the cells between the two, you only need to change or enter new data in one of them, and the results will automatically change to the other. And all this without having to navigate between different tables.
Secondly, this technique will avoid duplication of the same numbers in several tables. This will shorten your working time and reduce the likelihood of calculation errors.
In the next article, you will learn how to link individual cells in other sheets, link a range of cells, and how to link cells from different Excel documents.
How to join two single cells
Let’s start by linking two cells located on different sheets (or tabs) but in the same Excel file. To do this, follow these steps.
- On Sheet2, enter an equal symbol (=) in the cell.
- Switch to another tab (Sheet1) and click the cell you want to link to.
- Press Enter to complete the formula.
Now if you click on a cell in Sheet2, you will see that Excel is writing the path for you in the formula bar.
For example, = Sheet1! C3, where Sheet1 is the name of the sheet, C3 is the cell you are referencing, and an exclamation mark (!) Is used as the separator between them.
Using this approach, you can create a link manually without leaving the original sheet at all. Just enter the reference formula directly into the cell.
Note. If the sheet name contains spaces (for example, Sheet 1), you need to enclose the name in single quotes when you type the cell reference. Like = ‘Sheet 1’! C3. This is why it is sometimes easier and more reliable to let Excel write the reference formula for you.
How to link a range of cells
Another way to link cells in Excel is to link a whole series of cells from different tabs in Excel. This is useful when you need to store the same data on different sheets without having to edit both sheets.
To link more than one cell in Excel, follow these steps.
- On the original data tab (Sheet1), select the cells you want to reference.
- Copy the cells (Ctrl / Command + C or right-click and select Copy).
- Switch to another tab (Sheet2) and click the cell (s) where you want to place the links.
- Right-click the cell (s) and select Paste Special
- In the lower left corner of the menu, select Insert Link.
When you click the new linked cells in Sheet2, you can see the cell references from Sheet1 in the formula tab. Now whenever you change the data in the selected cells in Sheet 1, it will automatically change the data in the linked cells in Sheet 2.
How to relate a cell to a post
Linking to a cluster of cells can be useful when you are performing a summation and want to keep them separate from the original raw data in a worksheet.
Let’s say you want to write a SUM function on Sheet2 that will reference multiple cells from Sheet1. To do this, go to Sheet2 and click in the cell where you want to place the function. Write the function as usual, but when it comes to selecting a range of cells, move to another sheet and select them as described above.
You will have = SUM (Sheet1! C3: C7), where the SUM function will sum the contents of cells C3: C7 in Sheet1. Press Enter to complete the formula.
How to link cells from different Excel files
The linking process between different Excel files (or workbooks) is much the same as described above. Except when you are pasting cells, insert them into another spreadsheet instead of another tab. Here’s how to do it in 4 easy steps.
- Open both Excel documents.
- In the second file (Help Desk Geek), select a cell and enter an equal sign (=).
- Switch to the source file (Online Tech Tips) and click the cell you want to link to.
- Press Enter to complete the formula.
Now the formula for the linked cell also has a different workbook name in square brackets.
If you close the original Excel file and look at the formula again, you will see that now it also lists the location of the entire document. This means that if you move the original file that you linked to a different location, or rename it, the links will stop working. This is why it is safer to store all your important data in one Excel file.
Become a Microsoft Excel Pro user
Linking cells between sheets is just one example of how you can filter data in Excel and keep your tables in order. Check out some of the other Excel tips and tricks we’ve put together to help you become a power user.
What other useful Excel life hacks do you know and use? Do you know any other creative ways to link cells in Excel? Share them with us in the comments section below.
–