Use of Name Manager in Excel

Use of Name Manager in Excel

Name Manager helps user to define names for their formula which actually help them to understand formula easier.

Name Manager comes in use when you are creating any excel file with multiple references, like working on “Employee Appraisal Data” or “Financial Budget” or “Financial MIS” etc.

Let’s see how this works, we will take an example of electronic shop

I am providing Cell No inside the grid to understand how it works

Normal Way

Product Code	Product Name	Unit Price	Quantity	Total Bill
P1	TV	76000(D2)	2(E2)	152000(E2*D2)
P2	Washing Machine	24000(D3)	3(E3)	72000(E2*D3)
P3	Iron	1000(D4)	10(E4)	10000(E2*D4)
P4	Mixer	2000(D5)	12(E5)	24000(E2*D5)
Total with Formula				258000
Discount%				20%
Actual Bill				206400

Total with Formula= SUM(F2:F5)

Actual Bill = F6-(F6*F9)

With Name Manager

1) Select Formula TAB

2) Select Name Manager

3) Click New

4) Provide name (In above case, I have given “Final_Total”)

5) Provide Cell Reference (cell reference is sum of total bill)

Now follow same steps for Discount

1) Select Formula TAB

2) Select Name Manager

3) Click New

4) Provide name (In above case, I have given “Discount”)

5) Provide Cell Reference (Value of Discount% will be the cell reference for this)

Now in Actual Bill we can write formula directly Final_Total-(Final_Total*Discount) rather than giving formula.

This will make easy for the person who is reviewing excel sheet first time, no need to run around with cells and understand how you have arrived the value of Actual Bill

These are the basics of implementing and managing names that any Excel user should know. Once you get used to names, you can create and use formulas with ease. It may take some time to learn and get into this habit, but I suggest you put in the effort. It’s well worth it!

Keep Reading

Sanjay Bakshi

mesanjaybakshi@gmail.com