Pick Rows or Columns with CHOOSEROWS / CHOOSECOLS

365CHOOSEROWS

Pull specific rows or columns out of a range — in any order — with CHOOSEROWS and CHOOSECOLS. Reorder columns for a report or grab just rows 1, 3, and 5 in one formula.

Quick formula: reorder columns 3, 1, 2 of a table:

=CHOOSECOLS(A2:E100, 3, 1, 2)

List the column numbers in the order you want them. CHOOSEROWS does the same for rows.

Functions used (tap for the full reference guide):

CHOOSEROWS CHOOSECOLS

The example

Reordering columns without cutting and pasting.

	A	B	C
1	Region	Name	Sales
2	East	Ann	95

The formula

Select and order columns (or rows):

=CHOOSECOLS(A2:E100, 3, 1, 2) =CHOOSEROWS(A2:E100, 1, 3, 5) // any columns/rows, any order

How it works

List the indexes you want, in order:

CHOOSECOLS(array, n1, n2, …) returns those columns, in the order listed — great for reordering a report.
CHOOSEROWS does the same for rows: CHOOSEROWS(array, 1, 3, 5).
Use negative indexes to count from the end: -1 is the last column.
Repeat an index to duplicate that row/column. The result spills as a new array.

Reorder lookups cleanly: XLOOKUP can return whole rows; wrap with CHOOSECOLS to present the fields in your preferred order without rearranging the source data. Combine with TAKE/DROP for precise slices.

Try it: interactive demo

Live demo

Columns Region,Name,Sales → reorder.

Order (e.g. 3,1,2)

Columns:

Variations

Pick rows

Rows 1, 3, 5:

=CHOOSEROWS(range, 1, 3, 5)

Last column

Negative index:

=CHOOSECOLS(range, -1)

Reorder a lookup

Fields in your order:

=CHOOSECOLS(XLOOKUP(k, ids, data), 2, 1)

Pitfalls & errors

365 only. Both are recent dynamic-array functions.

Index out of range. Asking for column 6 of a 5-column array returns #VALUE!.

1-based. The first column is 1, not 0.

Practice workbook

📊

Download the free Pick Rows or Columns with CHOOSEROWS / CHOOSECOLS practice workbook

CHOOSEROWS/CHOOSECOLS examples (formula text + result) with rows, negative-index, and lookup variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I reorder columns with a formula in Excel?

Use =CHOOSECOLS(range, 3, 1, 2) to return those columns in that order. CHOOSEROWS does the same for rows. Requires Excel 365.

How do I pick specific rows from a range?

Use =CHOOSEROWS(range, 1, 3, 5) to return rows 1, 3, and 5 in that order.

How do I get the last column?

Use a negative index: =CHOOSECOLS(range, -1) returns the last column.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: CHOOSEROWS · CHOOSECOLS