How To Create A Dynamic Range Using The OFFSET Function In Excel

In this tutorial we are going to look at two ways of creating a dynamic range of data. We are summing up sales data and want to automatically, or dynamically adjust the range to sum as we incorporate additional rows or columns. One way will be using Tables and the other the OFFSET function. Let’s see how we can do this in Excel.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.


Here I have a data range of 8 months and 6 sales people. If I create a SUM function to calculate the total sales, I get the total shown in cell A2:

4916-1

However, if I add September sales or another sales person, the SUM function doesn’t pick up those additional values automatically. It still only shows a total of $14,711,800:

4916-2

Now, if I convert the data range into a table using the keyboard shortcut Ctrl + T, and name that table “CorpSales”, as I incorporate additional rows and columns to the table, the SUM function, using that Table name, automatically includes those values in the total:

4916-3

But what if you can’t use Tables, or are uncomfortable with them, how can we accomplish the same thing? One alternative is to use the OFFSET function to dynamically define the range of data to sum. The OFFSET function “Returns a reference to a range that is a given number of rows and columns from a given reference”.

The syntax for OFFSET is:

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

So, using the OFFSET function to define our SUM range, if we use cell C6 as our reference, and go down 1 row and over 1 column, we would then have cell D7 as our starting point. Then we need to define the height and width of the range we want to sum. In our scenario, that range is 6 rows high and 8 columns wide. If I enter the formula:

=SUM(OFFSET($C$6,1,1,6,8))

…in cell A3, I will get the correct total of sales:

4916-4

However, hard-coding the height and width of the range does not make it dynamic. We need to replace the 6 & 8 with the COUNT function so that it will count the number of rows and columns in the range that contain numbers. Remember, the COUNT function only counts values, not text, so it will ignore the row and column headers.

Replacing those values with the COUNT function, we have the following formula:

=SUM(OFFSET($C$6,1,1,COUNT(D:D),COUNT(7:7)))

…and the result of that formula is the correct total sales for our range:

4916-5

So now, when we add another column or row, the COUNT function will increase the range values and automatically include them in our totals:

4916-6

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , ,

No comments yet.

Leave a Reply