Excel OFFSET Function Examples

Use the OFFSET function to refer to a range, based on a starting cell. For example, get the total sales for April-June (start in column 4, include 3 columns).

OFFSET Introduction

The OFFSET function can return a reference to a range. Enter the starting point, number of rows and columns to move from starting point, and how big a range to include (number of rows and columns)

In this video, you’ll see four examples of how to use the OFFSET function. The written instructions are below the video.

OFFSET Warning

The OFFSET function is volatile, so it could slow down a workbook if used in too many cells.

Instead, you could use a non-volatile function, like INDEX, to return a reference.

OFFSET Function Syntax

The OFFSET function has five arguments: OFFSET(reference,rows,cols,height,width)

1. reference: a cell or range of adjacent cells.
2. rows: Rows to move from starting point; can be positive (below starting reference) or negative (above starting reference).
3. cols: Columns to move from starting point; can be positive (right of starting reference) or negative (left of starting reference)
4. height: If entered, number must be positive, and is number of rows in returned reference
5. width: If entered, number must be positive, and is number of columns in returned reference.

Sales for Selected Month

With the OFFSET function, you can return a reference to a range, based on a starting reference. In this example, we want the sales amount in cell G2:

• starting reference is cell C1
• number of rows to offset is entered in cell F2
• sales amount is in column C, so the number of columns to offset is zero
• height is 1 row
• width is 1 column

=OFFSET(C1,F2,0,1,1)

In cell H2, there is a similar OFFSET formula, to return the month name. The only difference is the column offset — 1 instead of zero.

=OFFSET(C1,F2, 1 ,1,1)

Note: The height and width arguments could be omitted, because we want a reference that is the same size as the starting reference. I used them in this example, to show how all the arguments work.

Sum a Specific Month

In this example, the OFFSET function returns a reference to the sales amounts for the selected month, and the SUM function returns the total for that range. In cell B10, the selected month number is 3, so the result is the total of the March sales.

• starting reference is A3:A6
• rows to offset is zero (you could omit the zero, for the same result)
• columns to offset is entered in cell B10
• height and width are omitted, because the reference should be the same size as the starting reference

=SUM(OFFSET(A3:A6,0,B10))

Create a Range Based on Count

You can also use the OFFSET function to create a dynamic range. In this example, I’ve created a name, MonthsList, with this formula:

=OFFSET(‘Ex03’!$C$1,0,0,COUNTA(‘Ex03’!$C:$C),1)

If another month is added to the list in column C, it will automatically appear in the data validation drop down list in cell F2, which uses MonthsList as its source.

Sum Specific Number of Months

In this final example, OFFSET is combined with SUM and COUNT, to show the total for the last n months. As new quantities are added, the formula result will automatically adjust to include the latest months. In cell E2, the number of months is 2, so the August and September amounts are summed.

• starting reference is cell C2
• number of rows to offset is calculated by counting numbers in column C, subtracting number in cell E3, adding 1
• quantity is in column C, so the number of columns to offset is zero
• height is entered in cell E3
• width is 1 column

=SUM(OFFSET(C2,COUNT(C:C)-E3+1,0,E3,1))

Get the Sample File

Excel Function Tutorials

Don’t Miss Our Excel Tips

Don’t miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.

Excel SUM and OFFSET Formula

If your Excel worksheet includes calculations that are based on a changing range of cells, use the SUM and OFFSET functions together in a SUM OFFSET formula to simplify the task of keeping the calculations up to date.

Instructions in this article apply to Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, and Excel 2010.

Create a Dynamic Range With the SUM and OFFSET Functions

If you use calculations for a period of time that continually changes — such as determining sales for the month — use the OFFSET function in Excel to set up a dynamic range that changes as each day's sales figures are added.

By itself, the SUM function can usually accommodate the insertion of new cells of data into the range being summed. One exception occurs when the data is inserted into the cell where the function is currently located.

In the example below, the new sales figures for each day are added at the bottom of the list, forcing the total to continually shift down one cell each time as the new data is added.

To follow along with this tutorial, open a blank Excel worksheet and enter the sample data. Your worksheet doesn't need to be formatted like the example, but be sure to enter the data in the same cells.

If only the SUM function is used to total the data, the range of cells used as the function argument would need to be modified each time new data is added.

By using the SUM and OFFSET functions together, the range that is totaled becomes dynamic and changes to accommodate new cells of data. The addition of new cells of data does not cause problems because the range continues to adjust as each new cell is added.

Syntax and Arguments

