Udemy logo

excel offset functionI 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:

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:

Screen Shot 2014-02-20 at 11.32.31 AM

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:

Screen Shot 2014-02-20 at 11.39.02 AM

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:

Screen Shot 2014-02-20 at 11.50.47 AM

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:

Screen Shot 2014-02-20 at 11.53.02 AM

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.

Page Last Updated: February 2014

Top courses in Excel

Microsoft Excel - Excel from Beginner to Advanced
Kyle Pew, Office Newb
4.6 (400,074)
Bestseller
Microsoft Excel: Data Analysis with Excel Pivot Tables
Maven Analytics, Chris Dutton
4.6 (55,103)
Bestseller
Microsoft Excel: Data Visualization, Excel Charts & Graphs
Maven Analytics, Chris Dutton
4.6 (42,830)
Bestseller
Microsoft Excel Pro Tips: Go from Beginner to Advanced Excel
Maven Analytics, Chris Dutton
4.6 (4,961)
Bestseller
Microsoft Excel: Beginner to Advanced for 2023
Kadhiravan Jayachandiran
4.6 (2,200)
Microsoft Excel - Excel from Beginner to Advanced 2023
Warrick Klimaytys
4.6 (8,018)
Bestseller
Microsoft Excel Certification Exam Prep: MO-201 Excel Expert
Maven Analytics, Enrique Ruiz
4.7 (1,886)
Bestseller

More Excel Courses

Excel students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy Business.

Request a demo