How to slice data in Excel without VBA using the CHOOSEROWS and CHOOSECOLS functions

14 min readJul 24, 2023

Slicing a two-dimensional data range is now possible in Excel thanks to Dynamic Array Functions introduced in September 2018. Throughout this series I will demonstrate how to use built-in functions such as TAKE, OFFSET, 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 functions CHOOSECOLS and CHOOSEROWS 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?

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 at 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 CHOOSEROWS and CHOOSECOLS functions

Note: The CHOOSEROWS and CHOOSECOLS functions are available only for Excel for Microsoft 365 and Excel for the web subscribers.

Firstly, you may ask what are dynamic array functions? This functionality was 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 first function is =CHOOSEROWS(array, row_num1, [row_num2]) with the following input arguments:

array is the data range containing the rows to be returned in the new dynamic array;
row_num1 is the first row index to be returned which is mandatory;
[row_num2] is the placeholder for optional row numbers to be returned.

Similarly, the syntax for the second function =CHOOSECOLS(array, col_num1, [col_num2])has the inputs:

array the data range containing the columns to be returned in the new dynamic array;
col_num1 is the first column index to be returned, required;
[col_num2] is the placeholder for optional column indices to be returned.

Each of these functions spills the requested row(s) or column(s) starting in the formula cell.

Given the following data in an Excel spreadsheet:

Example — Return the entire 1st row

To extract the first row from our given is achieved by running the formula =CHOOSEROWS(A1:F6, 1)which should return:

First data row

Example — Return the entire 3rd row

Extracting the third data row is achieved by simply changing the row number in the formula =CHOOSEROWS(A1:F6, 3)to return:

Third data row

Example — Return the 1st and 3rd rows

The formula that returns the stacked first and third data rows makes use of the optional input argument as follows =CHOOSEROWS(A1:F6, 1, 3)to obtain:

Another concept that we will be using when automating this functionality is to pass a set of row indices inside curly braces {}such as =CHOOSEROWS(A1:F6, {1, 3}), instead of passing the row numbers one by one.

Example — Return the first 3 rows

To return the first three rows of our data range we can run the formula as =CHOOSEROWS(A1:F6, 1, 2, 3)to return:

Note that we can input more than one optional argument for the number of rows. The same slice of data can be achieved by inputting the row indices as set as follows =CHOOSEROWS(A1:F6, {1, 2, 3}).

Introducing the SEQUENCE function

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

To make our formulas more flexible we shall use Excel’s SEQUENCE function to generate the sequence of indices as the name suggests. The syntax for this function is =SEQUENCE(rows, [columns], [start], [step])where the inputs are:

rows is the number of rows to generate. If only this input is passed as an argument then it will generate a column of integers 1 up to the number of rows input, for example =SEQUENCE(10) will return numbers 1 to 10.
[columns] is the optional number of columns to generate. The default is value 1. If we input the number of rows and columns, then it will output a 2D array a.k.a. “matrix” containing the numbers 1 up to rows * columns. To return the example data from 1 to 36 as given above in the range A1:F6, this can easily be generated as =SEQUENCE(6, 6).
[start] changes the starting number from the default of 1. For example, if you would like to start your sequence at the number 10, then simply change the third input argument to 10.
[step] specifies the step size other than the default 1. For example, if you would like to generate the first ten odd numbers, then run the formula =SEQUENCE(10, , , 2) or if you are not comfortable with passing blank arguments you can run =SEQUENCE(10, 1, 1, 2).

Using the CHOOSEROWS and SEQUENCE functions in combination

To replicate the example above for returning the first three rows =CHOOSEROWS(A1:F6, {1, 2, 3}), it is more flexible to use the SEQUENCE function to generate the row indices to be returned as follows =CHOOSEROWS(A1:F6, SEQUENCE(3, 1, 1, 1)). The reason for inputting all four arguments is that it will give us greater control later in automating the functionality. For example, if we wanted to replicate the example that returns row 1 and 3 only =CHOOSEROWS(A1:F6, {1, 3}), then we can generate odd row numbers =CHOOSEROWS(A1:F6, SEQUENCE(2, 1, 1, 2)).

Example — Return the 1st column

