Formula Errors

Fix Incorrect Values When Filling External References in Excel

Bushra ParveenBushra Parveen Nov 14, 2026 869 views

Question details

The user needs Excel formulas filled from another workbook to return the intended values.

Fix Incorrect Values When Filling External References in Excel
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.
Before you start

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.

Solution 1Recommended

Correct the Cell References Before Filling

Use this method when dragging a formula changes its source reference in an unintended way.

1
Inspect the original formula

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.

2
Choose a fixed or relative reference

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.

3
Fill and verify

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.

Correct the Cell References Before Filling
References to a range: A formula such as ='[MasterReservations2020.xlsx]Jan 2020 - Dec 2020'!F75:F79 references the full range. In versions that support dynamic arrays, it can spill into adjacent cells; spilled formulas cannot reside inside an Excel table, and linked dynamic arrays may require the source workbook to remain open.
Work with linked spreadsheets in WPS

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. 1. Open both workbooks: Open WPS Spreadsheets, then use File > Open to open the source and destination workbooks.
  2. 2. Create the external reference: In the destination cell, type =, switch to the source workbook, select the source cell, and press Enter.
  3. 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. 4. Fill and check: Drag the cell's fill handle down, then select several filled cells and check their formulas and values.
Fill linked formulas with fixed or sequential cell referencesOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet tools to inspect formulas and values
microsoft office alternative - wps office

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.