In this tutorial, I’m sharing a Google Sheets query-related tip to help you format the QUERY Pivot Header Row in a simple and elegant way! Let me walk you through the process of formatting the QUERY Pivot Header Row in Google Sheets.

Many users want to format the QUERY Pivot Header Row but may struggle to find an effective method. I’ve seen many Google Sheets experts making their formulas unnecessarily complex in attempts to solve this.

As I mentioned earlier, I use a different approach to format the header row in the output of a QUERY Pivot. The function I rely on is TRANSPOSE! Let’s take a look at an example.

You can format the QUERY Pivot Header Row for various types of data—whether it’s dates, times, or numbers.

1. How to Format the QUERY Pivot Header Row Containing Dates

Example:

Let’s use a small dataset in A2:C8 with three columns: Date (Column A), Description (Column B), and Amount (Column C). Row 2 (A2:C2) contains the headers.

Here’s a basic Pivot Table using the QUERY function in Google Sheets:

=QUERY(A2:C8, "SELECT B, SUM(C) GROUP BY B PIVOT A")

In this case, the header row contains dates because the Pivot column is A. Can you format this pivot column using the FORMAT clause as follows?

You might want to convert the header row dates from the format YYYY-MM-DD to DD/MMM/YYYY.

Incorrect Formula:

=QUERY(A2:C8, "SELECT B, SUM(C) GROUP BY B PIVOT A FORMAT A 'DD/MMM/YYYY'")

This formula won’t work and will return a #VALUE! error.

Unfortunately, Google Sheets doesn’t support formatting the QUERY Pivot Header Row with the FORMAT clause. So, how can you format the header row in the QUERY Pivot output?

Steps

1. Interchange the Group Column with the Pivot Column. This is the critical step.
   Instead of pivoting Column A, pivot Column B, and group Column A (according to the example dataset above).
   The formula becomes: =QUERY(A2:C8, "SELECT A, SUM(C) GROUP BY A PIVOT B")
2. Format the Grouped Column. You can now 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. Transpose the Result. Finally, transpose the output to flip the headers into rows: =TRANSPOSE(QUERY(A2:C8, "SELECT A, SUM(C) GROUP BY A PIVOT B FORMAT A 'DD/MMM/YYYY'"))

This way, you’ve effectively formatted the header row in your Google Sheets QUERY Pivot formula!

2. Format Numbers and Time in the QUERY Pivot Header

You can use the same approach outlined above to format numbers and time in the QUERY Pivot header. To explore the available formats for dates, times, and numbers in QUERY, check out How to Format Date, Time, and Number in Google Sheets QUERY.

3. How to Format the QUERY Pivot Header Row Containing Month Numbers

Here’s a practical tip on formatting month numbers in the QUERY Pivot Header row to display as month names in text format.

In this example, we group by item descriptions and pivot by the month number, resulting in the month numbers appearing in the header row.

=QUERY(A2:C8, "SELECT B, SUM(C) GROUP BY B PIVOT MONTH(A)")

Here’s how you can convert these month number headers into month names in text format.

What if we don’t group by month numbers directly? What’s the alternative?

We’ll adapt the approach from Example #1, making slight modifications to achieve the desired result.

Previous Approach (Example #1):

1. Group by the Date column.
2. Pivot by the Text column.
3. Format the Date column.
4. Transpose the output.

This method doesn’t work well for month numbers because the pivot column treats numbers differently than date formatting.

New Approach:

1. Convert dates in the Date column to beginning-of-month dates.
2. Group by this new Date column.
3. Pivot the Text column.
4. Format the new Date column.
5. Transpose the result.

Instead of grouping by the month number, group by the beginning-of-month dates. This ensures all dates within a month are grouped under one single date (e.g., the 1st of the month). Format this date to display the month in text format (e.g., “MMM”).

To implement this, ensure the data is well-structured, rather than simply specifying A2:C8 as in earlier examples.

Formula for Data Preparation:

=ArrayFormula(HSTACK(VSTACK(A2, IFERROR(EOMONTH(DATEVALUE(A3:A8), -1)+1)), B2:C8))

You should use this as the ‘data’ in the QUERY formula.

Formula Breakdown:

1. DATEVALUE(A3:A8): Converts the dates in range A3:A8 to actual date values. If a cell is blank, it generates an error, avoiding issues with defaulting to 30/12/1899 (serial number 0).
2. EOMONTH(..., -1): Finds the end of the previous month for each date.
3. +1: Moves to the first day of the following month (beginning of the month).
4. IFERROR(...): Replaces errors (e.g., from blank cells) with blanks.
5. VSTACK(A2, ...): Stacks the header value in A2 on top of the calculated beginning-of-month values.
6. HSTACK(..., B2:C8): Horizontally combines the processed date column with columns B and C.

Final QUERY Formula:

=TRANSPOSE(QUERY(ArrayFormula(HSTACK(VSTACK(A2, IFERROR(EOMONTH(DATEVALUE(A3:A8), -1)+1)), B2:C8 )), "SELECT Col1, SUM(Col3) GROUP BY Col1 PIVOT Col2 FORMAT Col1 'MMM-YYYY'"))

Key Notes:

1. The QUERY formula uses Col1, Col2, and Col3 as column identifiers because the data is virtual and not directly in the spreadsheet.
2. Replace 'MMM-YYYY' in the FORMAT clause with your desired date format (e.g., 'MMM' for abbreviated month names or 'MMMM' for full month names).

Resources

• Using QUERY Function Similar to Pivot Table in Google Sheets
• How to Pivot Multiple Columns in QUERY in Google Sheets
• Replace Blank Cells with 0 in QUERY Pivot in Google Sheets
• How to Retain All Column Labels in QUERY Pivot in Google Sheets
• Sort QUERY Pivot Headers in Descending Order in Google Sheets