Monday, July 20, 2015

Throwing Names around!

In the real world, we are so used to people "throwing names around" to get things done or to convince others that they are all powerful or that they are in touch with very powerful people.

I also advocate throwing names around - certainly not in the above manner, but in a more productive way, throw names at Excel and exploit the power of convenience and clarity that Excel provides you in return.

Take a look at the below figure to get a better idea of what I mean:

Look at the Formula Bar which reflects the formula contained in Cell C2.

Something striking? Yes, you see that the formula does not contain any cell references but is more of simple English text, which would appeal to any common user who does not have great working knowledge of Excel formulas. Anyone looking at Cell C2 can easily deconstruct the working of 4,000 and tell you that it is a multiplication product of Deposit value and Interest rate.

Practically, the technique of writing such named formulas come handy if you are constructing a large workbook with multiple variables stored in different corners of the file which make it difficult to go back and refer to the cells. Alternatively, if you are building a file which has to be very simple to understand and is intended for the not-so-advanced user, this is again a helpful thing to do.

Now if you have the question of how to go about writing such named formulas, it is pretty simple - just follow this sequence:

  • Name the cells that you want to use in a Formula. There are multiple ways to doing it - simplest way is to click on the cell that you need to name and then click in the Cell Address Box (for instance, look at the small box in the above figure carrying the word "C2" just to the left of the Formula bar). Technically, this drop down is called as the "Name Box".
  • Now you would be able to just block out the existing cell address ("C2" in above example) and type any name that you wish to give - something that is descriptive or indicative of what is contained in the cell. Press Enter and you are done with the naming. Let's presume we give the title "YearlyInterest" for this Cell C2 in the above example.
  • Now you are free to use the new name in any other formula within the same workbook. If you want to compute Monthly Interest in Cell D2, you would just enter this formula "=YearlyInterest/12" and it would automatically reference back to Cell C2 and divide that by 12 to get you the result.


Defined Names is one of the most powerful tools available for the modern day analyst and every other Excel power user - so get to learn and start using it as much as you can. You will find it very convenient and handy.

Remember, you can also use the name boxes to create names in local languages too and reference them in the same language elsewhere in the file. Just to give you some flavor, the above example can be written up in Tamil as below (this could appeal to users who would understand local languages better than English and complex formulas):


If you are thinking of how to type in the local language and create such naming, use the Google Transliterate tools that caters to almost every popular Language.

Hope this helps. Let me know if you have any questions or have any additional suggestions.

You can learn a lot more about the power of Defined Names and get interesting insights into the power of Microsoft Excel from my books on Excel. Check out the following:

Kindle Versions:

Excel for the CFO -http://www.amazon.in/Excel-CFO-Professionals-P-Hari-ebook/dp/B00AH4ZWEI/
Excel for the CEO - http://www.amazon.in/Excel-CEO-Professionals-P-Hari-ebook/dp/B001E67E0S/

Paperback Versions:

Excel for the CFO - http://www.amazon.in/Excel-CFO-Professionals-P-K-Hari/dp/1615470115/
Excel for the CEO - http://www.amazon.in/Excel-CEO-Professionals-P-K-Hari/dp/1932802177/

Ebooks:

Excel for the Small Business Owner: http://www.mrexcel.com/sbo.shtml
Excel for the CFO Ebook - http://www.mrexcel.com/store/index.php?l=product_detail&p=243

4 comments:

Devika L said...

Very useful Hari. Many thanks for sharing this info

Jo said...

nice tip Hari...

chandru said...

Nice one .

SENGOTAIYAN K S said...

Super Sir ! Very interesting !