Function Problems

How to Use a Named Range Across Multiple Worksheets in Excel

Emma Brown Oct 21, 2026 868 views

Question details

The user needs to reference a defined named range across different worksheets within the same workbook without encountering formula errors or manually specifying the source sheet name.

How to Use a Named Range Across Multiple Worksheets in Excel

Product: Excel
Device & OS: not provided
Scenario: Writing complex formulas such as VLOOKUP or INDEX/MATCH that reference data arrays or lookup tables stored on a separate, dedicated worksheet.
Observed behavior: Workbook-level named ranges can be utilized globally; they are directly available from every worksheet by using the defined name in the formula without adding the source sheet prefix.

Before you start

Verify that your named range has been defined with a 'Workbook' scope rather than a specific 'Worksheet' scope using the Name Manager.

Solution 1Recommended

Reference Workbook-Level Named Ranges Directly in Formulas

Reference a global named range simply by typing its exact name in the formula on any worksheet, entirely bypassing the need to prefix the sheet name.

By default, when you create a defined name in Excel, it is global to the entire workbook. This means the name acts as a universal reference.

Instead of writing complex sheet references like 'Lists!$A$2:$B$10', you can replace the entire array reference with your defined name. Excel automatically resolves the location.

Open the Name Manager

Navigate to the Formulas tab on the Excel ribbon and click on 'Name Manager' to review your existing defined names.

Verify Workbook Scope

Check the 'Scope' column next to your target named range. Ensure it says 'Workbook'. If it specifies a sheet name, you will need to recreate the name with Workbook scope.

Enter Your Formula

Click on the cell in any other worksheet where you want the result to appear, and begin typing your formula (for example, =VLOOKUP(F1, LookupName, 2, FALSE)).

Execute the Formula

Press Enter. Excel will successfully fetch the data from the source sheet using the global named range without requiring a sheet prefix.

Reference Workbook-Level Named Ranges Directly in Formulas

Scope Advantage: Because workbook-level defined names are globally accessible, they keep your formulas clean and significantly reduce the chance of typo-related errors when referencing other sheets.

Seamless Spreadsheet Editing

Create and Manage Named Ranges Easily in WPS Spreadsheet

WPS Spreadsheet offers a robust, user-friendly Name Manager that allows you to quickly define workbook-level ranges and reference them flawlessly across multiple sheets to streamline your data management.

1. Select the Data Range: Open your workbook in WPS Spreadsheet and highlight the cells you wish to define as a named range.
2. Access the Name Manager: Navigate to the 'Formulas' tab on the top ribbon and select 'Name Manager' or simply click 'Define Name'.
3. Set the Scope to Workbook: Type your desired name, ensure the 'Scope' dropdown is set to 'Workbook', and click OK to save it.
4. Use Across Worksheets: Switch to any other sheet in your file and use the newly defined name directly inside your functions or formulas.

Manage global and local named ranges effortlessly with an intuitive Name Manager tool.Fully compatible with Microsoft Excel (.xlsx) formulas, defined names, and cross-sheet references.A free, lightweight, and highly efficient alternative for all your complex spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel return a #NAME? error when I use my named range?

This usually happens if the named range is misspelled in your formula, or if the name was defined with a worksheet-specific scope instead of a workbook-level scope. Check the Name Manager to verify its exact spelling and scope.

Can I change a named range's scope from worksheet to workbook?

Excel does not allow you to directly edit the scope of an existing named range. You must delete the existing worksheet-level name in the Name Manager and recreate it with the scope explicitly set to 'Workbook'.

How do I reference a worksheet-level named range from another sheet?

If your named range is strictly scoped to a specific worksheet, you cannot use just the name globally. You must prefix it with the sheet name in your formula. For example, use =Sheet1!LocalName instead of just =LocalName.

Is there a keyboard shortcut to open the Name Manager?

Yes, you can press Ctrl + F3 on your keyboard to instantly open the Name Manager dialog box, where you can add, edit, or delete defined names in your workbook.

Excel & Data Problems