Excel Tip : Offset

In Excel there is a formula called “Offset”.  This returns the value of a cell that is a given number of rows and columns away from a cell that you referenced.

The syntax is =OFFSET(Reference, Rows, Columns).

• Reference is the cell you are going to refer to, or start from
• Rows is a number, it is the number of rows above or below your reference cell.  Negative numbers would be above the reference and positive numbers would be below the reference cell
• Columns is a number, this is the number of columns to the left or right of the reference cell.  Negative numbers would be to the left of your reference cell and positive numbers would be to the right.

We will use this table to demonstrate the offset formula

In cell E2 we will enter our Offset formula.

We will set our reference cell as A1, we want to move down 3 rows(to A4) and across 1 column (to B4), this returns the value “China” which is the value in cell B4.

How useful is this formula?  I’m sure it is useful, but personally I haven’t come across the need to use it as a formula.

However, when it comes to macros, it is something that I use in the majority of my macros.

From this post, you will know that when you record a macro, Excel records the cell reference that you recorded your formula in.

For example, if you recorded a macro where you summed cells A2 to A10 in cell A11, then every time you replay that macro it will sum A2 to A10 in A11, no matter how many rows of information you have.

To make sure that my total goes at the bottom of my table, first I move to the bottom of my table using the code behind Ctrl + Arrow keys and then I offset 1 row down from here, this is where my total formula will go.

 Code Snippet

• ActiveCell.Offset(1,0).Select

Offsetting by 1 row from the bottom row of a table will always be one row below the bottom of the table.

Using the code which will be covered in a future post, we will jump to the bottom of the table and offset by one.  This will work if you had 10 rows of data or 10,000.

[grwebform url=”http://app.getresponse.com/view_webform.js?wid=2766903&u=CucD” css=”on”/]