How to Use Offset Function in Excel

In this tutorial, you will learn how to use the OFFSET function in Excel.

The offset function returns a reference to a range that is a given number of arrows and columns away from a given reference.

In our example, we will want to check the last entries of column A and column B, the Date and the Closing Rate respectively.


Step 1. Position the cursor where you want the result to appear.

Position the cursor

Step 2. Type the following

=OFFSET(A1, COUNTA(A:A)-1, 0)
And hit Enter. The result is brought automatically.

type the formula in Excel cell

Step 3. We will do the same with the next field, Closing Rate.

Type the formula:

=OFFSET(B1, COUNTA(B:B)-1,0)
The result will return the last field of the column B.

type the formula in Excel cell

Step 4. Experiment with adding more: your calculations will update.

add more values

Result: Congratulations, you have learned how to use the OFFSET function in Excel.