In this post let’s learn about the syntax and usage of Offset function.
The “OFFSET” Function
OFFSET function returns the value of a cell/ range that is a specified number of rows and columns away from a cell or range of cells that you referenced in an adjacent range.
In simple words it returns the value of a cell/ range from a starting point with specified height and width (in rows and columns) per your requirement.
How to write OFFSET Function
The syntax for offset function is as follows
=OFFSET(reference, rows, columns, [height], [width]) |
Let us breakdown the syntax
Offset function takes 5 arguments
- Reference: is the Starting point. This is the cell or range which you want to offset.
- Rows: Number of rows to move from the starting point. This can be either positive or negative.
- Columns: Number of columns to move from the starting point. This can be either positive or negative.
- Height (optional): in number of rows that you want the returned reference to be.
- Width (optional): in number of columns that you want the returned reference to be.
Please note that Height and Width should be only positive numbers.
Let’s see the OFFSET formula with an example.
=OFFSET(A1,2,3,2,2)
The above formula returns the range D3:E4. Let’s try to understand the formula basing the above syntax.
Starting from A1, Offset 2 rows (positive values takes it to next row/column whereas negative values take you to the above row/column) which will be 3^{rd} row i.e cell A3.
From A3, Offset 3 columns i.e columns A, B and C and the cursor will be moved to cell D3.
Now we have the cursor in D3 and return the range 2 cells tall (D3 and D4) and 2 cells wide (E3 and E4). Therefore the final range will be from D3:E4.
See the below image to understand the different ways of writing the offset function and the return value/ range the function returns.
Is it mandatory to use only numbers in the OFFSET function?
The answer is No. Instead of numbers, cell names can also be used provided the cells have numbers in it. But make sure the references are written correctly as the cell reference value changes while using the auto fill unless it is set to absolute reference. (Know more about references). The formula can also be written as
=OFFSET(A1,B1,C1,D1)
Why to use OFFSET when I can use the range/ reference directly
The question comes to mind immediately is instead of using a range like D3:E4 why to use OFFSET function. Because,
Not knowing the exact address of the range. There might be cases where we just know the starting cell of the data.
Dynamic Data: Any range defined like D3:E4 is static. If your data is dynamic, say new rows and columns are added to the existing data you need to change the formula every time. Using OFFSET will avoid changing formula every time your data is changed. More on this in the below example. (Download Example workbook @ end of the post to understand the use of OFFSET function).
The problem
Assume that the daily productivity of your team is being updated in a spreadsheet on a day to day basis. You have been assigned a task of calculating the average productivity of your team on a weekly basis. A highlighted cell (Of course using a formula) on a sheet should show the average productivity of the latest 7 days. (See the below image) Assume that the data in columns B (Date) and C (Productivity %).
Let’s try this using OFFSET. The formulae to be used are
Average: To find out the average
CountA: To count the number of blank cells in a given range. (Learn More about COUNTA)
See the below image to understand the formula.
=AVERAGE(OFFSET(C3,COUNTA(C3:C500)-7,0,7,1))
Let’s evaluate the formula step by step.
OFFSET
C3 is starting range.
CountA (C3:C500)-7 (For rows): In the range C3:C500 count the number of values and subtracts 7 from it.
0 is columns.
7, 1 is the height and width stating return an area of 7 rows and 1 column.
The output value from offset which is a range will be passed as input to the average formula.
Limitations of OFFSET
As mentioned, OFFSET formula is dynamic in other words it’s volatile. That means, every time a change is made in excel OFFSET formula will be recalculated. Using more OFFSET formulas will slow down excel.
Debugging of OFFSET formulas will be slightly difficult as the ranges are dynamic and changes very frequently.
Alternatives to OFFSET
Excel Tables: Tables serve as a better alternative to offset as they are dynamic in nature and can be used in formulas. Click to know more about tables and their usages.
Index: It is a non-volatile formula and can also be used to generate ranges. Of course, not exactly similar to OFFSET (Why do we need same formula with a different name).
Download the Practice Workbook used in Example
Do you use OFFSET formula?
Please do share the situations/ cases where you use OFFSET formula or the alternatives to OFFSET in the comment box below.
Do share this post with your friends if you find it useful.