I have written blog about OFFSET earlier. Recently, I found one good application for OFFSET.

When you load data from system to Excel, the data needs to be rearranged.

Five columns and many rows. This is the way how the data looks in the system.

When you download the data, the data is in one column, between the rows in the system there are two empty cells.

The data should be modified as it is in the system, five columns and many rows. You can copy cells (A2:A6) and paste with transpose to have five cells horizontally. This takes time if you have 10000 values.

This can be tackled with OFFSET function.

Let’s have a short summary about OFFSET.

OFFSET consists of five arguments.

1. Reference. What is the starting point in the top left hand corner of the area.
2. Row. How many steps do you take from reference downwards, if the value is positive.
3. Col. How many steps do you take from reference to right, if the value is positive.
4. Height. How high is the selected area.
5. Width. How wide is the selected area.

Here is an example. The start cell is B3. Then we go two steps downwards, that is B5. From B5 we go two steps to right. The area is just one cell ( one times one). That is five.

The function in B10 is =OFFSET(B3;C10;D10;E10;F10) .

The OFFSET can be framed with another function like SUM.

The function in B10 is =SUM(OFFSET(B3;C10;D10;E10;F10)).

The area is two steps down from B3, that is again B5. Then we go three step to right to E5. Then area is two times two, from E5, (E5:F6). SUM(E5:F6) equals to 28.

Let’s move from theory to practice.

To use OFFSET function, we define A2 as the reference, it could also be A1, but I just picked up A2.

The second argument in OFFSET, row, is varying argument as the all the values are vertically.

The first function to extract the first value is =OFFSET($A$2;0;0;1;1) .

When the first value is taken, we don’t have to go anywhere from A2. Both row and col are zeroes. The area is just one cell, the height and width are one.

To extract the second value, all the arguments are same except row which bust one higher than previous time =OFFSET($A$2;0;0;1;1) . As the data is in one column, we don’t have to adjust col. Each time we are looking for one value and one cell, therefore height and width are always ones.

Between 5^th and 6^th values are two empty rows. After the 5^th, the row value must equal to previous value plus three.

In cells (I2:M4) I have inserted the row values. J2 is simply I2+1. When row the changed I3 value is M2+3.

The row values are taken into the function from (I2:M4) area. Other argument values are static.

Data here is pretty small, but same solution works for 10000 cells, and it does not take too much longer time than for three rows.