If you have been using Excel and been getting used to the different formulas therein, you will have come across the OFFSET function. The trouble is that the Excel Offset function can only be used in conjunction with other functions. Any aggregate function such as SUM or AVERAGE can make good use of the offset function so that you don't have to include empty cells in the formula. Are you creating a drop down list? If so, then you might want to use the Offset function to prevent you seeing those blank entries that appear in a drop down menu that look so unappealing and amateurish. This tutorial will show you what you can do with the Offset function, along with a couple of examples as to where and when you can make good use of it.
When you look at the description of the Offset function on the Microsoft support site you will read something along these lines:
"Returns a reference that is a certain number of rows and columns from a specific cell or range of cells. You can use Offset to return just one cell, or a whole range of cells."
You will also see from the Microsoft explanation is that the OFFSET function takes 4 arguments, (or sets of instructions).
This instruction is required, in that you must enter a value. This value can be either one cell or a group, or range, of cells.
This argument is also mandatory. Here you can enter a positive or negative integer indicating how many rows you want up or down. You use positive numbers to move down, while you would use negative numbers to move up.
For Example should you enter A1 in the reference and 5 in the rows =OFFSET(A1,5.... Then the range would return A1:A5. However, should you enter =OFFSET(A10,-5... the range would include cells from A5:A10.
If you don't want to move up or down then I would suggest that you type a 0 in here to make things clear.
The columns argument again is also mandatory. It works it pretty much the same way as the "Rows" argument but instead of moving up and down it moves left to right. You enter a negative number to move left and a positive number to move right. Although you don't technically have to enter a 0 it's probably a good idea to do so.
The height is an optional argument indicating the number of rows. This must be a positive number. This will give the height of the range from the reference point of the offset. For example: =OFFSET(A1,2,0,2... will point to range A3:A5. Which is two rows down from the reference point then another two down.
So you can see from the above picture the reference is A1. The rows are 2 and the height is 2 which means the SUM function is summing the range from A2:A4. The result should be 64. If you change the formula to =SUM(A1:OFFSET(A1,0,0,3)) the result will be 30. You might want to have a muck around with the height value to see how it workks.
The width argument within the OFFSET function completes the picture. Whereas height moves down, width moves to the right. The way you can add up all the figures within. Have a look at the following:
You can see the Reference Cell A1, along with zero rows and zero columns. However, with the height and the width set to 3 you can see that it selects 3 rows down (height) and 3 columns across (width). For example if you wanted now to add up all the cells in the range wrapped up in a nice SUM function you could do the following:
In fact you could accomplish the result of 118 by just using the OFFSET part of the function. There is no need for the A2 bit. However, it does make it look prettier in the above. So in a nutshell
Does work - This is because the latter returns a range.
So now you've seen how the offset function works, you might be asking how I might use this function? You know! In a practical example? Well, if you've been using Excel for any time, you may have come across some thorny problems with ranges. Now, it's true that on the face of it the OFFSET function is little more than a glamourous range. But it's when you combine the function with something like COUNTA, COUNT you can create a pretty nifty data validation drop down list..
Have you tried creating a drop down list from other cells in a worksheet just to have blanks turn up at the bottom. For example, you might have seen something along the lines of the picture below.
Note that under Sally is one of those dreadful blank entries. This is because when I selected the data range, which I typed in the G column, the drop down list also included all of the blank cells. So now is an ideal opportunity to use OFFSET. Here's how to use OFFSET to create a dynamic drop down list. That is a list you don't consistently have to change the values of.
So now you've seen how you can use the OFFSET function to create a dynamically drop down list. Now your drop down list validation in Excel will have that professional edge to them. If you want to see the OFFSET function in action with a drop-down list, check out our WhatIf analysis with Data Tables online tutorial.
You may have worked out, if you have been using Excel for while, that you can use Copy and Paste Special to transpose the data in cells. That is to change data in rows to data in columns and vice versa. Using the OFFSET formula, in conjunction with the row function, allows you to transpose your data automatically. Try the following:
So there you go that's the OFFSET function. There is of course so much that you can do with it such as using it in named ranges or creating charts that will dynamically update but just get stuck in.