Tuesday, December 5, 2017

Special Naming Ranges Tricks

As we have discussed in this blog, and most of us know quite well, Naming Ranges is a well agreed-upon Best Practice.

As with any tool within Excel, however, if you dig into it a little bit deeper you’ll find there are additional tips and tricks that can add to your knowledge and help you do even more in less time. Take for instance the following Two Tricks for Naming Ranges:

1.   Limiting the Scope of a Name
If you wish to reuse a Name on different worksheets within a workbook, you can limit the scope of a single use of the name to a single worksheet:
a.    Choose FORMULAS, and then click Define Name
b.   Fill in the details, specify SCOPE, and click OK

2.   Defining a Constant or Formula for a Name
The ability to have a Constant or Formula available in Name form can be a true timesaver when working with complex workbooks:
a.    Choose FORMULAS, and then click Define Name
b.   Type the NAME, SCOPE, and (if you wish, COMMENT)
c.    In the dialog box, REFERS TO, insert the Constant or Formula preceded by an =, and click OK

Knowing several Best Practices is the hallmark of any true Excel Guru. However, knowing a few extra special tricks in addition to these best practices can get you a place higher on the mountain.

No comments: