Friday, 10 April 2020

How to create an Excel name for a constant



How to create an Excel name for a constant

You can define a name without cell reference that will work as a named constant in Microsoft Excel. To understand easily take an example that you have an excel sheet of Employee’s salary. 

The House Rent Allowance (HRA) is 30% of Basic Pay. You have to create A Name “HRA” with a fixed value 30% or 0.03. To create name for a Constant steps are given below.
1         1. Open “Name Manager” or “Define Name” in Microsoft Excel in Defines Names Ribbon under Formulas tab.

      

2.     If you open Name Manager then click “New” or press alt+N.

3.     New Name dialog box open. If you open “Define Name” then also same Dialog Box open.
4.     Type Name (if this case HRA as per example), select scope from drop-down list(as example Salary sheet), assign fixed value (here =30% or =0.03)  in Refers to field and press “OK”.
5.     Name Manager dialog box open where you can see that HRA Name created with constant or fixed value assign for it without any cell reference. Click "Close"


 Now you can use this name (HRA) to calculate House Rent Allowance in your Salary Sheet.

Formula without HRA =c2*30% and
Formula with HRA =c2*HRA give you same house rent allowance value.





















If House Rent Allowance rate change, you have to update the value in HRA Name and all of your formulas (having HRA value) will get recalculated. To know how to change or modify click this link. how-to-edit-or-modify-named-in-excel

No comments: