Excel Offset Function – Give It Some Love!
Excel Offset Function – Give It Some Love!
RATIONALE
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!
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.
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.
VIDEO 1: RETRIEVE A VALUE FROM A TABLE
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:
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.
VIDEO 2: SUM A RANGE DYNAMICALLY
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).
VIDEO 3: CREATE A DYNAMIC CHART
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!
HOW DID YOU GET ON? LET US KNOW!