Tuesday, December 29, 2015

Naming Ranges – A Best Practice

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 RangeSalesEmployees”, (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.

 Named Ranges truly are a worthy Best Practice for you to adopt in 2016.  It will add efficiency and clarity to any applicable workbook.

Happy New Year, All!

No comments: