If you want to have the same data on one sheet as you have on another, the simplest way to do it is to copy and paste those values. But if you change data in one sheet, you will also have to change it to another one.
There’s a better way to do it. You can use a reference to a cell on another worksheet, so if those cells change, cells on our worksheet also change.
Let’s create a new sheet in the same workbook and name it “Names”.
Link a single cell
You can make a reference to each cell in the “Names” worksheet. Let’s make it to cell A2.
This formula makes an absolute cell reference.
Now, if you try to AutoFill the rest of the cells, of course, you will get the same name in other cells. That’s why you can use a relative reference to this cell and then use AutoFill.
If you want to copy cells quickly, without typing a worksheet name, which is especially useful, if your sheets have long names.
You can also copy multiple cells as a link.
This is what you have to do.
Select cells from A1 to A5 and copy these cells (Ctrl + C).
Right-click cell A1 in the current sheet and choose Paste Link (N).
The copied links are relative.
Now, the values are linked to the “Names” sheet, but the formatting is not copied, you can use the right-click one more time and this time paste Formatting (R).
Link from the different workbook
You can also create a link to a worksheet in another workbook.
Create a new XLSX file called “people.xlsx” in the same directory. Name one sheet “FirstNames”, and the other one “LastNames”.
Copy these to the current sheet, so it looks like this.
Take a look at the formula linking to these cells.
There is a file name, worksheet name, and cell reference.
If you close the “people.xlsx” file, the reference will change to the absolute path.
Automatic and manual calculation options
You can set it Excel, whether you want manual or automatic calculations.
This option can be found in Formulas >> Calculation >> Calculation Options.
By default, this option is set to automatic. If you change to manual, the results will not be updated, unless you do one of the following:
- Execute the formula for the cell you want to update.
- Click Calculate Now or Calculate Sheet, next to the Calculation Options button.
- Press the F9 key.