Formula Errors

How to Copy an Excel Cell While Keeping a Linked Reference

Maira Mehtab Sep 28, 2026 869 views

Question details

The user wants to copy a calculated subtotal or a specific cell value to another location while maintaining a dynamic formula link to the original source cell.

How to Copy an Excel Cell While Keeping a Linked Reference

Product: Microsoft Excel
Device & OS: not provided
Scenario: Displaying data from one worksheet or workbook in another location, ensuring that any changes to the original data are automatically reflected in the copied location.
Observed behavior: The destination cell dynamically displays the value of the source cell by using a formula reference (e.g., =Sheet2!A1) and automatically updates when the source changes.

Before you start

Ensure both the source worksheet containing your subtotal and the destination worksheet are open before attempting to create a linked reference.

Solution 1Recommended

Use the Equals Sign to Create a Direct Formula Link

This is the most straightforward method to link a destination cell directly to a source cell, ensuring real-time updates.

Typing an equals sign allows you to manually point Excel to the exact cell you want to reference, which is highly reliable for single-cell links like subtotals.

Select the destination cell

Click on the blank cell where you want the linked subtotal to appear and type an equals sign (=) to initiate a formula.

Switch to the source worksheet

Navigate to the specific worksheet or open workbook that contains the original calculated subtotal.

Select the source cell

Click directly on the cell containing the subtotal. You will notice a dashed moving border appear around the cell.

Press Enter to confirm

Hit the Enter key. Excel will automatically return your view to the destination sheet, which will now display a linked reference such as =Sheet2!A1.

Use the Equals Sign to Create a Direct Formula Link

Automatic Updates: Whenever you modify the original subtotal data, the destination cell will update instantly to reflect the new value.

Solution 2

Use the Paste Link Feature

Ideal for copying multiple cells or an entire data range while maintaining their formula references to the source.

Manage Spreadsheets Efficiently

Create Dynamic Cell Links Easily in WPS Spreadsheet

WPS Office offers a powerful, user-friendly Spreadsheet tool that allows you to easily link cells, manage complex cross-sheet formulas, and handle large datasets just as you would in Microsoft Excel.

1. Open your file in WPS Office: Launch WPS Spreadsheet and open the document containing your data.
2. Initiate the link: Select the destination cell and type an equals sign (=).
3. Select the source data: Click on the source cell in the same worksheet, a different sheet, or another open workbook.
4. Finalize: Press Enter to create the dynamic cell link instantly.

Fully compatible with Microsoft Excel (.xlsx, .xls) formats and standard formula syntax.Easily create cross-sheet and cross-workbook linked references.Lightweight software with a fast, intuitive tabbed interface.Free to use with comprehensive data analysis and reporting tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my linked cell showing a #REF! error?

This formula error occurs if the original source cell, row, column, or worksheet has been permanently deleted. To fix it, you will need to clear the #REF! error and recreate the link pointing to a valid existing cell.

Can I link cells between two completely different workbooks?

Yes, you can link cells across different workbooks. Ensure both files are open when you create the link. The resulting formula will automatically include the external file name in brackets, such as =[Budget.xlsx]Sheet1!A1.

How do I break a link to a cell while keeping the data?

If you want to keep the current value but remove the dynamic link, copy the destination cell, right-click it, and choose 'Paste Values' (the clipboard icon with 123). This replaces the formula reference with a static number.

Why isn't my linked destination cell updating automatically?

Your workbook calculation options might be set to Manual. Go to the Formulas tab on the ribbon, click on 'Calculation Options', and ensure 'Automatic' is selected so links refresh immediately.

Excel & Data Problems