How to Use CHOOSECOLS Function in Google Sheets

This guide will explain how to use the CHOOSECOLS function in Google Sheets to return specific columns from a target array.

Google Sheets offers multiple methods to filter data from a given range. However, trying to filter out specific columns from a range may be a challenge.

Suppose you want to use data in the range A1:H2000 but only want to return the first, second, and last columns. For scenarios like this, we can try using the CHOOSECOLS function. This function lets you specify what columns to return in a specified range.

In this guide, we will provide a step-by-step tutorial on how to use the CHOOSECOLS function in Google Sheets. We will look into each argument of the function and later explore a real example of the function in action.

Let’s dive right in!

 

The Anatomy of the CHOOSECOLS Function

The syntax of the CHOOSECOLS function is as follows:

=CHOOSECOLS(array, col_num1, [col_num2])

Let’s look at each argument to understand how to use the CHOOSECOLS function.

  • = the equal sign is how we start any function in Google Sheets.
  • CHOOSECOLS() refers to our CHOOSECOLS function. This function outputs a new array given an existing range and a set of column numbers to include from that range.
  • array refers to the array that contains the columns you want to output
  • col_num1 refers to the column number of the first column to return.
  • [col_num2] refers to the next column number you want to include in the final array output. The user can continue adding more arguments 
  • Providing a negative column number will indicate that you want to get the nth column starting from the right-most column. For example, -1 will return the last column in the array.
  • col_num arguments can also be provided as an array.

 

A Real Example of Using CHOOSECOLS Function in Google Sheets

Let’s explore a simple example where we can use the CHOOSECOLS function.

sample data

In the table above, we have four different metrics measuring monthly sales performance.

We want to create a new range that outputs the month and the last two metrics included in the table.

CHOOSECOLS function in Google Sheets to choose 1st, 4th, and 5th columns

We can output our desired columns by using the following formula:

=CHOOSECOLS(A1:E13,1,4,5)

The first argument of CHOOSECOLS indicates what range to retrieve the columns from. The following arguments determine what columns to include in the output.

Since we want to return the last two columns, we can also use negative numbers to refer to columns starting from the right.

CHOOSECOLS function in Google Sheets with negative numbers

In the image above, we used the value -1 and -2 to refer to the last two columns in our range.

CHOOSECOLS function in Google Sheets with array of numbers

We can also use an array of values as a parameter. In the image above, we added the array {1,4,5} as a parameter instead of adding three additional arguments to CHOOSECOLS.

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try working with CHOOSECOLS, head over to the next section to read our step-by-step breakdown on how to do it!

 

How to Use CHOOSECOLS Function in Google Sheets

Follow these steps to output specific columns from a range using CHOOSECOLS in Google Sheets.

  1. First, select an empty cell to place the CHOOSECOLS function. Ensure that there is enough space around the cell to fit the number of columns you want to return.
    select an empty cell
  2. Next, type the string “=CHOOSECOLS(“ to start the function.
    start CHOOSECULS function
  3. For the function’s first argument, select the range you want to choose columns from.
    select target rangeIn our table, we will use the range A1:E13.
  4. Next, add each column you want to output as a new argument.
    add column numbers to include in output
  5. Hit the Enter key to evaluate the function.
    hit the Enter key
  6. We can also use negative column numbers to indicate we want to count starting from the right-most column.
    use negative numbers
  7. We can also use an array to choose the column you want to include in the output.
    use an array of column numbers as an argument for CHOOSECOLS function in Google Sheets

That’s all the steps you need to follow to start using the CHOOSECOLS function to select specific columns in a range!

 

FAQs

Here are some frequently asked questions about this topic:

  1. Why does my CHOOSECOLS function return an error?
    Google Sheets will return a #VALUE error if the user provides a column number of 0 or a column number that exceeds the number of columns in the provided array.

    The function may also return a #REF error if there is existing data in your sheet that prevents the array result from expanding. Removing the existing data will solve this issue.

 

This tutorial covers everything you need to know to start using the CHOOSECOLS function in Google Sheets.

The CHOOSECOLS function is just one of many built-in functions available in Google Sheets. Another way we can extract data from a range is through the CHOOSEROWS function. You can read our step-by-step guide on how to use this function to extract rows instead of columns from a specified range.

You may also check our tutorial on the QUERY function in Google Sheets to learn another function that can filter and sort your spreadsheet data.

That’s all for this guide on the CHOOSECOLS function! Check out our library of spreadsheet resources, tips, and tricks! 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like