Is there simpler than OFFSET in Excel to get a cell range?

Question

Let's say you want a range of cells beginning at D3, of height given in cell A1 and width 2, then:

=OFFSET(D3, 0, 0, A1, 2)

works.

Is there something even simpler than OFFSET, like RANGE(D3, A1, 2)?

Answer 1

To answer your question if there's a shorter way of getting same result as =OFFSET(D3, 0, 0, A1, 2)

Other than removing the 0s I'd say no: =OFFSET(D3,,,A1,2)

But I'd advise to use INDEX instead, since that will not trigger recalculation at any change in the file. https://learn.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-volatile

This gives the same result but is non-volatile: =INDEX(D:E,SEQUENCE(A1,,3),{1,2})

Answer 2

Select the range, type a name into the name box to the left of the formula bar, for e.g. myrangename. Now you can use

=myrangename

Range names can also be constructed with relative references, so they range name applies, depending on the selected cell.

For example, with cell A1 selected, create a new range name mydynamicrange via the Name Manager and manually type the range as =A1:B3.

Note that there are no $ signs in the reference. That makes the range name relative to the currently selected cell. It basically defines a range that starts at the current cell (A1), and extends to a cell two rows below and one column to the right.

If you select cell D2, the range name mydynamicrange will now point to D2:E4. It starts at the current cell (D2), and extends to a cell two rows below and one column to the right.

That way, you can use one range name definition and apply it to calculations relative to the currently selected cell.

Edit after comment: Offset is a volatile function and will cause ANY cell in the whole workbook to recalculate whenever ANY value in the workbook changes. That can make for very slow workbook performance, and hence, Offset should be avoided.

Index() can often be used instead of Offset. In this case, like this:

=D3:INDEX(E:E,ROW(D3)+A1)

In words: create a range starting in D3 and extending to the cell in column E that is as many rows down from D3 as the value in A1.

You asked for a simpler formula, and this is not it. But at least it's not volatile.

You can combine this Index formula with the range name approach, though. Create a new range name via that Name Manager and let it reference

=$D$3:INDEX($E:$E,ROW($D$3)+$A$1)

Note the $ signs in the formula. They are important to anchor the formula to the cells D3 and A1.

In the screenshot, I've highlighted the range defined by your Offset formula and A1 = 3. The dotted line around the range including two cells below shows what the Index formula of the range name currently represents, now that A1 = 5.

Instead of the Offset function or the more complex Index formula, you can now simply refer to mySumRange in any worksheet formula.