Quick Navigation
CHOOSECOLS Function
Summary
The CHOOSECOLS function extracts specific columns from an array and returns them in a new dynamic array. Perfect for data cleaning, reshaping datasets, and creating custom column views without complex formulas or helper columns.
Syntax
CHOOSECOLS(array, col_num1, [col_num2], ... )Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| array | Array/Range |
Yes | Source array containing columns to extract |
| col_num1 | Number |
Yes | First column number (1=first column, -1=last column) |
| col_num2 | Number |
No | Additional column numbers (optional, repeatable) |
Using the CHOOSECOLS Function
CHOOSECOLS revolutionizes array manipulation by letting you surgically extract and reorder columns from any array. Use positive numbers for forward indexing (1=first column), negative numbers for backward indexing (-1=last column), or combine both for ultimate flexibility in data reshaping.
Common CHOOSECOLS Examples
Extract Multiple Specific Columns
=CHOOSECOLS(A2:E7,1,3,5,1)Returns columns 1, 3, 5, and 1 again from a 5-column dataset. Great for duplicating key columns.
Get Last Two Columns
=CHOOSECOLS(A2:D7,3,4)Extracts the final two columns (3rd and 4th) from a 4-column range.
Reverse Order Last Columns
=CHOOSECOLS(A2:D7,-1,-2)Gets last column first (-1), then second-to-last (-2), reversing their natural order.
Frequently Asked Questions
Common Errors and Solutions
#VALUE!
Cause: Column number is 0 or exceeds array column count
Solution: Use valid column positions (1 to column count or -column count to -1)
#SPILL!
Cause: Output area contains data blocking the spill range
Solution: Clear cells in the spill area or move formula
Too few arguments
Cause: Missing required array or col_num1 parameters
Solution: Provide at least array and one column number
Notes
- Supports both positive (left-to-right) and negative (right-to-left) indexing
- Maximum 255 column arguments
- Works with single-column and multi-column arrays
- Ideal for creating custom report views from raw data
Compatibility
Available in: Excel 365, Excel 2021
Not available in: Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365, Excel 2021