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.
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!
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.
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.
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.
How about there are two or more items in the Group clause instead of 1 column B?
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.
THANKS A LOT!! 🙂