How to use CHOOSECOLS and CHOOSEROWS

Google have recently added two useful new functions to Google Sheets, CHOOSECOLS and CHOOSEROWS, which allow us to easily show certain rows or columns we want from a data range.

Let’s see how they work with a couple of examples.

choosecols

Here, we have some questionnaire feedback on a group of teachers and the academy they work at.

I want to extract just the columns that relate to teachers and to change the order of the columns a little.

On a separate tab called TEACHERS, in cell A1, type in the following formula:

=CHOOSECOLS(ALL!A1:K,5,3,4,6,7,8,11)

The CHOOSECOLS function, gets an array of data, i.e. the original table of data on the sheet ALL, and then you state which column numbers you want to show. For example, column 5 is column E.

This extracts the data I want:

Note, you may need to format the data a bit afterwards.

---

chooserows

We can also choose specific rows we want. Here, we have the exam results of a group of students per term stored on the EXAM MARKS-YEAR sheet.

I want to show just the term 1 exam marks on another sheet.

On a separate tab in cell A1, type in the following formula:

=CHOOSEROWS(‘EXAM MARKS-YEAR’!A1:C,1,2,3,4,5,6)

The CHOOSEROWS function, gets an array of data, i.e. the original table of data on the sheet EXAM MARKS-YEAR, and then you state which row numbers you want to show. Like the CHOOSECOLS you can change the order of them.

This shows just the term 1 data:

You could achieve the same with the FILTER function and QUERY function but one of the good things about the above two functions is their simplicity.

---

Minus numbers

The above examples are working from left to right and top to bottom, but you can also use minus numbers to state how many columns or rows from the right or bottom you want.

You may want to do that when there are lots of rows or columns on your sheet, or if you are using a formula to determine which rows or columns to use.

For example, the following formula produces the same result as the first example (questionnaire feedback):

=CHOOSECOLS(ALL!A1:K,-7,-9,-8,-6,-5,-4,-1)

And the following formula produces the same result as the second example (exam marks):

=CHOOSEROWS(‘EXAM MARKS-YEAR’!A1:C16,-16,-15,-14,-13,-12,-11)

Note, I had to change the open-ended range so that I define where the last row is and it counts up from there.

---

Learn more about Google Sheets here.