What is the Offset Function?

The offset function is a powerful Excel formula, the purpose of which is to return a single value or a range of values that is a given number of rows or columns from a specified reference (i.e. a specific cell – your starting point). This function is particularly useful when we don’t know the exact location or range needed (i.e., when the output needs to be dynamic or is dependent on other variables). This will start to make more sense as we go through examples.

The offset function is great for dynamic sums, implementing a scroll bar into a chart, dynamic charts, dashboards, named ranges, use with other functions such as the Match function, and the list goes on.

Excel’s Offset Function

The Basics

The formula is =Offset(Reference,Rows,Cols,Height,Width)

  • Reference – this is where you want to base the offset, the starting location. It can be a single cell or a range of cells. There must be a value here, this is the only part of the offset function that requires a value.
  • Rows – is the number of rows (up or down) you want the upper-left cell of the result to refer to. A positive number moves down, negative moves up. If rows do not need to change, you can leave this blank or use a “0”.
  • Cols – is the number of columns (left or right) you want the upper-left cell of the result to refer to. A positive number moves right, negative moves left. If columns do not need to change, you can leave this blank or use a “0”.
  • Height – the number of rows you want to be in the result, by leaving this unused, the result will be the same height as the original reference. If you’re not using either height or width, the rest of the formula can be left blank, or you can use “0”.
  • Width – the number of columns you want to be in the result, by leaving this unused, the result will be the same width as the original reference. If you’re not using either height or width, the rest of the formula can be left blank, or you can use “0”.

Examples of the Offset Function

Each example is included within a tab of the download below – download the file to see how the function works and make modifications…the best way to learn. The first 5 examples are intended only to demonstrate what the offset function does, not best practices. As a best practice, formulas should be made as dynamic as possible – they shouldn’t need to be updated frequently. See the example in Tab 6 for a dynamic use of this formula.

Offset Function Example Download

Download 116.50 KB 735 downloads

Offset using Rows only – Tab 1

Excel Offset Function 1
In this example we start the offset formula in cell A2, and we want to pull the value of the 3rd point down, within the same column. Note, notice how there are no other values entered for Height, or Width – this formula could have also been written as =OFFSET(A2,3,0,0,0)
Offset using Columns only – Tab 2
Excel Offset Function 2
In this example, we’re starting at the same place as Tab 1, but we’re wanting to pull the value from 4 places to the right on the same row. Note, in this formula we put a “0” in the Row section, however, we could have written the formula as =OFFSET(A2,,4)

Offset using Height only – Tab 3

Excel Offset Function 3
Now that we’re moving on to Height and Width, we’re going to add the SUM function in order to demonstrate the result. In this case, we wanted to sum the first 3 days. Using cell E2 as our starting point, we left both the ROW, and COLS as “0”. Note: This formula could have also been written as =SUM(OFFSET(E2,,,3)). Notice the difference from the first two examples? The range of cells in the output includes the first cell (i.e., the REFERENCE), where as a “1” in the first two examples would be 1 away from the REF.

Offset using Width only – Tab 4

Excel Offset Function 4
Using the same theory as in the previous example, except this time, we’re summing across columns.

Offset using a Range for the Reference – Tab 5

Excel Offset Function 5
This example is very similar to Tab 4; however, it doesn’t use the WIDTH attribute. Because the REFERENCE includes a range, we only have to specify the ROWS and COLS.

Offset Function made Dynamic – Tab 6

Excel Offset Function 6
Okay, now that we’ve gone over some basics, it’s time to see an example of how we could really get some benefit out of this function. In this example, we replace the basic SUM function (i.e., = SUM(B3:D3)) with the formula seen to the left. This formula will allow you to add and delete entire columns.  Let’s say Item C goes away, or we add Item D, without having to update the formula. Here’s how it works – because we know we’ll only want to sum up anything that’s between the Date and the actual sum, we started the formula referenced on the date and told it to move 1 column to the left “=OFFSET(A3,,1” – this puts us in B3. In order to get it to go to the end, we used the COLUMN function, which returns a value relative to the column we’re in (in this case =COLUMN() would return a 5 – A = 1, B = 2, etc). You can see we’re subtracting 2 after the =COLUMN(), this is because we don’t want it to worry about Column A (it has dates) and we don’t want it to worry about Column E (that would create a circular reference), so we’re subtracting 2 columns. So, this formula reads as follows, sum up the range starting at cell B3 using a width of 3.
Back to Top

Other Uses of the Offset Function Found on this Site

We’ve used the offset function in some of the templates uploaded on this website. If you would like to download the excel templates and see some more real world uses of this fantastic function, please click on the links below, or see the offset function used for Dynamic Printing.

On October 29, 2015   /   How To, Useful Resources