How to Use the OFFSET Formula

The OFFSET formula is a very powerful formula, but unfortunately not easy to understand. It basically refers to another cell or cell range. You specify a starting point (“Reference”) from which you count rows and columns. If the starting point is cell A1 and you tell Excel to count 2 cells to the right and 3 down, it’ll return the reference to cell.

How to use the OFFSET formula

OFFSET, formula, Excel

The OFFSET formula has five parts, of which the first 3 parts are required and the 2 last parts are optional (the letters are corresponding with the picture above):

  1. The reference: The initial cell or cells which we will start counting from (letters A and D in above example).
  2. The number of rows counting from the reference (letters B and E in above example).
  3. The number of columns counting from the reference (letters C and F in above example).
  4. Height: If you want to get a cell range and not just a single cell, you can optionally define the height of the range (letter G in above example).
  5. Width: The same as for the height applies for the width.

The example above returns in both cases the result -10. Letters A to C refer directly to cell C5 (start counting from B2, 3 cells down and 1 to the right). The second formula (D to G) sums up the range C3 to C4 – starting from B2 1 cell down, 1 cell row and the height of 2 cells returns the range C3:C4.

Please note that if the last 2 parts are left blank, the same height or width as the reference are assumed.

The OFFSET formula is a volatile formula. That means, that it will be recalculated every time Excel calculates your workbook – no matter if there are changes to it’s input values. Especially with complex workbook this can slow down Excel.

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

Leave a comment

Your email address will not be published. Required fields are marked *