How to Create Range Names in Excel

Today, I am going to teach you How to Create Range Names in Excel. How to use range names in excel? Are you still trying to remember the cells addresses by clicking on the cell to get the address ? Well , your problems are over now. I am going to show you how to use and edit a named range in excel and it’s very easy, just keeping reading.

Assign range names in excel is a topic that comes up quite often on discussion forums and online communities. And since I am the biggest fan of using defined names for ranges in Excel, I want to share with you some more advanced uses of the name box and how to create own range names in excel.

If someone has to call me or refer to me, they will use my name (instead of saying a male is staying in so and so place with so and so height and weight).

Right?

Similarly, in Excel, you can give a name to a cell or a range of cells.

Now, instead of using the cell reference (such as A1 or A1:A10), you can simply use the name that you assigned to it.

For example, suppose you have a data set as shown below:

In this data set, if you have to refer to the range that has the Date, you will have to use A2:A11 in formulas. Similarly, for Sales Rep and Sales, you will have to use B2:B11 and C2:C11.

While it’s alright when you only have a couple of data points, but in case you huge complex data sets, using cell references to refer to data could be time-consuming.

Excel Named Ranges makes it easy to refer to data sets in Excel.

You can create a named range in Excel for each data category, and then use that name instead of the cell references. For example, dates can be named ‘Date’, Sales Rep data can be named ‘SalesRep’ and sales data can be named ‘Sales’.

You can also create a name for a single cell. For example, if you have the sales commission percentage in a cell, you can name that cell as ‘Commission’.

Benefits of Creating Named Ranges in Excel

Here are the benefits of using named ranges in Excel.

Use Names instead of Cell References

When you create Named Ranges in Excel, you can use these names instead of the cell references.

For example, you can use =SUM(SALES) instead of =SUM(C2:C11) for the above data set.

Have a look at ṭhe formulas listed below. Instead of using cell references, I have used the Named Ranges.https://96484e88265d0712b44099f4f8a595df.safeframe.googlesyndication.com/safeframe/1-0-38/html/container.html

• Number of sales with value more than 500: =COUNTIF(Sales,”>500″)
• Sum of all the sales done by Tom: =SUMIF(SalesRep,”Tom”,Sales)
• Commission earned by Joe (sales by Joe multiplied by commission percentage):
  =SUMIF(SalesRep,”Joe”,Sales)*Commission

You would agree that these formulas are easy to create and easy to understand (especially when you share it with someone else or revisit it yourself.

No Need to Go Back to the Dataset to Select Cells

Another significant benefit of using Named Ranges in Excel is that you don’t need to go back and select the cell ranges.

You can just type a couple of alphabets of that named range and Excel will show the matching named ranges (as shown below):https://96484e88265d0712b44099f4f8a595df.safeframe.googlesyndication.com/safeframe/1-0-38/html/container.html

Named Ranges Make Formulas Dynamic

By using Named Ranges in Excel, you can make Excel formulas dynamic.

For example, in the case of sales commission, instead of using the value 2.5%, you can use the Named Range.

Now, if your company later decides to increase the commission to 3%, you can simply update the Named Range, and all the calculation would automatically update to reflect the new commission.https://96484e88265d0712b44099f4f8a595df.safeframe.googlesyndication.com/safeframe/1-0-38/html/container.html

How to Create Named Ranges in Excel

Here are three ways to create Named Ranges in Excel:

Method #1 –  Using Define Name

Here are the steps to create Named Ranges in Excel using Define Name:

• Select the range for which you want to create a Named Range in Excel.
• Go to Formulas –> Define Name.
• In the New Name dialogue box, type the Name you wish to assign to the selected data range. You can specify the scope as the entire workbook or a specific worksheet, If you select a particular sheet, the name would not be available on other sheets.
• Click OK.

This will create a Named Range SALESREP.

Method #2: Using the Name Box

• Select the range for which you want to create a name (do not select headers).
• Go to the Name Box on the left of Formula bar and Type the name of the with which you want to create the Named Range.
• Note that the Name created here will be available for the entire Workbook. If you wish to restrict it to a worksheet, use Method 1.

Method #3: Using Create From Selection Option

This is the recommended way when you have data in tabular form, and you want to create named range for each column/row.

For example, in the dataset below, if you want to quickly create three named ranges (Date, Sales_Rep, and Sales), then you can use the method shown below.

Here are the steps to quickly create named ranges from a dataset:

• Select the entire data set (including the headers).
• Go to Formulas –> Create from Selection (Keyboard shortcut – Control + Shift + F3). It will open the ‘Create Names from Selection’ dialogue box.
• In the Create Names from Selection dialogue box, check the options where you have the headers. In this case, we select top row only as the header is in the top row. If you have headers in both top row and left column, you can choose both. Similarly, if your data is arranged when the headers are in the left column only, then you only check the Left Column option.

This will create three Named Ranges – Date, Sales_Rep, and Sales.

Note that it automatically picks up names from the headers. If there are any space between words, it inserts an underscore (as you can’t have spaces in named ranges).

Naming Convention for Named Ranges in Excel

There are certain naming rules you need to know while creating Named Ranges in Excel:

• The first character of a Named Range should be a letter and underscore character(_), or a backslash(\). If it’s anything else, it will show an error. The remaining characters can be letters, numbers, special characters, period, or underscore.
• You can not use names that also represent cell references in Excel. For example, you can’t use AB1 as it is also a cell reference.
• You can’t use spaces while creating named ranges. For example, you can’t have Sales Rep as a named range. If you want to combine two words and create a Named Range, use an underscore, period or uppercase characters to create it. For example, you can have Sales_Rep, SalesRep, or SalesRep.
  • While creating named ranges, Excel treats uppercase and lowercase the same way. For example, if you create a named range SALES, then you will not be able to create another named range such as ‘sales’ or ‘Sales’.
• A Named Range can be up to 255 characters long.

Conclusion

Range names are used to create easy to access cell address within cell ranges or rows that are quite complicated for automation or shorter version of worksheets.