Microsoft Excel’s CHOOSECOLS and CHOOSEROWS capabilities are a game-changer if you wish to shortly extract particular columns or rows out of your information with out nesting a number of capabilities in your components. What’s extra, they’re dynamic capabilities, which means they adapt to adjustments in your dataset.

On the time of writing (Might 2025), solely folks working with Excel for Microsoft 365 on Home windows or Mac or Excel for the online can use these capabilities.

The CHOOSECOLS and CHOOSEROWS Syntaxes

Excel’s CHOOSECOLS and CHOOSEROWS capabilities are like twins: their DNAs are very comparable, however they’re separated by delicate variations. The identical could be stated for his or her syntaxes.

This is the syntax for CHOOSECOLS:

=CHOOSECOLS(a,b,c,…)

And here is the syntax for CHOOSEROWS:

=CHOOSEROWS(a,b,c,…)

In each circumstances,

• a (required) is the unique array that comprises the columns (CHOOSECOLS) or rows (CHOOSEROWS) you wish to extract,
• b (required) is the index variety of the primary column (CHOOSECOLS) or row (CHOOSEROWS) to be extracted,
• c (elective) represents the index numbers of any further columns (CHOOSECOLS) or rows (CHOOSEROWS) to be extracted, every of which should be separated by commas.

If an index quantity represents a column or row within the heart of the array (in different phrases, not the primary or final column or row), including further columns or rows to the middle of the array will change the listed column or row.

You is likely to be pondering that these capabilities sound similar to the TAKE perform. Nonetheless, the place TAKE helps you to extract the primary or final x columns or rows, or a single column or row from elsewhere in a named desk, CHOOSECOLS and CHOOSEROWS allow you to extract any variety of columns in any order from anyplace in your information.

Instance 1: Extracting the First and Final Column or Row From a Desk

I’ve already misplaced rely of the variety of occasions I’ve used CHOOSECOLS and CHOOSEROWS to extract the primary and final column or row from a desk. That is notably helpful if the primary column or row is a header, and the final column or row comprises totals.

Think about you are an administrator for a neighborhood sports activities league, and also you’re producing a report that summarizes the factors scored by 5 groups throughout 5 video games.

The primary dataset you wish to generate will let you know the entire variety of factors scored total by every workforce. To do that, in a clean cell, kind:

