Using the name manager in excel

13
24475
Using the Name Manager in Excel
Excel Spreadsheets

The frequent use of Excel often brings the need to speed up certain tasks, either to reduce working time or even for the convenience of the user. In this way, the learning of some tips or tricks that are made available by this software can be of great value for the better execution of a work, as well as to add more knowledge to the user.

Thus, we will address in this article the use of a tool known as Name Manager that aims to name a certain range of cells and reference it as the user wishes. See more details below:

Cell Name

Before we start the explanation regarding the name manager, we must understand that in Excel each cell has a default name that is basically its range.

Look at the image below:

Using the Name Manager in Excel

If we select the cell C4, for example, we will have in the Name Box the name of the selected cell, ie for the example above we have the cell C4 refers to the cell that is in column C and 4 row. Note also that through the name box circled in the image above, you can also identify any name that is given to a cell or group of cells.

Manage Names

In Excel we can define a name for a range of cells, or simply for a cell in order to facilitate jobs that might involve them.

In this way, when using the name it becomes easier to apply the range of cells.

For a better understanding of this resource, we will use practical examples as well as a demonstration of how to apply it. Follow the following:

Example 1:

The image below presents a spreadsheet of averages of a certain class.

Using the Name Manager in Excel

For some reason the teacher wants to get the class average. In this way he would simply use the "Average" function in the ranges of cells in which are the averages of each student as shown below:

Using the Name Manager in Excel

Now note that this function, however simple it may seem, can still be simplified using the name manager.

In this case, just select the range of cells to be named and access the tab Formulas> Name Manager as shown below:

Using the Name Manager in Excel

It will open a new window in which just click the "New" button. In this way, a new window will be sent to name the set of cells.

Using the Name Manager in Excel

Name: You must enter a name for the set of cells that you want to name. In this option, names should not have spaces.

Scope: It is defined where the cell naming will be applied. In this case we select the Workbook option.

Comment: Write a review as references if necessary.

Refers to: Select the range of cells to reference.

At the end of the operation, click OK to finish.

Note that it will now be possible to obtain the average number of students indicating the name given to the set of cells containing the individual means of each student.

Using the Name Manager in Excel

It is worth mentioning that in addition to the "Average" function it is possible to use other functions referring to the name given to the set of cells to obtain a certain result.

Example 2:

Based on the knowledge gained in the first example, we can imagine the following scenario:

A supermarket controls your sales as shown below:

Using the Name Manager in Excel

Note that there is a table with sales for the month of January and another for the month of February.

In this case, there is a need to add up all sales in January and February to get a total sales value. Therefore, we refer to the sales columns of each month as shown below:

Using the Name Manager in Excel

Using the Name Manager in Excel

To make the sum of the sales of each month, we will use the SUM function and insert the named references of each column as shown below:

Using the Name Manager in Excel

At the end of the formula, just hit "Enter" and check the result.

Using the Name Manager in Excel

Example 3:

For a third example, we'll use name-server learning to make it easier to create a drop-down list.

In the image below we have the data validation window where it is accessed by the tab Data> Data Validation> Data Validation.

Using the Name Manager in Excel

Note that we are creating a data validation in the form of a drop-down list and to enter the data that must be presented in the list we have the following options:

A) Insert individual names separated by semicolons.

Using the Name Manager in Excel

B) Enter the range of cells where the list of names are located.

Using the Name Manager in Excel

In a third option, we can name the list of names and then reference it in the data validation.

Notice in the image below that we named the range of sellers names for "Sellers"

Using the Name Manager in Excel

Thus, when we create the drop-down list of these names, just reference it based on the given name, making it easier to create the drop-down list.

Using the Name Manager in Excel

Editing names

Either by typing or by any need for change, you can change the name given to a cell or set of cells.

To do this, simply access: Formulas tab> Name Manager to open the name manager box. Choose the name you want to edit and click the Edit button.

Using the Name Manager in Excel

A new window will open as below, where you simply change the name in the Name field. Click OK, and note that the name will change.

Using the Name Manager in Excel

Using the Name Manager in Excel

Another alternative and even more practical way to create and edit names in cells and cell ranges is to use the name box itself for this purpose.

In this case, just select the cell or set of cells that you want to name, enter the desired name in the name box and hit enter.

Using the Name Manager in Excel

Using the Name Manager in Excel

The procedure for editing the name is also performed in the same way.

Based on the learning acquired in this article, it can be observed that the option of Name Management in Excel aims to facilitate the reference of cells in a certain operation whether in use of functions or even for the creation of a simple list of validation of data.

Thus, in naming a group of cells, it becomes better to understand them to adopt them in operations as well as to make further adjustments in these when necessary.

Excel Spreadsheets

13 COMMENTS

  1. How to use the Name Manager to make a dynamic list recognized by the Combination Box (Form Control) without blank spaces. I managed to make the Combo Box display the list with a dynamic table allied to the name manager.

    However, if it is a dynamic list based on formulas, blank spaces appear in the combo box.

    Note: I need something only with formulas. Without using macro.

  2. I need to name a lot of lines but I need a different name for each of these lines, how to proceed to do this without being one by one please?
    It can not be selecting the entire range and giving only a single name, I need a different name for each of the lines.

  3. Yes, it's possible. You can use the following function at the time of determining the name of the range: = DISPLACEMENT (Data! $ H $ 4; 0; 0; CONTAR.VAL (Data! $ H: $ H) -2; 1)
    The above function returns a range. In this case the range in question is in column H and starts in cell H4, the second step determines how many rows I should include in the range, to find out how many I have used the function VALUES (my Excel is in PT-PT, in consideration) of H: H, that is, every H-column, but -2, which are the headings of the columns in question.

  4. Hi Adriano, I do not understand how your table is organized, but SOMASE sums the values ​​of one column, conditioned to the values ​​of another. If you want to add 4 columns, you can create a formula for each column

  5. Hi Rafael,

    First of all your blog is a great help with valuable tips. I have a spreadsheet where I need to use a SOMASE formula, I am using the name manager, but I use the 4 columns as January, then fill the cells with the values, when I type January, in the calculation is only pulling the values ​​of the first column, is it possible to change this?

  6. Hi, Mayra, the scope is created at the time the named range is created, usually referencing the workbook in which it is located

  7. How do I edit the scope of a created name? I can edit the name, but not the scope.

  8. In this case, if you use the INDIRECT function you should be able to get at the result you want. In the formula use = INDIRECT (A1) + INDIRECT (A2)

  9. Good afternoon! The tips helped me a lot!

    Is there any way for formulas to refresh automatically after I name a cell?

    For example, I had the formula = A1 + B1. I named A1 for "Weight" and B1 for "Height", but after naming the formula continues = A1 + B1. Is there any automatic way for the formula, after naming, to stay = Weight + Height? For in my case the spreadsheet was already set up when I started naming the cells.

    Thank you!

  10. Hi Robert, you have. The name manager takes a specific range. If you make this range larger, when you add a new value within the range, it has already been updated. For example:

    name1 for the range A1: A3. In that case, if I fill A4, I would need to adjust the name, but if I change the range to A1: A10, when I fill cells A4, A5, etc to A10, all will already be part of this named range.

  11. Good morning, I'm doing a monthly expense control sheet for a company, I used the definition of cell names to help me. Every day I change this worksheet by adding values, causing me to have to use the name manager every day to change the cells. Would you like to know if I can do this automatically? That is, when I add a value in my spreadsheet my name manager automatically changes.

LEAVE AN ANSWER

Please, write your comment
Please enter your name