In this formula, the SUM function is used to total the range of data supplied as the argument. The start point for this range is static and is identified as the cell reference to the first number to be totaled by the formula.

The OFFSET function is nested inside the SUM function and creates a dynamic endpoint to the range of data totaled by the formula. This is accomplished by setting the endpoint of the range to one cell above the location of the formula.

The formula syntax is:

The arguments are:

• Range Start: The starting point for the range of cells that will be totaled by the SUM function. In this example, the starting point is cell B2.
• Reference: The required cell reference used to calculate the range endpoint. In the example, the Reference argument is the cell reference for the formula because the range ends one cell above the formula.
• Rows: The number of rows above or below the Reference argument used in calculating the offset is required. This value can be positive, negative, or set to zero. If the offset location is above the Reference argument, the value is negative. If the offset is below, the Rows argument is positive. If the offset is located in the same row, the argument is zero. In this example, the offset begins one row above the Reference argument, so the value for the argument is negative one (-1).
• Cols: The number of columns to the left or right of the Reference argument used to calculate the offset. This value can be positive, negative, or set to zero. If the offset location is to the left of the Reference argument, this value is negative. If the offset is to right, the Cols argument is positive. In this example, the data being totaled is in the same column as the formula, so the value for this argument is zero.

Use the SUM OFFSET Formula to Total Sales Data

This example uses a SUM OFFSET formula to return the total for the daily sales figures listed in column B of the worksheet. Initially, the formula was entered into cell B6 and totaled the sales data for four days.

The next step is to move the SUM OFFSET formula down one row to make room for the fifth day’s sales total. This is accomplished by inserting a new row 6, which moves the formula to row 7.

As a result of the move, Excel automatically updates the Reference argument to cell B7 and adds cell B6 to the range summed by the formula.

Select cell B6, which is the location where the formula results will initially display.

Select the Formulas tab of the ribbon.

Choose Math & Trig.

Select SUM.

In the Function Arguments dialog box, place the cursor in the Number1 text box.

In the worksheet, select cell B2 to enter this cell reference in the dialog box. This location is the static endpoint for the formula.

In the Function Arguments dialog box, place the cursor in the Number2 text box.

Enter OFFSET(B6,-1,0). This OFFSET function forms the dynamic endpoint for the formula.

Select OK to complete the function and close the dialog box. The total appears in cell B6.

Add the Next Day's Sales Data

To add the next day's sales data:

Right-click the row header for row 6.

Select Insert to insert a new row into the worksheet. The SUM OFFSET formula moves down one row to cell B7 and row 6 is now empty.

Select cell A6 and enter the number 5 to indicate that the sales total for the fifth day is being entered.

Select cell B6, enter $1458.25, then press Enter.

Cell B7 updates to the new total of $7137.40.

When you select cell B7, the updated formula appears in the formula bar.

The OFFSET function has two optional arguments: Height and Width, which were not used in this example. These arguments tell the OFFSET function the shape of the output in terms of the number of rows and columns.

By omitting these arguments, the function uses the height and width of the Reference argument instead, which, in this example is one row high and one column wide.

OFFSET Excel Function

By Madhuri Thakur

Excel OFFSET Function (Table of Contents)

Introduction to OFFSET in Excel

The offset function helps to get the value stored in a selected row or column or array of a row-column matrix by calling out the row and column number with reference to cell value. The offset function starts counting the row and column number once we fix the reference cell and that cells will become its first point to start counting.

Excel functions, formula, charts, formatting creating excel dashboard & others

Below is the OFFSET Formula in Excel:

OFFSET Function in Excel consist of the following arguments:

• Reference: It is the argument from which we want to base the offset. It could be a cell reference or a range of adjacent cells; if not, Offset returns the #VALUE! Error value.
• Rows: It is the no. of rows to offset. If we use a positive number, it offsets to the rows below, and if a negative number is used, it offsets the rows above.
• Columns: It is for no. of columns to offset. So, it is the same concept as Rows; if we use a positive number, then it offsets to the columns to the right, and if a negative number is used, then its offsets to the columns on the left.
• Height: This argument is optional. It should be a positive number. It is a number that represents the number of rows in the returned reference.
• Width: This is also an optional argument. It must be a positive number. It is a number that represents the number of columns in the returned reference.

Notes:

• The OFFSET Function in Excel returns the #REF! error value if rows and columns offset reference over the edge of the worksheet.
• The OFFSET function is supposed to be the same height or width as a reference if height or width is omitted.
• OFFSET returns a reference; it does not actually move any cells or range of cells or change the selection. It can be used with any function expecting a reference argument.

