Avoid this referencing gotcha when using Excel's range names

Excel range names use absolute references, which can inhibit productivity if you're not careful. This simple trick lets you sidestep the problem.

Assigning a name to a range of cells is a great way to work more efficiently because you can refer to the range name instead of literal cell references in functions and formulas. However, there's an interesting behavior connected to range names that can be a bit limiting: Range names use absolute references.

The spreadsheet below has four named ranges: North, South, East, and West. To sum the regional totals, you might enter the formula =SUM(North) in cell F5 and then copy that function to cells F6:F8, but it won't work. Because range names are absolute references, Excel will not use a relative referencing scheme and enter the formulas, =SUM(South), =SUM(East), and =SUM(West), as you might expect.

When this happens, you might try dragging across the appropriate cells, but Excel will automatically usurp the cell reference and enter the range name.

There's a way around this, according to Excel guru Bob Umlas. When you want to include the cell reference of a named range in a function or formula, you must enter it via the keyboard. Then, when you copy it, Excel's relative cell referencing behavior will kick right in.

In a simple spreadsheet, range names are easy enough to avoid. In this particular example, the range names add nothing -– it's just as easy to enter the cell references as it is to enter the range name. In other words, often, this limitation is symptomatic of poor design rather than Excel's seemingly limiting behavior. However, if you run into it, you'll still want to know how to get around it if deleting the range name isn't practical (and it seldom is in a working spreadsheet). Keep this limitation in mind when designing future spreadsheets.