Using Named Ranges in Excel.

Shakespeare tells us that a rose by any other name would smell as sweet.¹ But think how confusing it would be if every florist had a different name for the same flower — it’s nice to know that a rose is a rose everywhere.

Likewise Excel becomes a lot easier to use when we give names to cell ranges we frequently work with. Then we can refer to them with easy to remember names like Revenue, Sales and Expenses rather than cell ranges like A1:A100.

How Names Can Help Us. Excel lists all named ranges in a drop down menu to the left of the Formula Bar. Choose a name from the list and Excel goes there and selects the cells. Thus, by naming cell ranges we frequently print, we won’t have to manually highlight them again and again every time we want to Print Selection. Some people use names to create a worksheet index that makes it easy to get around.

But the real power of names comes when we use them in formulas. For example, if a worksheet has ranges called “Revenue” and “Expenses,” we can write the formula =Revenue-Expenses instead of =A1-A2. Moreover, names can be used multiple times, so if there is a list of transactions called “Sales,” it can be used in all of the following: =SUM(Sales), =COUNT(Sales) and =AVERAGE(Sales). And if the range to which the name “Sales” applies is changed, all formulas referring to “Sales” immediately recalculate using the updated range.

Defining a Named Range. The easiest way to define a name is with the Name Box on the left of the Formula Bar. To do so (see Figure 1):

1. Select the cells to which the name will apply;
2. Type the name in the Name Box at the left of formula bar; and
3. Press the Enter key (otherwise the name is not recorded).

Names can also be defined with the Define Name command or from within the Name Manager (see Figure 2). Both are in the Defined Names area at the center of the Formulas tab.

There are a few technical requirements for a valid range name:

• It cannot contain a space;
• The first character must be letter or an underscore;
• It cannot be the same as a cell address, for example A1; and
• It must be unique either within the workbook or worksheet depending on its scope.

Workbook or Worksheet Scope. A name created with the Name Box can only apply to one range in the workbook (computer file). In other words, if the name “Sales” is assigned to cells A1:A100 on Sheet 1, it cannot also be assigned to a different group of cells elsewhere in the same file. It is possible to define names whose scope is limited to a single worksheet (tab), but that can only be done using the Define Name command on the Formulas ribbon (see Figure 2). In that case, the name “Sales” can refer to A1:A100 on Sheet 1 and B25:B300 on Sheet 2.

Editing Named Ranges. To change a name, or the cells to which it applies, click the Name Manager button at the center of the Formulas tab, choose a name in the list and click the Edit button (see Figure 3). Make the desired changes in the Edit Name dialogue box, click OK and close the Name Manager. Note that you cannot change the scope (workbook or worksheet) of an existing name.

Using Names in Formulas. Names make it easy to create and modify formulas. For example, if a worksheet has a range named “Budget” and another named “Actual,” the formula =SUM(Budget) calculates the total Budget while =SUM(Actual) does the same for Actual (see Figure 4). The beauty is that once the names are defined, they can also be used to compute the MAX, MIN, COUNT and AVERAGE for the ranges. And if a range is enlarged or reduced to accommodate a change in the number of sales reps, all the formulas are updated immediately.

The variance in our example is calculated as =Actual-Budget. The formula is the same for every row, but each calculation uses the Budget and Actual numbers for the correct sales rep. This is because when a formula combines two ranges of the same dimensions (4 rows and 1 column in our case), it matches cells by their relative positions in each range. Thus the first cell in Budget is matched with the first cell in Actual and so on. This would work even if each range had 1000 rows of data.

Names in Linking Equations. A formula in one workbook can reference data in another. This is called an external cell reference and enables one to combine data from multiple files without actually transferring the data.

The problem is that if rows or columns are inserted or deleted in a workbook while other workbooks linking to it are not open, the external linking formulas are not updated and continue to point to the old locations even though those cells no longer contain the desired data. The solution is to name the cells being referenced and have linking equations reference those names, not their cell addresses. Since name definitions are stored in the same workbooks as the cells they refer to, they are automatically updated whenever rows or columns are inserted or deleted, thereby preserving the integrity of linking formulas pointing to those locations.

* * * * *

Named ranges are one of those software features with little glitz, but lots of substance. Try them, chances are you’ll find that they save time, reduce errors and overall make your work easier.

-----

1. Juliet in Romeo and Juliet, Act 2, Scene 2, Lines 46-47. On-line at Folger Shakespeare Library.

Named ranges are covered in our Advanced Excel and Excel Dashboards classes.

Visit our Excel Productivity Guides page for more helpful articles.

This article originally appeared in our free twice-monthly e-mail newsletter. To receive future issues, please add your name to the subscription list.

Want to learn more? We offer seven different Excel classes: Mastering Excel, Advanced Excel, Excel Charts, Excel Dashboards, Macros and Pivot Tables and Power Pivot. Click any class name for a detailed description.

We can also create a private Personalized Excel Class for your group, or provide individual training or project consulting services.