Formula Errors

How to Fix Excel External Workbook Reference Not Adjusting When Copied

Maira Mehtab Sep 22, 2026 869 views

Question details

The user is trying to copy an Excel formula that references a cell in an external workbook, but the copied formulas continue to return the exact same source cell (e.g., B2) instead of adjusting dynamically.

Product: Excel
Device & OS: not provided
Scenario: Copying a formula that references an external workbook to multiple destination cells in a worksheet.
Observed behavior: The copied formulas fail to adjust their source references based on their new position, constantly pointing to the original absolute cell.

Before you start

Check your formula bar and inspect the external cell reference for dollar signs ($), which indicate that the reference is locked to a specific cell.

Solution 1Recommended

Change Absolute Reference to Relative Reference

Remove the dollar signs ($) from the external cell reference so Excel knows to adjust the row and column dynamically when the formula is copied.

When you reference an external workbook by clicking on a cell, Excel automatically creates an absolute reference by adding dollar signs (e.g., $B$2). This completely locks the reference to that exact row and column.

To make the reference adjust as you drag or copy the formula across other cells, you must change it to a relative reference by removing the dollar signs.

Select the Formula Cell

Click on the cell in your destination workbook that contains the external reference formula.

Edit the Formula

Click inside the formula bar at the top of the screen to edit the formula text.

Remove the Dollar Signs

Locate the cell reference (e.g., ='[Aarbakke Capacity.xlsx]Aarbakke Data Input'!$B$2) and delete the dollar signs so it becomes a relative reference (e.g., ='[Aarbakke Capacity.xlsx]Aarbakke Data Input'!B2).

Use the F4 Shortcut (Alternative)

Alternatively, you can highlight the cell reference in the formula bar and press the F4 key on your keyboard repeatedly until all dollar signs are removed.

Apply and Copy

Press Enter to save the changes. You can now drag the fill handle or copy and paste the cell to your desired locations, and the references will adjust automatically.

Understanding Reference Types: A reference with a $ before both the column letter and row number (like $B$2) is completely locked. A reference with no $ (like B2) is completely relative and will shift as you copy it.

Seamless Formula Management

Manage Formulas and External References Easily with WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and intuitive interface for managing complex formulas, including external workbook references. You can easily toggle between relative and absolute references using familiar keyboard shortcuts, ensuring your data is always accurate when copied.

1. Open Your Workbooks: Launch WPS Office and open both your destination workbook and the source workbook.
2. Create the Reference: In your destination cell, type an equals sign (=), switch to the source workbook, and click your target cell. WPS will auto-generate the reference link.
3. Toggle Reference Mode: Before pressing Enter, highlight the cell reference in the formula bar and press F4 to remove the dollar signs ($), changing it to a relative reference.
4. Drag to Fill: Press Enter, then click and drag the small square at the bottom right of the cell to copy the formula down or across. The references will adjust perfectly.

Fully compatible with Microsoft Excel (.xlsx) formats and formula syntax.Easily manage external workbook links and cross-sheet references.Familiar F4 keyboard shortcut to instantly toggle absolute and relative references.Lightweight, fast, and completely free to use for daily tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel add dollar signs to external references automatically?

When you link to another workbook or worksheet by pointing and clicking, the software assumes you want to pull data from that specific, fixed location. It defaults to an absolute reference (e.g., $B$2) to prevent the reference from shifting accidentally if you move or sort your data.

What is the fastest way to change a reference from absolute to relative?

The fastest method is using the F4 key on your keyboard. Highlight the specific cell reference in the formula bar and press F4. Each press will cycle through the different reference types: absolute ($B$2), mixed row (B$2), mixed column ($B2), and relative (B2).

Will my external relative references update if the source workbook is closed?

Yes, both relative and absolute external references will display the last saved value of the source workbook. When you reopen the destination workbook, you may be prompted to 'Update Links' to fetch any new changes made to the closed source file.

Excel & Data Problems