How to slice data in Excel without VBA using the OFFSET function

14 min readSep 10, 2023

Slicing a two-dimensional data range is now possible in Excel thanks to Dynamic Array Formulas introduced in September 2018. Throughout this series I will demonstrate how to use built-in functions such as OFFSET, TAKE, INDEX, CHOOSEROWS and CHOOSECOLS to slice data like in Python, MATLAB, etc. without writing a single line of VBA code.

This tutorial focuses on how to use the build-in Excel function OFFSET to slice data in Excel. Then we will use the LAMBDA function to automate the slicing functionality to achieve a similar look and feel to Python, etc.

If you would rather prefer to skip all the explanations and jump straight to the formula, then please go directly to the summary! 🚀

· What does it mean to “slice” data?
· Part I — Getting familiar with the OFFSET function
∘ Example — No offset
∘ Example — Offset the first 2 rows and columns
∘ Example — Return the last 4 rows and columns
∘ Example — Return the centre 2 rows and columns
∘ Example — Return the last 2 rows and columns
∘ Example — Return rows 3 to 5 and columns 2 to 5
∘ Example — Return the 4th row
∘ Example — Return the 5th column
∘ Get a data point from the sliced data range
· Part II — Automating the slice function
∘ Basic functionality replication
∘ Advanced functionality replication
∘ Storing the functionality as a LAMBDA function
· Summary and the revised formulas
∘ Further improvements
· Downloads

What does it mean to “slice” data?

To “slice” data simply means to select a specific range or subset of data to be returned back to the user for a particular data analysis purpose.

The idea of slicing can be easily shown in GNU Octave Online https://octave-online.net/ without having to install any software. Write the following code in the Octave Command Prompt to generate a 6-by-6 matrix containing integers from 1 to 36:

data = reshape(1:36, [6, 6]).'

To slice this matrix all you have to do is specify which row and column ranges you would like returned. For example, the following command would return rows 1 to 3 and columns 4 to 6:

data(1:3, 4:6)

If you are more comfortable with Python, then try the following code in your favourite IDE or try it online at https://jupyter.org/try in a new Jupyter Notebook:

import numpy as np
data = np.reshape(np.arange(1, 37), (6, 6))
print(data)

Note that indexing in Python starts at 0 and the last index is not included. To call rows 1 to 3, one would have to call index 0 to 3 where row index 3 is excluded. To return the same slice of data as above, run the command:

data[:3, 3:]

This slicing functionality is exactly what we will try to replicate in Excel without writing a UDF in VBA. We will use built-in functions only and a little bit of creativity.

Part I — Getting familiar with the OFFSET function

The traditional use of the OFFSET function was just that, to offset cell ranges in spreadsheets. To be clear, this function does not actually move reference cells. It simply returns a new reference i.e., a new Range object in VBA terms. Recently, with the introduction of dynamic array functions, the OFFSET function will return a dynamic array.

Dynamic array functions were introduced in September 2018 with Excel 2021 which automatically “spill” the formulas in the neighbouring blank cells. Thus making the legacy Ctrl+Shift+Enter(CSE) array formulas redundant.

The syntax for the OFFSET function is =OFFSET(reference, rows, cols, [height], [width])with the following inputs:

reference is the cells range that we want to base the offset on.
rows is the number of rows up or down. A positive rows offset means the number of rows below the starting reference. A negative offset means the number of rows above the starting reference.
cols is the number of columns left or right. A positive cols offset means the number of columns to the right of the starting refernce. A negative offset means the number of columns to the left of the starting reference.
height is the optional number of rows to be returned which must be a positive integer. If omitted, this defaults to the height of the reference.
width is the optional number of columns to be return which must also be a positive integer. If omitted, this defaults to the width of the reference.

Note: The rows and cols offsets are bounded by the edges of the spreadsheet i.e., if you exceed the boundaries then the function returns the #REF! error.

Due to the introduction of dynamic array functions in 2018, the OFFSET function provides us with a very flexible way of slicing data ranges in Excel. Given the following data in an Excel spreadsheet:

Example — No offset

To start understanding the OFFSET function we start from scratch i.e., literally from zero. Type in the following formula in a new cell =OFFSET(A1:F6, 0, 0)

