Dynamic range in excel using OFFSET and COUNTA functions

May 23rd, 2011 by Tips Master | Posted under Excel Functions.

Dynamic range to create a Invoice lookup in ExcelThis tutorial is the continuation on working with the Offset function in Excel. Hope you have enjoyed the first part. In this part we will see how to create dynamic range using the offset function which will expand and contract based on user inputs . See the first part to know about the Offset function - Offset function made simple.

I have already mentioned in the earlier post about the best use of Offset function, which is none other than creating dynamic ranges. Many times we get frustrated while working with the database, when new entries come and we need to manually change the range every time. Whether it be a Pivot, a Chart or ranges inside functions, its going to be annoying changing every time when we have a new entry.

 
What is the possible solution? Start using Offset function in naming your range. Inserting an Offset function with the Height and width parameters set using COUNTA function will create a Dynamic range which expand or reduce based on the data input by the user.  See below where you will find step by step illustration on creating Dynamic range for rows, columns and for the entire database.

Dynamic range for rows

See the below screen shot which gives a quick snap shot  about creating dynamic rows in a range using Offset function.

Dynamic range for a Pivot table

A dynamic range with the name Pivot is created (See the earlier post to see how to name a range). The function is added as =OFFSET($B$2,0,0,COUNTA($B:$B),2). Here we have decided that the columns will be fixed, so we will be concerned only with the height of the table, which is going to be dynamic as an when new month is added to it. Instead of directly entering the height parameter, we will use the COUNTA function. COUNTA will return the number of non empty cells in column B. The value of height will be the result of the COUNTA function. Whenever you add a row, It will automatically increase the range. Try adding a row and just refresh the pivot to see the same reflected in the Pivot table.

Dynamic range for pivot data

 

Dynamic range for columns

Dynamic ranges for Columns
Its done in the same way, as we done for the columns and the difference is that COUNTA function is used for the width.
The formula used is =OFFSET($J$4,0,0,2,COUNTA($J$4:$R$4)). I have limited the dynamic range only to column R for the illustration purpose. In the actual scenario  you can either select the whole row without giving the column reference(4:4) . Try adding a month and value and refresh the pivot to see the additions.

Dynamic range for the database

If you want to create an entire database as dynamic range, you need to add COUNTA funtion to both the height and width parameter in the offset function. In the file available for download i have worked out a simple invoice lookoup from the sales database, where the lookup function and the database both are dynamic.

Dynamic Sales database

 

Dynamic sales Database

As you can see in the file the range is named as database and the function used is =OFFSET(‘Data Base’!$A$6,0,0,COUNTA(‘Data Base’!$A:$A)-4,COUNTA(‘Data Base’!$6:$6)). I have created some dynamic range for the columns customer and Invoice nuber which we will be using in the Invoice details tab.

Invoice details

Invoice Lookup using Offset function
You can use cell E6 as the Invoice number reference from which the details are required. I have done a data validation to get the list of invoice numbers updated in the dymanic range. This is also a dynamic range with the name defined as “Invoice” and the offset function used is =OFFSET(‘Data Base’!$E$6,1,0,COUNTA(‘Data Base’!$A:$A)-5). All the look up value cells are also updated with the dynamic lookup formula.

Data Validation for invoice number

Download the file and feel free to change it and use it according to your convenience. Please let me know if you find this useful. Please give your comments / Queries

Download the Excel file

Use the link to download the 2007 versions – Dynamic Ranges using offset function

Use the link to download the 2003 versions - Dynamic Ranges using offset function

Comments

3 Responses to “Dynamic range in excel using OFFSET and COUNTA functions”
  1. TJ Emsley says:

    Very helpful! Having the video is really a great way to demonstrate this.

  2. Manuel says:

    Hey!, thx for the useful info. I got a question..where did you use the name “costumer”? I know that you use “database” for the pivot and “invoice” for the invoice details but i cant find “costumer”‘s utility.

    Thanks

Do you have any comments on Dynamic range in excel using OFFSET and COUNTA functions ?