Computer Tutoring Logo
Est. 2002
Menu Button

Excel Offset Function

What is the OFFSET function?

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.

The basics of the Offset function

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).

Reference

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.

Rows

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.

Columns

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.

Height

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.

Offset function Height explanation

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.

Width

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:

OFFSET function width

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:

OFFSET function width and height

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

This: =SUM(OFFSET(A2,2,2))

Doesn't work

This: =SUM(OFFSET(A2,0,0,4,2))

Does work - This is because the latter returns a range.

When can I use the OFFSET function?

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..

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.

Data Validation with Blanks in Excel

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.

  1. Type a list of students on a blank sheet. (E.g. Fred | Sarah | Tim | Sally). I typed the values in G1:G4.
  2. Type the word Student in Cell A1 then click in Cell A2.
  3. Click on Data then Data Validation from the data tools section of the Ribbon.
    Data Validation in Data Tools
  4. From the Allow drop down menu choose List.
  5. In the source box enter: =OFFSET($G$1,0,0,COUNTA($G:$G))
    Note that in the above, the COUNTA function is used to determine how many items there are in the list. You may want to minus one from this value should you have a header included at the top.
  6. Now you can see the glory of your list. Now to test out the benefit of this list. Add or remove a student from the list in column G.
  7. Type Tracy to the list. In Cell G5.
  8. Click on the drop down list and you can see that Tracy has been added and what is more there are no blank entries.
    (Try deleting Tracy from the list in the G column and note that there are still no spaces within the drop down list. That's because the OFFSET function can take advantage of the COUNTA function to count how many entries there are in column G.)

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.

Using OFFSET to Transpose Horizontal Data to Vertical

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:

  1. Create the below spreadsheet.
    Offset Transpose horizontal text
  2. Click in Cell H1 and type the following formula:
    =OFFSET($A$1,0,ROW(A1)-ROW($A$1))
    Notice that we use the Row function to take away the first row from the current row.
  3. Now you can Autofill down to row 6 and voila!

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.