Using the OFFSET Function to Specify a Range in Excel 2007:-


The OFFSET function allows you to specify a rectangular range of data. You have to provide five arguments to specify the range:

1. Any starting cell—An example is Sheet1!A1.

2. A number of rows to move down from the starting cell to the first cell in the range—if you specify a positive number, the range starts below the starting cell. If you specify 0, the range starts in the same row as the starting cell. If you specify a negative number, the range starts above the starting cell.

3. A number of columns to move right from the starting cell to arrive at the first cell in the range—You can specify a positive number to move right, 0 to stay in the same column as the starting cell, or a negative number to move to the left.

4. The number of rows in the range—If you specify 1, you describe a range that is one row tall.

5. The number of columns in the range—If you specify 1, you describe a range that is one column wide.

To understand the various types of ranges that the OFFSET function can return, consider the shapes in the Figure (given below).



(Examples of the OFFSET function.)

The top-left shape in the above Figure highlights a range that is 8 rows tall by 4 columns wide. If the starting cell is G12, you would have to move 9 rows up from the starting cell and 4 columns left from the starting cell to arrive at the top-left corner cell of the range. The function to refer to this range is =OFFSET(G12,-9,-4, 8, 4).

The box underneath cell G12 is 12 rows tall by 1 column wide. It starts 1 row from the starting cell of G12 and 0 columns to the right. The function to refer to this range is =OFFSET(G12, 1, 0, 12, 1).

The single cell in I9 is 3 rows above the starting cell, 2 columns to the right, 1 row tall and 1 column wide. The formula is =OFFSET(G12,-3, 2, 1, 1).

Of course, it is silly to write any of these formulas. If you knew that your chart range was always going to be in cells G13:G23, you would simply refer to Sheet1!G13;G23. The power of the OFFSET function is that you can use other functions for some of the arguments. For example, you could count the category labels in your dataset today by using the formula COUNTA ($J;$J). The formula =OFFSET(J13, 0, 0, COUNTA(J;J), 1) starts in column J and extends down to include the number of cells with data in column J. This formula counts on the data in column J to not include any blank cells.