Copilot in Excel With CHOOSECOLS and CHOOSEROWS: Practical Patterns

Business users often need to rearrange or extract specific columns and rows from large Excel tables without manually dragging data or writing complex formulas. The CHOOSECOLS and CHOOSEROWS functions, introduced in Excel 365, let you pick columns or rows by their numeric index directly within a formula. When combined with Copilot in Excel, you can generate these formulas faster by describing your intent in plain English. This article covers practical patterns for using CHOOSECOLS and CHOOSEROWS with Copilot, including reordering columns, extracting non-adjacent rows, and building dynamic reports. You will learn how to prompt Copilot to produce these formulas and how to adjust them for your specific data layout.

What Are CHOOSECOLS and CHOOSEROWS in Excel 365

CHOOSECOLS returns a new array made of specific columns from a given array or range. You provide the source array and then list the column numbers you want. For example, =CHOOSECOLS(A1:C10,1,3) returns columns A and C from the range A1:C10. The function preserves the original row count and order of rows.

CHOOSEROWS works the same way but extracts rows instead. =CHOOSEROWS(A1:C10,2,5) returns rows 2 and 5 from the range A1:C10. Both functions are part of the dynamic array family in Excel 365, meaning their output automatically spills into adjacent cells. You do not need to press Ctrl+Shift+Enter.

These functions require Excel 365 or Excel for the web. They are not available in Excel 2019, 2021, or earlier versions. If you are using an older version, you must use INDEX or OFFSET with manual cell references instead.

Why Use These Functions with Copilot

Copilot in Excel understands natural language requests about data manipulation. Instead of memorizing the syntax for CHOOSECOLS or CHOOSEROWS, you can describe what you need in a prompt. Copilot then inserts the correct formula into a cell. This reduces formula errors and speeds up repetitive tasks like building reports from large datasets. However, Copilot may not always infer the correct column order or handle non-contiguous columns perfectly. You should review the generated formula and adjust the column numbers if needed.

How to Prompt Copilot for CHOOSECOLS and CHOOSEROWS

Follow these steps to generate CHOOSECOLS or CHOOSEROWS formulas using Copilot in Excel. The steps assume you have a table or named range in your workbook.

1. Open Copilot in Excel
   Click the Copilot icon on the Home tab of the ribbon. The Copilot pane opens on the right side of the window.
2. Select the source data
   Click anywhere inside the table or range you want to extract columns or rows from. Copilot uses the active cell context to understand which data you mean.
3. Write your prompt
   Type a request like “Show me only columns 2 and 4 from this table” or “Get rows 1, 3, and 5 from my SalesData table”. Include the column or row numbers explicitly. Do not use vague terms like “some columns” or “a few rows.”
4. Review the generated formula
   Copilot inserts a formula suggestion in a cell near your active selection. Verify the formula uses the correct function name and the column or row numbers match your request. For example, =CHOOSECOLS(Table1,2,4).
5. Accept or modify the formula
   If the formula looks correct, press Enter to accept it. If Copilot inserted the wrong function or wrong indices, edit the formula directly in the formula bar. You can also type a refined prompt like “Use CHOOSECOLS instead of CHOOSEROWS.”

Practical Patterns for CHOOSECOLS with Copilot

The following patterns show common business scenarios where CHOOSECOLS saves time. Each pattern includes a sample Copilot prompt and the resulting formula.

Extract Non-Adjacent Columns for a Report

You have a table with 20 columns but only need columns 1, 3, 7, and 12 for a summary report. Prompt Copilot: “Extract columns 1, 3, 7, and 12 from my DataTable.” Copilot generates =CHOOSECOLS(DataTable,1,3,7,12). The output appears as a new dynamic array starting from the cell where you typed the prompt.

Reorder Columns Without Moving Source Data

You want columns in a different order than the source table. For example, move column 5 to the first position and keep column 2 as the second column. Prompt: “Show columns 5, 2, and 1 from the Products table.” The formula =CHOOSECOLS(Products,5,2,1) returns a three-column array with column 5 first, column 2 second, and column 1 third.

Extract Every Nth Column

