Dynamic Formulas that Update When you Add Data in Excel

Add to Favorites
Author:

I'll show you how to make formulas and functions that automatically update when more data is added to a range in Excel. 

A simple example is when you want to sum a range that will have more data added to it over time.  You don't want to have to continue to update your formulas and functions, so you want dynamically updating formulas and functions.

Here is our sample spreadsheet:

Now, let's add a number to the list and see what happens:

The regular sum did not change but the dynamic sum updated to show the correct amount.

(Make sure to download the workbook for this tutorial so you can follow along).

Here is the regular SUM:

=SUM(A2:A5)

Here is the dynamically updating sum function:

=SUM(OFFSET(A2,0,0,COUNT(A:A)))

To make the above formula compatible with versions of Excel prior to Excel 2007, use this:

=SUM(OFFSET(A2,0,0,COUNT(A1:A100)))

This is just because you can't reference an entire column in Excel 2003 and earlier and A:A references an entire column, column A.

Now, let's break-up this formula.

Basically, I am just using the OFFSET() function to return a range that the SUM() function will then use instead of hard-coding a range into the SUM() function.

This is the portion of the above formula that makes it dynamic:

OFFSET(A2,0,0,COUNT(A:A))

All you need to do is to change these two parts of it to work for you:

OFFSET(A2,0,0,COUNT(A:A))

To use the OFFSET() function to return a dynamic range we simply need to put the first cell in the range as the first argument, A2 in this case.

Then, we need to use the COUNT() function as the 4th argument for the OFFSET() function in order to figure out how big our range should be.  Change A:A to whatever column contains your list of data.

The COUNT() function counts all cells in the range that have a number.  The range reference that you use for the COUNT() function should be large enough to hold any size list that you could have in the future so it does not need to be changed.

If you use Excel 2007 or later, just use A:A as the argument for the COUNT() function, as shown in the first dynamic example above.  This way, the entire column will be referenced.

Dynamic Ranges that Include Text and More

If you are in Excel 2007 and later you can use the COUNTA() function instead of the COUNT() function.  The COUNTA() function counts all cells that are NOT empty whereas the COUNT() function only counts the cells that have numbers in them.

Best Practices

If you make formulas and function dynamic by using the OFFSET() function as mentioned above, it will cause confusion and clutter very quickly when you make large formulas.

One commonly used way around this is to use Named Ranges that dynamically update when you add new values to a range.  This uses the same method as we illustrated above except that the OFFSET() function and its contents are contained within a Named Range and this Named Range is then used in the formulas and functions in the spreadsheet whenever you need to reference a range that you want to update each time you add a value to the range or list.

Here is our Dynamic Named Range in Excel Tutorial that will show you how to do that.

The OFFSET() function is the key to creating dynamic formulas and functions in Excel.  Make sure to check out our OFFSET Function Tutorial if you want a more in-depth explanation of how it works. 


Excel Function: COUNT(), COUNTA(), OFFSET()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Automatically Shade Every Other Row When You Add Data in Excel
Tutorial: How to make Excel automatically add row shading to every other row when you add data to th...
Convert Numbers into True and False in Excel
Tutorial: How to convert numbers into the TRUE and FALSE Boolean values in Excel. This is very impor...
MOD Function in Excel
Tutorial: The MOD function is very simple but it can be used to do wonderful things in Excel. It ret...
OFFSET Function in Excel
Tutorial: The OFFSET function in Excel returns a cell or range reference that is a specified number...
3D References - Formulas that Reference Multiple Sheets at Once in Excel
Tutorial: Have one simple formula that will reference the same cell or range of cells on multiple w...
Dynamic Named Range in Excel
Tutorial: How to create a named range that expands automatically when a new value is added to the r...
Tutorial Details
Excel Function: COUNT(), COUNTA(), OFFSET()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course