How to Use the OFFSET Function in Excel?

OFFSET Function in Excel is very simple and easy to use. Let understand the working of the OFFSET Function in Excel by some OFFSET Formula example.

Example #1

The Offset formula returns a cell reference based on a starting point, rows, and columns which we specify. We can see it in the given below example:

=OFFSET (A1, 3, 1)

The formula tells Excel to consider cell A1 for starting point (reference), then move 3 rows down (rows) and 1 column to the left (columns argument). After this, the OFFSET formula returns the value in cell B4.

The picture on the left shows that the route of function and the screenshot on the right demonstrate how we can use the OFFSET formula on a real-time basis. The difference between the two formulas is that the second (on the right) includes a cell reference (D1) in the row’s argument. But since cell D1 contains number 1, and exactly the same number appears in the rows argument of the first formula, both would return an identical result – the value in B2.

Example #2

As we know, the OFFSET Function can also be used with the other Excel function; hence, in this example, we will see with the SUM Function.

In the above pic, suppose we have to find out the sum of marks, then we can also use the OFFSET function.

The formula is below:

So, in the above formula, D5 is the first cell where the data begins, which is a reference as starting cell. After that, the rows and column value is 0, so we can put it as 0,0. Then there is 4, which means 4 rows below the reference for which Excel needs to calculate the sum and then, at last, it is 1, which means there is 1 column as width.

We can refer to the above pic to co-relate the explained example.

And below is the result:

Just we have to press Enter after entering a formula in the required cell, and then we have the result.

Things to Remember

• The OFFSET Function in Excel just returns a reference; it does not move any cells or range of cells in actuality.
• If the OFFSET Formula returns a range of cells, the rows and columns always refer to the upper-left cell in the returned range.
• The reference should include a cell or range of cells; otherwise formula will return the #VALUE error.
• If the rows and columns specified over the edge of the spreadsheet, the Offset formula in Excel will return the #REF! error.
• OFFSET function can be used within any other Excel function which accepts a cell or range reference in its arguments.

For example, if we are using the formula = OFFSET (A1, 3, 1, 1, 3) on its own, it will give a #VALUE! Error, since a range of return (1 row, 3 columns) does not fit in a cell. However, if we collate it into the SUM function like below:

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

The Offset formula returns the sum of values in a 1-row by a 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.

As we can see, the total of 36+63+82 of highlighted Mar is equaled to 181, which came as a result in G2 after applying the sum and Offset formula. (highlighted in yellow)

Use of OFFSET Function in Excel

As we have seen that what the OFFSET function actually does with the help of the above example, we may have questions that Why to bother sung OFFSET formula that is a bit complex, why not simply use a direct reference like sum or B4:D4?

The OFFSET function in Excel is very good for below:

• Getting the range from the starting cell: Sometimes, we do not know the exact or actual address of the range; we only know from where it starts from a certain cell. In this case, we can use the OFFSET Function, which is easy to go.

• Creating Dynamic Range: The references like the above example B1:C4 are static, which means they always refer to a given or fixed range. But in some cases, the tasks are easier to perform with dynamic ranges. It especially helps when we work with changing data. For example, we could have a spreadsheet where a new row or column is inserted or added every day/week/month; in this case, the OFFSET function will help.

Limitations and Alternatives of the OFFSET Function in Excel

As each and every formula in Excel has its own limitations and alternatives, we have seen how and when to use the OFFSET Function in Excel.

Below are the critical limitations of the OFFSET function in Excel:

• Resource-hungry Function: It means that the formula will be recalculated for the entire set whenever there is a change in the source data, which keeps Excel busy for a longer time. If there is a small spreadsheet, it will not affect that much, but if there are many spreadsheets, then Excel may take time to recalculate.

• Difficulty in review: As we know, the references returned by the Offset function are dynamic or changing and may contain a big formula, which may find tricky to correct or edit as per requirement.

Alternatives of the OFFSET Function in Excel:

• INDEX function: The INDEX function in Excel may also be used to create a dynamic range of references. It is not exactly the same as OFFSET. But like OFFSET, the INDEX function is not that much volatile; hence it won’t slow down the Excel, which is a limitation for OFFSET.

• INDIRECT Function: We can also use the INDIRECT function to create a dynamic range of references from many sources like cell values, text and the named ranges.

Recommended Articles

This has been a guide to OFFSET in Excel. Here we discuss the OFFSET Formula in Excel and How to use the OFFSET Function in Excel, along with practical examples and a downloadable excel template. You can also go through our other suggested articles –