Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is the meaning of Name in Excel?
3.
Creating a Named Range in Excel
4.
Creating an Excel Name for a constant
5.
How to name columns in Excel
6.
Excel Naming Rules
7.
Excel Name Scope
8.
How to Edit Named Range in Excel
9.
How to filter names in Excel
10.
How to delete Named Range in Excel
11.
Benefits of using names in Excel
12.
FAQs
13.
Key Takeaways
Last Updated: Mar 27, 2024
Easy

Named Ranges

Author Komal Shaw
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

In this article, we will learn about Excel Named Ranges, i.e., how to define and use names in Excel. We will understand how to create a named range in Excel, and we will also use this feature to make our formulae much easier to read and write. We can give human-readable names to a cell and refer to that cell by name rather than by reference.

What is the meaning of Name in Excel?

Let's take an example, to find the total of sales (B2:B10) for a specific item (E1); we can use the following formula: =SUMIF($A$2:$A$10, $E$1, $B$2:$B$10)
 

Or, we can also give meaningful names to the ranges of cells and individual cells and supply those names to the formula: =SUMIF(items_list, item, sales)
 

Source

 

There are two types of Names in Excel:- Defined Name and Table Name.

  1. defined name refers to a single cell, a range of cells, or a formula. When we want to specify a name for a range of cells, it's called a named range/defined range.
  2. table name refers to a name of an Excel Table created automatically when we want to insert a table in a worksheet.
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Creating a Named Range in Excel

There are three ways to define a name in Excel:-

  1. By Typing a name in the Name Box
    We select a cell or a range of cells that we want to name, then type a name in the Name Box, and after that, press Enter.
     
  2. Creating a name by using the Define Name Option
    We select a cell or a range of cells. Then on the Formulas tab under Define Names Group, we click the Define Name button. Then a New Name dialog box pops up where we need to specify three things.
    We need to type the range name in the Name Box. Then we set the name scope in the Scope Dropdown. At last, we check the reference and correct it if needed in the Refers to the box. In the end, we click OK to save all the changes and close the dialog box.
     
  3. Making a Named Range using Excel Name Manager
    We go to the Formulas tab > Defined Names group, click Name Manager. Or, press Ctrl + F3. Then in the top left-hand corner of the Name Manager dialog window, click the New button. This will then open the New Name dialog box where you configure a name as done previously.

Creating an Excel Name for a constant

Microsoft allows us to define a name without a cell reference that will work as a named constant. Thus, we use the Excel Define Name feature or Name Manager to create such a name, as explained above.

How to name columns in Excel

  1. Select the entire table (including the column and row headers).
  2. Go to the Formulas tab > Define Names group, and click the Create from Selection button. Or, press the keyboard shortcut Ctrl + Shift + F3.
  3. Either way, the Create Names from Selection dialogue box will open. You select the column or row with headers, or both, and click OK.

Excel Naming Rules

  1. An Excel name should be under 255 characters long.
  2. The names should not contain spaces and most of the punctuation marks.
  3. A name should begin with a letter, underscore (_), or backslash (\).
  4. These names are case-sensitive.

Excel Name Scope

There are two types of Excel Name Scope:-

  1. Specific Worksheet - the local worksheet level
  2. Workbook - the global workbook level

How to Edit Named Range in Excel

First, we open the Name Manager, then select the name and click the Edit button. This will open the Edit Name dialog box where we can change the name and reference. We cannot change the scope of the name. 

We do not need to open the Edit Name dialog box to edit a name reference. We need to select the name of interest in the Excel Name Manager, type a new reference directly in the Refers to box, or click the button at the right and select the desired range on the sheet. After that, we click the Close button. Then Excel will ask if we want to save the changes, and we click Yes.

How to filter names in Excel

The filters available are:-

  1. Names scoped to Worksheet or workbook
  2. Names with or without errors
  3. Defined names or table names

How to delete Named Range in Excel

Delete a named range - select it in the Name Manager and click the Delete button at the top.

Delete several names - click the first name, then press the Ctrl key and hold it while clicking other names we want to remove. Then click the Delete button, and all selected names will be deleted.

Delete all defined names in a workbook - select the first name in the list, then press and hold the Shift key, and then click the last name. Release the Shift key and click Delete.

Benefits of using names in Excel

  1. Excel names help in making formulas easier to read.
  2. It makes formulas easier to re-use.
  3. It allows us to create expandable formulas.
  4. It helps to simplify navigation.
  5. It also allows us to create dynamic dropdown lists.

FAQs

  1. What is the shortcut to open the Excel Name Manager?
    Ctrl+F3
     
  2. What is the shortcut to getting a list of all Excel names in a workbook?
    F3
     
  3. What is the shortcut to creating named ranges from the selection?
    Ctrl+Shift+F3
     
  4. What is #REF! error?
    If we delete all cells that make up an Excel named range, the names become invalid and display a #REF! error in the Name Manager.
     
  5. What is #NAME? error?
    This error comes up when a formula refers to a non-existing name.

Key Takeaways

In this article, we have extensively discussed the Named Ranges of Excel. We hope that this blog has helped you enhance your knowledge regarding Named Ranges.

If you want to learn more, check out our articles on Days and DATEDIF FunctionHLOOKUP FunctionToday and Now FunctionDynamic Array Functions.

Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. 

Enrol in our courses and refer to the mock test and problems available.

Take a look at the interview experiences and interview bundle for placement preparations.

Do upvote our blog to help other ninjas grow. 

Happy Coding!

Previous article
Heatmaps in Excel
Next article
Pie Chart
Live masterclass