=CHOOSECOLS(T_Games[#All],1,-1)

the place

• CHOOSECOLS is the perform you wish to use, because you’re extracting the info from the Workforce and Complete columns,
• T_Games is the title of the desk the place the array is saved, and [#All] tells Excel that you just wish to embrace the header and complete rows within the end result,
• 1 is the primary column (on this case, the column named “Workforce”), and
• -1 is the final column (on this case, the column named “Complete”)

and press Enter.

By default, CHOOSECOLS counts from left to proper, and CHOOSEROWS counts from prime to backside. To reverse this, place the minus image (-) in entrance of the related index numbers.

This is the end result you get once you press Enter, and this information could be duplicated on one other sheet in the identical workbook, equivalent to a dashboard tab, or copied and pasted as textual content in an electronic mail or Phrase doc.

Now, even when extra information is added, the end result—together with the Complete row—updates accordingly.

You might additionally use the identical methodology with unformatted tables through the use of direct cell references quite than structured references. Nonetheless, in case you add extra rows to the suitable or columns to the underside of your array, the components will not decide these up until you modify the cell references manually. Additionally, typically talking, tables offer better tools and adaptability than unstructured cells.

The subsequent report you wish to generate will present the variety of factors scored in every recreation (rows 1 and eight).

So, in a clean cell, kind:

=CHOOSEROWS(T_Games[#All],1,-1)

and press Enter. Keep in mind that including [#ALL] after the desk title within the components forces Excel to rely the header and complete rows when addressing the index numbers.

Instance 2: Extracting Columns From Extra Than One Vary

Now, for example you might have this spreadsheet, and your purpose is to provide an inventory containing the entire scores of every workforce throughout Leagues 1 (inexperienced), 2 (blue), and three (grey).

This time, you do not need Excel to extract the column headers and row totals, since you are going to nest the VSTACK perform throughout the CHOOSECOLS components to stack the three tables straight on prime of one another. So, in cells I1 and J1, create the column headers manually.

Then, in cell I2, kind:

=CHOOSECOLS(VSTACK(League_1,League_2,League_3),1,-1)

the place

• CHOOSECOLS is the perform you will use to extract columns,
• VSTACK helps you to mix the outcomes vertically,
• League_1,League_2,League3 are the names of the tables that symbolize the arrays, and the absence of [#ALL] after the desk names tells Excel to not embrace the header and complete rows within the end result,
• 1 tells Excel to extract the primary column (“Workforce”) from every array, and
• -1 tells Excel to extract the final column from every array (“Complete”)

and press Enter.

At this level, you can go one step additional and kind the lead to descending order by typing:

=SORTBY(I2#,J2:J16,-1)

into cell L2, and urgent Enter.

Instance 3: Utilizing CHOOSECOLS With Information Validation and Conditional Formatting

Thus far, I’ve proven you the advantages of utilizing CHOOSECOLS and CHOOSEROWS to extract the primary and final columns and rows from an array. Nonetheless, this time, I am going to present you the way you need to use CHOOSECOLS to extract different columns, and mix this with further Excel instruments to make your spreadsheet stand out.

This spreadsheet reveals 5 groups’ scores over 5 video games, together with the entire factors scored in every recreation within the complete row. Your purpose right here is to provide a end result that reveals every workforce’s rating and the general complete in cells A11 to B15 for a recreation quantity you’ll enter into cell B9.

First, kind a recreation quantity into cell B9, so that you’ve got one thing to work with once you generate your CHOOSECOLS components. Then, in cell A11, kind:

=CHOOSECOLS(T_Scores[[#Data],[#Totals]],1,B9+1)

the place

• CHOOSECOLS is the components used to extract columns,
• T_Scores is the title of the desk, and [[#Data],[#Totals]] tells Excel to incorporate the info and the totals within the end result, however not the header row,
• 1 represents the primary column (“Totals”), and
• B9+1 tells Excel that the second index argument is represented by the worth in cell B9, plus one. The explanation you want to embrace +1 right here is as a result of the sport numbers begin within the second column of the desk. In consequence, typing 3 into cell B9 extracts the info from the fourth column of the desk, which is recreation 3.

Though typing the sport quantity into cell B9 works nicely, in case you or another person by chance enter an invalid quantity, the CHOOSECOLS perform will return a #VALUE error.

Excel returns a #VALUE error if any of the index numbers are zero or exceed the variety of columns or rows within the array.

It is a good case for utilizing information validation in cell B9 to create a drop-down listing of the accessible recreation numbers.

My most popular means to do that for a header row in an Excel desk is to first choose all of the related cells, plus a couple of further to permit for development, and title the vary within the title field within the top-left nook of the Excel window. Discover how I’ve not chosen the column header “Workforce,” as a result of I do not need this to look within the recreation drop-down listing in cell B9.

Subsequent, choose cell B9, and click on the highest half of the “Information Validation” button within the Information tab on the ribbon.

Now, within the Information Validation dialog field, choose “Listing” within the Permit subject, make sure that “Ignore Clean” is checked, kind an equal image (=) adopted by the title you simply gave to the column header vary, and click on “OK.”

Now you can click on the drop-down arrow in cell B9 to pick a legitimate recreation quantity, secure within the data that even in case you add further columns to your desk, CHOOSECOLS and the named vary in information validation will work collectively to replace the accessible choices accordingly.

Lastly, to visualise your information even additional, choose cells B11 to B15, and within the Dwelling tab on the ribbon, click on “Conditional Formatting,” hover over “Information Bars,” and select a stable fill coloration.

In consequence, not solely have you ever extracted particular columns out of your information utilizing CHOOSECOLS, however you have additionally made the end result dynamic by including a drop-down listing, and you have formatted the info so as to add visible readability.

---

CHOOSECOLS and CHOOSEROWS produce dynamic arrays, which means the end result spills out from the cell the place you entered the components. In consequence, earlier than you enter your components, make sure that there’s sufficient room in your spreadsheet—in any other case, you will get the dreaded #SPILL! error. Additionally, since dynamic arrays and formatted Excel tables do not combine, if you wish to format the end result, you will have to take action manually or through the use of conditional formatting.