You notice that we have the same data returned, as the net effect of the offset was zero.

Example — Offset the first 2 rows and columns

Next to really understand what the offset function actually returns, type the following formula in a new cell =OFFSET(A1:F6, 2, 2)

Reference returned after offsetting by 2 rows and cols.

Let’s us analyse the new reference that Excel has returned. We have offset the starting cell A1by two rows down indicated by the green arrow below and two columns to the right indicated by the red arrow thus, marking the new starting cell as C3. In VBA terms, we start with Cells(1, 1)and end up with the offset Cells(1 + 2, 1 + 2)= Cells(3, 3)= Range("C3").

Excel returns the same height and width as the input reference since we did not provide a height or width i.e., six rows down and six columns to the right. This effectively marks the last cell as H8. Excel pads the bottom two rows and the last two right columns with zeros as there is not data there.

Keep this example in mind, as it will help us further.

Example — Return the last 4 rows and columns

The previous example leads us nicely into the next example. If we want to return the last 4 rows and columns without the 0 padding, then specify the height and width as in the following formula =OFFSET(A1:F6, 2, 2, 4, 4). This should return:

The last 4 rows and columns of the data.

Example — Return the centre 2 rows and columns

Type the following formula in a new cell =OFFSET(A1:F6, 2, 2, 2, 2)which should return:

The centre 2 rows and columns of the data.

As in the previous example, we have offset the starting range to cell C3which contains the number 15. We can return two rows and two columns by setting the both the height and width as 2.

Example — Return the last 2 rows and columns

To obtain the last two rows and columns, we need to first offset the starting cell by 4 rows down and 4 columns right to E5with the following formula =OFFSET(A1:F6, 4, 4)

Offsetting the data reference by 4 rows and columns.

The formula above should return the last two rows and columns of our data, plus 32 cells with zero padding. We can then return a reference with both the height and width set to 2 as in formula =OFFSET(A1:F6, 4, 4, 2, 2), without the zero padding:

Example — Return rows 3 to 5 and columns 2 to 5

Following the same logic as in the previous example we can come up with more elaborate slices of the data. For example, let us offset down two rows and one column to the right with the formula =OFFSET(A1:F6, 2, 1)to offset the starting range B3i.e., Cells(3, 2)in VBA terms:

Next, if want to return rows 3 to 5 then we need a height of 3 rows. And for returning columns 2 to 5 i.e., columns B to E, then we need a width of 4 columns. Thus, we run the formula =OFFSET(A1:F6, 2, 1, 3, 4)to obtain:

Rows 3 to 5 and Cols 2 to 5 of the data.

Example — Return the 4th row

To slice one row we mainly require the rows offset and the height. Firstly, offset 3 rows down to get to the 4th row,=OFFSET(A1:F6, 3, 0)

Next set the height to 1, as in =OFFSET(A1:F6, 3, 0, 1)

4th row of the data.

Note that the columns offset is required, so it is left as 0. The width is optional, which defaults to the width of the input reference range.

Example — Return the 5th column

To slice a column we require the cols offset and the width. Firstly, we offset 4 columns to the right to get to the 5th column =OFFSET(A1:F6, 0, 4)

Lastly, specify width of 1, noting that we can leave the height input as blank =OFFSEET(A1:F6, 0, 4, , 1)

5th column of data.

Get a data point from the sliced data range

