How to Use the CHOOSECOLS and CHOOSEROWS Functions in Excel to Extract Data

1 week ago 5

A Microsoft Excel spreadsheet with a file and a enactment selected.

Microsoft Excel's CHOOSECOLS and CHOOSEROWS functions are a game-changer if you privation to rapidly extract circumstantial columns oregon rows from your information without nesting respective functions successful your formula. What's more, they're dynamic functions, meaning they accommodate to changes successful your dataset.

At the clip of penning (May 2025), lone radical moving with Excel for Microsoft 365 connected Windows oregon Mac oregon Excel for the web tin usage these functions.

The CHOOSECOLS and CHOOSEROWS Syntaxes

Excel's CHOOSECOLS and CHOOSEROWS functions are similar twins: their DNAs are precise similar, but they're separated by subtle differences. The aforesaid tin beryllium said for their syntaxes.

Here's the syntax for CHOOSECOLS:

=CHOOSECOLS(a,b,c,…)

And here's the syntax for CHOOSEROWS:

=CHOOSEROWS(a,b,c,…)

In some cases,

a (required) is the archetypal array that contains the columns (CHOOSECOLS) oregon rows (CHOOSEROWS) you privation to extract,
b (required) is the scale fig of the archetypal file (CHOOSECOLS) oregon enactment (CHOOSEROWS) to beryllium extracted,
c (optional) represents the scale numbers of immoderate further columns (CHOOSECOLS) oregon rows (CHOOSEROWS) to beryllium extracted, each of which indispensable beryllium separated by commas.

If an scale fig represents a file oregon enactment successful the halfway of the array (in different words, not the archetypal oregon past file oregon row), adding other columns oregon rows to the halfway of the array volition alteration the indexed file oregon row.

You mightiness beryllium reasoning that these functions dependable precise akin to the TAKE function. However, wherever TAKE lets you extract the archetypal oregon past x columns oregon rows, oregon a azygous file oregon enactment from elsewhere successful a named table, CHOOSECOLS and CHOOSEROWS fto you extract immoderate fig of columns successful immoderate bid from anyplace successful your data.

How to Use TAKE successful Excel to Extract Data

Extract circumstantial information from your Excel table.

Example 1: Extracting the First and Last Column oregon Row From a Table

I've already mislaid number of the fig of times I've utilized CHOOSECOLS and CHOOSEROWS to extract the archetypal and past file oregon enactment from a table. This is peculiarly useful if the archetypal file oregon enactment is simply a header, and the past file oregon enactment contains totals.

Imagine you're an head for a section sports league, and you're producing a study that summarizes the points scored by 5 teams crossed 5 games.

An Excel array containing 5 teams, their scores successful 5 games, and a full enactment and column.

The archetypal dataset you privation to make volition archer you the full fig of points scored wide by each team. To bash this, successful a blank cell, type:

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

where

