• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / Make a Named Range Grow Automatically

Make a Named Range Grow Automatically

Named ranges provide many benefits when using spreadsheets. They allow easy access to data on other sheets, make references absolute and provide meaningful names to data.

However, if the list is updated often with new records or columns of data, then the named range needs to frequently be updated also. This is an irritating and time consuming role, so this post aims to solve that problem by making the named range grow automatically when new rows and columns are added.

I was reminded of the brilliance of this feature just yesterday when I was asked by a company how they could get their formulas to include any new rows of data automatically. They had various formulas and PivotTables running on a large list of data to perform sales analysis. This data is frequently updated as more sales are made.

I ended up using the method below and had the formulas and PivotTables use the dynamic named range for their data source.

Create a Dynamic Named Range

In this first example, I have the following list of names.

List of names to make dynamic

This list is used to populate a Data Validation list. But the list may grow and shrink in size so lets make it dynamic.

  1. Click the Formulas tab on the Ribbon
  2. Click the Define Name button from the Defined Names group
  3. Type a name for the named range. The name cannot include spaces or start with a letter
Define a dynamic named range with OFFSET
  1. Click in the Refers to: field and enter the formula below
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A))

Change the references to cells, columns and rows in the formula to those required in your spreadsheet.

  1. Click Ok

The named range is created. It does not appear in the Name Box list, but it can be used by typing its name into the Name Box.

How Does the Formula Work?

The formula uses the OFFSET function to make the range dynamic. Let’s have a closer look at how this works.

  • Sheet1!$A$2 is used as a start cell for the range. This avoids the header which we do not want in the Data Validation list.
  • The two zeros ensure that the range does not move from its base reference.
  • The COUNTA(Sheet1!$A:$A) function makes the height of the range dynamic. It is used to count the number of rows that are not blank. The OFFSET function then uses this for the height of the range. Whenever a new row is added, the COUNTA function recognises it and tells the OFFSET function.

Dynamic Named Range for Rows and Columns

The previous named range was only dynamic in height. If you needed a range dynamic in width also for when columns are added and removed you could use the following formula.

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

The COUNTA(Sheet1!$1:$1) function is used to find the width of the range. This ensures that new columns are included in the named range.

This example also uses Sheet1!$A$1 as the start cell as maybe we want to include the headers.

Although these examples count column A and row 1 for find the edges of the rage, please bear in mind that these may need to be changed to work for your ranges.

Related Posts:

  • How to Make Table Column References Absolute
    How to Make Table Column References Absolute

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • Excel IMAGE Function – Insert Images from a Cell Value
  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·