Excel OFFSET Function (Table of Contents)
Introduction to OFFSET in Excel
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. 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.
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 then it offsets to the rows below and if a negative number is used then it offsets the rows above.
- Columns: It is for no. of columns to offset. So, it is the same concept like 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.
- In the OFFSET Function, it 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 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 pic on the left shows that the route of function and the screenshot on the right demonstrates how we can use the OFFSET formula on a real-time basis data. 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 that the OFFSET Function can also be used with the other function of Excel, 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 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, it means there is 1 column as width.
We can refer 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 actual.
- 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, 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 equals to 181, which came as a result in G2 after applying 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 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, it means they always refer to a given or fixed range. But in some case, 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 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 whenever there is a change in the source data, the formula will be recalculated for the entire set, which keeps Excel busy for a longer time. If there is a small spreadsheet then it will not affect that much but if there are a number of spreadsheets then Excel may take time to recalculate.
- Difficulty in review:
As we know the references returned by 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 downloadable excel template. You can also go through our other suggested articles –
