HOW TO USE NAME MANAGER AND DEFINE NAMES IN EXCEL?

Table of Contents

INTRODUCTION

Do you imagine how would it be if we didn’t have names or had names something like HUMAN NO. 1 , HUMAN NO. 2 and so on.

It’d be pretty awkward, but yes we do use such names in Excel.

We use the cell number to identify any location. But , do you know that we can also name the cells?

Yes, we have NAME MANAGER in Excel which allow us to name the cells.

Name Manager keeps the track of all the defined names in Excel.

The Names can be defined by selecting a group of cells and defining their name.

The names are helpful in understanding and making the formulas easier to understand and apply.


Name manager shows all the defined names for the ranges and define names helps to name the range.

PURPOSE OF NAME MANAGER IN EXCEL

There can be many helpful things name manager can do for us. Some of them can be

  1. If our formula is too long, it can become cumbersome to keep the cell name accurate all the time. For example, you may type C16 instead of D16 or any other mistake can take place. But if you have a name say “weight”, you’ll write weight in place of that cell in the formula. The chances of the mistake are highly reduced as the mistake in the spelling will be caught at once and you need not to learn the cell address.
  2. Creating a formula using the names in Excel is far more easier than looking at the address again and again. For example, if we want to write a formula
    FORCE=MASS X ACCELERATION, we need to look at the address of cell containing mass and acceleration values, but if we name the cell as mass and acceleration, we need to not look for the cell address any more . Simply use the word [ mass ] and [ acceleration ] wherever you need them in the formula.
  3. These formulas [ using names ] are far more readable than the formulas using cell addresses. Remember deciphering a formula and again and again trying to look at the cell address and then column header.
  4. Overall, once we name the important cells , the overall time consumed is very less which improves our efficiency.

BUTTON LOCATION OF NAME MANAGER AND DEFINE NAME IN EXCEL

We’ll find the NAME MANAGER option under the FORMULAS TAB under DEFINED NAMES section as shown in the picture below.

The location is same for all the versions of Excel say 2010,2013,2016,2019 and highers.

BUTTON OPTION LOCATION OF NAME MANAGER

HOW TO DEFINE NAMES IN EXCEL USING NAME MANAGER?

It is very easy to define names using name manager.

It helps to define the names of any cell or range.

FOLLOW THE STEPS TO DEFINE NAMES:

  • Select the cell or a range [ group of cells ] which you want to name.
  • Just select the cell or cell range ( A group of cells) and click define name. [FORMULA TAB > DEFINED NAME ]
  • Choose the scope, which can be workbook or sheets.
  • Just have a look at the REFERS TO , if it is showing the correct references.
  • Click OK.
DEFINE NAME DIALOG BOX

The name has been created.

HOW TO USE NAME MANAGER IN EXCEL?

Name manager shows all the defined names for the ranges and define names helps to name the range.

You can create a new range directly from the name range also.

The following picture shows the created NAME and also gives a NEW OPTION.

If we click NEW it’ll take us to the name define dialog box as discussed in the previous section.

NAME MANAGER DIALOG BOX

HOW TO GIVE NAMES AUTOMATICALLY IN EXCEL?

If we don’t want to give a custom name, we can create the names automatically too.

The RANGE NAMES can be given automatically by an option called CREATE FROM SELECTION.

This option gives automatic names from the selection. User need not to give customized names to the range or cells. The excel itself understands and gives the name from the row heading or column heading as per the user’s requirement.

STEPS:

  • Select the cells for which the names are needed including the column heading or row heading, whatever is applicable.
  • Click on the button, CREATE FROM SELECTION.
CLICK CREATE FROM SELECTION

  • The following screen will appear.
AUTOMATIC NAME ASSIGNMENT IN EXCEL
  • Choose from the options present in the dialog box. It asks from where the automatic names are to be taken. e.g. we don’t need top row as we need only the marks. So select only LEFT COLUMN AND CLICK OK.
  • NOW click on NAME MANAGER to find out how many names have been created. Editing can be done if something is not as per convenience.

NAME MANAGER WITH CELL NAMES

The student names has been given to the marks cells also. Now suppose we need to sum the marks of any two student, Abraham and Ravi.

We need not to write =SUM(cell address of Abraham marks, cell address of Ravi marks).

In place of this we will simply write =SUM(Abraham, Ravi).

This is the benefit of naming a cell. It speeds up the work.