Find Your Home on the Range

If you are an old pro at Excel, you probably know that Naming Ranges can save you a lot of time and make your formulas more intuitive to any user of your data.

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 working in Human Resources, and you are using a dropdown box in a cell that refers to a list of 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 “Employees”, (with sufficient room to add names in the future), and refer to “Employees” in the list box in the Validation dialogue box. Cool!

Using a list with the Validation tool in Excel is a useful way of maintaining consistency in a spreadsheet. Combining it with a Named Range can make it More Effective!

Two Easy Ways of Creating Named Ranges:

1.   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 Bamm! Your range is named.

2.   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 Presto! All of your ranges are named at once!

Named Ranges. Another Best Practice for Excel professionals!