For wide tables, you may want every second or third column. CHOOSECOLS combined with SEQUENCE handles this. Prompt: “Use CHOOSECOLS with SEQUENCE to get every second column starting from column 2.” Copilot may generate =CHOOSECOLS(Table1,SEQUENCE(,3,2,2)). This returns columns 2, 4, and 6. Adjust the SEQUENCE parameters to change the count, start index, and step.

Practical Patterns for CHOOSEROWS with Copilot

CHOOSEROWS is useful when you need specific rows from a dataset, such as top performers, sample records, or header rows plus selected data rows.

Extract Specific Rows for Spot Checking

You want to review rows 10, 25, and 50 from a 500-row table without scrolling. Prompt: “Show rows 10, 25, and 50 from my Orders table.” Copilot returns =CHOOSEROWS(Orders,10,25,50). The output includes only those three rows.

Combine Header with Selected Data Rows

To include the header row plus specific data rows, prompt: “Get row 1 and then rows 5, 8, and 12 from the Employees table.” The formula =CHOOSEROWS(Employees,1,5,8,12) places the header first, then rows 5, 8, and 12 in order. This is useful for creating custom extracts that still include column labels.

Extract Every Nth Row for Sampling

For statistical sampling, you may need every 10th row. Prompt: “Use CHOOSEROWS with SEQUENCE to get every 10th row starting from row 2.” Copilot may generate =CHOOSEROWS(Table1,SEQUENCE(10,1,2,10)). This returns rows 2, 12, 22, 32, and so on up to 10 rows. Adjust the first argument of SEQUENCE to change the number of rows returned.

Common Mistakes and Limitations

Copilot Returns an INDEX Formula Instead of CHOOSECOLS

If Copilot does not recognize your table as a modern Excel 365 table, it may suggest an INDEX formula. To force CHOOSECOLS, explicitly include the function name in your prompt. Example: “Write a CHOOSECOLS formula that returns columns 2 and 5 from Table1.” Also ensure your data is formatted as an Excel table using Ctrl+T or Insert > Table.

CHOOSECOLS or CHOOSEROWS Returns #VALUE! Error

This error occurs when the column or row number you specified exceeds the actual number of columns or rows in the source array. Check the source range dimensions. For a table with 10 columns, =CHOOSECOLS(Table1,11) returns #VALUE!. Adjust the index numbers to stay within the range.

Output Does Not Spill Correctly

If cells below or to the right of your formula contain data, Excel cannot spill the dynamic array. Clear the blocking cells or move the formula to an empty area. You can also wrap the formula inside IFERROR to handle blocked spill ranges, but the cleaner fix is to ensure the target area is empty.

CHOOSEROWS Ignores Filtered or Hidden Rows

CHOOSEROWS works on the underlying row numbers of the source array, not on visible rows. If your source table has filters applied, CHOOSEROWS still returns rows by their original index, not by their visible position. To extract only visible rows, use the AGGREGATE or SUBTOTAL function with FILTER instead.

Item	CHOOSECOLS	CHOOSEROWS
Function purpose	Extract specific columns from an array	Extract specific rows from an array
Primary argument	Column numbers, comma-separated	Row numbers, comma-separated
Can reorder output	Yes, order of column numbers determines output order	Yes, order of row numbers determines output order
Works with SEQUENCE	Yes, for every Nth column pattern	Yes, for every Nth row pattern
Spills output	Yes, dynamic array behavior	Yes, dynamic array behavior
Requires Excel 365	Yes	Yes

Both functions share the same dynamic array engine and syntax structure. Choose CHOOSECOLS when you need to reshape columns and CHOOSEROWS when you need specific rows. In many reports, you may use both together. For example, =CHOOSECOLS(CHOOSEROWS(Table1,1,10,20),2,4) extracts columns 2 and 4 from rows 1, 10, and 20 of Table1.

To get the most out of Copilot with these functions, always name your tables with descriptive names like SalesData or Inventory. Then prompt Copilot using the table name to improve formula accuracy. For advanced patterns, combine CHOOSECOLS with SORT or FILTER inside Copilot prompts to generate complete report formulas in one step.