Time for some more #formulafriday fun. Today let’s look at 2 ways to create a name range in Excel. If you want to read more about using named ranges in your Excel formulas then you can read my more in depth blog posts below.

But back to today where we will be exploring two methods of making named ranges dynamic. What do we mean by dynamic, well it just means they automatically adjusting either expanding or contracting as data is added is is deleted from the named range. That will be very useful!.

Method 1. Using Excel Tables.

So this first method is probably the most straightforward. From Excel 2007 onward Excel gave us Tables, this made it really easy to convert data into a formatted data table. You can then go ahead and use one or more columns of your table as a named range. Let’s look at an example to demonstrate.

First we take our data set which is based on an extract of monthly sales volumes and convert it to an Excel table.

Select your data (CTRL+A)
Hit CTRL+T or Insert Tab – Table
Confirm your Table has headers ( this will be the top row of your data)
Hit Ok

You now have an Excel data table!. Now the cool part let’s make the Month column a dynamic list.

Highlight cells in Column A (Months) excluding the headers

In the Name Box and type a name for the named range, in this example I have named this INVOICEMONTH

Once you hit enter, the name will be saved

To view the name click the drop down arrow in the Name Box or click in the Name Manager and you will see the Named Range INVOICEMONTH and the name of the table you have created (in this example it is Table2).

As this named range is part of the Excel table, any new data added to it will automatically expand the INVOICEMONTH named range.

Let’s test it – adding in an entry for May 2017. Now we can take a look at the named range INVOICEMONTH and it has now included May 2017 along with the original data entries.

Method 2. Using An Formula.

This next method uses a formula to define the named range rather than using an Excel table. Each time new data is added the range will expand just as with Method 1 above. If you chose this formula method then the named range will NOT appear in the Name Box drop down list, but you can still navigate to them by typing the name in the Name Box.

Let’s write the formula, we use OFFSET and COUNTA

The Syntax of OFFSET Formula is-

=OFFSET(reference, rows, cols, [height], [width])

reference – this argument is required it is the reference from where you want to base the OFFSET. This must refer to a cell or a range of adjacent cells.

rows – this argument is required it is the number of rows either up or down that you want the left uppermost cell to refer to.

columns – this argument is required it is the number of columns, left or right that you want the upeermost cell to refer to.

height – this argument is optional and is the height in the number of rows that you want the returned reference to be – it must be a positive number.

width – this argument is optional and is the width in the number of columns that you want the returned reference to be -it must be a positive number.

To create the first part of the formula

Formulas Tab – Defined Names Group- Define Name

In the New Name dialog box, type your name – again I have used “INVOICEMONTH".

In the Refers To section is where we enter the OFFSET Formula, which returns a reference to a range specified by number of rows and columns.

I have my sample data set below which is two columns in width- so I need to design a dynamic range which is two columns wide.

The arguments in this OFFSET formula are-

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4)

The data is contained Sheet1

Reference Cell -Sheet1!$A$1 -we need the Column Names so we start at reference A1

Rows to offset -0

Columns to offset – 0
Number of rows –COUNTA(Sheet1!$A:$A)
Number of columns -2

The second part of the formula – the COUNTA counts the number of values in the range that are not empty. We use this in the ROWS argument. When you add a value to the range i.e a new row of data, COUNTA($A:$A) increases. As a result, the named range expands. (Or reduces as rows are deleted). Isn’t this handy?.

The syntax of COUNTA is

=COUNTA(value1, [value2], …)

value1 Required. The first argument representing the values that you want to count.

value2, These are now optional. Additional arguments representing the values that you want to count, up to a maximum of 255 arguments.

You can see the finished formula below.

Now it is time to test our formula again. Let’s add in a record for May 2017 and see if the range has automatically expanded by typing the name of the range in the Name Box. (Remember!, the name will NOT appear in the Name Box automatically like on Method 1.)

We can see the highlighted range has now expanded to include the May 2017 record.

Over To You!

So, which of these methods have you used to create dynamic named ranges?, have you used named ranges in your formulas, comment below to share!

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Formula Friday series you can do so by clicking on the link below.

How To Excel At Excel – Formula Friday Blog Posts.

How To Excel At Excel

Formula Friday – 2 Ways To Create A Dynamic Named Range In Excel