How to Format Query Pivot Header Row in Google Sheets

Here I am sharing with you a Google Sheets Query related secret tip. You can format Query Pivot Header Row with the help of another function that in a simple and elegant way! Let me go to the details on how to format Query Pivot Header Row in Google Sheets.

Most of us want to format the Query Pivot Header Row but may not find a proper way. I have seen many experts in Google Sheets finding it tough and makes their formula very complex on the course. I hope this post can be an eyeopener.

Must Read: Learn Google Sheets Query Function

As I’ve said, I am using another function to format the header in the output of the Query Pivot. The function that I am using is none other than the TRANSPOSE! How? See the below example.

How to Format Query Pivot Header Row in Google Sheets

You can format Query Pivot Header Row contents no matter whether it’s date, string, time or numbers.

1. How to format Query Pivot Header Row Containing Date?

Example:

Here is a normal Pivot Table using the Query function in Google Sheets.

format Query Pivot Header Row Containing Date

In this the header row containing the Column A dates. Because the Pivot column is A. Can I format this pivot column using the format clause in Query as below?

I want to convert the header row dates to DD/MMM/YYYY format from YYYY/MM/DD format.

Wrong Formula:

=query(A2:C8,"Select B, Sum(C) group by B Pivot A format A 'dd/mmm/yyyy'")

This formula won’t work!

Unfortunately, Google Sheets doesn’t support Query Pivot Header Row formatting with the Format clause! Then how can I format header row in Query Pivot output?

Steps to Format Query Pivot Header Row

1. In the Query formula, interchange the group column with Pivot Column. This is the important step.

That means instead of pivoting Column A, Pivot Column B. Group Column A
(this as per my dataset in the above example)

=query(A2:C8,"Select A, Sum(C) group by A Pivot B")

2. Format the Grouped Column. You can format the Grouped Column using the format clause.

=query(A2:C8,"Select A, Sum(C) group by A Pivot B format A'dd/mmm/yyyy'")

3. Then Transpose this formula. Virtually I’ve formatted the header row in this Google Sheets Query Pivot formula!

example to query pivot header row formatting

2. Format Text, Number and Time in Query Pivot Header

Follow my above same approach to format text, numbers and time too in Query pivot header.

To see the available formats that you can use in Query, feel free to visit my below tutorial.

Must Check: How to Format Date, Time, and Number in Google Sheets Query

3. How to format Query Pivot Header Row Containing Month Number?

Here is another secret tip that about converting the month number in Query Pivot Header to the Month in Text format, that also in Chronological Order.

But first, see how we can use the MONTH function in Query to group and Pivot.

This example shows the month number wise grouping in Query and pivoting the month number. So the month numbers occupy the header row.

month number query

The number 5 in the header row represents June and #6 represents the month of July as the month numbering starts from 0 to 11 not from 1 to 12 in Query.

Let me explain how to convert this header row containing month number to month text in Pivot in Query.

Our earlier approach was as below. That method won’t work with month number in the same way.

Earlier Approach: Group Date column > Pivot Text Column > Format Date Column > Transpose.

The procedure above is like instead of pivoting the column that we want in the header, group it first, then format this column and finally transpose the formula output. That may not work in the month number wise Pivot.

Regarding the month number formatting, In the above chain, there is one addition. First, we should convert the dates in column A to end of the month dates.

New Approach: Convert Dates in the Date Column to End of the Month Dates > Group this new Date column > Pivot Text Column > Format this new Date Column > Transpose.

We are not going to group the month number instead we are grouping the end of the month dates. The grouping effect will be same as we are converting all the dates in a month to one single date (end of the month date) in that month and group that date. Format this date to “MMM”.

We can use the EOMONTH date function to get the end of the month date of any given date.

Formula:

=eomonth("25/06/2018",0)

Result: 30/06/2018

In the below Query formula the data range is modified. Instead of Date, Description and Amount, the new range is like End of the month dates, Description, and Amount.

On this range, you can group the end of the month dates, pivot the Description column, format the end of the month dates to “MM-YYYY” or “MMM” then transpose.

EOMONTH in Query and Grouping

More Query Formulas:

1. Filter by Month and Year in Query in Google Sheets

2. How to Use IF Function in Google Sheets Query Formula

3. How to Find Distinct Rows in Google Sheets Using Query

There are plenty of Query formula examples on this site. Click this Link to find them.

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...

5 COMMENTS

    • Hi, An Dang,

      In that case, the above tips won’t work. You should format the data before processing with Query.
      E.g.:

      Formula as per the tutorial:-

      =transpose(query(A2:C8,"Select A, Sum(C) group by A Pivot B format A'dd/mmm/yyyy'"))

      Recommended:-

      =ArrayFormula(query({text(A2:A8,"dd/mmm/yyyy"),B2:C8},"Select Col2, Sum(Col3) group by Col2 Pivot Col1"))

      In the second one, you can group by two or more columns.

      • Million thanks, this is a direct way, no need to work around with the transpose.

        What if we want to sort the Query Pivot Header from the farthest day to the nearest day. Could you help?

        • Hi, An Dang,

          For that, you may please try the below syntax.

          =transpose(query(transpose(query_formula),"select * order by Col1 desc"))

          You can also try the built-in pivot table (Insert > Pivot table), which has the said feature.

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.