How to make use of the new sliced dynamic array? For example, let’s say that you ran the formula =OFFSET(A1:F6, 4, 4, 2, 2) in cell A9. To obtain the data point in the 2nd row and 2nd column of the sliced data, we then make use of the INDEX function =INDEX(A9#, 2, 2)in another cell. Note that the hash symbol specifies the cell where the dynamic array starts to spill from. Alternatively, simply enter =B10into the new cell, and it should return the correct value from the dynamic array.

Part II — Automating the slice function

Automating the slice functionality can be achieved similarly to the functionality in Python, MATLAB, etc. with a bit of thought and creativity. That is, we would like to construct a named function such as =SLICE(source_data, row_start, row_end, column_start, column_end)as not to repeat complicated nesting functions in Excel, and perhaps add some validation logic.

Warning: The logic will get more complicated from here on! This section is for the adventurous few who enjoy building complicated formulas.

Basic functionality replication

We need to build the basic concept of how to use the OFFSET function in general i.e., =OFFSET(source_data, rows_offset, cols_offset, height, width). So the first job is to workout what the rows_offset, cols_offset, height and width formulas should be for the given inputs row_start, row_end, column_start and column_end.

Recall the examples in Part I, that the rows and cols offsets will be the row and column that we want to start slicing from less 1:

rows_offset = row_start - 1
cols_offset = column_start - 1

The height and width are simply the count of rows between row_start and row_end, and likewise, the count of columns between column_start and column_end:

height = row_end - row_start + 1
width  = column_end - column_start + 1

The following is a demonstration of these calculations in a spreadsheet:

Where the parameters in column M are as specified above with the given the inputs in column J:

rows_offset = J1 - 1
cols_offset = J3 - 1
height      = J2 - J1 + 1
width       = J4 - J1 + 1

The resulting dynamic array “slice” in P1# follows the basic concept above of =OFFSET(A1:F6, M1, M2, M3, M4).

Advanced functionality replication

Next, we can customize our logic further to cater for blank arguments and the end of the array arguments.

We start first with providing the user the option to keep arguments blank as it is done in Python, etc. If the start arguments are blank, we simply set them to 1 in our calculations, else if the end arguments are blank, then we set them to the rows count or the columns count of the source range.

Note that I have left cells J1 and J4 blank on purpose. Thus, the Excel formulas in column M are starting to get a bit more complicated:

rows_offset
=IF(OR(ISBLANK(J1), TRIM(J1)=""), 1, J1) - 1

cols_offset
=IF(OR(ISBLANK(J3), TRIM(J3)=""), 1, J3) - 1

height
=IF(OR(ISBLANK(J2), TRIM(J2)=""), ROWS(A1:F6), J2) 
- IF(OR(ISBLANK(J1), TRIM(J1)=""), 1, J1) + 1

width
=IF(OR(ISBLANK(J4), TRIM(J4)=""), COLUMNS(A1:F6), J4) 
- IF(OR(ISBLANK(J3), TRIM(J3)=""), 1, J3) + 1

Next, we add some extra validation for the input arguments and we also check if the end arguments are negative like in Python. For example, if column_end = -1then it means the user is asking for the last column. If column_end = -2then it means the user is asking for the second last column, and so on. Equivalently, in MATLAB we would specify the end of the array argument asend. I find it easier to follow Python conventions using negative numbers to specify the location from the end of the array. See next the use of negative arguments in cells J2 and J4.

Supporting negative end row and column arguments

Therefore, the Excel formulas in column M for the inner and outer intermediate parameters have evolved to the following:

rows_offset
=IF(OR(ISBLANK(J1), TRIM(J1)=""), 1, J1) - 1

cols_offset
=IF(OR(ISBLANK(J3), TRIM(J3)=""), 1, J3) - 1

height
=IF(OR(ISBLANK(J2), TRIM(J2)=""), ROWS(A1:F6), 
IF(AND(J2<0, J2>= -1*ROWS(A1:F6)), ROWS(A1:F6) + J2 + 1, 
IF(AND(J2>=1, J2<=ROWS(A1:F6)), J2, NA()))) 
- IF(OR(ISBLANK(J1), TRIM(J1)=""), 1, J1) + 1

width
=IF(OR(ISBLANK(J4), TRIM(J4)=""), COLUMNS(A1:F6),
IF(AND(J4<0, J4>= -1*COLUMNS(A1:F6)), COLUMNS(A1:F6) + J4 + 1,
IF(AND(J4>=1, J4<=COLUMNS(A1:F6)), J4, NA())))
- IF(OR(ISBLANK(J3), TRIM(J3)=""), 1, J3) + 1

So far, all of the intermediate parameters calculated in column M have been simply input in cell P1 as =OFFSET(A1:F6, M1, M2, M3, M4). It is possible to consolidate all of these formulas into one massive formula, but it is not very pretty. Hence, the need to write a LAMBDA function which is much tidier.

Storing the functionality as a LAMBDA function

Note: The LAMBA function is only available for Excel for Microsoft 365 and Excel for the web subscribers.

We can package all the logic nicely in a LAMBDA function for further use in the current spreadsheet or to export it to another Excel file. As mentioned earlier, the general basic concept is =OFFSET(source_data, rows_offset, cols_offset, height, width). If we do consolidate the intermediate parameter calculations into one formula, then it would look something like this:

=OFFSET(A1:F6, IF(OR(ISBLANK(J1), TRIM(J1)=""), 1, J1) - 1,
IF(OR(ISBLANK(J3), TRIM(J3)=""), 1, J3) - 1,
IF(OR(ISBLANK(J2), TRIM(J2)=""), ROWS(A1:F6),
IF(AND(J2<0, J2>= -1*ROWS(A1:F6)), ROWS(A1:F6) + J2 + 1,
IF(AND(J2>=1, J2<=ROWS(A1:F6)), J2, NA())))
- IF(OR(ISBLANK(J1), TRIM(J1)=""), 1, J1) + 1,
IF(OR(ISBLANK(J4), TRIM(J4)=""), COLUMNS(A1:F6),
IF(AND(J4<0, J4>= -1*COLUMNS(A1:F6)), COLUMNS(A1:F6) + J4 + 1,
IF(AND(J4>=1, J4<=COLUMNS(A1:F6)), J4, NA())))
- IF(OR(ISBLANK(J3), TRIM(J3)=""), 1, J3) + 1)

This massive formula might look intimidating at the moment. However, this will be very useful in building the LAMBDA function which is specified as follows =LAMBDA(parameters, calculation)(input_arguments). Therefore, our lambda function would be structured as as =LAMBDA(source_data, row_start, row_end, column_start, column_end, calculation_formula)(A1:F6, J1, J2, J3, J4), where the calculation_formulais the monster formula above.

Using an editor such as Notepad++, we can replace the range references in the gigantic formula for the parameter names specified above. Thus, we get the following lambda formula for slicing in Excel:

=LAMBDA(source_data,row_start,row_end,column_start,column_end,
OFFSET(source_data, 
IF(OR(ISBLANK(row_start), TRIM(row_start)=""), 1, row_start) - 1,
IF(OR(ISBLANK(column_start), TRIM(column_start)=""), 1, column_start) - 1,
IF(OR(ISBLANK(row_end), TRIM(row_end)=""), ROWS(source_data),
IF(AND(row_end<0, row_end>= -1*ROWS(source_data)), 
ROWS(source_data) + row_end + 1,
IF(AND(row_end>=1, row_end<=ROWS(source_data)), row_end, NA())))
- IF(OR(ISBLANK(row_start), TRIM(row_start)=""), 1, row_start) + 1,
IF(OR(ISBLANK(column_end), TRIM(column_end)=""), COLUMNS(source_data),
IF(AND(column_end<0, column_end>= -1*COLUMNS(source_data)), 
COLUMNS(source_data) + column_end + 1,
IF(AND(column_end>=1, column_end<=COLUMNS(source_data)), column_end, NA())))
- IF(OR(ISBLANK(column_start), TRIM(column_start)=""), 1, column_start) + 1))

This lambda formula was tested by inserting the whole formula in another cell =LAMBDA(…)(A1:F6, J1, J2, J3, J4), note the input arguments at the end.

The last step of automation is to save the massive lambda formula in the Name Manger, which can be found in the Formulas ribbon. NB: Copy only the formula LAMDA(…)into the “Refers to” section and not the input arguments (A1:F6, J1, J2, J3, J4).

Go to the Functions ribbon, click on Name Manger, add new Name for the custom LAMBDA function.

The arguments will be input as follows in the new function SLICE(A1:F6, J1, J2, J3, J4). And voila!

With regards to “exporting” this named lambda function to another Excel workbook, simply copy a blank sheet from your current workbook where the LAMBDA named function has been saved to a new workbook.

Summary and the revised formulas

I realise that the formulas presented above are much too complicated and cumbersome. Therefore, I have revised the messy formulas to make them more efficient and easily readable.

The main idea of slicing data in Excel with the OFFSET function is this:

=OFFSET(source_data, rows_offset, cols_offset, height, width)

where the indexing parameters are as follows:

rows_offset = row_start - 1,
cols_offset = column_start - 1,
height = row_end - row_start + 1,
width = column_end - column_start + 1.

Since the OFFSET function works well as a dynamic array function since Microsoft for Excel 365, the input parameters do exactly “what it says on the tin” i.e.:

The row and column offsets shift the address of the whole source data reference range.
The height and width scale down the offset reference range.

The resulting subset is the shrunk offset reference range.

The first step for automating the 2D array slicer is achieved with the LAMBDA function:

=LAMBDA(
source_data,
row_start,
row_end,
column_start,
column_end,
OFFSET(source_data, row_start - 1, column_start - 1, 
row_end - row_start + 1, column_end - column_start + 1))

Then we tidy up the LAMBDA using the LET function by introducing intermediate variables that will hold values and calculations:

=LAMBDA(source_data,row_start,row_end,column_start,column_end,
LET(
rows_offset, 
row_start - 1,
cols_offset, 
column_start - 1,
height, 
row_end - row_start + 1,
width, 
column_end - column_start + 1,
OFFSET(source_data, rows_offset, cols_offset, height, width)))

Therefore, this is a much cleaner automation of the SLICE function, and it should easily read like the main idea above.

Further improvements

Validation is an important step to writing fail-safe functions. Checking for blank start and end indices, allowing for negative indices and validating that the start index is less than or equal to the end index for both rows and columns can be done as follows:

=LAMBDA(source_data, row_start, row_end, column_start, column_end,
LET(
valid_row_start,
IF(OR(ISBLANK(row_start), TRIM(row_start)=""), 1, row_start),
valid_row_end,
IF(OR(ISBLANK(row_end), TRIM(row_end)=""), ROWS(source_data), 
IF(AND(row_end<0, row_end >= -1*ROWS(source_data)), 
ROWS(source_data) + row_end + 1, 
IF(AND(row_end>=1, row_end<=ROWS(source_data)), row_end, NA()))),
valid_column_start,
IF(OR(ISBLANK(column_start), TRIM(column_start)=""), 1, column_start),
valid_column_end,
IF(OR(ISBLANK(column_end), TRIM(column_end)=""), COLUMNS(source_data), 
IF(AND(column_end<0, column_end>= -1*COLUMNS(source_data)), 
COLUMNS(source_data) + column_end + 1, 
IF(AND(column_end>=1, column_end<=COLUMNS(source_data)), column_end, NA()))),
rows_offset, 
valid_row_start - 1,
cols_offset, 
valid_column_start - 1,
height, 
valid_row_end - valid_row_start + 1,
width, 
valid_column_end - valid_column_start + 1,
OFFSET(source_data, rows_offset, cols_offset, height, width))
)(A1#, , 3, 4, -1)

Allowing for various step sizes and negative step sizes to reverse the indexing order can be done as follows:

=LAMBDA(source_data,row_start,row_end,row_step,column_start,column_end,col_step,
LET(
rows_offset,
row_start - 1,
cols_offset,
column_start - 1,
height,
row_end - row_start + 1,
width,
column_end - column_start + 1,
valid_row_step,
IF(OR(ISBLANK(row_step), TRIM(row_step)="", row_step=0), 1, row_step),
valid_col_step,
IF(OR(ISBLANK(col_step), TRIM(col_step)="", col_step=0), 1, col_step),
INDEX(OFFSET(source_data, rows_offset, cols_offset, height, width),
SEQUENCE(CEILING.MATH(ABS(height/valid_row_step)),,
IF(valid_row_step < 0, ABS(height), 1), row_step),
SEQUENCE(,CEILING.MATH(ABS(width/valid_col_step)),
IF(valid_col_step < 0, ABS(width), 1), col_step)))
)(A1#, 1, 9, -4, 1, 9, 3)

Note that I am making use of the INDEX and SEQUENCE function to accommodate for the custom step sizes, and I have to recalculate the number of rows and columns taken for the custom step sizes.

Of course, these two customizations can be joined together. I will leave this for you to do as a fun exercise. 😎

Downloads

You are welcome to download a copy of the example file. Thank you for reading my article and I hope you found it useful.

Before you open the file downloaded from GitHub for the first time, make sure to right-click on the file and select Properties. In General > Security, select the “Unblock” check-box and then click the “Apply” button.