This is the first in a series of posts in which I will introduce you to the Excel concept of dynamic named ranges, which is a very useful piece of knowledge to have at your disposal. In this week’s post, I’ll discuss what dynamic named ranges are, and why you would want to use them.

What is a dynamic named range?

In Excel, a group of cells is referred to as a range of cells (for example the range A7:G42). Any range of cells can be given a name, such as “SalesTable” (making it a “named range”), so that the name can be substituted in place of the range reference (i.e. instead of typing A7:G42 into a formula you can use “SalesTable”).

A “dynamic” named range is one where the size or shape of the range changes based on a formula. So instead of saying “SalesTable” is from A7 to G42, you would say that “SalesTable” is from A7, down by the number of entries in column A, and across by the number of entries in row 7. This of course means that whenever information is added immediately below or immediately right of the range, the size of the “SalesTable” range automatically extends!

Why would I use a dynamic named range?

There are a variety of reasons to use dynamic named ranges. The three main ones are:

Readability

A2:G47 doesn’t mean anything, but SalesTable does.

Efficiency

Without using a dynamic named range, it is very tempting to use an entire column or row reference in a formula (A:A), so that when rows are added to a table, all the formulae relying on that table still work correctly. This does of course mean that each of those formulae are calculating across several MILLION rows. Excel is reasonably smart about doing this, but it WILL eventually slow right down.

Accuracy

Ever transposed a couple of numbers in a cell reference and spent 4 hours trying to work out why nothing adds up? Named ranges avoid this. The #Name error makes it REAL obvious you’ve made a mistake in typing the name of the range, so it’s easy to correct. In fact from 2007 onward Excel will even provide “code completion” in the formula bar so that you can select the named range once you have started typing.