Want to Organize your spreadsheets and enhance your workflow? Here is one of the best ways to do so: link cells between sheets and workbooks in Excel.
Microsoft Excel is a software program (developed by Microsoft) that uses spreadsheets to organize numbers and data with formulas and functions. It is one of the well-known software that helps you make assignments related to statistical data. It has a number of rows and columns where digits or data are placed. Excel is a powerful and multipurpose tool that any professional or employer can use. Well, if you are one of them which works mostly with Excel that you should know about the basics of how to use Excel and also, how to link cells in Excel between sheets and workbooks.
How to Link Cells Between Sheets And Workbooks
However, there are a few ways that one should be aware of. When these ways are learned then, it saves a lot of time and confusion too. Well, we will let you know how to link cells in Excel between sheets and workbooks. So, let’s get started!
Why Link Cell Data in Excel?
Making use of data on different sheets seems a valuable skill for some reason which is as under:
- It makes it easier to organize the spreadsheets for e.g., you can use one sheet or workbook for collecting raw data and then, create a new tab or workbook for reports or summations. When you link the cells between the two then, only change or enter new data in one of them and the results of the other will change automatically (i.e., without moving back and forth between different spreadsheets).
- It will let you avoid the duplication of the same number in more than one spreadsheet. As a result, it will reduce your working time and the chance of making fewer calculation mistakes.
However, in the upcoming section, you will learn how to link single cells in other worksheets, link a range of cells. Also, how to link cells from different Excel documents.
How to Link Two Single Cells?
Now, let’s begin with linking two cells located in different sheets (or tabs) but in the same Excel file. Well, to do so, follow the steps given below:
- In Sheet2 of Excel, type an equal symbol (=) into any cell
- Now, go to Sheet1 (another tab) and click the cell, you want to link to.
- Click the Enter option to complete the formula
Now, if you click the cell in Sheet2, you will see that Excel writes the path in a formula bar e.g., =Sheet1!C3 (where Sheet1 is a sheet’s name, C3 is a linking cell and exclamation mark (!) is used as a separator). So, in this way, you can link the two cells into one without leaving the actual sheet at all.
Note: If the sheet name contains spaces (e.g., Sheet 1) then, put the name in single quotation marks when typing a reference into a cell ( like =’ Sheet 1′!C3 ). That is why it is sometimes easier and more reliable to let Excel write the reference formula.
How to Link a Range of Cells?
Another way is to link cells is to link the whole range of cells from more than one Excel tab. It is considered useful when storing the same data in different sheets without having to edit multiple sheets. Well, to do so, follow the simple steps given below:
Also see: How to Add Promotion on LinkedIn
- In the original tab (Sheet1), highlight the cells having data that you want to reference.
- Copy the cells by pressing Ctrl/Command + C or right-click and choose Copy.
- Go to the other tab (Sheet2) and click the cell (or cells) where you place the links.
- Then, right-click on the cell(-s) and select Paste Special… option
- Choose Paste Link placed at the bottom left corner of the menu.
When done then, you can see the reference to the cells in the newly linked cells in Sheet2 in a formula tab. Now, whenever you change the data in the chosen cells in Sheet1, it will automatically change the data in Sheet2.
How to Link a Cell With a Function?
You can link to a cluster of cells which is considered useful when doing summations and keep them on a sheet separate from the original raw data. For example, if you want to write a SUM function in Sheet2 that links cells from Sheet1. To do so, go to Sheet2 and click a cell where you want to place a function. Write a function as normal but if you want a range of cells then, go to another sheet and highlight them. Now, this appears =SUM(Sheet1!C3:C7) in a formula tab (where SUM is the sum of highlighted cells of C3:C7 in Sheet1. Hence, Press Enter to complete the formula.
How to Link Cells From Different Excel Files?
The process of linking cells is the same for different Excel files or workbooks as mentioned above. One thing that differs is when pasting the cells, paste them in a different spreadsheet rather than a different tab. Well, to do so, follow the simple steps given below:
- Open both Excel documents.
- In the second file (Help Desk Geek), choose a cell and type an 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.
However, in the formula tab, the linked cell’s formula will have other workbooks’ names mentioned with it in square brackets. Nonetheless, if you close the original Excel file and want to see the formula again then now, it has the entire document’s location. It means that if you rename the file or forget where it was placed then, links will stop working. So, it is more convenient to place the data in the same Excel file.