CHOOSECOLS Function in Excel: Explained

In this article, you will learn about the CHOOSECOLS formula and its uses in Excel.

‍

What is the CHOOSECOLS function in Excel?

The CHOOSECOLS function in Excel is used to extract specific columns from an array or range. It returns an array of the specified columns in the order they are provided as arguments. The CHOOSECOLS function returns a dynamic array of columns that match the specified criteria. The function automatically spills the results to adjacent cells, so you don't need to enter the formula in multiple cells.

‍

Syntax of the CHOOSECOLS function in Excel

The syntax of the Excel CHOOSECOLS function is as follows:

=CHOOSECOLS(array,col_num1,[col_num2],…)

The CHOOSECOLS function syntax has the following arguments:

array: This is the range or array of data from which you want to select columns.

col_num: This is a range or array of integers that specifies the column numbers to be returned from the array. You can enter column numbers manually or use other Excel functions, such as MATCH, to generate the list of column numbers.

‍

Important note about CHOOSEROWS Function in Excel

Note that the CHOOSECOLS function is only available in Excel for Microsoft 365 (Windows and Mac) and Excel for the web.

‍

How to use the CHOOSECOLS formula in Excel?

Let us understand the CHOOSECOLS function with the help of an example case. The dataset below contains customer names and other relevant information, such as addresses and contact information. Now the objective is to extract the necessary information from the dataset by column.

‍

Mapping of the dataset with the syntax of the CHOOSECOLS function is as follows:

=CHOOSECOLS(array,col_num1,[col_num2],…)

array: In the example dataset, the array would be a range of cells from Cell A1:L10, representing all cells within the range.

col_num: This shall be the column number that needs to be extracted from the dataset. For instance, imagine you want to excerpt Column 1 containing the first names, Column 2  incorporating the last names, and Column 6 showing the country names. Hence values in the arguments will be 1,2,6.

The formula meeting the above assumptions would look as follows:

=CHOOSECOLS(A1:L10,1,2,6)

Now we need to apply this function in the corresponding cell A12 and press enter to generate the function result.

‍

As you can see, all the chosen columns appear, and these are dynamic, as any change in source data would instantly reflect the same in the output. 

Excel returns a #VALUE error if the absolute value of any of the col_num arguments is zero or exceeds the number of columns in the array.

Consider another scenario in the example dataset where we shall include another Column number 13 as one of the parameters in the function. The updated function would look as below.

=CHOOSECOLS(A1:L10,1,2,6,13)

Now, if you update this function in the Cell A12, it shall result in the error since the column number exceeds the number of columns in the array.

Look at another scenario where you want to showcase the summary table containing customer names, physical addresses, and email addresses. You can simply do so by updating the function as below:

=CHOOSECOLS(A1:L10,1,2,4,11)

Now, if you update this function in Cell A12, it shall result in the required dataset columns appearing adjacent to each other.

‍

When should you use the CHOOSECOLS formula in Excel? 

Here are some examples of when you might want to use the CHOOSECOLS function:

1. Retrieving the top or bottom N columns of data: Suppose you have a large dataset with many columns, and you want to extract only the first or last N columns based on a specific criterion, such as the highest or lowest values. You can use the CHOOSECOLS and other Excel function(s), such as MATCH.
2. Randomly selecting columns from a dataset: Suppose you have a dataset with many columns, and you want to select a few columns for analysis or further processing randomly. The RANDBETWEEN function helps you pick up some columns randomly from your dataset.

‍

What potential errors do you have while using the CHOOSEROWS formula in Excel?

If you encounter an error while using the CHOOSECOLS formula in Excel, it's most likely due to one of the following reasons.

1. A #VALUE! shows up when the col_num argument is either zero or exceeds the total number of columns in the array.
2. When you see a #NAME? error, you may misspell the function's name, or the function is not supported in your version of Excel.
3. If you see a #SPILL! error, it means that there are not enough empty cells to display the results. To resolve this error, you can clear the cells that are blocking the results.