CHOOSECOLS is the relation you privation to use, since you're extracting the information from the Team and Total columns,
T_Games is the sanction of the array wherever the array is stored, and [#All] tells Excel that you privation to see the header and full rows successful the result,
1 is the archetypal file (in this case, the file named "Team"), and
-1 is the past file (in this case, the file named "Total")

and property Enter.

By default, CHOOSECOLS counts from near to right, and CHOOSEROWS counts from apical to bottom. To reverse this, spot the minus awesome (-) successful beforehand of the applicable scale numbers.

Here's the effect you get erstwhile you property Enter, and this information tin beryllium duplicated connected different expanse successful the aforesaid workbook, specified arsenic a dashboard tab, oregon copied and pasted arsenic substance successful an email oregon Word document.

An Excel expanse containing an array of information successful a table, and the effect of a CHOOSECOLS formula.

Now, adjacent if much information is added, the result—including the Total row—updates accordingly.

An Excel expanse containing an array of information successful a table, and the effect of a CHOOSECOLS look aft much information is added.

You could besides usage the aforesaid method with unformatted tables by utilizing nonstop compartment references alternatively than structured references. However, if you adhd much rows to the close oregon columns to the bottommost of your array, the look won't prime these up unless you alteration the compartment references manually. Also, mostly speaking, tables connection amended tools and adaptability than unstructured cells.

The adjacent study you privation to make volition amusement the fig of points scored successful each crippled (rows 1 and 8).

So, successful a blank cell, type:

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

and property Enter. Remember that adding [#ALL] aft the array sanction successful the look forces Excel to number the header and full rows erstwhile addressing the scale numbers.

An Excel expanse containing an array of information successful a table, and the effect of a CHOOSEROWS formula.

Example 2: Extracting Columns From More Than One Range

Now, let's accidental you person this spreadsheet, and your purpose is to nutrient a database containing the full scores of each squad crossed Leagues 1 (green), 2 (blue), and 3 (gray).

An Excel expanse containing each team's scores crossed 3 leagues.

This time, you don't privation Excel to extract the file headers and enactment totals, since you're going to nest the VSTACK relation wrong the CHOOSECOLS look to stack the 3 tables straight connected apical of each other. So, successful cells I1 and J1, make the file headers manually.

Column headers person been entered manually into an Excel spreadsheet, acceptable for the CHOOSECOLS effect to beryllium placed underneath.

How to Combine, Reshape, and Resize Arrays successful Excel

Take power of the arrays successful your spreadsheet and put them arsenic you please.

Then, successful compartment I2, type:

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

where

CHOOSECOLS is the relation you'll usage to extract columns,
VSTACK lets you harvester the results vertically,
League_1,League_2,League3 are the names of the tables that correspond the arrays, and the lack of [#ALL] aft the array names tells Excel not to see the header and full rows successful the result,
1 tells Excel to extract the archetypal file ("Team") from each array, and
-1 tells Excel to extract the past file from each array ("Total")

and property Enter.

The effect of the CHOOSECOLS being utilized with VSTACK to extract the archetypal and past columns from 3 tables successful Excel.

At this point, you could spell 1 measurement further and benignant the effect successful descending bid by typing:

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

into compartment L2, and pressing Enter.

An Excel expanse containing a effect produced by combining CHOOSECOLS and VSTACK, and a 2nd effect that uses SORTBY to put the data.

An Excel spreadsheet with '=A2#' written connected it and a blurred laptop successful the background.

Example 3: Using CHOOSECOLS With Data Validation and Conditional Formatting

So far, I've shown you the benefits of utilizing CHOOSECOLS and CHOOSEROWS to extract the archetypal and past columns and rows from an array. However, this time, I'll amusement you however you tin usage CHOOSECOLS to extract different columns, and harvester this with further Excel tools to marque your spreadsheet basal out.

This spreadsheet shows 5 teams' scores implicit 5 games, including the full points scored successful each crippled successful the full row. Your purpose present is to nutrient a effect that shows each team's people and the wide full successful cells A11 to B15 for a crippled fig you volition participate into compartment B9.

An Excel expanse containing 5 teams' scores implicit 5 games, and an country beneath this array wherever the CHOOSECOLS volition extract data.

First, benignant a crippled fig into compartment B9, truthful that you person thing to enactment with erstwhile you make your CHOOSECOLS formula. Then, successful compartment A11, type:

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

where

CHOOSECOLS is the look utilized to extract columns,
T_Scores is the sanction of the table, and [[#Data],[#Totals]] tells Excel to see the information and the totals successful the result, but not the header row,
1 represents the archetypal file ("Totals"), and
B9+1 tells Excel that the 2nd scale statement is represented by the worth successful compartment B9, positive one. The crushed you request to see +1 present is due to the fact that the crippled numbers commencement successful the 2nd file of the table. As a result, typing 3 into compartment B9 extracts the information from the 4th file of the table, which is crippled 3.

An Excel expanse containing a CHOOSECOLS look that uses a worth successful different compartment to find a file scale number.

Although typing the crippled fig into compartment B9 works well, if you oregon idiosyncratic other accidentally participate an invalid number, the CHOOSECOLS relation volition instrumentality a #VALUE error.

Excel returns a #VALUE mistake if immoderate of the scale numbers are zero oregon transcend the fig of columns oregon rows successful the array.

This is simply a bully lawsuit for utilizing information validation successful compartment B9 to make a drop-down database of the disposable crippled numbers.

Microsoft Excel unfastened connected a Mac.

How to Add a Drop-Down List to a Cell successful Excel

It beats typing successful the aforesaid options 200 antithetic times manually.

My preferred mode to bash this for a header enactment successful an Excel array is to archetypal prime each the applicable cells, positive a fewer other to let for growth, and sanction the scope successful the sanction container successful the top-left country of the Excel window. Notice however I've not selected the file header "Team," due to the fact that I don't privation this to look successful the crippled drop-down database successful compartment B9.

The file headers successful an Excel table, positive a fewer other cells to the right, are selected and named Games successful the sanction container successful Excel.

Next, prime compartment B9, and click the apical fractional of the "Data Validation" fastener successful the Data tab connected the ribbon.

A compartment successful Excel is selected, and the Data Validation fastener successful the Data tab is selected.

Now, successful the Data Validation dialog box, prime "List" successful the Allow field, marque definite "Ignore Blank" is checked, benignant an adjacent awesome (=) followed by the sanction you conscionable gave to the file header range, and click "OK."

The Data Validation dialog container successful Excel, with List selected, Ignore Blank checked, =Games typed, and the OK fastener highlighted.

You tin present click the drop-down arrow successful compartment B9 to prime a valid crippled number, harmless successful the cognition that adjacent if you adhd other columns to your table, CHOOSECOLS and the named scope successful information validation volition enactment unneurotic to update the disposable options accordingly.

A information validation drop-down successful Excel, moving alongside the CHOOSECOLS relation to nutrient an extracted dataset.

Finally, to visualize your information adjacent further, prime cells B11 to B15, and successful the Home tab connected the ribbon, click "Conditional Formatting," hover implicit "Data Bars," and take a coagulated capable color.

Some cells are selected successful Excel, and the Data Bars options successful the Conditional Formatting drop-down paper are highlighted.

As a result, not lone person you extracted circumstantial columns from your information utilizing CHOOSECOLS, but you've besides made the effect dynamic by adding a drop-down list, and you've formatted the information to adhd ocular clarity.

An Excel expanse containing the effect of a CHOOSECOLS look based connected a drop-down list, and the figures are besides formatted conditionally with information bars.

CHOOSECOLS and CHOOSEROWS nutrient dynamic arrays, meaning the effect spills retired from the compartment wherever you entered the formula. As a result, earlier you participate your formula, marque definite there's capable country successful your spreadsheet—otherwise, you'll get the dreaded #SPILL! error. Also, since dynamic arrays and formatted Excel tables don't mix, if you privation to format the result, you'll person to bash truthful manually oregon by utilizing conditional formatting.

Read Entire Article