How to Use the CHOOSECOLS and CHOOSEROWS Functions in Microsoft Excel

When analyzing large enterprise spreadsheets, source datasets often contain dozens of columns—many of which hold internal IDs, system metadata, or non-essential figures. Creating clean summary dashboards or executive reports previously required hiding unwanted columns, building brittle INDEX/MATCH formula chains, or duplicating data blocks manually. In modern Microsoft 365 versions of Microsoft Excel, dynamic array manipulation is streamlined by two specialized functions: CHOOSECOLS and CHOOSEROWS, allowing users to extract and reorder specific columns and rows instantly.

Understanding the Syntax of CHOOSECOLS and CHOOSEROWS

The CHOOSECOLS function returns specified columns from an array or range. Its sibling function, CHOOSEROWS, returns specified rows.

The syntax for CHOOSECOLS is defined as:

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

The syntax for CHOOSEROWS follows the identical pattern for rows:

=CHOOSEROWS(array, row_num1, [row_num2], ...)

The parameters operate as follows:

  • array (Required): The master table, range, or dynamic array to extract subsets from.
  • col_num1 / row_num1 (Required): An integer representing the index of the first column or row to return (1-indexed).
  • [col_num2], ... (Optional): Additional integer indices indicating subsequent columns or rows to extract.
  • Negative Indexing Support: Both functions support negative numbers. For example, -1 returns the last column/row in the array, -2 returns the second to last, etc.

Extracting and Reordering Non-Adjacent Columns

Suppose you have an inventory table in range A2:G500 containing columns: [1] SKU, [2] Category, [3] Cost, [4] Retail Price, [5] In Stock, [6] Supplier, [7] Margin. To generate a report containing only SKU, Retail Price, and Margin—while reordering Margin before Price:

=CHOOSECOLS(A2:G500, 1, 7, 4)

Excel immediately spills a 3-column table containing Column 1 (SKU), followed by Column 7 (Margin), followed by Column 4 (Retail Price). The underlying source range remains completely untouched.

Extracting Top and Bottom Rows with CHOOSEROWS

To extract specific records, such as the first row (headers) alongside the final row (totals) of a sorted sales table:

=CHOOSEROWS(A2:D100, 1, -1)

Using -1 guarantees that even if rows are inserted or appended to the dataset, the formula dynamically captures the final summary row without manual coordinate adjustments.

Combining CHOOSECOLS with SORT and FILTER

The true power of CHOOSECOLS emerges when paired with modern array functions. To filter transactions for the “Electronics” department, sort by revenue in descending order, and extract only Customer Name and Total:

=CHOOSECOLS(SORT(FILTER(A2:E200, B2:B200="Electronics"), 5, -1), 1, 5)

In this nested workflow:

  • FILTER isolates rows where Column B equals “Electronics”.
  • SORT orders the filtered records by Column 5 (Total) descending.
  • CHOOSECOLS extracts only Column 1 (Customer Name) and Column 5 (Total), discarding intermediate columns.

Passing Array Constants to Extract Sequential Blocks

Instead of typing individual column numbers, you can supply an array constant (e.g. {1, 3, 5}):

=CHOOSECOLS(A2:Z100, {1, 3, 5, 8})

Mastering CHOOSECOLS and CHOOSEROWS empowers Excel users to construct clean, modular, and dynamic reporting dashboards without complex lookup formulas or data duplication.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.