Yes, you can absolutely name a column and use the column name in your formulas instead of the the usual A1-style column reference. Here’s how.
Named ranges are pretty cool because you can take a formula like the following…
=FILTER(A:C, NOT(A:A=""))And, instead of saying “A:C” (which is rather abstract), you can give the column a descriptive name that reflects the data it contains. Then you can use those descriptive column names in your formulas like so:
=FILTER(id:last_name, NOT(id=""))In this example, we’ve got the following table of data.
| id | first_name | last_name |
| 1 | Bob | Smith |
| 2 | Susan | Bullerman |
| 3 | Steve | Martin |
We’ve got three columns: id, first_name, and last_name. We’ve added these labels in the first row, of course. But as it is, when referring to these columns in formulas, we still have to refer to the “id” column with the letter “A”, the “first_name” column with the letter “B”, and the “last_name” column with the letter “C”.
Obviously, what we’d like to be able to do is use these column names in our formulas instead.
To name your columns…
Repeat for each of your columns.
That’s all there is to it. Now, instead of using “A:A” in your formulas, you can use “id”, “first_name”, and “last_name”.
=COUNT(id)3Heck yeah!
Let’s say you have a named range called “LookUpTable”, containing a really important column, say, “ImportantColumn”.
How would you reference that particular field in your named range?
The first thing I would say is: I typically give each column its own name, rather than grouping multiple columns together and giving the group of columns a single name. In fact, you can have single column named ranges within another multi-column named range. For example, you could have a named range for Sheet1!A:C as well as a named range for Sheet1!C:C.
However, if you do group a set of columns together as a named range and don’t want to create named ranges for individual columns, you can use QUERY to reference a specific column within the range. For example, suppose you created a named range “myTable” for Sheet1!A:C. You could reference column C as follows:
=QUERY(myTable, "SELECT C")Hi Dave!
I have problem like that: I use Query function, and it can not understand column BY.
How can fix it? Help me, please.
Thank you,
Happy to help. If you’re able to share your sheet, please do so and I’ll be happy to take a look. If not, please share your formula.