10. Updating Formulas with New Data
When you add additional records to a dataset, you don't have to update all the formulas each time. Instead, you can automatically grow the arrays when new records are added.
Dynamic ranges for updating formulas
- Most companies add new data to existing datasets over time
- Automatically updating your formulas to include new data is a huge timesaver
- We use OFFSET and COUNTA to do this
- Put the OFFSET formula in your named range to keep formulas readable
OFFSET(reference, rows, cols, [height], [width])
--- reference: The value off which you base the offset - normally column header
--- rows: The number of rows the array is away from reference
--- columns: The number of columns the array is away from reference
--- height: Height of the array. Use COUNTA to count non-blank cells in column
--- width: Width of the array in columns
CTRL + F3: Open the Name Manager dialog box
ALT + N: Create a new Name
F2: Jump inside formula
SHIFT + →: Select an additional cell
CTRL + →: Move to the last cell in the data region
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
In companies, most data sets are updated over time with new records, and as a result, the formulas for these data sets must also be updated.
As you can imagine, updating the size of our arrays in formulas can be really time consuming, particularly if we have a lot of calculations as we do in our search panel.
Wouldn't it be great if the arrays in our formulas could automatically update to include new entries? Well thankfully, they can, using a command called OFFSET.
Let's start off with a simple example that highlights the problem.
I currently have revenue for 4 months of the year, and a simple lookup using INDEX and MATCH that returns the revenue for a given month.
Unfortunately, when I add May to the array by typing the month and a new revenue figure, it won't work in the lookup.
I'd like my lookup value to update automatically when I add a new month to my data.
So let's add OFFSET to our lookup equation.
Unfortunately, the OFFSET formula has quite a few inputs.
So to make our formulas more readable, let's create named arrays with the OFFSET formula included there, and then we’ll include these names in our formulas.
So I'll create a new name with Ctrl + F3, Alt + N, and I'll simply write MONTHS.
And then in the Refers to box I'll include my OFFSET formula.
The OFFSET function simply returns an array that is a certain number of cells away from a reference cell.
And we start the function by writing “=offset”, and we first include our reference cell, which in this case is month.
Next we include the position of the array, which is one row down from our reference cell, and zero columns across.
Next we include the height of the array.
And to do this I'll use the COUNTA function which counts all the non blank cells in a column.
And then I'll select the B column with Ctrl + Shift + Down Arrow twice.
The height of our array will then need to be reduced by one to account for the first row, which just includes the headers.
And lastly I'll include the width of the array, which is simply one.
I'll close the bracket and press OK.
I'll now create another offset for revenues.
Let's call this MONTHLY_REVENUES.
Again “=offset”, I'll start by selecting my reference cell, again the position of the array is one row down and zero rows across.
I'll use COUNTA, this time to count all the non black cells in C, I'll take one away, and the array will be one column wide.
I'll close the bracket and press OK.
Now let's remove the arrays in our index formula and replace it with our named arrays.
So this will be months, and this will be monthly revenues.
And when I press return, we can now calculate the May revenue.
What's more, if I enter a June value, the array automatically updates to include June.
As you can see, the OFFSET formula is very powerful, but there are quite a few inputs to enter into the function.
Don't worry about learning these all for now because I’ve included them in the show notes below the video.
If at some stage you want to check if you use the OFFSET function correctly, simply go back into the Name Manager, I'll select MONTHLY_REVENUES and then click in the Refers to box.
And this will show you the cells that are currently highlighted by the array.
And here we can see that we’ve implemented it correctly.
Our final task in this course will be to apply the OFFSET function to our sales data set that includes a new transaction that I’ve added for the sales person Baines.
However, I'll leave this until the next lesson.