Excel Offset Function – Give It Some Love!


The Excel Offset Function is an awesome Excel formula with unique capabilities. Yet, the Excel Offset Function receives little attention from Excel users, and is overshadowed by its more glamorous cousins – the LOOKUP functions. Why is this? The aim of this series is to explore this issue, to shed light on the capabilities of the function, and to inspire you to use it in your work. So go on, give it some love!

Excel Offset Function Picture 1

First, why does the Excel Offset function receive little recognition? We think that there is a problem with the function’s name. The role of other functions, in comparison, can be easily understood from their names. Take VLOOKUP, for example, whose role is to ‘look up’ data from a table. Maybe this helps explain why VLOOKUP is so popular with Excel users.

Offset, on the other hand, sounds rather obscure. Very few people realise intuitively that the name of the function is derived from its ability to control position; in other words, to establish an anchor point and to ‘offset’ or ‘move away’ from that point by a certain number of rows / columns, then return a value from a cell, or to perform an operation on a range of cells. The first function is really useful; the second is unique to offset and, when combined with other functions, creates awesome dynamic functionality.


The series comes with a download file, and we recommend that you work along with the video. Click to download the Excel spreadsheet file below:

Excel Offset Function practice file

In the first video, we explore a basic task: retrieving data from a table using the offset function. Now, usually we would deploy a LOOKUP formula to do this. However, the structure of the table dictates that a LOOKUP formula would not be easy to set up. Offset, however, can handle this non-conventional format. It is an illustration of why it is useful to have a range of possible approaches at your disposal.


In the second example video, we exploit the unique capabilities of the Excel Offset function. We have already seen that the formula consists of three main components: (1) a reference, or anchor point, (2) the number of rows to move away from the reference, and (3) the number of columns to move away from the reference. With these components, the formula returns the value of the cell that it arrives at.

We can optionally add a 4th and 5th component to the formula. These are often ignored, but are actually the key to creating awesome dynamic functions. The 4th and 5th components determine the ‘height’ (in rows) and ‘width’ (in columns) of the range to be returned. Now, on its own, this functionality would not be particularly useful. However, when combined with other formulae – in this case the SUM formula – powerful functions result. Take a look at the video, and make sure you work along in the download file (link above).


In the final example video, we combine offset with an Excel chart to create a chart that changes according to a user input. This means that the chart is ‘dynamic’ – a great way of saving space in your spreadsheets. As we have seen, the Excel Offset function allows us to reference a range of cells. In this case, we link the offset formula to a match formula which is, in turn, linked to the user input. This chain of dependencies creates awesome ‘click-of-a-button’ functionality. Check it out!


We hope you’ve found these videos helpful, and feel enthused about the Excel Offset function. Make sure you try it out, We think that you will not regret it! Let us know how you get on, and what applications you have found. Get in touch here.

Now, learn how to use visual basic code in Excel.

Read more about the #ExcelRevolution here. Or, follow us on Facebook and Twitter.

This entry was posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized. Bookmark the permalink. Comments are closed, but you can leave a trackback: Trackback URL.