You can move, copy or link cells between worksheets to organize your data the way you want. You can copy sheets in the same workbook, or between two workbooks.
Copy a Worksheet Inside the Same Workbook
Each workbook has at least one worksheet. Every worksheet is identified by a worksheet tab at the bottom-left corner. If you create more sheets, the tabs are placed to the right of the current sheet.
To quickly copy the entire worksheet, right-click the sheet tab and select Move or Copy.
After you click the button, a new window will appear. Here, you can select whether a new sheet should be placed before or after the source sheet. I want it to be placed on the right side of the copied sheet, therefore I select (move to end).
At the bottom-left corner, there is a checkbox called Create a copy. You have to select it if you want to duplicate the source sheet, otherwise, Excel will just move the sheet.
In this case (one sheet), nothing will happen because the sheet can’t be moved after itself.
After you click the OK button, a new sheet will appear. It’s a copy of the source sheet with the same name, and number 2 in parentheses because sheet names have to be unique inside a single workbook.
Now, you have two identical sheets with different names.
To change the name of the new one, right-click it and select Rename. You can also double-click it to make the field editable.
Type a new name and press Enter.
Copy a Worksheet Between Different Workbooks
You are not restricted to copying a sheet inside a single workbook. You can copy sheets between multiple workbooks. The condition is that both workbooks have to be opened at the same time.
To copy a sheet from one workbook to another, right-click the sheet tab and select Move or Copy, the same way as you did before.
But this time, in the To book: drop-down list select the workbook where you want to copy the sheet. Don’t forget to check that you Create a copy checkbox, otherwise the sheet will be moved to the new workbook.
If you choose (new book), Excel will create and open a new workbook with the copied sheet.
Copy Sheets by Dragging
There is another, quicker way to make a copy of a worksheet.
While holding the Ctrl key, drag the sheet tab to the right.
This way of copying the sheet is quicker but gives you the same result.
Link Cells Between Sheets with a Formula
Let’s say, you have the Source sheet and the new empty Destination sheet. Now, you want to create a link from cell A1 in the Source sheet to the Destination sheet.
You can do it with the equality operator (=).
Enter the name of the source file and cell you want to link to and place it to cell A1 in the destination file:
1 |
=Source!A2 |
If the name of the sheet is too long, you can enter the equal sign in cell A1 in the Destination sheet, click the tab of the Source sheet, and then A2. This operation will enter the formula.
It’s time to see whether this link works. Change the cell value in the source sheet.
Now, you can see that the value changed in the Destination sheet.
Link Range Between Sheets
In the last step, we linked a single cell. But you can also do it to entire ranges.
This range is linked and can be modified only in the source sheet. You can’t delete or copy any value in the B1 to D1 range. If you try to modify one of these cells, it will result in an error:
Link Data Between Sheets with Paste Link
If you want to have a link to each value of the source sheet cell separately, and you also want to modify individual values in the destination sheet (this will break the link to this cell), you can use one of the pasting methods called Paste Link.
First, select cells from the source sheet you want to have links to and copy them (Ctrl + C).
Right-click cell A1 in the destination sheet and select the Paste Link option.
All these values are references to the source sheet.
You can also do it between different workbooks.
Notice, that the link changed, and now there is information that the data is from a different workbook.
If you close the source workbook, the link will be pointing to the absolute path of the sheet.
If you open the workbook with the source sheet, references will automatically change back.
Link Data Only if The Condition is Met
So far, we copied all cells without any requirements. You can also require a condition to be met to display linked data.
In this example, there is a bunch of numbers. Some of them are positive and some are negative.
We want to create a link to the value, only if the value is greater or equal to 0.
For this task, use the following formula and enter it into a different worksheet.
=IF(Numbers!A1>=0, Numbers!A1, “”)
Autofill this formula for a row and then for columns:
The positive values are copied as before, and the negative is replaced with space. You can, of course, change empty spaces to different values, for example, dashes.