CHOOSECOLS function in Excel to get columns from array or range
This tutorial will introduce you to a new Excel 365 dynamic array function named CHOOSECOLS and show how you can use it to extract any specific columns from an array.
Imagine that you are working with a dataset of hundreds or thousands of columns. Obviously, some columns are more important than others, and naturally you may want to read their data first. Excel 365 offers a perfect function for the job, which can instantly retrieve specific certain from an array, so you can focus on the most relevant information.
Table of Contents
Excel CHOOSECOLS function
The CHOOSECOLS function in Excel lets you pick and return specific columns from a range or array.
Here’s how the function works:
- array – This is the group of cells (range) you’re working with.
- col_num1 – This is the number of the first column you want to pull out.
- col_num2, … – (Optional) You can add more column numbers if you want to get more than one column.
Here’s what the CHOOSECOLS function might look like in your Excel sheet:
CHOOSECOLS function availability
Currently, the CHOOSECOLS function is available in Excel for Microsoft 365 (Windows and Mac) and Excel for the web.
How to use CHOOSECOLS function in Excel
CHOOSECOLS is a dynamic array function, meaning it works with arrays automatically. You only need to enter the formula in one cell (the top-left cell of where you want the results). It will then automatically fill in as many columns as you specify and as many rows as in the original array. The result is a single dynamic array, also known as a “spill range.”
To create a CHOOSECOLS formula in Excel, follow these steps:
- For array:
Provide a range of cells or an array of values. - For col_num:
Provide a positive or negative integer to indicate which column you want to return.- A positive number selects a column counting from the left side of the array.
- A negative number selects a column counting from the right side of the array.
- For multiple columns:
You can either add extra arguments for each column or provide an array constant with the column numbers.
For example, to get columns 2, 3 and 4 from the range A4:E19, the formula is:
=CHOOSECOLS(A4:E19, 2, 3, 4)
Alternatively, you can use a horizontal array constant such as {2,3,4} or a vertical array constant such as {2;3;4} to specify the column numbers:
=CHOOSECOLS(A4:E19, {2,3,4})
=CHOOSECOLS(A4:E19, {2;3;4})
All three formulas above will deliver the same result:
In some situations, you may find it more convenient to input the column numbers in some cells, and then reference those cells individually or provide a single range reference. For example:
=CHOOSECOLS(A4:E19, G4, H4, I4)
=CHOOSECOLS(A4:E19, G4:I4)
This approach gives you more flexibility – to extract any other columns, you simply type different numbers in the predefined cells without having to modify the formula itself.
Now that you know the essentials, let’s dive into the extras and explore a slightly more complex CHOOSECOLS formulas to handle specific scenarios.
Get last columns from range
If you want to get columns from the end of a range, you can use negative numbers in the col_num part of the CHOOSECOLS function. This tells Excel to start counting columns from the right side instead of the left.
Here are some examples:
- To get the last column from the range A4:E19:
=CHOOSECOLS(A4:E19, -1) - To get the last two columns:
=CHOOSECOLS(A4:E19, -2, -1) - To get the last two columns but in reverse order:
=CHOOSECOLS(A4:E19, -1, -2)
Get every other column in Excel
To extract every other column from a range, you can use CHOOSECOLS along with other functions. Below are two formulas for getting odd and even columns.
- To get odd columns (like 1, 3, 5, etc.), use this formula:
=CHOOSECOLS(A4:E19, SEQUENCE(ROUNDUP(COLUMNS(A4:E19)/2, 0), 1, 1, 2)) - To get even columns (like 2, 4, 6, etc.), use this formula:
=CHOOSECOLS(A4:E19, SEQUENCE(ROUNDDOWN(COLUMNS(A4:E19)/2, 0), 1, 2, 2))
The screenshot below shows the first formula in action:
How this formula works:
Brief explanation: The CHOOSECOLS function returns every other column based on an array of sequential odd or even numbers produced by the SEQUENCE function.
A detailed formula break-down:
The first step is to calculate how many columns to return. For this, we use one of these formulas:
ROUNDUP(COLUMNS(A4:E19)/2, 0)
or
ROUNDDOWN(COLUMNS(A4:E19)/2, 0)
The COLUMNS function counts how many columns are in the range you’re working with. You divide that number by 2 because you’re only taking every other column. Then, depending on whether you want odd or even columns, you round the result up or down:
- Use ROUNDUP to get odd columns
- Use ROUNDDOWN to get even columns
This rounding is important when your range has an odd number of columns—so the math works out correctly.
For example, if your range has 5 columns:
- Odd columns: ROUNDUP(5/2, 0) gives 3
- Even columns: ROUNDDOWN(5/2, 0) gives 2
That number is used as the first part (number of rows) in the SEQUENCE function.
- To get odd columns:
SEQUENCE(3, 1, 1, 2)
This gives you the numbers {1; 3; 5} - To get even columns:
SEQUENCE(2, 1, 2, 2)
This gives you the numbers {2; 4}
These arrays are passed into the CHOOSECOLS function, which uses them to pull out just the columns you want—either the odd or even ones.
Flip an array horizontally in Excel
To reverse the order of columns in an array from left to right, you can use the CHOOSECOLS, SEQUENCE and COLUMNS functions together in this way:
=CHOOSECOLS(A4:D19, SEQUENCE(COLUMNS(A4:D19)) *-1)
As a result, the original range is flipped horizontally like shown in the image below:
How this formula works:
In this example, we use the SEQUENCE function to create a list of numbers that match the number of columns in the source array. To do this, we put COLUMNS(A4:D13) inside the rows part of the SEQUENCE function:
SEQUENCE(COLUMNS(A4:D19))
We don’t need to provide the other arguments (columns, start, step), so they will use their default values of 1. This makes SEQUENCE create a list of numbers like 1, 2, 3, …, up to n, where n is the total number of columns in the array.
To make the CHOOSECOLS function count columns from right to left, we multiply each number in the sequence by -1. This changes the list to negative numbers, like {-1; -2; -3}, which we then pass into the col_num part of CHOOSECOLS.
As a result, CHOOSECOLS will return the columns from the right side of the array:
CHOOSECOLS(A4:D19, {-1; -2; -3; -4})
Extract columns based on string with numbers
In situation when the index numbers of the target columns are provided in the form of a text string, you can use the TEXTSPLIT function to split the string by a given delimiter, and then pass the resulting array of numbers to CHOOSECOLS.
Let’s say the column numbers are listed in cell H3, separated by a comma and a space. To get the columns of interest, use this formula:
=CHOOSECOLS(A4:E19, TEXTSPLIT(H3, “, “) *1)
Extract columns from multiple ranges
To get specific columns from different ranges that are not next to each other, you first combine all the ranges into one using the VSTACK function. Then, you can use CHOOSECOLS to pick the columns you want from the combined range.
For example, if you want to get columns 1 and 3 from the ranges A4:D8, A12:D15, and A19:D21, use this formula:
=CHOOSECOLS(VSTACK(A4:D8, A12:D15, A19:D21), 1, 3)
That’s how to use the CHOOSECOLS function in Excel to return particular columns from a range or array. Thank you for reading and see you on our blog next week!
