In this post let’s know about the OFFSET Function in excel. Also, the Syntax, how to use along with limitations and alternatives to OFFSET function.
What is “OFFSET” Function in Excel
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.
Syntax to write OFFSET Function
=OFFSET(reference, rows, columns, [height], [width])
Let us breakdown the syntax and try to understand how Offset function work.
Understanding Offset Syntax
OFFSET function has 5 arguments out of which the one’s in square brackets are optional.
- Reference: is the Starting point. This is the cell or range which you want to offset.
- Rows: Number of rows to movefrom the starting point.This can be either positive or negative.
- Columns: Number of columns to movefrom 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.
Points to Remember while using Offset
- Height and Width should be positive Numbers only.
- Rows and Columns can have either positive or negative numbers. Positive Number takes you to Next row or column and negative number takes you to previous row or column.
Using OFFSET function in excel – Example 1
Let’s see how to use OFFSET function in excel with an example.
- Enter Random Data from Rows 1 to 10 and A To F Columns. In Short, fill in data from Cell A1 to F10. Not Sure what a Cell is. (Click Here to know about cell and Other excel Terminology.)
- Type in the below formula in any of the blank cells in excel.
=OFFSET(A1,2,3,2,2)
Above formula returns the values in the range (Know more about Ranges in Excel) from “D3:E4″. Let’s try to understand the formula basing the above syntax
Starting from A1 (Reference), Offset 2 rows (positive values takes it to next row/column whereas negative values take you to the above row/column) which will be 3rd row i.e Cell A3.
From A3, Offset 3 columns i.e columns A, B and C and the cursor will now move 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 selected will be from D3:E4 and the same will become the output.
See the below image to understand various conditions for OFFSET function in excel
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.
You can write the same formula in the above example with cell names as given below.
=OFFSET(A1,B1,C1,D1)
The immediate question that comes to mind is instead of using a range like D3:E4 why to use OFFSET function.
Why to use OFFSET function when I can use the range/ reference directly
Not knowing the exact address of the range. There can 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 there is a requirement to add new rows and columns to the existing data then you need to change the formula every time. Using OFFSET will avoid changing/ updating the formula as and when your data changes.
See the below example to understand how OFFSET function is helpful when the data is dynamic.
Using OFFSET Function in excel – Example 2 – The Problem
Download the sample file to follow along. The sample file have the problem for which we need to get a solution and the details are as below.
The daily productivity of your team is updated in a spreadsheet on a day to day basis. You have been assigned the 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. The data is in columns B (Date) and C (Productivity %). (See the below image)
Let us try this using OFFSET function along with average and countA.
Average: To find out the average
CountA: To count thenumber of blank cells in a given range. Know more about COUNTA function.
The formula to use here is
=AVERAGE(OFFSET(C3,COUNTA(C3:C500)-7,0,7,1))
Let us evaluate the formula step by step.
- 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.
See the below image to understand the formula and the scenario with and without Offset.
Limitations of OFFSET function in excel
Using OFFSET function in excel for more number of times can slow down excel. Reason being, OFFSET function is dynamic in other words it’s volatile. That means, every time a change is made in excel OFFSET formula will update accordingly.
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 formulae. Know More about Excel Tables.
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). Know more about INDEX function in excel.
How Frequently do you use OFFSET function in excel?
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.