Blog

Excel Naming Columns – How to Create New Names for Excel Cell Range

How to Create New Names for Excel Cell Range?
Blog

Excel Naming Columns – How to Create New Names for Excel Cell Range

What if you don’t have a name, how would someone call you?

Definitely, people will use random words to make you attentive when you don’t have a name. Identity is the most precious thing in this world, if you lose it you will get vanished. Similarly, when you are talking about other things, naming them is crucial.

In your work area, talking about Excel, you have to give a name to each column, row, and cell. Well, it entirely depends on you how you assign names because you are working on the project that’s why you know better about it. People often think about Excel naming columns or rows as an unnecessary thing. They have no idea how important it is to name some column so that you can write, read and re-use Excel formulas.

What Does Name Mean in Excel?

As already talked about naming something, we know it is important to name things to refer to different objects, places, foods, and so on. For instance, instead of saying a rounded and red object that tastes sweet and comes from a tree, we simply say “Apple”. Isn’t it as easy as it seems?

How convenient it is to name something, we can save lots of time that goes into explaining details about that object. Likewise, in Microsoft Excel, giving a readable name to every cell or a cell range is highly important.

For instance, when you need to find the total of sales (B2:B10) for a particular item (E1), the formula will be as:

=SUMIF ($A$2:$A$10, $E$1, $B$2:$B$10)

On the other hand, you can even assign names to the ranges as well as individual cells to supply naming to the formula:

=SUMIF (items_list, item, sales)

One must understand the significance of Excel naming columns, as for data management tasks you should have customized column names. Once you are done, it will ensure that you are having a mess-free and easily assemble worksheet.

To minimize the readability issues, you must not use special characters including double-quotes, brackets, and upper and lower case letters. 

Excel Name Types

In Excel, you will encounter two types of names:

Defined Name

It refers to a single cell, constant value, range of cells, or formula. For instance, defining a name for a range of cells is known as a named range or defined range.

Table Name

An Excel table is created by default when you put a table in a worksheet using CTRL + T is known as the table name.

How Do You Make an Excel Named Range?

Typically, we have 3 different ways to define a name in Excel: Name Box, Define Name button, and Excel Name Manager.

How Would You Type a Name in the Name Box?

To create a named range, the Name Box in Excel is the widely used method. For this, you have to follow the steps given below:

Choose a cell or a range of cells to name.

In the Name box, type the name you want to assign.

Hit the Enter key.

All set! You have successfully created an Excel named range.

Using the Define Name Option to Create a Name

Other than the above method, we have another way to make a named range in excel.

Choose the cells you need to name.

Click on the Define Name button given in the Define Names group option that is placed on the Formulas tab.

You will have to denote specify three things in the New Name dialog box:

  • Type the range name in the Name box.
  • Set the name scope in the Scope dropdown menu.
  • Check the reference and ensure it is correct in the Refers to the box.

Click OK to save the changes and close the dialog box.

Also, keep in mind that Excel with absolute references can create a name by default. You will have to remove the $ sign from the reference in case you need to have a relative named range.

Define Name in Excel method has more options than the earlier method explained in this post. The plus point of this method is you can set the scope of the name and put a comment to explain anything about the name. In addition, using this method is convenient for a constant or formula. 

Use Excel Name Manager to Make a Named Range

The Name Manager typically is used in Excel when you already have names. However, you can create new names as well. Below are the steps to follow:

Click on the Formulas tab, then the Defined Names group, and click on the Name Manager option. Or else, you can even press CTRL + F3, which is a simple shortcut to this function.

Click the New button placed in the upper left side corner of the Name Manager window.

Once it is done, you will see a “New Name” dialog box where you can build up a new name.

If you intend to check the newly created name, simply select it in the Name Box dropdown list. Once you release the mouse, the range on the worksheet will get selected.

How Would You Create an Excel Name for a Constant?

Microsoft Excel not only lets you assign named ranges, but you can even define a name without cell reference that works as a named constant. For this purpose, you can use either the Excel Define Name feature or the Name Manager option.

For instance, you may create a name like USD_EUR (USD – EUR conversion rate) and put a fixed value to it. Here, you will have to type the value headed by an equal sign (=) in the Refers to the field, e.g. =0.93:

Now, this name can be used in the formulas to convert USD to EUR.

Once the exchange rate appears, in one central location, the value will get updated and all the formulas will get recalculated in a single step!

To Sum Up

That’s how you can Excel naming columns or rows. Now, you have a thorough understanding of how you can create new names for a cell or cell range. Keep practicing and continue sharing useful information with others.