The OFFSET function is a kind of lookup function in Excel that allows us to find a value in a table based on a particular reference point. This is different from Offset function used in VBA. In VBA, we can only refer a single cell from another cell but when used as Excel formula, it becomes one of the most important to learn. It is used in conjunction with named ranges, charts(to make them dynamic), Sum formula, SUMIF formula, Pivot Tables (to make source range dynamic), VLookup Formula, Array formulas etc.
My next 4-5 posts will concentrate on full use of OFFSET function and I promise you to make expert in all tricks related to OFFSET Function.
You tell the OFFSET function how far to move from the initial reference point (in terms of columns and rows) in order to find the required value. OFFSET function depending on the inputs may return a single cell or a bunch of cells (Range array). We talked a lot about OFFSET function, now let's have a look on it. i.e. How it works? What are its input and output options? etc etc.
Syntax of the OFFSET Function:
* initial reference – The cell or group of cells that you would like to use as the initial reference for the OFFSET function. However, you can choose any reference cell you like but generally corner cells are taken in real world examples.
* # of rows – This is a numeric value(strictly integer) that represents the number of rows you want to move to find your OFFSET function value. If you are moving down one or more rows, this value should be positive. If moving up, then the value will be negative.
* # of cols – Exactly, like the rows argument, this is a numeric value that represents the number of columns you want to move to find the function result. If moving to the right, this value should be positive. Negative values represent moving to the left.
* height(optional) – This value is optional and denotes the number of rows you want included in the OFFSET function result. 1 by default, if skipped.
* width(optional) – This value is optional as well and denotes the number of columns you want to include in the OFFSET function result. 1 by default, if skipped.
If any one of the height and width is more than 1, then Offset function must be used in conjunction with named range (to make source data of charts, pivots etc. dynamic) or array formulas because then it will not return a single cell but a bunch of cells.
Some Basic Examples you can use:-
Type - I:
Initial Reference (Single Cell)
Return Value (Single Cell)
1. OFFSET(E5,1,3) - It says go one row down(Row 6) and 3 column right(Column H).
2. OFFSET(E5,1,-3) - It says go one row down(Row 6) and 3 column left(Column B).
3. OFFSET(E5,-1,3) - It says go one row up(Row 4) and 3 column right(Column H).
4. OFFSET(E5,-1,-3) - It says go one row up(Row 4) and 3 column left(Column B).
It will return Error #REF!, if you try to use it like this:-
5. OFFSET(E5,-5,3) - It says go five row up (Row 0, which don't exists) and 3 column right(Column H).
6. OFFSET(E5,1,-5) - It says go one row down (Row 6) and 5 column left(before Column A, which don't exist).
So, keep this in mind when using the offset function, we can use it in your logic or take care of not using it.
Type - II:
Initial Reference (Multiple Cells)
Return Value (Single Cell)
7. OFFSET(E5:E15,-1,3) - It says go one row up (Row 4 to Row14) and 3 column right (Column H). It says H4 to H14 but as single cells needs to be return - It will return value of H4. As mentioned before, if height and width omitted, they're taken as 1. So it's equivalent to OFFSET(E5:E15,-1,3,1,1)
Type - III: (Using Offset as Array Formulas / Using Offset for Dynamic ranges by Named Range)
Initial Reference (Multiple Cells)
Return Value (Multiple Cells)
Whenever a formula return multiple cells, it becomes mandatory to use array formulas else the formula will return #Value! error. The example given here cannot be used in excel worksheet without array. These examples set base for our coming posts on using OFFSET in conjunction arrays and named ranges. Excel users must understand here what offset function returns with respective inputs rather than trying them in worksheet unlike before.
8. OFFSET(E5:E15,0,2,11,1) will return G5:G15 as it says go nowhere row wise and 2 column right (Column G) then take 11 rows starting G5 and 1 column which means G5:G15.
9. OFFSET(E5:E15,0,-2,11,1) will return C5:C15 as it says go nowhere row wise and 2 column left (Column C) then take 11 rows starting C5 and 1 column which means C5:C15.
10. OFFSET(E5:E15,-2,-2,11,1) will return C3:C13 as it says go 2 rows up and 2 column left (Column C) then take 11 rows starting C3 and 1 column which means C3:C13.
11. OFFSET(E5:E15,-2,-2,5,1) will return C3:C7 as it says go 2 rows up and 2 column left (Column C) then take 5 rows starting C3 and 1 column which means C3:C7.
12. OFFSET(E5:E15,2,3,4,5) will return H7:L10 as it says go 2 rows down(7) and 3 columns right (Column H) then take 4 rows and 5 columns starting H7 which means H7:L10.
Now we'll see same examples, if height and width skipped for them.
13. OFFSET(E5:G15,0,5) will return J5:L15 as it says go nowhere row-wise and 5 columns right (i.e.Column J to Column L) which means J5 to L15. As height and width skipped, they will be calculated from initial reference which says 11 rows as height and 5 columns as width.
14. OFFSET(E5:G15,-2,-2) will return C3:E13 as it says go 2 rows up(So instead of 5 to 15, it will be 3 to 13) and 2 column left (i.e.Column C to Column E) which means C3:E13. As height and width skipped, they will be calculated from initial reference which says 11 rows as height and 3 columns as width.
So, in case of initial reference with multiple cells, height and width (if skipped) don't necessarily be 1, by default.
Type - IV: (Using Offset for Dynamic ranges by Named Range / Using Offset as Array Formulas)
Initial Reference (Single Cell)
Return Value (Multiple Cells)
It works same as Type III and no explanation required here except the fact that it becomes mandatory here to give either height or width or both in order to return multiple cells.
15. OFFSET(E5,0,2,11,1) will return G5:G15 as it says go nowhere row wise and 2 column right (Column G) then take 11 rows starting G5 and 1 column which means G5:G15
16. OFFSET(E5,0,-2,11,1) will return C5:C15 as it says go nowhere row wise and 2 column left (Column C) then take 11 rows starting C5 and 1 column which means C5:C15.
17. OFFSET(E5,-2,-2,11,1) will return C3:C13 as it says go 2 rows up and 2 column left (Column C) then take 11 rows starting C3 and 1 column which means C3:C13.
18. OFFSET(E5,-2,-2,5,1) will return C3:C7 as it says go 2 rows up and 2 column left (Column C) then take 5 rows starting C3 and 1 column which means C3:C7.
19. OFFSET(E5,2,3,4,5) will return H7:L10 as it says go 2 rows down(7) and 3 columns right (Column H) then take 4 rows and 5 columns starting H7 which means H7:L10.
With these examples, I finish this post here. Stay tuned to eXceLiTems as we'll move to create dynamic charts and pivots with the help of OFFSET Function in coming posts. We'll use it in array, sum function, subtotal function and much more ....
My next 4-5 posts will concentrate on full use of OFFSET function and I promise you to make expert in all tricks related to OFFSET Function.
You tell the OFFSET function how far to move from the initial reference point (in terms of columns and rows) in order to find the required value. OFFSET function depending on the inputs may return a single cell or a bunch of cells (Range array). We talked a lot about OFFSET function, now let's have a look on it. i.e. How it works? What are its input and output options? etc etc.
Syntax of the OFFSET Function:
OFFSET(initial reference, # of rows(to move), # of cols(to move), height, width)
* initial reference – The cell or group of cells that you would like to use as the initial reference for the OFFSET function. However, you can choose any reference cell you like but generally corner cells are taken in real world examples.
* # of rows – This is a numeric value(strictly integer) that represents the number of rows you want to move to find your OFFSET function value. If you are moving down one or more rows, this value should be positive. If moving up, then the value will be negative.
* # of cols – Exactly, like the rows argument, this is a numeric value that represents the number of columns you want to move to find the function result. If moving to the right, this value should be positive. Negative values represent moving to the left.
* height(optional) – This value is optional and denotes the number of rows you want included in the OFFSET function result. 1 by default, if skipped.
* width(optional) – This value is optional as well and denotes the number of columns you want to include in the OFFSET function result. 1 by default, if skipped.
If any one of the height and width is more than 1, then Offset function must be used in conjunction with named range (to make source data of charts, pivots etc. dynamic) or array formulas because then it will not return a single cell but a bunch of cells.
Some Basic Examples you can use:-
Type - I:
Initial Reference (Single Cell)
Return Value (Single Cell)
1. OFFSET(E5,1,3) - It says go one row down(Row 6) and 3 column right(Column H).
2. OFFSET(E5,1,-3) - It says go one row down(Row 6) and 3 column left(Column B).
3. OFFSET(E5,-1,3) - It says go one row up(Row 4) and 3 column right(Column H).
4. OFFSET(E5,-1,-3) - It says go one row up(Row 4) and 3 column left(Column B).
It will return Error #REF!, if you try to use it like this:-
5. OFFSET(E5,-5,3) - It says go five row up (Row 0, which don't exists) and 3 column right(Column H).
6. OFFSET(E5,1,-5) - It says go one row down (Row 6) and 5 column left(before Column A, which don't exist).
So, keep this in mind when using the offset function, we can use it in your logic or take care of not using it.
Type - II:
Initial Reference (Multiple Cells)
Return Value (Single Cell)
7. OFFSET(E5:E15,-1,3) - It says go one row up (Row 4 to Row14) and 3 column right (Column H). It says H4 to H14 but as single cells needs to be return - It will return value of H4. As mentioned before, if height and width omitted, they're taken as 1. So it's equivalent to OFFSET(E5:E15,-1,3,1,1)
Type - III: (Using Offset as Array Formulas / Using Offset for Dynamic ranges by Named Range)
Initial Reference (Multiple Cells)
Return Value (Multiple Cells)
Whenever a formula return multiple cells, it becomes mandatory to use array formulas else the formula will return #Value! error. The example given here cannot be used in excel worksheet without array. These examples set base for our coming posts on using OFFSET in conjunction arrays and named ranges. Excel users must understand here what offset function returns with respective inputs rather than trying them in worksheet unlike before.
8. OFFSET(E5:E15,0,2,11,1) will return G5:G15 as it says go nowhere row wise and 2 column right (Column G) then take 11 rows starting G5 and 1 column which means G5:G15.
9. OFFSET(E5:E15,0,-2,11,1) will return C5:C15 as it says go nowhere row wise and 2 column left (Column C) then take 11 rows starting C5 and 1 column which means C5:C15.
10. OFFSET(E5:E15,-2,-2,11,1) will return C3:C13 as it says go 2 rows up and 2 column left (Column C) then take 11 rows starting C3 and 1 column which means C3:C13.
11. OFFSET(E5:E15,-2,-2,5,1) will return C3:C7 as it says go 2 rows up and 2 column left (Column C) then take 5 rows starting C3 and 1 column which means C3:C7.
12. OFFSET(E5:E15,2,3,4,5) will return H7:L10 as it says go 2 rows down(7) and 3 columns right (Column H) then take 4 rows and 5 columns starting H7 which means H7:L10.
Now we'll see same examples, if height and width skipped for them.
13. OFFSET(E5:G15,0,5) will return J5:L15 as it says go nowhere row-wise and 5 columns right (i.e.Column J to Column L) which means J5 to L15. As height and width skipped, they will be calculated from initial reference which says 11 rows as height and 5 columns as width.
14. OFFSET(E5:G15,-2,-2) will return C3:E13 as it says go 2 rows up(So instead of 5 to 15, it will be 3 to 13) and 2 column left (i.e.Column C to Column E) which means C3:E13. As height and width skipped, they will be calculated from initial reference which says 11 rows as height and 3 columns as width.
So, in case of initial reference with multiple cells, height and width (if skipped) don't necessarily be 1, by default.
Type - IV: (Using Offset for Dynamic ranges by Named Range / Using Offset as Array Formulas)
Initial Reference (Single Cell)
Return Value (Multiple Cells)
It works same as Type III and no explanation required here except the fact that it becomes mandatory here to give either height or width or both in order to return multiple cells.
15. OFFSET(E5,0,2,11,1) will return G5:G15 as it says go nowhere row wise and 2 column right (Column G) then take 11 rows starting G5 and 1 column which means G5:G15
16. OFFSET(E5,0,-2,11,1) will return C5:C15 as it says go nowhere row wise and 2 column left (Column C) then take 11 rows starting C5 and 1 column which means C5:C15.
17. OFFSET(E5,-2,-2,11,1) will return C3:C13 as it says go 2 rows up and 2 column left (Column C) then take 11 rows starting C3 and 1 column which means C3:C13.
18. OFFSET(E5,-2,-2,5,1) will return C3:C7 as it says go 2 rows up and 2 column left (Column C) then take 5 rows starting C3 and 1 column which means C3:C7.
19. OFFSET(E5,2,3,4,5) will return H7:L10 as it says go 2 rows down(7) and 3 columns right (Column H) then take 4 rows and 5 columns starting H7 which means H7:L10.
With these examples, I finish this post here. Stay tuned to eXceLiTems as we'll move to create dynamic charts and pivots with the help of OFFSET Function in coming posts. We'll use it in array, sum function, subtotal function and much more ....
BONUS VBA MACRO: Excel 2003 Style menu in Excel 2007 / 2010
Are you going for an interview ?
3 things to remember before Excel VBA Interview
50 Excel VBA Interview questions
or looking for a job ?
Excel, Access, SQL, VBA, MIS, Reporting and Data Analysts Jobs
3 things to remember before Excel VBA Interview
50 Excel VBA Interview questions
or looking for a job ?
Excel, Access, SQL, VBA, MIS, Reporting and Data Analysts Jobs
Comments
thank you