Using OFFSET function in Excel - formula examples

Required arguments:

• Reference - a cell or a range of adjacent cells from which you base the offset. You can think of it as the starting point.
• Rows - The number of rows to move from the starting point, up or down. If rows is a positive number, the formula moves below the starting reference, in case of a negative number it goes above the starting reference.
• Cols - The number of columns you want the formula to move from the starting point. As well as rows, cols can be positive (to the right of the starting reference) or negative (to the left of the starting reference).

Optional arguments:

• Height - the number of rows to return.
• Width - the number of columns to return.

Both the height and width arguments must always be positive numbers. If either is omitted, it defaults to the height or width of reference.

And now, let's illustrate the theory with an example of the simplest OFFSET formula.

Excel OFFSET formula example

Here is an example of a simple OFFSET formula that returns a cell reference based on a starting point, rows and cols that you specify:

=OFFSET(A1,3,1)

The formula tells Excel to take cell A1 as the starting point (reference), then move 3 rows down (rows argument) and 1 column to the right (cols argument). As the result, this OFFSET formula returns the value in cell B4.

The image on the left shows the function's route and the screenshot on the right demonstrates how you can use the OFFSET formula on real-life data. The only difference between the two formulas is that the second one (on the right) includes a cell reference (E1) in the rows argument. But since cell E1 contains number 3, and exactly the same number appears in the rows argument of the first formula, both would return an identical result - the value in B4.

Excel OFFSET formulas - things to remember

• The OFFSET function in Excel doesn't actually move any cells or ranges; it just returns a reference.
• When an OFFSET formula returns a range of cells, the rows and cols arguments always refer to the upper-left cell in the returned range.
• The reference argument must include a cell or range of adjacent cells, otherwise your formula will return the #VALUE! error.
• If the specified rows and/or cols move a reference over the edge of the spreadsheet, your Excel OFFSET formula will return the #REF! error.
• The OFFSET function can be used within any other Excel function that accepts a cell / range reference in its arguments.

For example, if you try to use the formula OFFSET(A1,3,1,1,3) in pre-dynamic array Excel (2019 and lower), it will throw a #VALUE! error since a range to return (1 row, 3 columns) does not fit into a single cell. However, if you embed it into the SUM function, like this:

=SUM(OFFSET(A1,3,1,1,3))

the formula will return the sum of values in a 1-row by 3-column range that is 3 rows below and 1 column to the right of cell A1, i.e. the total of values in cells B4:D4.

In situation when you wish to base the offset from the current cell, you can use the INDIRECT and ADDRESS functions in combination with ROW() and COLUMN() to get the starting reference. For example:

=SUM(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())), 3, 1, 1, 3))

Why do I use OFFSET in Excel?

Now that you know what the OFFSET function does, you may be asking yourself "Why bother using it?" Why not simply write a direct reference like B4:D4?

The Excel OFFSET formula is very good for:

Creating dynamic ranges: References like B1:C4 are static, meaning they always refer to a given range. But some tasks are easier to perform with dynamic ranges. This is particularly the case when you work with changing data, e.g. you have a worksheet where a new row or column is added every week.

Getting the range from the starting cell. Sometimes, you may not know the actual address of the range, though you do know it starts from a certain cell. In such scenarios, using OFFSET in Excel is the right way to go.

Excel OFFSET and SUM functions

The example we discussed a moment ago demonstrates the simplest usage of OFFSET & SUM. Now, let's look at these functions at another angle and see what else they can do.

Using OFFSET function with AVERAGE, MAX, MIN

In the same manner as we calculated the bonuses for the last N months, you can get an average of the last N days, weeks or years as well as find their maximum or minimum values. The only difference between the formulas is the first function's name:

=AVERAGE(OFFSET(B1,COUNT(B:B)-E1+1,0,E1,1))

=MAX(OFFSET(B1,COUNT(B:B)-E1+1,0,E1,1))

