Use OFFSET and COUNTA to create dynamic ranges with automatically updating formulas in Excel 2007 and Excel 2010
Welcome to my latest hub on Excel. Today, we are going to look at something that is rather cool and something I have needed in my own Excel Spreadsheets for some time.
Imagine if you will, a spreadsheet where you record invoices, sales, web hits etc and then SUM them to give yourself a running total. Every time you add extra rows to your spreadsheet you have to update your SUM formula to allow for the extra items.
Well not anymore! Welcome to the dynamic range using the OFFSET and COUNTA functions. Using this function, whenever rows are added (or deleted) and also each time copied cells are inserted, the SUM formula will automatically and dynamically update to reflect this.
Using the OFFSET function in a formula in Excel 2007 and Excel 2010
The OFFSET function allows you to ask Excel to tell you the contents of a cell (or a range of cells) a certain number of rows and columns away from a reference cell (or cells).
If you use OFFSET on its own, it will return the value of the cell you referred it to. We will start with that as an example:
The formula is made up of the following parts:
- The cell you are asking Excel to start from (in this instance B3) (known by Excel as the Reference)
- The next part of the formula is the number of Rows (which can be either up or down) from the reference cell to the cell you are looking at. We are interested in cell C4 so this value is 1 as it is one row down from cell B3.
- Next, we need to know how many Columns from the starting to the reference cell. This is 1 again as it is one column from B3 to C4
- The fourth element of the formula is Height. This is the number of rows you want the returned data to be.
- Finally, we have Width or the number of columns you want the data to be.
Note: Rows and Columns can be a negative or positive number. Please refer to the below table to understand when to use positive or negative values.
When to use Positive and Negative Numbers for Rows and Columns in OFFSET formulas
Right of Reference
Left of Reference
Note: Height and Width must both be positive. If they are not specified, Excel will use the height and width of the reference.
Note: If you get a #VALUE you have typically asked Excel to display something it cannot display.
To illustrate this:
If I use the following formula:
I am asking Excel to display the results of one cell in two cells which it cannot do so it returns an error.
Note: If you get a #REF error you have gone off the edge of your worksheet.
This formula results in a #REF error, as there is no such row four rows above B3.
Using OFFSET with COUNTA in a formula to create a dynamic range in Excel 2007 and Excel 2010
Now that we understand the OFFSET function and how to use it in a formula, we get to the really interesting part of this hub; we get to make a dynamic range using it alongside COUNTA. We can then SUM the values in the dynamic range and this formula will automatically recalculate if cells are added, removed or their contents changed. Of course, SUM is just an example that I chose for this hub, you could use AVERAGE, MAX or any other function you felt was appropriate for your needs alongside the OFFSET and COUNTA functions.
First, let’s quickly look at what COUNTA does for us in this example:
It simply counts the number of cells that are not blank between the start and end cell (in this case there are 29 non blank cells between cell C3 and cell C32).
Now, we include the COUNTA formula above as part of our OFFSET formula to create a dynamic range. The finished formula is:
We will begin by looking at the OFFSET section of the formula:
- We start at cell C3 as our Reference
- The number of Rows we are asking Excel to move from the starting cell of C3 is 0
- Likewise, the number of Columns is also 0 (I like to add and remove columns to my data so keeping it at 0 means that the formula will never break)
- Now comes the dynamic part of the whole formula. For the Height, we are telling Excel that the height is the result of the COUNTA formula which is currently 29. Should we add or remove rows, this will adjust automatically!
- Finally, we advise Excel of the Width, which is in this case 1 column.
Now that we understand the OFFSET section of the formula we add the SUM function to it to complete the overall formula giving us:
If we now Insert an additional blank row you will notice that the Total Sales goes down by 18 (which is the value in cell C32 which is the final row) from 1,085 in the figure above to 1,067 in the figure below.
I now add a value into the blank row and the Total Sales is now correct and includes the value in C32.
Note: Inserting a blank row into your dynamic range will lead to the results shown in your SUM formula being incorrect.
Note: Should you add additional rows, for example by Inserting Copied Cells Excel will simply increase the COUNTA part of the formula which makes it dynamic.
By way of an example, I added four rows to my spreadsheet and Excel updated my formula automatically!
The ability to have dynamic ranges in your Excel spreadsheets containing data such as sales, invoices, web hits etc which can then be added up or averaged is a very useful thing indeed. This means that you no longer have to manually update your formulas whenever you make additions or deletions to your data.
To achieve this:
- We started by using the OFFSET function in the formula. This function allows you to start with a reference cell (or cells) and ask Excel to display the contents of another area a specific number of rows and columns from the reference cell
- Next we added the COUNTA function to provide the Height of the area for OFFSET. Whenever cells are added or removed from the range, the COUNTA section of the formula is updated automatically.
- By adding the SUM function to this formula, we can now add up the cells that Excel has displayed via OFFSET.
When all of these functions are added to a formula, it will now dynamically sum a range of cells, updating automatically when data is added or deleted from the range. This for me is the beauty and power of Excel, the fact that you can add two functions together and create something that is not only very useful, but also more than the sum of its parts.
I hope that you have found this hub useful and informative and that using OFFSET will transform your spreadsheets in the same way that it has transformed mine. Many thanks for reading, please feel free to leave any comments you many have below.
Has OFFSET transformed the way you work with data in Excel?See results without voting
More by this Author
- 0Use the Rank and Percentile Tool from the Analysis ToolPak in Excel 2007 and Excel 2010 to create ranking tables
The Excel 2007 / 2010 Rank and Percentile tool allows you to create ranking tables. In this hub we use Rank and Percentile with MATCH, INDEX and IF functions to create a fully automated ranking table.
Dynamic charts in Excel change automatically if data it is added or removed saving you time and effort. They use a formula with the OFFSET and COUNTA functions and named ranges using Defined Names.
If your data range contains many blank cells, it can be hard to correctly create a chart. Use the IF function to convert blank cells to #N/A which Excel ignores or change how Excel deals with blanks.
No comments yet.