How to Retain All Column Labels in Query Pivot in Google Sheets

To know how to keep or retain all the column labels in a Query Pivot in Google Sheets, you must know the reason for the missing columns.

We may get data in multiple rows (not necessarily) when using the Group By clause together with the Pivot clause in Query.

I mean to say, we will get a table where each cell contains the aggregation result for the relevant row (Group By) and column (Pivot).

When you use the Where clause to filter out some of the rows in the grouping, the relevant column(s) may become blank and subsequently disappear from the output.

This post explains how to retain all column labels in Query Pivot in Google Sheets.

For example, running the following QUERY in cell G1 on the table range C1:E will return the response shown in G1:I3.

=query({C1:E},"Select Col1, sum(Col3) where Col1 is not null group by Col1 pivot Col2")
Example to Retain All Column Labels in Query Pivot - Single Column Criteria

See what happens in G6:H7 when we filter out “orange” by running the following Query in G6.

=query(C1:E,"Select C, sum(E) where C='apple' group by C pivot D")

One Pivot column label is missing!

How to retain all column labels as per G10:I11 in Query Pivot in Google Sheets?

We will see to that with two examples below.

Retaining All Column Labels in Query Pivot – Criterion in Select Clause Column

If you check the three Query responses in columns G1:I above, you will get the logic to keep all the columns in the Pivot table.

What is that?

To retain all column labels in Pivot, nest two Query formulas.

We will apply the criterion in the outer Query.

I have the following formula in G10, where you can see the highlighted ‘moved’ criteria.

=query(query(C1:E,"Select C, sum(E) where C is not null group by C pivot D"),"Select * where Col1='apple'")

The above logic applies only when you filter the column specified in the Select clause.

It’s that simple to retain all column labels in Query Pivot! Let’s go to one more example.

Retaining All Column Labels in Query Pivot – Multiple Criteria Columns

The above logic may not work as it is when we want to filter based on the column in the Select clause and by another column.

Here also, we will use the criteria within the outer Query. But that applies to the column in the Select clause only.

Let’s see what we will do here with an example.

Sample Table for Running Query

In this example, we will select and group column 1 (A1:A) and Pivot column 3, i.e., C1:C.

But we want to apply conditions in columns 1 and 2 (A1:A and B1:B).

Here are the criteria (please refer to the image below):

E2: “Centre Speakers” (for filtering column B)

F2: 2022/06/01 (start date to filter column A)

G2: 2022/06/03 (end date to filter column A)

Usually, we may use the following Query (in E4) to group column A, apply the given criteria in columns A and B, and Pivot column C.

=Query({A2:C},"Select Col1,Count(Col2) where Col1 >= date '"&text(F2,"yyyy-mm-dd")&"' and Col1 <= date '"&text(G2,"yyyy-mm-dd")&"' and Col2='"&E2&"'group by Col1 pivot Col3 label Col1 'Date'",0)
Example to Retain All Column Labels in Query Pivot - Multiple Column Criteria

See the ‘actual response’ on the image. You can see two blank columns, i.e., “Follow Up” and “Preparing,” are missing.

How to get the ‘expected response’, i.e., to retain all column labels in Query Pivot?

Helper Range to Add to Query Data

Of course, we will move the date criteria to the outer Query as it applies to the column in the Select clause, i.e., A1:A.

Regarding the condition to apply in column range B1:B, we should follow a workaround. Here it is.

First of all, get the UNIQUE values in the Pivot column C. For that, key in the below formula in K1.

=unique(C2:C)

It will return three unique values and that is “Sent,” “Preparing,” and “Follow up.” Please refer to the below image.

Use the below SUBSTITUTE formula in J1 to repeat the E2 criterion thrice.

=ArrayFormula(SUBSTITUTE(E2,"",sequence(counta(K1:K))))

Then the below SUBSTITUTE formula in I1 will repeat the G2 (end-date) criterion +1 thrice.

=ArrayFormula(SUBSTITUTE(G2+1,"",sequence(counta(K1:K))))
Helper Range Using Pivot Column and Criteria

Why should we add +1 to the end date?

The source data is in A1:C. Instead, we will use {A2:C;I1:K} as the source data.

The +1 to the end date will later help us exclude the helper range from the Query response.

Actually, the above helper formulas will help us retain all column labels in the Pivot table in Query.

We can use the below formula (E8), which will retain the missing column labels in Query Pivot.

=query(Query({A2:C;I1:K},"Select Col1,Count(Col2) where Col2='"&E2&"' group by Col1 pivot Col3 label Col1 'Date'",0),"Select * where Col1 >= date '"&text(F2,"yyyy-mm-dd")&"' and Col1 <= date '"&text(G2,"yyyy-mm-dd")&"'")

The column 2 condition is within the first query.

Regarding the column 1 conditions, you can find them within the outer Query.

That’s all. Thanks for the stay. Enjoy!

Example_Sheet_17622

Resources

  1. What is the Correct Clause Order in Google Sheets Query?
  2. How to Format Query Pivot Header Row in Google Sheets.
  3. How to Pivot Multiple Columns in Query in Google Sheets.
  4. Replace Blank Cells with 0 in Query Pivot in Google Sheets.
  5. How to Use QUERY Function Similar to Pivot Table in Google Sheets.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.