# Google Sheets Query to select a large range of columns without manually entering each column

I have a column with dozens and dozens of columns in it that I'm querying data from. I'm using something similar to below to do this, but I'm wondering if there is an easier way? For instance, something like `"Select B:Z where K = X"` or `"Select B through Z where K = X"` or `"Select * excluding S where K = X"`?

``````=QUERY(data!B1:J30000, "Select B, C, D, E, F,.......
``````

Is it possible to select columns based on a 'range' instead of manually entering each column?

You can generate a comma-separated string of column names with a formula such as

``````=arrayformula(join(",", substitute(address(1, column(C:F), 4), "1", "")))
``````

(here C:F is the example of a range of columns). The above formula returns the string C,D,E,F which is suitable for use in a query. Optionally, the `arrayformula` part can go outside. Example:

``````=arrayformula(query(A1:F, "select " & join(",", substitute(address(1, column(C:F), 4), "1", "")) & " where A <> ''"))
``````

For a short range like C:F this obviously isn't worth the effort, but if you have something like `M:AT`, then it's probably going to save time.

### Explanation

• `column` returns a column number
• `address` returns A1 notation of the cell at the top of that column (option 4 means no \$ will be used)
• `substitute` removes row number 1
• `join` joins by commas

As long as your returned columns are continuous and include the item you are checking on, you can use the select *:

``````=QUERY(data!B1:J, "Select * where K = 5")
``````

Or some other Where clause that returns items other than blank rows.

If your columns are not continuous, or you want to change the order, I have not seen a method which allows for defining a range. You would need to list each column.

• Thank for your reply. I've used the wildcard before, but I'm looking specifically for something that allows me to pick 'ranges' and exclude or skip a few columns. Jan 9, 2017 at 23:07
• Wildcard is not the issue; it's about a long series of adjacent columns without having to specifically write each and every column in the "where" statement. Dec 17, 2018 at 9:48
• You can select columns using query({A2:C,E2:F} to limit the data worked on. If you are looking for a value in the entire range I would use other functions such as a lookup, find, or match. You might want to start a new thread for your specific need. Dec 20, 2018 at 13:01

Try this...

`=QUERY(TRANSPOSE(QUERY('Data'!A1:AH,"SELECT * ",1)),"SELECT * WHERE Col1 <> 'Column name'",1)`