How to Use the OFFSET Function in Microsoft Excel

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

The OFFSET function is one of the built-in functions in Microsoft Excel.

Its purpose is to return a range that is a specified number of rows and columns from a reference cell or range.

The range that the OFFSET function returns can be a single cell or a range of multiple adjacent cells.

When returning a range, OFFSET allows you to specify the size by inserting the number of rows and columns.

To explain the usage of OFFSET, we will present some working examples.

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Kasper Langmann, Co-founder of Spreadsheeto

Syntax

The syntax of OFFSET is straightforward. There are a few arguments that you need to understand.

The syntax of the OFFSET function

=OFFSET(reference, rows, cols, [height], [width])

You can see there are five possible arguments, two of them optional.

reference – This required argument is the cell or range of adjacent cells we wish to offset our result from. If not a single cell, this argument must refer to an adjacent range of cells or it will return the #VALUE error.

rows – This required argument tells Excel the number of rows to move up or down from the ‘reference’ argument value. Positive numbers are downward from the reference. Negative numbers are upward from the reference. OFFSET will return the #REF error if this argument is greater than the number of rows available.

cols – This argument, also required, tells Excel the number of rows to move to the left or right from the reference argument value. Positive numbers are to the right while negative numbers are to the left of the reference value. OFFSET will return the #REF error if this argument is greater than the number of columns available.

[height] – This is an optional argument that allows you to specify the size of the returned range in number of rows. If omitted, the formula returns the default size of a single row.

[width] – This is also an optional argument. It allows you to specify the size of the returned range in number of columns. If omitted, the formula returns the default size of a single column.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

cloud-download

BONUS: Download the Offset Function Exercise Workbook File (with 3 pre-loaded exercises) to go along with this post.

Using the OFFSET function

To get the basic idea of what the OFFSET function does, let’s look at a simple table. It has a Number column and a Letter column.

offset-sample-data

Let’s look at a simple example first by selecting B2 as our reference argument and then 1 for our row and 0 for our cols.

This should return the value from cell B3 (1 row down from B2 in the same column).

simple-offset

Now let’s see what happens when the result of our OFFSET formula is a cell that contains no value at all.

offset-empty

Three rows down and two columns to the left of cell C5 is cell A8, which contains no value. So, the formula returns a value of 0.

Since the arguments for rows and cols are number values, you can also use cell references.

offset-cell-ref

In this case, the value in cell A5 is 4, so that is our rows argument. 4 rows down and 0 to the right of B2 is B6.

Subsequently, our OFFSET formula returns the value E.

Now let’s see what happens when we select a rows or cols value that is not within the usable range of our worksheet.

offset-error

We get the #REF! error here because there is no range that exists two rows above or two columns to the left of cell B2.

What about returning a range of cells

So far all we have seen is formulas where we have omitted the height and width arguments.

When you supply these arguments, you can do more with OFFSET.

Let’s say we use OFFSET to select the range of cells in our table containing the numbers.

We could also use an additional function to create a formula to add up all the number values in that range.

offset-range

All we have done here is OFFSET 1 row down from B2 and specified a range height of 5 rows and width of a single column.

Then we wrapped that in a simple SUM formula.

Thus, we get the sum of all the numeric values in that range (15 = 1 + 2 + 3 + 4 + 5).

More on using OFFSET with other functions

Let’s continue using OFFSET with other functions to create some interesting formulas.

Let’s consider the following table with class average test scores within the given letter grade ranges A, B, C, and D.

class-scores

We could use the AVERAGE function to get our results in the Average column on the right.

With a bit of ingenuity, we can use the OFFSET function to arrive at the same results like our earlier SUM example.

offset-formulas

In this set of formulas, we have established B2 as our reference argument.

Note that we change our rows argument on each next row to accommodate that.

The value of the cols argument shifts the OFFSET to the right one column from the Letter Grade column.

The height and width values set the returned range to a size of one row and three columns wide.

This encompasses the range of each class average score per letter grade.

By wrapping this in an AVERAGE formula, we get the average score for all three classes per letter grade.

Advanced use of OFFSET

One particularly common use of the OFFSET function is in creating a dynamic named range in Excel.

A dynamic named range expands or contracts as the user adds or removes data from the existing data.

This is very useful in automating things like the source lists for drop downs and comboboxes.

We insert the COUNTA function as the height or width argument in the OFFSET formula.

The dimensions of the returned range can change based on its result.

If you are unfamiliar with COUNTA, you need to know that it counts the number of cells in a range that contain data.

If we have a list of names in column A, the COUNTA function will count the number of names in that column.

As we add or remove names, the result of our COUNTA formula changes. This allows the resulting range height to expand or contract automatically.

Let’s take a closer look at what that formula would look like.

offset-counta

While this formula is quite complex, the takeaway is this.

COUNTA turbo charges the OFFSET formula to automate an otherwise manual proposition.

This formula allows the user to add and remove names from the list without having to recreate it every time.

name-list

Conclusion

OFFSET may not one of the more common built-in functions you may hear about, but it is useful in the right scenario.

You have seen enough here to get you thinking about ways you might be able to integrate it into your own work.

Its utility becomes clear when used in combination with other functions.

2019-11-07T21:22:09+00:00