I get cool ideas from my occasional interaction with my blog users. How to insert blank columns in Google Sheets QUERY is one of them.

We can easily insert single or multiple blank columns in a Google Sheets QUERY formula result. However, if you don’t follow certain steps, it can get a bit tricky.

Specifically, the formula will vary depending on whether you have headers in your QUERY result. Additionally, the method of adding columns will differ based on the number of columns.

Let’s delve into these concepts with examples using the sample data provided below.

The sample data consists of four columns: Date, Item, Category, and Region, arranged in cell range A1:D. The field labels are in the cell range A1:D1.

The following QUERY formula filters for “Category 1”:

=QUERY({A1:D}, "SELECT Col1, Col2, Col3, Col4 WHERE Col3='Category 1' ", 1)

How can we incorporate blank columns into the result of this QUERY formula?

Inserting Blank Columns to a QUERY Result with a Header Row

To insert blank columns, use space characters in the SELECT clause within the QUERY function.

For the first column, use one space; for the second column, use two consecutive spaces; for the third column, use three consecutive spaces, and so forth.

Additionally, it’s essential to specify the headers using the LABEL clause.

The following formula inserts a blank column after column 1 (Date), labeled “Test 1”:

=QUERY({A1:D}, "SELECT Col1, ' ', Col2, Col3, Col4 WHERE Col3='Category 1' LABEL ' ' 'Test1' ", 1)

Please refer to the yellow and cyan highlighting to observe the changes made when inserting blank columns in the formula.

To insert a second blank column after the “Item” column, use this formula:

=QUERY({A1:D}, "SELECT Col1, ' ', Col2, '  ', Col3, Col4 WHERE Col3='Category 1' LABEL ' ' 'Test1', '  ' 'Test2' ", 1)

When you want to add a third blank column, use three consecutive space characters. I hope you understand the logic behind this.

Please note that the LABEL clause is the second to last clause in the Google Sheets QUERY function. The only clause that comes after this is the FORMAT clause.

Related: What is the Correct Clause Order in Google Sheets Query?

Inserting Blank Columns to a QUERY Result without a Header Row

As far as I know, the QUERY function is the only function in Google Sheets that can recognize field labels (header rows). Its syntax includes an optional third argument called ‘headers’:

Syntax: QUERY(data, query, [headers])

In the examples provided, since we have a header row, we specify 1 in headers. If there’s no header row, you can specify 0. If you omit the headers argument, the function will often try interpreting it based on the data.

In a scenario where the output doesn’t have a header row, as demonstrated in the following formula filtering for “Category 1”:

=QUERY({A2:D}, "SELECT Col1, Col2, Col3, Col4 WHERE Col3='Category 1' ", 0)

When inserting blank columns in this case, you should specify '' as the label in the LABEL clause:

For inserting one blank column:

=QUERY({A2:D}, "SELECT Col1, ' ', Col2, Col3, Col4 WHERE Col3='Category 1' LABEL ' ' '' ", 0)

For inserting two blank columns:

=QUERY({A2:D}, "SELECT Col1, ' ', Col2, '  ', Col3, Col4 WHERE Col3='Category 1' LABEL ' ' '', '  ' '' ", 0)

If you do not use the LABEL clause, the formula will leave the labels " "(), " "(), … depending on the number of columns.

Key Reminders

You have learned to add blank columns to QUERY formula results. However, if you enter any value in that blank column, the formula may break!

Then what is the purpose of adding blank columns?

It serves two purposes:

1. Enhance the readability of your results by minimizing clutter.
2. When printing, the hardcopy provides spaces to fill values such as leaving user comments, checkmarks, etc.

If you are particular about adding values in the QUERY results within Sheets, copy the result and paste it as values. The “paste value” option is available within the context menu, which you can access by right-clicking.

Resources

• Replace Blank Cells with 0 in Query Pivot in Google Sheets
• Filter Out Blank Columns in Google Sheets Using Query Formula
• How to Insert Blank Rows Using a Formula in Google Sheets
• How to Automatically Insert a Blank Row below Each Group in Google Sheets
• Insert Subtotal Rows in a Google Sheets Query Table
• Insert Blank Rows to Separate Week Starts/Ends in Google Sheets