Excel Dynamic Range Formulas to Automatically Increase or Decrease Your Range

Microsoft Office offers far more than just what it says on the box. Programs like Word and Excel are far more powerful than just an average word processor and spreadsheet. Part of the power behind these applications can be found in the built-in functions that they offer. Excel for example offers over 400 built-in functions that use the power of VBA for applications to accomplish difficult tasks simply and efficiently. And when these functions are used in combination, the sky really becomes the limit to the functionality offered by Excel.

For a fantastic course on Excel, aimed at the beginner and more advanced user, sign up for the Microsoft Excel 2010 Course Beginners/ Intermediate Training course now from Udemy and take advantage of the power of tutorials to learn to harness the power of Excel.

This tutorial will show you how to create dynamic ranges for your worksheet. A dynamic range is a named range that atomically adjusts the size of the range when values are added to the range or when values are removed from the range.

The tutorial assumes you know how to create and name a range. For an article on how to work with cell references and create named ranges, you can read Excel Cell References and How to Use Them in Your Worksheets and Formulas, available from the Udemy blog. The tutorial also makes use of the OFFSET and COUNTA functions. For an explanation of how the COUNTA function works, you can read Excel COUNTA: A Step-by-Step Tutorial to Show You How to Use COUNTA.

This tutorial will use the following simple spreadsheet to show you how to create your own dynamic range:

The worksheet contains fictitious sales data. The total sales value is calculated using the following formula:

The average sale per day value is calculated using the following formula:

The reason we are using COUNTA to calculate the total number of sales for the formula, is because once we create the dynamic range, then we will need a way to calculate the number of sales dynamically as well. COUNTA allows you to dynamically calculate the number of sales by counting the number of cells in the column that contains values. The count would include the title cell so the formula takes off one for the title cell.

For a great course on Excel, sign up for the Microsoft Excel 2013 Training Tutorial now and learn to harness the power of formulas in your worksheet.

Now let’s add a sale to our worksheet:

You will notice that the total sales value has not increased. The average sales per day is also incorrect because the sale was not calculated as part of the total sales made.

To add a row automatically when we add further sales to our worksheets, we need to create dynamic ranges that increase or decrease as we add records to the worksheet.

To create a dynamic range we first need to create named ranges for the columns in our worksheet.

How to Create Dynamic Named Ranges

For the purposes of this tutorial we have named the three columns in the example worksheet as “SalesDate”, “Salesman” and “SalesTotal”.

To name the ranges, select the range you want to name and select the formulas tab, and then select the Define Name option from the Defined Names section of the menu.

The following window will appear:

To create the named range you need to enter the name of the range under name. We are going to call the dates range we have selected “SalesDate”.

Next we need to enter the formula under the “refers to” section. This formula will create a range that grows or shrinks as we add or remove a new sales date to our worksheet.

The formula you will enter will be:

=OFFSET(Sheet3!$B$1;0;0;COUNTA(Sheet3!$B:$B);1)

You also need to create a dynamic range for the “Salesman” and “SalesTotal” ranges in the same way, using the necessary columns in each named range.

This is what the named range for the “Salesman” will look like:

And this is what the formula for the “SalesTotal” range will look like:

The OFFSET function is used to create the dynamic range. The OFFSET function uses 5 parameters to return a reference to a range that is offset from the original range. In this case we are not using the offset to offset the range as such but to create a new range based on whether the range has increased or decreased.

By using the COUNTA function within the OFFSET function, we are effectively creating a range based on the value returned by the COUNTA function that reflects the size of the range based on the number of cells that contain values included within the range. So for example if there are 8 cells that contain values within the SalesTotal range, the OFFSET function will create a range of 8 cells. Similarly if you remove a value from the cell, the COUNTA function will count that there are now 7 cells that contain values and the OFFSET function will then create a range of 7 rows for that range.

For an advanced tutorial on Excel, sign up for the Microsoft Excel – Advanced Excel 2010 Training course from Udemy today.

Now we need to adjust our Sum functions in the Total sales per day and Average sales per day formulas to calculate the SUM of the named ranges rather than the column references.

The new formula for the total sales calculation will be:

And the formula for the average sales will be:

If you take a look at the answers to the Sales total and average sales now, you will notice that the Total sales and average sales now correctly reflect the new sale we added.

The worksheet with dynamic ranges looks like this:

If we add further sales to our worksheet now, the figures and ranges automatically increase to include those new sales.

The advantages of Excel Dynamic Ranges

Excel worksheets are most often works in progress. Most of the worksheets we work on are constantly being updated in terms of the data we need to store and manipulate. When you add rows or columns to a worksheet, formulas need to be changed or adapted to include the new information.

Knowing how to create dynamic ranges means that you no longer have to spend valuable time adjusting your formulas and functions. The dynamic range automatically adjusts all of these formulas for you so that you can concentrate on the data itself.

For a comprehensive course on how to harness the power of Excel for you, sign up for the Excel for Business – Learn Excel Online course from Udemy today.