Advantage of Naming a Range
When there is need to deal with big datasheets and complex formulas, it could create many problems when you access data by specifying its cell location. Naming a range a clever way to remember the purpose of a formula, rather than using cell references.
For example in the image below, the formula =Sum(Energydrink_Totalsales) is much more convenient than =SUM(D3:D11).
Using named ranges is more convenient than typing the address or selecting the data each time you want to refer to them. Named ranges also provide an advantage in that if you expand the range, perhaps to include additional data, the pivot table report will automatically include the new data when it is refreshed.
Select the cell or cell range that you want to name.On the Formulas tab, click Define Name in the Defined Names group.( as shown in the image above)
The New Name dialog box appears.
In the Name text box, type the name of the range. Try to use something descriptive. Like for the example in image 1 the Named Range is Energydrink_Totalsales.
Click OK.
Points to remember while creating a range name :
a) The first character must be a letter, an underscore, or a backslash.
b) No spaces are allowed in a range name, use a underscore if needed to separate words.
c) The range name should not be the same as a cell address. For example, you can't name a range A27
or GH40.
How to use a Named Range
To use a named cell or range, click the down arrow in the Name box at the left end of the Formula bar. Select the range name you want to access, and Excel highlights the named cells.
OR
Select a range name in the Name box to quickly locate an area of a worksheet.
OR
Use range names with the Go To dialog box. Press F5 to display the Go To dialog box, select the range name you want to jump to, and click OK.
You can insert range names into formulas just like they were normal cell references.
(Click on images to enlarge it )
When there is need to deal with big datasheets and complex formulas, it could create many problems when you access data by specifying its cell location. Naming a range a clever way to remember the purpose of a formula, rather than using cell references.
For example in the image below, the formula =Sum(Energydrink_Totalsales) is much more convenient than =SUM(D3:D11).
Using named ranges is more convenient than typing the address or selecting the data each time you want to refer to them. Named ranges also provide an advantage in that if you expand the range, perhaps to include additional data, the pivot table report will automatically include the new data when it is refreshed.
 |
| Image 1 |
How to Define a Range :
Select the cell or cell range that you want to name.On the Formulas tab, click Define Name in the Defined Names group.( as shown in the image above)
The New Name dialog box appears.
In the Name text box, type the name of the range. Try to use something descriptive. Like for the example in image 1 the Named Range is Energydrink_Totalsales.
Click OK.
Points to remember while creating a range name :
a) The first character must be a letter, an underscore, or a backslash.
b) No spaces are allowed in a range name, use a underscore if needed to separate words.
c) The range name should not be the same as a cell address. For example, you can't name a range A27
or GH40.
How to use a Named Range
To use a named cell or range, click the down arrow in the Name box at the left end of the Formula bar. Select the range name you want to access, and Excel highlights the named cells.
 | |
| Image 2 |
Select a range name in the Name box to quickly locate an area of a worksheet.
 |
| Image 3 |
Use range names with the Go To dialog box. Press F5 to display the Go To dialog box, select the range name you want to jump to, and click OK.
 |
| Image 4 |
(Click on images to enlarge it )
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.