The Problem of Massive Datasets
When you download a report from an enterprise CRM like Salesforce or a database like SQL Server, the resulting Excel file is often overwhelming. It might contain 50 columns of data (IDs, Addresses, Phone Numbers, Status Codes, etc.).
If you are building a presentation for the sales team, you probably only need three columns: “Customer Name,” “Region,” and “Total Revenue.” Historically, you had to manually highlight the other 47 columns, right-click, and select “Hide,” or painstakingly copy and paste the three columns you wanted onto a new tab.
With the introduction of Dynamic Arrays in Microsoft 365, Excel introduced two incredibly powerful functions designed specifically for data extraction: CHOOSECOLS and CHOOSEROWS. These functions allow you to instantly rip specific columns or rows out of a massive table and spill them into a clean, dynamic array.
1. Extracting Columns with CHOOSECOLS
The CHOOSECOLS function is wonderfully simple. You feed it a large array of data, and then you give it the specific index numbers of the columns you want to keep.
The Syntax
=CHOOSECOLS(array, col_num1, [col_num2], ...)Example Scenario
Assume you have a massive table spanning A2:Z1000.
- Column A (Column 1) is Customer Name.
- Column D (Column 4) is Region.
- Column Z (Column 26) is Total Revenue.
On a brand new tab, click in cell A2 and type:
=CHOOSECOLS(DataTab!A2:Z1000, 1, 4, 26)Hit Enter. Excel will instantly ignore the other 23 columns and spill a perfectly clean, three-column table containing only the data you requested. If the master data on the first tab changes, your clean extraction updates instantly.
Reordering Columns on the Fly
CHOOSECOLS has a brilliant secondary feature: it extracts the columns in the exact order you request them.
If you want the Total Revenue (Column 26) to appear first in your new table, you just change the order of the numbers in the formula:
=CHOOSECOLS(DataTab!A2:Z1000, 26, 1, 4)2. Extracting Rows with CHOOSEROWS
CHOOSEROWS operates on the exact same logic, but horizontally. You feed it an array, and it returns only the specific rows you request.
The Syntax
=CHOOSEROWS(array, row_num1, [row_num2], ...)Example Scenario
Suppose you have a leaderboard of 500 sales reps, sorted by revenue. You only want to extract the Top 3 reps to put on a dashboard.
=CHOOSEROWS(A2:C500, 1, 2, 3)This instantly extracts row 1, row 2, and row 3, bringing all their associated columns along with them.
3. The Power of Negative Numbers
Both functions support negative indexing, which tells Excel to count backward from the end of the data.
If you have a dataset where new sales are constantly added to the bottom, and you want to instantly extract the very last transaction that occurred (the bottom row of the table), you don’t need to know if the table has 500 rows or 5,000 rows.
You just use -1.
=CHOOSEROWS(A2:C5000, -1)This will always pull the absolute last row of data in the array.
Conclusion
CHOOSECOLS and CHOOSEROWS eliminate the need for manual hiding, deleting, and copy-pasting. By allowing users to programmatically extract and reorder specific slices of massive datasets, these functions enable the creation of clean, dynamic dashboards that are permanently linked to the raw source data.