CHOOSECOLS vs CHOOSEROWS Function in Excel + Tips

Hello everyone! Today i will show you how to use CHOOSECOLS and CHOOSEROWS functions in Microsoft Excel step by step. Very easy and powerful formulas in Excel 365, Excel 2024, and Excel for Web to pick exact columns or rows you want!

1. What is CHOOSECOLS and CHOOSEROWS Function?

• CHOOSECOLS: Extract specific columns from a table or range by column numbers.
• CHOOSEROWS: Extract specific rows from a table or range by row numbers.

Think of CHOOSECOLS and CHOOSEROWS like custom selection tools:

• Need only Column 1 and Column 3? Use CHOOSECOLS(array, 1, 3).
• Need only Row 2 and Row 5? Use CHOOSEROWS(array, 2, 5).
• Want to reorder columns or rows (e.g. put column 3 before column 1)? Both formulas can reorder easily!

2. 🛠️ Formula Syntax & Parameters

=CHOOSECOLS(array, col_num1, [col_num2], ...)
=CHOOSEROWS(array, row_num1, [row_num2], ...)

• array: Required. The source table range or data array (e.g., A2:D6).
• col_num1 / row_num1: Required. The first column or row index to extract.
• [col_num2] / [row_num2]: Optional. Additional column or row index numbers (up to 126 numbers).
  • Positive number (e.g., 1, 3): Count from top/left (first row/column is 1).
  • Negative number (e.g., -1, -2): Count from bottom/right (last row/column is -1).

3. 📋 Practical Examples

Example Data Table (A2:D6)

Example 1: Extract Specific Columns with CHOOSECOLS

If you only want Name (Column 2) and Salary (Column 4):

=CHOOSECOLS(A2:D6, 2, 4)

👉 Result: Returns 2 columns only (Name and Salary), ignoring ID and Department!

Example 2: Reorder Columns (Put Salary First)

You can change column display sequence without moving real table data:

=CHOOSECOLS(A2:D6, 4, 2, 1)

👉 Result: Returns Salary first, then Name, then ID!

Example 3: Extract Specific Rows with CHOOSEROWS

Extract 1st row (John) and 3rd row (Sara):

=CHOOSEROWS(A2:D6, 1, 3)

👉 Result: Returns 2 rows only for John and Sara!

Example 4: Use Negative Numbers for Last Column / Last Row

• Extract the last column (Salary):

  =CHOOSECOLS(A2:D6, -1)
  

• Extract the last 2 rows (David and Emma):

  =CHOOSEROWS(A2:D6, -2, -1)
  

👉 Result: Negative index makes it super simple to get bottom rows or rightmost columns without counting total length!

Example 5: Combine CHOOSECOLS + CHOOSEROWS Together!

You can nest both formulas to get exact cell matrix (e.g., get last row, with Name and Salary columns only):

=CHOOSECOLS(CHOOSEROWS(A2:D6, -1), 2, 4)

👉 Result: Returns "Emma" and "$2,800"!

💡 Quick Tips for CHOOSECOLS & CHOOSEROWS

1. Use Negative Numbers to Pick From End: -1 is always the last row/column, -2 is second to last. You don't need COLUMNS() or ROWS() length math!
2. Reorder Columns or Rows Instantly: Swap column order like =CHOOSECOLS(A2:D6, 3, 1, 2) without modifying original source table!
3. Repeat Columns or Rows: You can pass the same index multiple times (e.g., =CHOOSECOLS(A2:D6, 1, 1, 2) duplicates column 1 twice).
4. Excel Version Requirement: Both functions are dynamic array formulas, available in Microsoft 365, Excel 2024, and Excel for Web.

📚 Related Resources & Tutorials

• TOCOL vs TOROW in Excel
• How to Use TAKE Function in Excel
• How to Use DROP Function in Excel
• Top 15 Basic Excel Formulas for Beginners ↗