How to use the OFFSET function

Author: Oscar Cronquist Article last updated on January 20, 2023

The OFFSET function returns a reference to a range that is a given number of rows and columns from a given reference.

The OFFSET function is volatile and extensive use in a workbook may slow down calculations noticeably. In most cases I prefer using the INDEX function.

OFFSET Function Syntax
OFFSET Function Arguments
OFFSET function example
ROW function and OFFSET function
OFFSET function returns a reference to a cell range. Explaining row and height arguments.
SUBTOTAL and OFFSET functions
Named ranges and the OFFSET function
Create a cell reference to a cell range
Get tutorial file

1. OFFSET Function Syntax

OFFSET(reference,rows,columns,[height],[width])

2. OFFSET Function Arguments

reference	Required. A cell reference from which you want to start.
rows	Required. The number of rows you want to move from the start cell. The number can be positive or negative.
columns	Required. The number of columns you want to move from the start cell. This argument can also be a positive or negative number.
[height]	Optional. The number of rows you want to include to the returned reference. Must be a positive number.
[width]	Optional. The number of columns you want to include to the returned reference. Must be a positive number.

If [height] and [width] arguments are not used OFFSET function returns a cell reference with the same number of rows and columns as argument reference.

The OFFSET function returns #REF error if the returning cell reference is outside the cell grid.

Examples

In this post, I am going to provide some basic examples to demonstrate how the OFFSET function works. I also show practical examples where I use the OFFSET function.

3. OFFSET Function example

This example uses only the two first arguments in the OFFSET function, row, and column.

B3 is the start cell. 1 row below B3 is B4. 1 column to the right of B4 is C4.

The OFFSET function returns a cell reference to cell C4. Cell C4 contains number 22.

4. Example 2 - Row function and offset function

This example shows that the offset function returns a cell reference.

ROW(reference) returns the row number of a reference. Combining the ROW and OFFSET functions demonstrates that the OFFSET function returns a cell reference.

5. Example 3 - Explaining row and height arguments. OFFSET function returns a reference to a cell range

The Offset function can also return a reference to a cell range. For this to work, you need to enter the formula as an array formula.

Array formula in cell range B9:B10:

=OFFSET(B3,1,1,2,1)

The optional arguments height and width are used in this example. The height is 2 and the width is 1, which is 2 cells high and 1 cell wide.

The array formula returns a reference to a cell range with the same width and height as the two last arguments.

5.1 How to enter an array formula

Select cell range B9:B10
Press with left mouse button on in formula bar
Paste array formula to formula bar
Press and hold CTRL + SHIFT
Press Enter
Release all keys.

6. SUBTOTAL and OFFSET functions

This example shows how to determine if a row in an excel defined table is hidden or visible.

Array formula in cell range C7:C9:

=SUBTOTAL(3,OFFSET(B3,MATCH(ROW(Table1[Name]),ROW(Table1[Name]))-1,0,1))

The technique is described here by David Hager and John Walkenbach: Excel Experts E-letter

I have written a couple of posts and developed this technique one step further:

7. Named ranges and the OFFSET function

You can use the OFFSET function to create a dynamic named range. First I want to explain what named ranges is and how you can use them.

So what is a named range? You can select a cell or a range and name it. When you enter the arguments in a function you can use the name instead of the corresponding cell reference. Why? This makes it easier to read and understand a function. Compare this function =Sum(C1:C11) to this =Sum(Sales2011). The latter is easier to understand.

What is dynamic named range? It is a cell range that expands automatically when new values are added.

Named range formula:

=OFFSET(Sheet5!$B$4, 0,0,COUNTA(Sheet5!$B$4:$B$100))

COUNTA counts the number of cells in a range that are not empty. COUNTA(Sheet5!$B$4:$B$100) returns 3.

OFFSET(Sheet5!$B$4, 0,0,COUNTA(Sheet5!$B$4:$B$100))

