Quick Links
The CHOOSECOLS and CHOOSEROWS functions in Google Sheets are ideal for quickly extracting specific columns or rows from your data without creating confusing nested formulas. What’s more, since they’re dynamic functions, they adapt to changes in the source data.
To follow along as you read,create your own copyof the Google Sheets template. After you click the link,sign in to your Google account, and click “Make A Copy” to add a copy of the file to your Google Drive. Then, follow the tabs along the bottom of the Google Sheets window as you work through the examples below.
The CHOOSECOLS and CHOOSEROWS Syntaxes
Once you work out how to use one of these two functions inGoogle Sheets, you’ll have a good understanding of how the other one works.
a
Yes
The array that contains the columns (CHOOSECOLS) or rows (CHOOSEROWS) you want to extract.
b
The index number of the first column (CHOOSECOLS) or row (CHOOSEROWS) to be extracted.
A positive number counts from the left (CHOOSECOLS) or top (CHOOSEROWS) of the array, while a negative number counts from the right (CHOOSECOLS) or bottom (CHOOSEROWS).
c
No
The index numbers of any additional columns (CHOOSECOLS) or rows (CHOOSEROWS) to be extracted.
Each index number must be separated by commas.
If any of the index numbers are zero or exceed the number of columns or rows in the array, Google Sheets will return the #VALUE! error.
If an index number pulls a column or row from the center of the array, inserting extra columns or rows will change which column or row is extracted.
Example 1: Extracting the First and Last Columns or Rows
Since I learned how the CHOOSECOLS and CHOOSEROWS functions work, I’ve used them endlessly to extract the first and last columns or rows from my data. This is a particularly handy trick if the first column or row is a header, and the last column or row contains totals.
Extracting the First and Last Columns
In this example, let’s imagine you’re a business administrator, and you want to create a report that summarizes the number of products sold by each shop in the first two quarters of the year.
where
Rather than typing the array manually in argumenta, use your mouse to select the cells you want to reference.
Here’s the result when you press Enter:
Notice how, even though you only typed the formula into cell J1, the result has spilled from that cell to cell K12 to accommodate the number of columns and rows in the result.
Also, because the source data is in a structured table, if the data changes, so does the result of the CHOOSECOLS function. This is also true if further columns or rows are added to the dataset.
If you use direct cell references in argumentarather than a table-based reference, the formula won’t pick up any new data unless you change the cell references manually. Whether you’re using Google Sheets orMicrosoft Excel, I’d always recommend using tables ahead of unstructured cells, as they offer better tools and adaptability.
Now, you can duplicate the summary data on another Google Sheets spreadsheet (such as a dashboard tab) or aGoogle Docsdocument.
Extracting the First and Last Rows
By the same token, pulling the first and last rows is handy for generating horizontal summaries of your data.
Using the same example as before, including the added Shop K, let’s say you now want to summarize the total number of products sold each month.
In a blank cell, type:
and press Enter. As in the last example, adding[#ALL]after the table name tells Google Sheets that you want the header row to be counted as row 1 in the index lookup.
Example 2: Extracting Columns From More Than One Range
In Example 1, we extracted specific columns and rows from individual ranges. However, you can combine CHOOSECOLS with the VSTACK function to combine extractions from more than one range at the same time.
Here, you plan to generate a list of the total scores of each team in League 1 (red) and League 2 (green).
The first thing to note is that you don’t want to extract the column headers, since you want to stack the data from each table, one directly on top of the other. If you include the column headers, they’ll appear at the top of both datasets in the result, thus removing the sense that you’re looking at a combined dataset.
As a result, in cells J1 and K1, type the column headers manually.
When you press Enter, here’s the result you get:
You could go one step further and nest the whole formula inside the SORT function, so the summary table displays the teams in order of their overall scores.
To do this, double-click the cell where you previously typed the CHOOSECOLS-VSTACK combination, and add the following SORT arguments:
Impressively, if any values in the original dataset change, the summary table adjusts accordingly. Here, changing Team H’s Game 6 score from 51 to 89 promotes them from ninth position to fourth position.
Example 3: Making the CHOOSECOLS and CHOOSEROWS Functions Dynamic
So far, I’ve shown you how to use CHOOSECOLS and CHOOSEROWS to extract the first and last columns and rows from an array in Google Sheets. However, in this final example, I’ll show you how to extract other columns or rows dynamically by referencing cells—rather than using hard-coded index numbers—in the arguments.
I’m going to show you how to use CHOOSECOLS dynamically, though you may use the same principle with the CHOOSEROWS function.
This Google Sheets table shows five teams' scores over six games, as well as the combined total for each game, and your aim is to produce a summary according to the game number you specify in cell B9. First, type a game number into this cell so that you have something to work with when you generate your CHOOSECOLS formula.
Here’s what you get when you press Enter:
Now, type a different game number into cell B9 to see the result update accordingly.
Pro Tip: Add a Data Validation Rule
Although typing the game number into cell B9 works exactly as expected in making the CHOOSECOLS function dynamic, if an invalid number is entered, you’ll be abruptly met with a #VALUE! error. So, to prevent this from happening, you can use data validation to control the cell input parameters.
To do this, select cell B9, and in the Data tab, click “Data Validation.”
Then, in the Data Validation Rules pane on the right-hand side, click “Add Rule.”
Next, in the Criteria field, select “Dropdown,” and type the first possible input for this cell into the text field. Then, click “Add Another Item,” and repeat this process until all the inputs are stated. When all the options are listed, click “Done.”
You can also choose “Dropdown From A Range” in the Criteria field, where you can select cells that contain the valid entries. However, this doesn’t always work as expected with column headers, even if you name the range. Instead, set up a separate spreadsheet where you can keep all your data validation ranges.
Now, head back to cell B9, click the drop-down arrow, and select a different game number to see the result update accordingly.
The CHOOSECOLS and CHOOSEROWS functions in Google Sheets produce dynamic arrays, meaning the result spills out from the cell where you entered the formula. As a result, you must ensure there’s enough room in your spreadsheet before you enter your formula. Otherwise, you’ll see a #REF! error.
