Name Manager in excel

by on July 10, 2017

Name Manager in excel

Name Manager in Excel is an important function where we give a name to a cell or range of cells. Instead of referring cells in our formula, we refer to the name. Here, we will learn how to use the Name Manager function in excel and what’s are its benefits. 

Our aim is to get the total sales for each product in Column G. we can get this objective by using the sumif formula with the cells reference or sumif formula with the names in place of cells numbers. The results will be identical.

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 one.

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.   

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.

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.

Important Tip

To keep the Name List dynamic,  use “Format as Table”.

 

share the article with your friends and office colleagues if you like it.

 

 

Please follow and like us:
0

Leave a Reply

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