Naming Ranges is one of those Best Practices that is so often neglected. In spite of the fact that you can save you an enormous amount of time and make your formulas more intuitive to any user of your data, most users of Excel simply do not take the (very little) time to complete this important step.
Why
Named Ranges
Using a Named Range in a formula does away with the need to make the range an absolute reference because it will always point to the correct range, regardless of where you copy the formula.
For example, let’s say you are managing a Sales Department, and you are using a dropdown box in a cell that refers to a list of your employees. For convenience and to assure consistent spelling, you have created the dropdown box using the List feature on Validation.
Rather than having to put up with the bother of adding/deleting employees from the reference list and then having to adjust the list range, you can create a Named Range “SalesEmployees”, (with sufficient room to add names in the future), and refer to “Employees” in the list box in the Validation dialogue box. Wow, amazingly simple!
Combining Named Ranges with the Validation tool in Excel is a very powerful way of maintaining consistency in a spreadsheet, and making them more accessible for anyone to understand.
How to Create Named Ranges:
1.
The Quickest
Way is to Use the Create from Selection Tool
a.
Select your Table or Database
(including the headers…)
b.
Go the to the Defined Names
group on the ribbon
c.
Choose Create from Selection
d.
Assure the Top Row is
checked
e.
Click OK and Bamm! All of your ranges are named at once!
2.
Alternatively,
You Can Use the Name Box
a.
First Select the Range of cells you
wish to name
b.
Now click inside the “Name” box on
your toolbar at the upper-left of your screen
c.
Type Your Chosen Name for the range
d.
Hit the Enter key and Presto! Your
individual range is named.
Happy New Year, All!
No comments:
Post a Comment