How to Link Excel Cells Between Worksheets and Keep Values Updated
Question details
The user wants to link a specific range of cells from one worksheet to another so that any updates in the source worksheet reflect automatically in the destination cells.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing data across multiple worksheets where destination cells need to stay synchronized with source cells without manually updating them.
- Observed behavior
- The goal is to establish a dynamic link between cell ranges (e.g., IVCC!G3:G18 to Year!C41:C56) rather than pasting static values.
Ensure both the source and destination worksheets are located within the same workbook for seamless syncing. If linking across different workbooks, make sure both files are saved and accessible.
Use the Paste Link Feature
The quickest and most efficient way to link an entire range of cells between worksheets without manually typing formulas.
The Paste Link tool automatically creates absolute cell references linking back to your source data. Whenever the original data changes, the destination cells will instantly update to reflect the new values.
Navigate to the source worksheet (e.g., IVCC), highlight the range of cells you want to link (such as G3:G18), and press Ctrl+C to copy.
Switch to your destination worksheet (e.g., Year) and click on the top-left cell where you want the linked data to begin (such as C41).
Right-click the destination cell, open the Paste Special menu, and select 'Paste Link' (the icon with a chain link). The data will appear and stay synced with the source.
Manually Enter a Cell Reference Formula
Best for linking specific individual cells or when you want precise control over relative and absolute references.
Use a Dynamic Array Formula (Excel 365)
Ideal for users on Excel 365 or newer versions who want to link an entire block of data using a single spilled formula.
Link Cells Across Worksheets Easily in WPS Spreadsheet
WPS Spreadsheet offers powerful, user-friendly tools identical to Excel for linking cells and keeping your data dynamically updated. You can easily link multiple worksheets to streamline your data management.
- 1. Open your workbook in WPS: Launch WPS Spreadsheet and open the workbook containing your target worksheets.
- 2. Copy the source data: Highlight the range of cells you want to link from your source sheet and copy it (Ctrl+C).
- 3. Navigate to the destination: Switch to the destination sheet and select the starting cell where the data should appear.
- 4. Use Paste Link: Right-click the cell, hover over 'Paste Special', and click 'Paste Link' to establish a live connection.
Frequently Asked Questions
Why are my linked cells showing a #REF! error?
A #REF! error occurs when the source worksheet or the specific source cells have been deleted. To fix this, you must restore the deleted cells or update the formula to point to a valid active cell.
Can I link cells between completely different Excel files?
Yes. Open both workbooks, type '=' in the destination cell of the first file, switch to the second file, click the source cell, and press Enter. Keep in mind that external links may prompt you to 'Update Links' when you reopen the destination file.
How do I break a link to a cell and keep just the value?
To convert dynamically linked cells into static values, highlight the linked destination cells, copy them, right-click, select 'Paste Special', and choose 'Values'. This replaces the linking formula with the actual text or number.
Will linked cells update if the source file is closed?
If you linked cells between different workbooks, the destination file will typically update when you open it and agree to 'Enable Content' or 'Update Links'. If linking within the same workbook, changes apply instantaneously.
