Name Manager in excel

by on July 10, 2017

Name Manager in excel

 

Name Manager function in excel is a function where a name is given to a cell or range of cells. Instead of referring cells in the formula, a name is referred. The advantage of using or referring names in place of cells is obviously that it is easy to remember names as compared to cells. After reading this article, you will learn how to use the Name Manager function in excel.

Let’s take an example of calculating sales for each product. Normally the calculation is performed with the help sumif function adding numbers based on criteria. Our aim is to get the total sales for each product in Column G. The results will be identical.

Name Manager in Excel

Select Cells range from B3: D19 and go to “Formula Tab”. Click on the create from selection option. You can also use the shortcut (CTRL+F3) to open the Name Manager dialog box.  Select new in case you want to create a new one or select edit if you are editing one of your already created ones.

Important point to Note

Kindly note that Name Manager will not allow a name which has a space between it. A name that starts with a number is also allowed. For example, “Name Manager” will not be allowed but “Namer_manager” will be.   

Name Manager in Excel

Once you choose the above option, it will open up the below box, Select the Top row option and click ok. Your Name Manager is ready to be used in the formula.

Name Manager in Excel

In case if we are not using Name Manager function, the SUMIF formula for this will be G2=SUMIF(C3: C19,G2,D3:D19)

Now let’s look at the formula where we are using the Name Manager function  G2=SUMIF(Product_Name,F2, Sales). The answer in both the cases will be the same. Names are easy to remember as compared to cells numbers. Just in case you forget what names you have selected, click F3 key and it will open the list of Names list in Name Manager. Select the one you want to use.

For calculation of cells from G3 to G6, simply copy the formula in cell G2 and paste it in cells G3 to G6 and you are done.

Use of Name Manager in Pivot Table

Another useful use of the name manager function would be to give a data table a name and then use this name when inserting a pivot table for the data. Have a look at the below screen shots.

In the first screen, we give a name to the data table. After selecting the table, hit CTRL+F3, click on new and then enter a name (Sales_data).

Name Manager in excel

Here, you will go the insert tab, click on Pivot table and write down the name (Sales_data). Click OK and your Pivot table is ready to drag data into required areas.

Name Manager in excel

The Pivot table is ready to be used for further report preparation. In case you want to make the data table dynamics so as to incorporated new data appear in the pivot table. Select the table and hit CTRL+T and hit enter. Now add some data to the table and refresh the pivot table and it will reflect the new data. In similar ways, you can use this valuable excel function.

Name Manager in excel

Hit the share buttons to share it with your friends.

 

 

Please follow and like us:
0

Leave a Reply

Your email address will not be published. Required fields are marked *