×

Quickly Create A Dynamic Named Range in Excel

By Chris Newman •  Updated: 01/10/24 •  4 min read
Dynamic Named Range Excel Tutorial

What Is a Dynamic Named Range?

A dynamic Named Range in Excel is a powerful feature that allows you to create a range of cells that automatically adjusts or expands as you add or remove data. This means that the range is not fixed to a specific number of cells but can change in size dynamically based on the data it contains. This is particularly useful for creating formulas, charts, or pivot tables that need to update automatically as new data is added or existing data is removed.

Why Use a Self-Expanding Named Range?

  1. Automatic Updates: When you add or remove data from your dataset, the dynamic Named Range updates automatically. This means any formulas or charts that use the self-expanding Named Range will also update without needing manual adjustments to the range size.
  2. Efficiency: It saves time and reduces errors because you don't have to manually update range references in your formulas or charts every time the data changes.
  3. Flexibility: Auto-adjusting Named Ranges can adapt to varying amounts of data. Whether you have 10 rows of data today and 1000 tomorrow, the dynamic Named Range will adjust accordingly.
  4. Improved Readability: By assigning a meaningful name to a range of cells, you make your formulas easier to read and understand. For example, using a name like "SalesData" instead of a cell range like "A1:A100" makes it clear what data the formula is working with.

Setting Up The Dynamic Named Range

You can easily create an expanding Named Range using Excel's Name Manager. Here are the steps to set one up after you have determined the formula you want to utilize:

  1. Formula Tab
  2. Define Name button
  3. Name your Named Range
  4. Expand the Dialog to make it wider - bottom right-hand corner (this will help you view/edit the formula more easily)
  5. Paste & modify one of the starter formulas in this article
  6. Hit OK button
How To Create An Auto-Expanding Dynamic Named Range in Microsoft Excel

It is a good idea to validate your Dynamic Named Range formula after you have created the Named Range. To do this, simply navigate to the Name Manage dialog box, select your Named Range, and click into the Refers To formula bar.

You should then see the "marching ants" outline the boundaries that the auto-expanding range is calculating. If you don't need to make any changes, hit your ESC key to exit out of the input box.

Confirm Expanding Range Formula Is Working Properly In Excel Spreadsheet

If you are looking to set up something similar in your VBA code, you can check out an article I wrote covering a handful of coding solutions called 5 Ways to Create A Dynamic Auto-Adjusting VBA Range.


Using The INDEX Function

My preferred way to create a flexible named range is to use the INDEX function combined with the COUNTA function (counts non-blank cells). Below are some examples covering various scenarios of popular auto-expanding named range types.

For accurate results, ensure the COUNTA function is pointed to a column or row range that will not have any blanks within the data body range.

Single Column (List)

=A1:INDEX(A:A,COUNTA(A:A))

Single Row (Headings)

=A1:INDEX(1:1,1,COUNTA(1:1))

Range Of Data (Multiple Rows + Columns)

The Data begins in cell A1:

=A1:INDEX(1:1048576,COUNTA(A:A),COUNTA(1:1))

The data begins in cell B5:

=B5:INDEX(5:1048576,COUNTA(B:B),COUNTA(5:5))


Dynamic Named Range With OFFSET Function

An alternative method you can utilize to build a dynamic named range formula is to utilize the OFFSET function. For accurate results, ensure the COUNTA function is pointed to a column or row range that will not have any blanks within the data body range.

Warning! The OFFSET function is a volatile function which means it will recalculate every time you make a change to your spreadsheet. Depending on how much data is in your spreadsheet and the amount of formulas you are using, this may cause performance issues (slowness).

Single Column (List)

The Data begins in cell A1:

=OFFSET(A1,0,0,COUNTA(A:A))

The data begins in cell C4:

=OFFSET(C4,0,0,COUNTA(C:C)-(ROW(C4)-1))

Single Row (Headings)

The Data begins in cell A1:

=OFFSET(A1,0,0,1,COUNTA(1:1))

The data begins in cell C4:

=OFFSET(C4,0,0,1,COUNTA(4:4)-(COLUMN(C4)-1))

Range Of Data (Multiple Rows + Columns)

The Data begins in cell A1:

=OFFSET(A1,0,0,COUNTA(A:A),COUNTA(1:1))

The data begins in cell B5:

=OFFSET(B5,0,0,COUNTA(B:B)-(ROW(B5)-1),COUNTA(5:5)-(COLUMN(B5)-1))


I Hope This Microsoft Excel Article Helped!

Hopefully, I was able to explain how you can easily write formulas in the Name Manager to provide you with a dynamic named range. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.