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:
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:
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.
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:
The image below presents a spreadsheet of averages of a certain class.
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:
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:
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.
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.
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.
Based on the knowledge gained in the first example, we can imagine the following scenario:
A supermarket controls your sales as shown below:
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:
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:
At the end of the formula, just hit "Enter" and check the result.
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.
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.
B) Enter the range of cells where the list of names are located.
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"
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.
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.
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.
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.
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.