The usage of the CHOOSECOLS is similar to the above. Instead of specifying which rows to be returned, we now specify the column numbers to be returned. That is, the formula =CHOOSECOLS(A1:F6, 1)will return the first column:

First column

Example — Return the first 3 columns

Let us skip ahead to using the SEQUENCE function instead of specifying the first 3 column indices as {1, 2, 3}, as follows =CHOOSECOLS(A1:F6, SEQUENCE(1, 3, 1, 1))to return the first three columns:

Nesting CHOOSEROWS inside CHOOSECOLS to return a specific slice of data

The idea is to replicate the slicing functionality we see in MATLAB, Python, etc. and nesting these functions will bring us closer to that result. That is, we would like to construct the following nested formula =CHOOSECOLS(CHOOSEROWS(array, row_num1, [row_num2]), col_num1, [col_num2]). Note that this is not the only order we can choose. Whichever order of nesting you choose, please take care of inputting the correct arguments. Of course, we will make use of the SEQUENCE function to specify the indices.

Example — Return the first 3 rows and the first 3 columns

To slice the first three rows and the first three columns we could run the following nested formula =CHOOSECOLS(CHOOSEROWS(A1:F6, SEQUENCE(3, 1, 1, 1)), SEQUENCE(1, 3, 1, 1))to obtain:

Example — Return the first 3 rows and the last 3 columns

One last example to return row numbers 1 to 3 and column numbers 4 to 6 of the data can be written as follows =CHOOSECOLS(CHOOSEROWS(A1:F6, SEQUENCE(3, 1, 1, 1)), SEQUENCE(1, 3, 4, 1)) by changing the starting column to return the slice:

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.

In addition, we could also specify the row and column step sizes as we could in Python, MATLAB, etc. which is beyond the purpose of this tutorial. Do keep in mind that the CHOOSEROWS and CHOOSECOLS functions allow for the step size to be specified.

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

The basic concept has already been demonstrated in the previous two examples, with the SEQUENCE function specifying the indices as =CHOOSECOLS(CHOOSEROWS(source_data, SEQUENCE(num_rows, 1, row_start, 1)), SEQUENCE(1, num_cols, column_start, 1)) , where the source_data is the range A1:F6 in our example:

The basic concept for automating the slice functionality

Here we have the number of rows and number of columns to be returned calculated simply as:

num_rows = J2 - J1 + 1
num_cols = J4 - J3 + 1

The resulting data slice in the dynamic range P1# is exactly the basic formula:

=CHOOSECOLS(CHOOSEROWS(A1:F6, SEQUENCE(M2, 1, J1, 1)), 
SEQUENCE(1, M4, J3, 1))

Note that the step sizes are not necessary in the nested formula, as they do default to 1. We leave them in for now, in case we want to automate the step size in the future as well.

Advanced functionality replication

Next, we can customize the logic to allow for blank input arguments and for negative arguments indicating the end of the array. This customization should bring our functionality closer to MATLAB, Python, etc.

We first cater for blank input arguments. So if the row_start is blank the column_start is left blank then these will get set to 1. If the row_end is blank then it is set to the number of rows in the source range, and if the column_end is blank than that will be set to the number of columns in the source range.

Thus, for cell P1’s formula =CHOOSECOLS(CHOOSEROWS(A1:F1, SEQUENCE(M2, 1, M1, 1)), SEQUENCE(1, M4, M3, 1)), we have the following intermediate calculations to be fed in:

where the formulas in column M have been adjusted for blank entries:

row_start
=IF(OR(ISBLANK(J1), TRIM(J1)=""), 1, J1)

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

column_start
=IF(OR(ISBLANK(J3), TRIM(J3)=""), 1, J3)

num_cols
=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. See the negative arguments in cells J2 and J4:

Column M’s formulas contain added validations for these negative end of the array arguments:

row_start
=IF(OR(ISBLANK(J1), TRIM(J1)=""), 1, J1)

num_rows
=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

column_start
=IF(OR(ISBLANK(J3), TRIM(J3)=""), 1, J3)

num_cols
=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

