EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Excel Named Range
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Excel Named Range

By Jeevan A YJeevan A Y

Named Range in Excel

Excel Named Range (Table of Contents)

  • Named Range in Excel
  • Define Names from a Selected Range
  • How to Create a Named Range in Excel?

Introduction to Named Range in Excel

Named Range just makes the process of working with formulas exciting, especially to keep track of things. It is just very convenient to assign a name to a range. If there is any change in that range, it becomes infinitely easier to update the range through the Name Manager. It totally allows us to bypass the process of manually updating every formula. Also, in cases of nested formulas, where one formula has to be used in another formula or maybe just used in a different location, all that needs to be done is to refer to the formula by its name.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

The importance of named ranges is that one can use any names in the formula without worrying about the cell addresses/references. Any name can be assigned to a range. Any named constant or any data can be given a named range, and these names can be used instead of the actual formula. In this way, it becomes easier to understand the formulas. So, what this does is that it gives a range of cells in Excel a human-understandable name. By making use of named ranges, one can make the usage of formulas straightforward in Excel. One can give a name to a range in a formula or function, a constant, or a table. After starting to use names in a sheet, one can easily comprehend these names.

Define Names from a Selected Range

Firstly, one has to select the range that you want to give a name. Thereafter navigate to Formulas and then select Create from Selection.

Name Range Function 1

From the “Create Names from Selection” box, select either Bottom Row, Top Row, Left Column, or Right Column, and then click on OK.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,368 ratings)

Excel Named Range

The cells are named based on labels in the designated range:

  • First, use names in formulas and then focus on a cell and then enter a formula.
  • The cursor has to be placed at the location where we want to use the named range in a formula.
  • The name can be selected from the list that opens up after typing the name’s first letter.
  • Alternatively, navigate to Formulas and then Use in Formula, followed by selecting the name that you intend to use.
  • Press Enter.

It is also possible to update the named ranges from the Name Manager. The shortcut to update the named ranges from the Name Manager is Ctrl+F3. We just need to select the name that we wish to change and then proceed to change the reference directly.

How to Create Named Ranges in Excel?

Let us understand the concept of the Named Range through some examples.

You can download this Named Range Excel Template here – Named Range Excel Template

Example #1

Create a Named Range by using the Define Name option

  • First, focus on the cells by selecting the range of cells.

range of cells

  • Now, navigate to the Formulas tab. Next, go to the Defined Name group. Click to select Define Name.

Name Range Example 1-2

  • Now once the New Name dialog box opens, mention three things -Name, Scope, Comment.

dialog box opens

  • Now in the “Name:” category, provide a name for the range.

Name Range Example 1-4

  • Now in the “Scope:” category, from the dropdown, the name scope has to be set (by default, it is Workbook)

Excel Named Range

  • Now move on to the ‘Refers to:’ section, verify the available reference, and modify it if required. After that, click on OK to save the changes you have made and close the dialog box.

Name Range Example 1-6

Example #2

Create a Named Range by utilizing the Name Manager

  • Navigate to the Formulas tab and then proceed towards Define Name Group and then click on Name Manager.

Name Range Example 2-1

  • Alternatively, you can also press Ctrl+F3, which is the shortcut for the same.

Name Range Example 2-2

  • Now, on the top, towards the Name Manager’s left corner, we have the ‘New’ button. Click on it.

Excel Named Range

Then the New Name dialog box will open.

New Name dialog box

Example #3

Create a Named Range using VBA

Name Range Example 3-1

We can also create a Named Range in VBA.

So, this is what we did in the code snippet above:

  • We created three variables – myWorksheet of Worksheet datatype, which will store the name of the Excel Worksheet, myNamedRange of Range datatype, which will hold the name of the range of cells that we wish to name, and myRangeName, of String datatype, which will hold the name of the Named Range.
  • We defined our Worksheet (in the variable myWorksheet).
  • We then went on to define the range of cells that we wanted to name (in the variable myNamedRange).
  • After that, we defined the name of our Named Range in the variable myRangeName.
  • After that, we proceed to create the Named Range and provide the reference to which it will point to.

This concludes the Named Range creation process through VBA.

Things to Remember

The following things must always be kept in mind while creating or dealing with Named Ranges in Excel.

  • Named Ranges can have names that start with either a backslash (\), letter, or underscore (_). Numbers or other special symbols are invalid as the starting character. If any other character other than letters, backslash or an underscore is given as the starting character, then Excel will throw an error.
  • There is also a limitation on how long the name of a Named Range can be. The maximum length of a name can be 255 characters. Anything above 255 characters will cause Excel to throw an error.
  • Every name has to be a continuous sequence of characters. There is no scope to have spaces, tabs, or any punctuation marks in the name. This is done so as to maintain uniformity and make the names easier.
  • Another important instruction while working with Named Ranges is that a Named Range’s name must not have any conflict with cell references.
  • As we saw, there is a limit on the maximum length for a name. Conversely, a name must also be of at least one character length at a minimum. Names are allowed to be of a single character length; however, there are few characters such as ‘r’ and ‘c’, which are reserved by Excel and can not be used as Names.
  • As a bonus, Names aren’t case sensitive, which means that names such as “Jon”, “JON”, “jon” are all the same to Excel.

Recommended Articles

This has been a guide to Named Range in Excel. Here we discussed how to create a named range in excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles to learn more –

  1. Excel Function for Range
  2. Range in Excel
  3. VBA Named Range
  4. VBA Range
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Named Range Excel Template

EDUCBA

Download Named Range Excel Template

EDUCBA

डाउनलोड Named Range Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more