Offset Formula in excel is a powerful worksheet function in excel which is used to display the range of the parts from the start point of the cell, this function has a total of five arguments and all the arguments are mandatory, the method to use this function is as follows, =offset( reference, rows, columns, height, width), height and width refer to the referred reference.
OFFSET Formula in Excel – Table of Contents
What is OFFSET Formula in Excel?
What if you want value from specific cell to go through “x” number of cells, “Y” number of column and get the value from the required cell to the reference cell. Looks tedious, isn’t it???
Yes, tedious it is to do. But in this article, we will take you through the OFFSET formula which will perform this task. Offset function will return a value of the cell based on the rows & columns to the specific cell.
I know it is difficult to understand, but don’t worry about the meaning at his point of time because will show you a clear practical example of OFFSET formula in excel.
The Syntax of OFFSET Formula in Excel
Look at the syntax of OFFSET formula in excel.
- Reference: This is nothing but from which cell you want to offset.
- Rows: From the above reference cell how many rows you want to go up or go down. If you want to go down mention the number as positive and if you want to go up mention the number as negative.
- Column: From the above reference cell how many columns you want to go right or go left. If you want to go right mention the number as positive and if you want to go left mention the number as negative.
- [Height]: This is optional. After moving cells (rows & column) how many rows you want to offset. This should be a positive number.
- [Width]: This is optional. After moving cells (rows & column) how many columns you want to offset. This should be a positive number.
How to Use OFFSET Formula in Excel? (with Examples)
Below are the examples of OFFSET Formula in excel.
OFFSET Formula Excel Example #1
Take a look at the simple example of the OFFSET formula. Consider the below data for the example.
From the above data, from cell A1 we want to go down 3 rows and go right 1 column and we need the value of that cell.
Step 1: Open OFFSET formula.
Step 2: Select the cell A1 as the reference.
Step 3: How many rows we need to move down i.e. 3 rows.
Step 4: Now how many columns we need to move right i.e. 1 column.
Step 5: Since we need only one cell to be offset leave the last two arguments.
Ok, we got the value from B4 cell. Using the formula OFFSET (A1, 3, 1)
From A1 cell moves down 3 rows i.e. it will reach A4 cell, from A4 cell move one column to the right i.e. B4 cell. From this cell, OFFSET returned the value.
OFFSET Formula Excel Example 2
Take the same data from the above example. But we will see how to offset more than one row.
From the above list, we need to move from cell A1 to 3 rows down, one column right but from here we need the total sum of two rows.
Step 1: Open OFFSET and apply formula same as from the above example.
Step 2: Height is nothing but how many rows we need to offset i.e. 2 rows.
Step 3: Close bracket and hit enter.
Step 4: Oh we got an error, the reason behind this is when we offset more than one row as height we need to apply some other formula in front of OFFSET. In this example, we need the SUM value so apply SUM formula before OFFSET function.
Step 5 Hit enter key to get the result.
So SUM formula added B4 & B5 cell values which are offset by the OFFSET formula.
OFFSET Formula Excel Example #3
Now we will see how to offset more than one column i.e. width of the range. Consider the below data for an example.
From this data, we need a total of month 4 i.e. April month’s total of Salary & Incentive.
Step 1: Since we need to add the number first open SUM formula.
Step 2: Inside the SUM open OFFSET formula.
Step 3: Select the reference as A1 cell.
Step 4: We need to move down 4 rows to reach April month, instead of entering the 4 exclusively give cell reference to A2 cell.
Step 5: We need to move 2 columns to the right.
Step 6: Since don’t want to offset more than one row, leave height argument blank.
Step 7: For width enter 2 because we need to add Salary & incentive together.
Step 8: Close the bracket and hit the enter key. We will get a total amount for April month.
So the total of Salary and Incentive for April is 62305.
Now Change the month number to 6 to get the total for June.
Now we got June total i.e. 62,497.
Things to Remember
- We will get a #REF error if the range is not valid.
- If you want to move down of rows then the number should be positive.
- If you want to move up of rows then the number should be negative.
- If you want to move right of columns then the number should be positive.
- If you want to move left of the column then a number should be negative.
This has been a guide to OFFSET Formula in Excel. Here we discuss how to use OFFSET Formula in Excel to offset more than one column with examples and downloadable excel template. You may learn more about excel from the following articles –