Excel Offset Function: Showing Off In Excel

I never expected it to be possible to show off in Excel, but some functions seem as if they were made specifically for that reason. The pertinent example here being the Offset Function. What the Offset Function does is relatively simple, but the process and syntax is comparatively complex for such a straight-forward idea.

In essence, the Offset Function is used to reference the location of a specific cell or cell range. It’s that simple. And yet, the syntax is composed of five arguments. This isn’t to say the function isn’t useful (sometimes it’s just what you need), just that it seems designed for aspiring tech billionaires. The good news is, I can help you join this exclusive club with relative ease. Further increase your stock value with this advanced Excel training course.

Offset Function Defined

The Offset Function returns the value of a cell or range of cells that is x number of rows and y number of columns away from an original reference point; usually this is simply a cell or range of cells that is in some way related or adjacent to what you are referencing.

When used by itself, there is only so much you can do; but this is true of the majority of Excel functions. They work miracles in tandem. The key to using the Offset Function with other functions is understanding that they must accept cell references as arguments, SUM being the most common example (you can learn the SUMIF function with this tutorial, which is another natural partner to the Offset function). The hard part is learning the Offset Function; inserting OFFSET into other functions is as easy as typing the cell reference that contains it. You can gain some basic training through this intermediate course for Excel 2013.

Syntax

Following is the Offset Function syntax:

OFFSET (reference, rows, cols, [height], [width])

Now let’s take a quick look at the individual arguments. Think of these arguments as directions; you will be essentially instructing the function as if you were giving a car driver directions:

• Reference: This is the starting point of your Offset Function. It can be either a cell or range of cells (keep in mind that a range must consist of adjacent cells). If you try to use two separate cells or ranges, you will return the dreaded #VALUE! error.
• Rows: This is direction number one. The “rows” argument specifies how many rows you want to move from the reference. You can move up or down as you please. If, for example, you use 10 for your row argument, this means you are referencing a cell 10 rows below  the reference (if you want to reference a cell above the reference point, you would use a negative number)
• Cols: This works just like the row argument, but for columns. Again, you can move left or right as needed. A positive argument moves to the right of your reference point, while a negative argument moves to the left.
• Height: I want to mention that the first three arguments are always required. The Offset Function will return in error if you try to omit one of them. Height and width, however, are optional. Height, which has to be a positive number for a reason soon to be obvious, is the height, in rows, that you want the returned reference to occupy. The example will make this easier to understand.
• Width: Width is similar to height and must also be a positive number. It refers, of course, to the width, in columns, that you want the returned reference to occupy.

You can learn all the practical formulas that use the Offset Function in this advanced Excel course for professionals, which covers everything from SUM to HLOOKUP. You might also benefit from this post on the top-ten Excel formulas that helped the writer of the post keep his job.

Example

The example is going to show you just how simple the Offset Function can be. Take a look at the screenshot below:

As you can see, the data doesn’t have to be complex, at least to gain a basic understanding of how this function works.

The first thing to do is pick a reference point. Let’s start with Johnson; cell A2. Let’s say we want to offset the reference to align with Johnson’s age, meaning that anytime we use the cell containing the Offset Function we want it to reference Johnson but return his age. Take a look at the screenshot below; you will notice that the Offset Function appears in the formula bar at the top of the screenshot:

I want to briefly explain the reasoning behind the numbers in our Offset Function. Obviously, I used cell A2 as the reference point, because that is Johnson’s cell. I entered a “0” for the rows argument because you must enter a value; the “0” is because we were not moving up or down. The “2” refers to the number of columns I wanted to move from the right of Johnson. Even though I didn’t have to enter “1” and “1” for width and height, I did so just to give you an idea of what a full-length function looks like. As expected, the function returns “25” in cell E1. You would only have to adjust the height and/or width if you were referencing a range of cells. Let’s say we were referencing all four ages in our example. In that case, we would use “4” for our height, in order to accommodate the four ages. Look what happens when I try to do that with a single value:

Notice the “4” in the height argument. The dreaded #VALUE! is displayed because it is against the laws of tech physics to fit one number into four cells. Just to give you an idea of how this can be used in conjunction with other formulas, look at a very simple SUM example:

You can use the cell containing the Offset Function as you would any other cell. This may seem almost redundant in this simple example, but if our Offset Function referenced a cell range containing 50 cells, we could insert this easily into a variety of scenarios and thereby save ourselves loads of precious time.

Using the Offset Function with other formulas is a whole other lesson. You can, however, pick up this knowledge from this handy workshop that covers in-depth Excel formulas, including VLOOKUP and Pivot training.