=MIN(OFFSET(B1,COUNT(B:B)-E1+1,0,E1,1))

The key benefit of these formulas over the usual AVERAGE(B5:B8) or MAX(B5:B8) is that you won't have to update the formula every time your source table gets updated. No matter how many new rows are added or deleted in your worksheet, the OFFSET formulas will always refer to the specified number of last (lower-most) cells in the column.

Excel OFFSET formula to create a dynamic range

Used in conjunction with COUNTA, the OFFSET function can help you make a dynamic range that may prove useful in many scenarios, for example to create automatically updatable drop-down lists.

The basic OFFSET formula for a dynamic range is as follows:

=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)

Where Sheet1!$A$1 is the topmost cell to be included in the dynamic range. This should be an absolute reference that includes the name of the sheet.

At the heart of this formula, you use the COUNTA function to get the number of non-blank cells in the target column. That number goes to the height argument of OFFSET instructing it how many rows to return.

Apart from that, it's a regular OFFSET formula, where:

• Reference is the starting point from which you base the offset. For example, if your data starts in A1 in Sheet1, the reference is Sheet1!$A$1. If the data starts in column B in row 2, then it'll be Sheet1!$B$2, etc.
• Rows and Cols are both 0 because there are no columns or rows to offset.
• Width is 1 column.

If there is some data above the items (such as column headers), COUNTA will count those non-blank cells, causing OFFSET to add an extra blank(s) at the end of the dynamic range. To avoid this, you can subtract the corresponding number of rows from the COUNTA result. For example, to exclude one header row, set the starting cell as $A$2 and subtract 1:

=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A:$A) -1, 1)

Once you've created a dynamic named range with the above OFFSET formula, you can use Data Validation to make a dynamic dropdown menu that will update automatically as soon as you add or remove items from the source list.

For the detailed step-by-step guidance on creating drop-down lists in Excel, please check out the following tutorials:

• Creating drop-down lists in Excel - static, dynamic, from another workbook
• Making a dependent drop down list

Excel OFFSET & VLOOKUP

As everyone knows, simple vertical and horizontal lookups are performed with the VLOOKUP or HLOOKUP function, respectively. However, these functions have too many limitations and often stumble in more powerful and complex lookup formulas. So, in order to perform more sophisticated lookups in your Excel tables, you have to look for alternatives such as INDEX, MATCH and OFFSET.

Alternatives to using OFFSET in Excel

As is often the case in Excel, the same result can be achieved in a number of different ways. So, here are three elegant alternatives to OFFSET.

1. Excel tables

   Since Excel 2002, we have a truly wonderful feature - fully-fledged Excel tables, as opposed to usual ranges. To make a table from structured data, you simply click Insert > Table on the Home tab or press Ctrl + T.

   By entering a formula in one cell in an Excel table, you can create a so-called "calculated column" that automatically copies the formula to all other cells in that column and adjusts the formula for each row in the table.

   Moreover, any formula that refers to a table's data automatically adjusts to include any new rows you add to the table or exclude the rows you delete. Technically, such formulas operate on table columns or rows, which are dynamic ranges in nature. Each table in a workbook has a unique name (the default ones are Table1, Table2, etc.) and you are free to rename your table via the Design tab > Properties group > Table Name text box.

   The following screenshot demonstrates the SUM formula that refers to the Bonus column of Table3. Please pay attention that the formula includes the table's column name rather than a range of cells.
   

2. Excel INDEX function

   Although not exactly in the same way as OFFSET, Excel INDEX can also be used to create dynamic range references. Unlike OFFSET, the INDEX function is not volatile, so it won't slow down your Excel.

3. Excel INDIRECT function

   Using the INDIRECT function you can create dynamic range references from many sources such as cell values, cell values and text, named ranges. It can also dynamically refer to another Excel sheet or workbook. You can find all these formula examples in our Excel INDIRECT function tutorial.