Automatically updating one Excel worksheet from another sheet is a good way to reference data in Excel so that you only have to update data in a single location. There are multiple ways to update a worksheet from another worksheet.
Update one Excel worksheet from another sheet with the Paste Link command
One of the simplest ways to automatically update one Excel worksheet from another sheet is using Excel’s built-in Paste Link command.
- Select the cell(s) on the original sheet that you want to show on another sheet
- Click copy (Shortcut: Ctrl + C) Learn more time-saving shortcuts: 25+ most useful Excel shortcuts
- Select the sheet that you want to be automatically updated
- Select the cell (or top left cell, if a range was selected) where you want the data to be updated
- Click the Paste options, and select Paste Link
The data is pasted as a link, or more specifically a reference, to the original data on the original worksheet. Anything updated on the original worksheet will be updated on this worksheet.
Ace tip: Formatting does not automatically paste with the Paste Link command. To keep the original formatting as well, Paste Formatting in addition to Paste Link.
Update one Excel worksheet from another sheet using a formula
A worksheet can be automatically updated by using a reference formula. This is the same exact way that the Paste Link command automatically updates the worksheet, however we don’t have to use Paste Link. Simply using a reference formula may be faster if we just want to reference certain portions of a worksheet, but not an entire range.
- Navigate to the worksheet that you want to be automatically updated
- Enter “=” and the exact name of the worksheet you wish to reference
- Enter “!” and the cell you wish to reference
- Press Enter
In the example below, the cell B5 from another worksheet (Sheet1) is referenced using the formula