OFFSET() is a nifty function that when used in conjunction with other formulas be very useful at times. Here are some examples of how it can be used to optimize some tasks.
In one of my earlier blogs, I demonstrated a formula to transform a matrix of data into a single, continuous column of data. While that formula is very powerful, it is quite long, perhaps hard to grasp for most people, and it also requires that the columns in the matrix be of equal lengths (i.e. rows).
We’ll use the same matrix example, but will use simpler formula with OFFSET but it can handle equal or unequal sized of columns.
So we have 200 columns, each with variable number of rows ranging from 10,000 to 20,000. But we need one column that captures all the rows from all of the 200 columns. How would we do that?
The same way as I show here in this very simplified, smaller data set. Here we have 3 columns with 1o rows each but they could be of variable lengths. We want the final column to look as shown below in blue shades.
First row’s cell of the target column can hold this formula:
=OFFSET(A4,0,0, COUNTA(A4:A13),1)
When we paste this it automatically fills all the values from column T1. The beauty of this is that since I’ve nested COUNTA function in it, the column can be of any variable size and we don’t need to enter that value in the formula. So, it copies the value starting at cell A4 and then calculates the number of rows in it, and copies into a width of 1…meaning a single column.
Because it’s an array/spill function, you don’t need to copy/paste this for every cell, and it’ll automatically indicate the last cell with the output value.
Right below that final cell, enter the formula again! Only this time, we’re starting over at column T2’s first value, which is in column B, therefore it now becomes:
=OFFSET(B4,0,0, COUNTA(B4:B13),1)
As soon we enter this formula (E13 in this example), it’ll retrieve all the values from T2 in the target T Final column and fill until cell E22. To pull in the T3 column, you’ll apply the same formula in E23 (changing the starting reference to C4 and so on). And we are done!
This works even if T1, T2, T3 were of different sizes since we’re dynamically calculating their rows individually.
Here’s another good use of OFFSET. Say, you have that huge matrix as stated in the beginning of this blog, but you only need to work on the first y number of rows and x number of columns without affecting the source data.
Going with a simplified example, we have the same matrix as above A4:C13. But we only want to pull a smaller matrix from it using its 4 rows and 3 columns.
We can achieve this with OFFSET function again. This time, we’ll use the 2 optional parameters for height and width. Height tells it how many rows from the reference cell to pull, and Width indicates how many columns. So, the solution is:
Positive offset number for column indicates moving right, and for row means moving downward; but you can also go left by using negative value for offset for columns, and upward by using negative value for offset for rows.
Given these tips, how would you extract the first 100 rows from a data set of 20,000 rows into a column to work with? (without manually copying/pasting)
Have fun with it!
