Excel CHOOSECOLS function

Summary

The Excel CHOOSECOLS function is used to return specific columns from an array or range. You can specify which columns to extract by providing their numeric indices as separate arguments. This function is particularly useful for selectively retrieving data from a broader dataset, reorganizing columns, or simplifying complex arrays for analysis.
Syntax
				
					=CHOOSECOLS(array, col_num1, [col_num2], ...)
				
			
  • array: The array or range to extract columns from
  • col_num1: The numeric index of the first column to return
  • col_num2: [Optional] The numeric index of the second column to return, and so on
Return value

Extracted columns are combined into a single array.

How to use

To use CHOOSECOLS, first specify the array or range you want to extract columns from. Then, add the column indices as additional arguments. Each argument should be a whole number representing the column’s position in the source array.

Examples

Simple CHOOSECOLS
Extracting Specific Columns from a Range: To select the 1st and 3rd columns from a given range:
				
					=CHOOSECOLS(A1:C5, 1, 3)
				
			
This formula selects the 3rd and then the 1st column from the range A1:C5, effectively reversing their order in the output array.
CHOOSECOLS with Reversed Columns
Reversing Column Order: To retrieve columns in reverse order from a range:
				
					=CHOOSECOLS(A1:C5, 3, 1)
				
			
This formula selects the 3rd and then the 1st column from the range A1:C5, effectively reversing their order in the output array.
CHOOSECOLS with Out-of-Range Columns
Error Handling for Out-of-Range Columns: To demonstrate error handling when a requested column number is out of range:
				
					=CHOOSECOLS(A1:C5, 4)
				
			
Since the range A1:C5 only has three columns, requesting the 4th column results in a #VALUE! error.
CHOOSECOLS with Array Constants
Using Array Constants to Specify Columns: To select multiple columns using an array constant:
				
					=CHOOSECOLS(B3:F9, {1, 3, 5})
				
			
With the array constant {1, 3, 5}, this formula returns the 1st, 3rd, and 5th columns from the range B3:F9.
CHOOSECOLS with Dynamic Arrays
Reversing Columns Order Using Dynamic Arrays: To reverse the column order of a range dynamically:
				
					=CHOOSECOLS(A1:C5, SEQUENCE(COLUMNS(A1:C5),,COLUMNS(A1:C5),-1))
				
			
In this example, SEQUENCE generates a reversed array of column indices for CHOOSECOLS, returning the columns of A1:C5 in reverse order.

Additional Notes

  • CHOOSECOLS simplifies data manipulation tasks, especially when working with large datasets.
  • It’s an advanced function that can greatly aid in data analysis and report preparation by allowing selective column extraction.
  • Remember that CHOOSECOLS will return a #VALUE! error if any of the specified column numbers are outside the range of the source array.

Related Functions

Excel TAKE function

The Excel TAKE function extracts a specific subset of rows and columns from an array, ideal for isolating sections of a dataset.

Excel DROP function

The Excel DROP function trims arrays by removing specified rows and columns, perfect for focusing analysis on relevant data sections.

Excel EXPAND function

The Excel EXPAND function enlarges arrays by adding rows/columns, ideal for dataset dimension increase or padding with values.

Excel CHOOSEROWS function

The Excel CHOOSEROWS function selectively returns specified rows from a range, streamlining dataset reorganization or analysis.

Content Navigation