becomes

=OFFSET(Sheet5!$B$4, 0,0,3)

and returns a reference to cell range B4:B6.

Read more about named ranges:
Create a dynamic named range in excel

8. Create a cell reference to a cell range

OFFSET function create cell ref to cell range

The image above shows a formula that uses two cell references to create another cell ref to a cell range.

The first cell ref is A3, the second cell reference is created by the OFFSET function, and the colon creates a new cell reference pointing to a cell range.

Array formula in cell B3:

=ROW(A3:OFFSET(A3,5,0))

Explaining formula in cell B3

Step 1 - Create cell ref

OFFSET(A3,5,0)

returns A8.

Step 2 - Concatenate cell refs

A3:OFFSET(A3,5,0)

returns

A3:A8.

Step 3 - Calculate row numbers based on cell ref

The ROW function returns a number representing the row for a given cell ref, in this case, the cell ref is pointing to a cell range and the formula returns an array of row numbers from 3 to 8.

ROW(A3:OFFSET(A3,5,0))

Get the Excel file

Offset-function-explained.xlsx

'OFFSET' function examples

Apply drop-down lists dynamically
This article demonstrates how to automatically create drop-down lists if adjacent data grows, there are two methods explained here. The […]

Basic invoice template
Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]

Calculate machine utilization
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]

More examples

Functions in this article

OFFSET SUBTOTAL MATCH ROW COUNTA

Functions in 'Lookup and reference' category

The OFFSET function function is one of many functions in the 'Lookup and reference' category.

ADDRESS function

Returns the address of a specific cell, you need to provide a row and column number.

AREAS function

Returns the number of cell ranges and single cells in a reference.

CHOOSE function

Gets a value based on a number.

COLUMN function

Returns the column number of the top-left cell of a cell reference.

COLUMNS function

Calculates the number of columns in a cell range.

FILTER function

Extracts values/rows based on a condition or criteria.

FORMULATEXT function

Returns a formula as a text string.

HLOOKUP function

Searches the top row in a data range for a value and return another value on the same column in a row you specify.

HYPERLINK function

Builds a link in a cell.

INDEX function

Returns a value or reference from a cell range or array, you specify which value based on a row and column number.

INDIRECT function

Returns the cell reference based on a text string and shows the content of that cell reference.

LOOKUP function

Find a value in a cell range and return a corresponding value on the same row.

MATCH function

Returns the relative position of an item in an array that matches a specified value in a specific order.

OFFSET function

Returns a reference to a range that is a given number of rows and columns from a given reference.

ROW function

Calculates the row number of a cell reference.

ROWS function

Calculate the number of rows in a cell range.

SORT function

Sorts values from a cell range or array

SORTBY function

Sorts a cell range or array based on values in a corresponding range or array.

STOCKHISTORY function

Downloads stock prices based on a stock quote

TRANSPOSE function

Converts a vertical range to a horizontal range, or vice versa.

UNIQUE function

Returns a unique or unique distinct list.

VLOOKUP function

Lets you search the leftmost column for a value and return another value on the same row in a column you specify.

XLOOKUP function

Search one column for a given value, and return a corresponding value in another column from the same row.

XMATCH function

Searches for an item in an array or cell range and returns the relative position.

Excel function categories

Array manipulation

Excel functions that let you resize, combine, and shape arrays.

Compatibility

Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.

Database

Perform basic operations to a database-like structure.

Date and Time

Functions that let you perform calculations to Excel date and time values.

Engineering

Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.

Financial

Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.

Information

Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.

Logical

Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.

Lookup and reference

These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.

Math and trigonometry

You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.

Statistical

Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.

Text

Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.

Web

Get data from the internet, extract data from an XML string and more.

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

One Response to “How to use the OFFSET function”

INDIRECT function | Get Digital Help - Microsoft Excel resource says:

December 16, 2014 at 1:48 pm

[…] Offset function […]

Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form