Fix Incorrect Values When Filling External References in Excel
Question details
The user needs Excel formulas filled from another workbook to return the intended values.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user drags formulas that reference cells or ranges in a separate Excel workbook.
- Observed behavior
- Filled formulas return unexpected values when references shift or the source link or range is not configured as intended.
Open both the source and destination workbooks, and identify whether each filled formula should refer to the same source cell or the next cell in a sequence. Confirm that the source workbook contains the expected values.
Correct the Cell References Before Filling
Use this method when dragging a formula changes its source reference in an unintended way.
Select the first formula cell and check the external reference in the formula bar. For example, ='[MasterReservations2020.xlsx]Jan 2020 - Dec 2020'!F75 refers to cell F75 in the source workbook.
To keep every filled formula linked to F75, change the reference to ='[MasterReservations2020.xlsx]Jan 2020 - Dec 2020'!$F$75. To reference F75, F76, F77, and so on when filling down, leave it as F75 without dollar signs.
Drag the fill handle from the first formula cell down the destination column. Select several filled cells and check their references in the formula bar against the intended source cells.

Update the External Workbook Link
Check the link when the references look correct but the displayed values are outdated or come from the wrong source.
Test the Formula Outside an Excel Table
Use this check if a table fills formulas automatically or formatting differs from what you expect.
Fill External Workbook References in WPS Spreadsheets
Use WPS Spreadsheets to link cells in another workbook, choose fixed or relative references, and fill formulas down a column.
- 1. Open both workbooks: Open WPS Spreadsheets, then use File > Open to open the source and destination workbooks.
- 2. Create the external reference: In the destination cell, type =, switch to the source workbook, select the source cell, and press Enter.
- 3. Set the reference behavior: Select the formula cell and edit its reference in the formula bar. Add dollar signs, such as $F$75, to keep the source cell fixed, or leave F75 relative to advance the row when filling down.
- 4. Fill and check: Drag the cell's fill handle down, then select several filled cells and check their formulas and values.

Frequently Asked Questions
Why does my external reference change when I drag it down?
Excel adjusts relative references as you fill. For example, F75 becomes F76 in the next row. Use $F$75 in the formula bar if every row should reference the same source cell.
How do I fill a formula with consecutive cells from another workbook?
Start with a relative reference such as ='[MasterReservations2020.xlsx]Jan 2020 - Dec 2020'!F75 and drag the fill handle down. Check the next formula to confirm it references F76.
Why are linked values still outdated after I fix the formula?
Open and save the source workbook, then use Data > Workbook Links or Data > Edit Links to update the link. Also check that Formulas > Calculation Options is set to Automatic.
Can I use a full external range instead of filling individual formulas?
Yes. A reference such as ='[MasterReservations2020.xlsx]Jan 2020 - Dec 2020'!F75:F79 specifies the range. In Excel versions with dynamic arrays, the result may spill into surrounding cells; use an area outside a table and keep the source workbook open if needed.




