Formula Errors

How to Change Multiple Excel Formula References at Once

Natalie Taylor Oct 10, 2026 868 views

Question details

Update row or column numbers for multiple formula references across a workbook simultaneously.

How to Change Multiple Excel Formula References at Once

Product: Excel
Device & OS: not provided
Scenario: A user has an Excel workbook where multiple formulas reference a specific row (e.g., row 73), and needs to update all of them to reference a new row (e.g., row 78) without manually editing each cell.
Observed behavior: The formulas currently point to the old row, requiring a batch update to accurately point to the newly designated target row.

Before you start

Ensure you have selected only the specific range of cells containing the formulas you want to change to avoid accidentally altering unrelated numbers or sheet names.

Solution 1Recommended

Use Find and Replace to Update Formula References

The fastest way to change multiple formula references at once is by using the built-in Find and Replace tool to target specific row or column values.

This method searches within the formulas themselves and replaces the specific row or column identifier with the new one. It is much more efficient than manually double-clicking and editing each cell.

Select the target cells

Highlight the specific cells or range containing the formulas you need to update.

Open Find and Replace

Press Ctrl+H on your keyboard to open the Find and Replace dialog box.

Enter the old and new references

In the 'Find what' field, type the old row number (e.g., 73). In the 'Replace with' field, type the new row number (e.g., 78).

Execute the replacement

Click the 'Replace All' button to update the references, then carefully review your selected range to ensure no unintended numbers were changed.

Use Find and Replace to Update Formula References

Precision is Key: If your old row number appears elsewhere in the formula (like in a sheet name or static value), it will also be replaced. To be safe, include part of the column reference or punctuation, such as finding 'D73' and replacing it with 'D78'.

Update Formulas Efficiently

Batch Update Formula References Easily with WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and intuitive environment for handling complex data and bulk formula updates. You can seamlessly use the Find and Replace tool just as you would in Microsoft Excel.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your spreadsheet, then highlight the cells containing the formulas you wish to change.
2. Access Find and Replace: Press Ctrl+H or navigate to the Home tab and select 'Find and Replace'.
3. Input reference values: Enter your old reference number in the 'Find what' box and the new reference number in the 'Replace with' box.
4. Apply changes: Click 'Replace All' to instantly update your formula references across the selected range.

Easily replace formula references across multiple cells or entire workbooks.Fully compatible with Microsoft Excel (.xlsx) file formats and complex formulas.Advanced Find and Replace options to target specific formula elements.Free and lightweight software for fast and reliable data processing.

microsoft office alternative - wps office

Frequently Asked Questions

Why did Find and Replace change parts of my formula I didn't want to change?

If the number you searched for exists in other parts of the formula, such as the sheet name or a fixed numerical value, Find and Replace will update it too. To prevent this, make your search more specific by including column letters or punctuation, like finding '!D73' instead of just '73'.

Can I change formula references across the entire workbook at once?

Yes. In the Find and Replace dialog box, click on 'Options' and change the 'Within' dropdown menu from 'Sheet' to 'Workbook'. Proceed with caution to ensure unintended replacements do not occur on other worksheets.

Does Find and Replace work on locked or protected cells?

No. If the worksheet is protected and the cells containing the formulas are locked, you will not be able to modify them. You need to unprotect the sheet first from the Review tab before using the Find and Replace tool.

Excel & Data Problems