How to Use Label Clause in Google Sheets

The label clause in Google Sheets is useful when you need to set labels or remove existing labels for one or more columns in a QUERY formula.

You can set labels to any column in the given data range and any output of aggregation functions and arithmetic operators.

Let’s take a look at a quick example of where this clause might be useful in a query. 

To prepare for a monthly report, you’ve used the QUERY function to aggregate and filter a large dataset in Google Sheets. The data itself is valid, but the column names are not very descriptive, mainly since the raw data includes vague column names such as employee_id_x, cust_shop_id, or grass_date. 

Fortunately, the Google Visualization API Query Language includes adding new column names using the label clause. We can use this clause to rename one or more columns or even set new labels for aggregate functions such as sum or max.

This guide will show you how to use the label clause in Google Sheets and show you real examples of it in use.

 

 

A Real Example of Using Label Clause in a Query

Let’s look at a real example of the label clause being used in a Google Sheets spreadsheet.

In the example below, we have employee data consisting of the employee’s name, department, and their average weekly output. The second table is a filtered dataset consisting of employees in Department B. 

Use the label clause in google sheets to rename a column name

 

This was accomplished using the QUERY function. In addition to filtering, we also added a new label to column A. Observe how the column labeled Name is now labeled “Employee Name”.

To get the values in the query output, we just need to use the following formula:

=QUERY(A1:C7,"SELECT * where B = 'B' label A 'Employee Name'")

In the formula above, the label clause consists of the following three parts: “label”, “A”, and “Employee Name”.

The syntax for a single label clause is as follows:

label column_id label_string

If using a range like A1:J10, the column_id refers to the actual letter of the column, such as A or B. The label_string refers to the label you want to assign to the specified column_id

We can also add multiple labels in our label clause. In the example below, we changed each of the column names in the original dataset.

Example of changing multiple labels using the label clause in google sheets

 

To get the values in the query output, we just need to use the following formula:

=QUERY(A1:C7,"SELECT * where B = 'B' label A 'Employee Name', B 'Dept. Name', C 'Output'")

When adding multiple labels, we only need to indicate the clause name once. Each successive pair is separated by a comma.

In this final example, we’ll show you how simple it is to use the label clause to name the output of aggregate values.

Below, you can see that we used a sum aggregation function in our query and grouped it by department. We used the label clause to name our sum “Total_Weekly_Output”. If we did not label it, it would just say “sum Weekly Output” which is less clear to the user.

using the label clause in google sheets to rename aggregate functions

 

You can make your own copy of the spreadsheet above using the link attached below. 

If you want to try out using the label clause yourself when using the QUERY function, read the following section to learn how to write it step-by-step!

 

 

How to Use Label Clause in Google Sheets Queries

In this section, we will go through the steps needed to add a limit clause in a Google Sheets query. This guide will show you how we renamed columns and aggregate functions like sum in previous examples.

Follow these steps to start using the label clause:

  1. First, select the cell where we will add our QUERY function. Make sure that there is ample space below and to the right of this cell.
    Select a cell to place our QUERY function
  2. Next, we simply type the equal sign ‘=‘ to begin the function, followed by ‘QUERY(‘. 
  3. You may find a tooltip box with hints on how we can use the QUERY function. Click on the arrow in the top-right-hand corner of the box to minimize if necessary.
    Type QUERY function into formula bar
  4. The next step is to start typing our arguments. For our first argument, we’ll type in our data source. In this example, we’ll be using the range A1:C7.
    select the data range
  5. Next, we’ll type in our query text. For this step, we’ll simply add a query that selects everything in the dataset but changes the column Name into Employee Name.
    Type in the query text with the label clause in google sheets
  6. If you need to change multiple labels, simply add a comma after each pair of column ids and label strings.
    Example of a query text with multiple labels

 

 

Frequently Asked Questions (FAQ)

  1. Can I use the Label Clause to Modify a Pivot Header?
    Yes. We can wrap the query formula with another query formula that uses the label clause.  
  2. Where do I place my label clause?
    The Google Sheets Query language follows a specific clause order. You must write the clauses in the following order: SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LIMIT, OFFSET, LABEL, FORMAT.
    Usually, you can add the label clause after all other clauses, except for the format clause.

This step-by-step guide shows how easy it is to rename columns in the output of your QUERY function.

The QUERY function is just one example of a powerful and versatile function you can use in Google Sheets. With so many other Google Sheets functions out there, you can indeed find one that significantly improves your workflow.

Are you interested in learning more about what Google Sheets can do? Subscribe to our Google Sheets newsletter to be the first to know about the latest guides and tutorials from us. 

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:
1 comment
  1. Can you give an example of this part: “Can I use the Label Clause to Modify a Pivot Header?
    Yes. We can wrap the query formula with another query formula that uses the label clause.” How do we wrap a query with a query to label it with a header?

Leave a Reply

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

You May Also Like