Using Google Spreadsheets, you can write queries. However, if you have column letters in quotes, then they aren't updated as column order changes.

Is there a way to write these queries so they don't need to be updated every time a column is added or removed?

Is it possible to use named ranges in queries to solve this problem?

Here's an example: If you add a column after 'F', then column 'G' gets pushed to 'H' and the meaning of the formula changes.

=Query(B:J,"select avg(J) group by G")

Related questions

This question is not the same as Using Query with column headers instead of column letters because this one is focused on the use of named ranges.


5 Answers 5


It's a kind of tricky, but it is possible with a helper Range and some concatenation.

What needs to be done:

  1. Create a named range, COLS, to carry the column letters like this:


    Do it in a vertical way as shown.

  2. Assemble the query string like this:

  • 1
    The named range will work both in vertical or horizontal shape. Commented Aug 11, 2016 at 0:18
  • @Rubén is probably right.
    – Oneide
    Commented Nov 15, 2016 at 0:07

Is there a way to write these queries so they don't need to be updated every time a column is added or removed?

Is it possible to use named ranges in queries to solve this problem?

Yes, you could use named ranges instead of built-in column headers but the basic idea is the same as other answers to this an other similar questions in this site suggested: the spreadsheet should be set to build a dynamic SQL string.

Let say that J and G corresponds to named ranges Grades and Class respectively.

The following approach works only when the columns are between the columns A and Z

CELL("address",Grades) will return "$J:$J". To get the column reference use MID(CELL("address",Grades),2,1) to get "J". The final formula is

") GROUP BY "&

The following approach works for all cases

COLUMN(Grades) will return an integer (a number of the form 1,2,3 etc.) To simplify the formula to create the SQL statement argument I suggest to force SQL to use column references of the form COL1, COL2, etc. by using the array notation ({B:J}). The final formula is

=QUERY({B:J},"SELECT AVG(Col"&COLUMN(Grades)-1&") GROUP BY Col"&COLUMN(Class)-1)

Demo Spreadsheet


Using arrays in Google Sheets - Google Docs editors Help


Ruben's answer doesn't solve the problem for all cases because MID(CELL("address",Grades),2,1) fails if the Grades range is moved to a double-letter column like $AA:$AA.

In that case, the formula MID(CELL("address",Grades),2,2) is needed. For named ranges that span or abut the $Z:$AA range, this formula becomes problematic.

The formula REGEXEXTRACT( CELL("address", Grades), "\$([a-zA-Z]+)\$") solves the problem for the general case, but I strongly question its CPU efficiency when used all over a large spreadsheet.

  • 1
    In my answer there are two approachs but this answer only critizes one of them. The second approach should work for all cases. Commented Aug 17, 2016 at 20:19
  • I dont think this will work for named ranges that are on on different sheets. Wondering if +2 years since, if there are better solutions to this...such as simply being able to reference the darn named ranges in the query itself
    – mike01010
    Commented Oct 15, 2019 at 3:46

I came across this page at infoinspired.com that explains one way of doing it. I tested it exactly as in the example he gave and it worked. The setup:

      A        B        F                                     G
 1  names     Age   =left(address(row(B1),column(B1),4))   =QUERY(A:B,"Select "&Age)
 2  joey      32
 3  nicole    26
 4  sally     66

His explanation of F1: The ADDRESS formula which includes the ROW and COLUMN functions returns the cell address of B1. The LEFT function extracts the first letter that is the column heading from it.

--- Click on cell F1 and then go to the menu Data > Named ranges and name the range "Age" (without quotation marks)

Now you can formulate your query as: =QUERY(A:B,"Select "&Age)

He's got more complicated examples in his page that I'm about to test now, but I'm encouraged that the first example worked!

Edit - sorry if this is exactly what others have been proposing with their answers. I just found this example crystal clear and was able to figure it out. Thought it might help others with the same question.


I was also attempting to solve this issue and came up with a short script that allows you to write queries like this:

You use it like this:

=QUERY(A9:C13, QueryByName(A9:C9, "SELECT `Name`, `country` WHERE `age`>7"))

You can easily replace the two ranges above with named ranges if desired, and if a new column is added to the data set at a later point, you shouldn't need to update the query. 🎉

Using Google Sheets QUERY with column headings/labels instead of letter

I posted the code and more detailed instructions in another thread, see here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.