How To Define Named Range Across Worksheets In Excel ?

With the help of the robust spreadsheet programme Excel, you can effectively handle and arrange massive volumes of data. Its ability to construct named ranges, which makes it simpler to refer to certain cells or ranges inside your spreadsheets, is one of its important advantages. You may make your spreadsheets more useful overall and make formulas easier to interpret by giving a region of cells a name. Although it's normal practise to define named ranges within a single worksheet, there may be times when you need to expand the named range across many worksheets. You will learn how to define named ranges that span several Excel sheets in this article.

Understanding how to construct named ranges across worksheets will save you time and effort whether you're working on a challenging financial model, a project plan, or any other spreadsheet requiring data consolidation. When new sheets are added or current ones are changed, this ability will let you easily update your data and develop more dynamic formulas. This tutorial will walk you through the process of creating named ranges across worksheets in Excel, with step−by−step instructions and real−world examples. By the time you're done, you'll have a firm grasp on the idea and be prepared to use it in your own projects.

Define Named Range Across Worksheets

Here we can complete the task by defining the name directly. So let us see a simple process to learn how you can define named ranges across worksheets in Excel.

Step 1

Consider any Excel sheet. First, click on Formulas and then on Define Name. In the pop−up, enter the name "Range," then set scope to workbook," select all the sheets in the reference, and click OK to complete the task.

Formula > Define name > Name > Scope > Refers to > Ok.

You can select multiple sheets by holding the shift key


In this tutorial, we have used a simple example to demonstrate how you can define a named range across worksheets in Excel to highlight a particular set of data.

Updated on: 07-Jul-2023


Kickstart Your Career

Get certified by completing the course

Get Started