Lastly, we will insert all these intermediate formulas into the general formula =CHOOSECOLS(CHOOSEROWS(A1:F1, SEQUENCE(M2, 1, M1, 1)), SEQUENCE(1, M4, M3, 1)) to enable us to automate the slicing functionality with the aid of the LAMBDA function.

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 will package the general formula in a LAMBDA function for further use in the current spreadsheet or to export it to another Excel file. Quite simply, we substitute the intermediate formulas from references M1 to M4 into the general formula =CHOOSECOLS(CHOOSEROWS(A1:F1, SEQUENCE(M2, 1, M1, 1)), SEQUENCE(1, M4, M3, 1)) to obtain this terrifying formula below.

=CHOOSECOLS(CHOOSEROWS(A1:F6, SEQUENCE(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, 1, IF(OR(ISBLANK(J1), TRIM(J1)=""), 1, J1), 1)), 
SEQUENCE(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, IF(OR(ISBLANK(J3), TRIM(J3)=""), 1, J3), 1))

Warning: The following formula may cause sever headaches!

Using an editor such as Notepad++ we substitute the cell refences with the parameter names specified at the beginning of Part II, pop the massive formula into the LAMBDA() function, and voila:

=LAMBDA(source_data, row_start, row_end, column_start, column_end, 
CHOOSECOLS(CHOOSEROWS(source_data, SEQUENCE(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, 1, 
IF(OR(ISBLANK(row_start), TRIM(row_start)=""), 1, row_start), 1)), 
SEQUENCE(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, IF(OR(ISBLANK(
column_start), TRIM(column_start)=""), 1, column_start), 1)))(A1:F6, J1, J2, 
J3, J4)

Note the input arguments after the function =LAMBDA(…)(A1:F6, J1, J2, J3, J4), those are our inputs.

The last step is to save this massive formula in the Name Manager, which can be found in Formulas ribbon. NB: copy on the formula =LAMBDA(…)into the “Refers to” input 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

And now when type =SLICE(, we can see the saved formula:

Which we can then give the inputs that we would require:

The source data in range A1:F6 was generated with =SEQUENCE(6,6) and thus can be accessed by reference A1# since it is a dynamic array as well.

We can “export” the named lambda function SLICE()to another Excel workbook by copying 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 CHOOSEROWS and CHOOSECOLS functions is simply to nest the two functions:

=CHOOSECOLS(CHOOSEROWS(source_data, SEQUENCE(num_rows,,row_start)), 
SEQUENCE(num_cols,,column_start))

where the indexing parameters are:

num_rows = row_end – row_start + 1
num_cols = column_end – column_start + 1

The resulting subset will be at the intersection of these two functions.

The sliced subset is at the intersection of the nested CHOOSEROWS and CHOOSECOLS.

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, 
CHOOSECOLS(CHOOSEROWS(source_data, 
SEQUENCE(row_end - row_start + 1,, row_start)), 
SEQUENCE(column_end - column_start + 1,, column_start)))

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

=LAMBDA(source_data,row_start,row_end,column_start,column_end,
LET(
num_rows,
row_end - row_start + 1,
num_cols,
column_end - column_start + 1,
CHOOSECOLS(CHOOSEROWS(source_data, SEQUENCE(num_rows,,row_start)),
SEQUENCE(num_cols,,column_start))))

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()))),
num_rows,
IF(valid_row_end >= valid_row_start, valid_row_end - valid_row_start + 1, 
NA()),
valid_col_start,
IF(OR(ISBLANK(column_start), TRIM(column_start)=""), 1, column_start),
valid_col_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()))),
num_cols,
IF(valid_col_end >= valid_col_start, valid_col_end - valid_col_start + 1, 
NA()),
CHOOSECOLS(CHOOSEROWS(source_data, SEQUENCE(num_rows,,valid_row_start)),
SEQUENCE(num_cols,,valid_col_start)))
)(A1#, 2, 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(
num_rows,
row_end - row_start + 1,
num_cols,
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),
CHOOSECOLS(CHOOSEROWS(source_data,
SEQUENCE(CEILING.MATH(ABS(num_rows/valid_row_step)),, IF(valid_row_step < 0, 
row_end, row_start), valid_row_step)),
SEQUENCE(CEILING.MATH(ABS(num_cols/valid_col_step)),, IF(valid_col_step < 0, 
column_end, column_start), valid_col_step)))
)(A1#, 2, 6, -2, 2, 6, -2)

Note that I am making use of the 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.