In the previous post, we learnt about how to protect a MS Excel file. In this post, we shall learn how to created Named range, Named constant in MS Excel and how to edit, delete and use them.
Named range is a range of cells that has been given a specific name so that it is easy to understand in the Formula.
Named constant is a name given to a constant value so that the same can be easily understood and can be reused at multiple places.
There are two ways to create Named ranges.
Click on 'Define name' from the Formula tab, you would see 'New Name' dialog box. Write the Name in the 'Name:' box and constant value in the 'Refers to:' box.
Click OK. This will create a Named constant that can be used in the formulas.
To delete, edit named range or named constant click on 'Name Manager'.
that will open following dialog box.
To edit, select a Name (row) and click OK. Modify the respective textboxes values and click OK.
To delete, select any of the formula and click on Delete button.
To use Named range or Named constant, press F3 at the edit mode of Cell and you would see 'Paste Name' dialog box. Click OK and the selected name appears in blue color in the cell.
or select a cell, click on 'Use in Formula' command dropdown from Formulas tab on the ribbon and you would see list of Names, click on desired one to paste in the cell.
The result looks like this
The other way of using Named range or constant is to start typing their name in the Formula and you woudl see an intellisens like below. Select the formula name and that particular name will be in the cell.
You can see that instead of writing '=(E4*15)/100', we have written '=(E4*PPFRate)/100'. Here PPFRate is Named constant.
The benefit of using Named constant here is that if for some reason PPFRate is changing, we can simply go to that Named constant and change it's value and all the related cells value will be changed. If this were a direct value instead of Named constant then we had to go to every cell to update the calculations.Views: 684 | Post Order: 13