Formula Errors

How to Replace Worksheet References in Multiple Excel Formulas

Huda Qurayshi Oct 8, 2026 869 views

Question details

Update existing formulas to reference a new worksheet instead of the previous year's worksheet.

How to Replace Worksheet References in Multiple Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Duplicating a worksheet for a new year or period, but the formulas inside the new sheet still point to the old sheet's data.
Observed behavior: Formulas continue calculating based on the previous year's worksheet data instead of the new worksheet.

Before you start

Verify that the newly created worksheet maintains the exact same layout and structure as the previous one to ensure the updated references calculate the correct cells.

Solution 1Recommended

Use the Find and Replace Feature to Update Formulas

The quickest and most efficient way to perform a bulk update of worksheet references inside your formulas is by using the Find and Replace tool.

Instead of manually editing each formula, you can search for the text string of the old sheet name within the formulas and replace it with the new sheet name across the entire active sheet.

Open the replacement worksheet

Navigate to the specific worksheet where the formulas need to be updated (for example, the newly created 2024 sheet).

Open the Find and Replace dialog

Press the Ctrl+H shortcut on your keyboard to immediately bring up the Find and Replace dialog box.

Enter the old worksheet name

In the 'Find what' field, type the exact name of the previous worksheet as it appears in your formulas (e.g., '2023 SL Groups').

Enter the new worksheet name

In the 'Replace with' field, type the exact name of the new worksheet (e.g., '2024 SL Groups').

Apply the changes

Click the 'Replace All' button. Excel will automatically update all references in the formulas to point to the new worksheet.

Use the Find and Replace Feature to Update Formulas

Confirm Formula Updates: Excel will display a confirmation dialog showing how many replacements were made. Review a few formula cells to ensure they are now pointing to the correct worksheet.

Manage Spreadsheets Efficiently

Update Formula References Easily with WPS Spreadsheet

WPS Spreadsheet provides a seamless, highly compatible environment to handle large datasets, complex formulas, and bulk reference updates with ease.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing the formulas you want to update.
2. Access Find and Replace: Navigate to the worksheet containing the outdated references and press Ctrl+H to open the Find and Replace tool.
3. Input the references: Enter the old sheet name in the 'Find what' field and the new sheet name in the 'Replace with' field.
4. Replace all instances: Click 'Replace All' to instantly update every formula reference in your active sheet.

Quickly bulk-replace formula references with an advanced Find and Replace toolFully compatible with Microsoft Excel (.xls, .xlsx) files and formatsFree, lightweight application featuring a familiar interface for seamless migration

Frequently Asked Questions

Why does a file prompt to 'Update Values' appear when I click Replace All?

This prompt appears if the software cannot find the exact worksheet name you entered in the 'Replace with' field. Double-check your spelling to ensure the target sheet exists and is typed exactly as it is named, including spaces.

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

Yes. In the Find and Replace dialog, click on 'Options' (or 'More') to expand the menu, and change the 'Within' dropdown setting from 'Sheet' to 'Workbook' before clicking 'Replace All'.

What if my worksheet name contains spaces or special characters?

When a sheet name has spaces, Excel automatically wraps it in single quotes within the formula (e.g., '2024 SL Groups'!A1). You can usually perform the Find and Replace by just typing the text itself (e.g., replacing 2023 SL Groups with 2024 SL Groups), and the single quotes will remain intact.

Excel & Data Problems