In this tutorial, we are going to shed some light on one of the most mysterious inhabitants of the Excel universe - the OFFSET function. Show
So, what is OFFSET in Excel? In a nutshell, the OFFSET formula returns a reference to a range that is offset from a starting cell or a range of cells by a specified number of rows and columns. The OFFSET function may be a bit tricky to get, so let's go over a short technical explanation first (I'll do my best to keep it simple) and then we will cover a few of the most efficient ways to use OFFSET in Excel. Excel OFFSET function - syntax and basic usesThe OFFSET function in Excel returns a cell or range of cells that is a given number of rows and columns from a given cell or range. The syntax of the OFFSET function is as follows: OFFSET(reference, rows, cols, [height], [width]) The first 3 arguments are required and the last 2 are optional. All of the arguments can be references to other cells or results returned by other formulas. It looks like Microsoft made a good effort to put some meaning into the parameters' names, and they do give a hint at what you are supposed to specify in each. Required arguments:
Optional arguments:
Both the height and width arguments must always be positive numbers. If either is omitted, it defaults to the height or width of reference. Note. OFFSET is a volatile function and and may slow down your worksheet. The slowness is directly proportional to the number of cells recalculated. And now, let's illustrate the theory with an example of the simplest OFFSET formula. Excel OFFSET formula exampleHere is an example of a simple OFFSET formula that returns a cell reference based on a starting point, rows and cols that you specify:
The formula tells Excel to take cell A1 as the starting point (reference), then move 3 rows down (rows argument) and 1 column to the left (cols argument). As the result, this OFFSET formula returns the value in cell B4. The image on the left shows the function's route and the screenshot on the right demonstrates how you can use the OFFSET formula on real-life data. The only difference between the two formulas is that the second one (on the right) includes a cell reference (E1) in the rows argument. But since cell E1 contains number 3, and exactly the same number appears in the rows argument of the first formula, both would return an identical result - the
value in B4. Excel OFFSET formulas - things to remember
For example, if you try to use the
formula
the formula will return the sum of values in a 1-row by 3-column range that is 3 rows below and 1 column to the right of cell A1, i.e. the total of values in cells B4:D4. In situation when you wish to base the offset from the current cell, you can use the INDIRECT and ADDRESS functions in combination with ROW() and COLUMN() to get the starting reference. For example:
Why do I use OFFSET in Excel?Now that you know what the OFFSET function does, you may be asking yourself "Why bother using it?" Why not simply write a direct reference like B4:D4? The Excel OFFSET formula is very good for: Creating dynamic ranges: References like B1:C4 are static, meaning they always refer to a given range. But some tasks are easier to perform with dynamic ranges. This is particularly the case when you work with changing data, e.g. you have a worksheet where a new row or column is added every week. Getting the range from the starting cell. Sometimes, you may not know the actual address of the range, though you do know it starts from a certain cell. In such scenarios, using OFFSET in Excel is the right way to go. How to use OFFSET function in Excel - formula examplesI hope you haven't get bored with that much of theory. Anyway, now we are getting to the most exciting part - practical uses of the OFFSET function. Excel OFFSET and SUM functionsThe example we discussed a moment ago demonstrates the simplest usage of OFFSET & SUM. Now, let's look at these functions at another angle and see what else they can do. Example 1. A dynamic SUM / OFFSET formulaWhen working with continuously updated worksheets, you may want to have a SUM formula that automatically picks all newly added rows. Suppose, you have the source data similar to what you see in the screenshot below. Every month a new row is added just above the SUM formula, and naturally, you want to have it included in the total. On the whole, there are two choices - either update the range in the SUM formula each time manually or have the OFFSET
formula do this for you. Since the first cell of the range to sum will be specified directly in the SUM formula, you only have to decide on the parameters for the Excel OFFSET function, which will get that last cell of the range:
So, here goes the SUM / OFFSET formula pattern: =SUM(first cell:(OFFSET(cell with total, -1,0) Tweaked for the above example, the formula looks as follows:
And as demonstrated in the below screenshot, it works flawlessly: Example 2. Excel OFFSET formula to sum the last N rowsIn the above example, suppose you want to know the amount of bonuses for the last N months rather than grand total. You also want the formula to automatically include any new rows you add to the sheet. For this task, we are going to use Excel OFFSET in combination with the SUM and COUNT / COUNTA functions:
or
The following details can help you understand the formulas better:
Using OFFSET function with AVERAGE, MAX, MINIn the same manner as we calculated the bonuses for the last N months, you can get an average of the last N days, weeks or years as well as find their maximum or minimum values. The only difference between the formulas is the first function's name:
The key benefit of these formulas over the usual AVERAGE(B5:B8) or MAX(B5:B8) is that you won't have to update the formula every time your source table gets updated. No matter how many new rows are added or deleted in your worksheet, the OFFSET formulas will always refer to the specified number of last (lower-most) cells in the column. Excel OFFSET formula to create a dynamic rangeUsed in conjunction with COUNTA, the OFFSET function can help you make a dynamic range that may prove useful in many scenarios, for example to create automatically updatable drop-down lists. The OFFSET formula for a dynamic range is as follows:
At the heart of this formula, you use the COUNTA function to get the number of non-blank cells in the target column. That number goes to the height argument of OFFSET instructing it how many rows to return. Apart from that, it's a regular Offset formula, where:
Note. If you are making a dynamic range in the current sheet, there is no need to include the sheet name in the references, Excel will do it for you automatically when creating the named range. Otherwise, be sure to include the sheet's name followed by the exclamation point like in this formula example. Once you've created a dynamic named range with the above OFFSET formula, you can use Data Validation to make a dynamic dropdown list that will update automatically as soon as you add or remove items from the source list. For the detailed step-by-step guidance on creating drop-down lists in Excel, please check out the following tutorials:
Excel OFFSET & VLOOKUPAs everyone knows, simple vertical and horizontal lookups are performed with the VLOOKUP or HLOOKUP function, respectively. However, these functions have too many limitations and often stumble in more powerful and complex lookup formulas. So, in order to perform more sophisticated lookups in your Excel tables, you have to look for alternatives such as INDEX, MATCH and OFFSET. Example 1. OFFSET formula for a left Vlookup in ExcelOne of the most infamous limitations of the VLOOKUP function is inability to look at its left, meaning that VLOOKUP can only return a value to the right of the lookup column. In our sample lookup table, there are two columns - month names (column A) and bonuses (column B). If you want to get a bonus for a certain month, this simple VLOOKUP formula will work without a hitch:
However, as soon as you swap the columns in the lookup table, this will immediately result in the #N/A error: To handle a left-side lookup, you need a more versatile function that does not really care where the return column resides. One of possible solutions is using a combination of INDEX and MATCH functions. Another approach is using OFFSET, MATCH and ROWS: OFFSET(lookup_table, MATCH(lookup_value, OFFSET(lookup_table, 0, lookup_col_offset, ROWS(lookup_table), 1) ,0) -1, return_col_offset, 1, 1) Where:
In our example, the lookup table is A5:B9 and the lookup value is in cell B1, the lookup column offset is 1 (because we are searching for the lookup value in the second column (B), we need to move 1 column to the right from the beginning of the table), the return column offset is 0 because we are returning values from the first column (A):
I know the formula looks a bit clumsy, but it does work :) Example 2. How to do an upper lookup in ExcelAs is the case with VLOOKUP being unable to look at the left, its horizontal counterpart - HLOOKUP function - cannot look upwards to return a value. If you need to scan an upper row for matches, the OFFSET MATCH formula can help again, but this time you will have to enhance it with the COLUMNS function, like this: OFFSET(lookup_table, return_row_offset, MATCH(lookup_value, OFFSET(lookup_table, lookup_row_offset, 0, 1, COLUMNS(lookup_table)), 0) -1, 1, 1) Where:
Assuming that the lookup table is B4:F5 and the lookup value is in cell B1, the formula goes as follows:
In our case, the lookup row offset is 1 because our lookup range is 1 row down from the starting point, the return row offset is 0 because we are returning matches from the first row in the
table. Example 3. Two-way lookup (by column and row values)Two-way lookup returns a value based on matches in both the rows and columns. And you can use the following double lookup array formula to find a value at the intersection of a certain row and column: =OFFSET(lookup table, MATCH(row lookup value, OFFSET(lookup table, 0, 0, ROWS(lookup table), 1), 0) -1, MATCH(column lookup value, OFFSET(lookup table, 0, 0, 1, COLUMNS(lookup table)), 0) -1) Given that:
You get the following two-dimensional lookup formula:
It's not the easiest thing to remember, is it? In addition, this is an array formula, so don't forget to press Ctrl + Shift + Enter to enter it correctly. Of course, this lengthy OFFSET formula is not the only possible way to do a double lookup in Excel. You can get the same result by using the VLOOKUP & MATCH functions, SUMPRODUCT, or INDEX & MATCH. There is even a formula-free way - to employ named ranges and the intersection operator (space). The following tutorial explains all alternative solutions in full detail: How to do two-way lookup in Excel. OFFSET function - limitations and alternativesHopefully, the formula examples on this page have shed some light on how to use OFFSET in Excel. However, to efficiently leverage the function in your own workbooks, you should not only be knowledgeable of its strengths, but also be wary of its weaknesses. The most critical limitations of the Excel OFFSET function are as follows:
Alternatives to using OFFSET in ExcelAs is often the case in Excel, the same result can be achieved in a number of different ways. So, here are three elegant alternatives to OFFSET.
Do you remember the question asked at the beginning of this tutorial - What is OFFSET in Excel? I hope now you know the answer : ) If you want some more hands-on experience, feel free to download our practice workbook (please see below) containing all the formulas discussed on this page and reverse engineer them for deeper understanding. Thank you for reading! Practice workbook for downloadOFFSET formula examples (.xlsx file) You may also be interested inWhich part of interface refers a cell group that runs from the left side of the page to the right page?Row. A row is a group of cells that runs from the left of the page to the right. In Excel, rows are identified by numbers.
What is a group of cells that run from left to right and are identified by numbers?A range is a group of two or more cells in a worksheet. The cells in a range can be adjacent or nonadjacent. Commands executed by Excel will affect the entire range. A range is identified by the cell references of the cells in the upper left and lower right corners of the range.
Which of the following keyboard shortcuts inserts a worksheet to the left of the active sheet?Press and hold the Shift key on the keyboard. Press and release the F11 key. Release the Shift key. A new worksheet is inserted into the current workbook to the left of all existing worksheets.
Which of the following options copies formatting attributes and styles from one cell to another cell or range?Select the cell with the formatting you want to copy. Select Home > Format Painter. Drag to select the cell or range you want to apply the formatting to. Release the mouse button and the formatting should now be applied.
|