How to Insert Blank Columns in Google Sheets QUERY

Published on

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.

DateItemCategoryRegion
15/3/24Item 1Category 1North
15/3/24Item 1Category 2North
15/3/24Item 2Category 3North
15/3/24Item 1Category 4North
20/3/24Item 2Category 1South
20/3/24Item 1Category 2South
20/3/24Item 1Category 3South
20/3/24Item 2Category 4South
25/3/24Item 2Category 1East
25/3/24Item 2Category 2East
25/3/24Item 1Category 3East

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.

Inserting a Blank Column in QUERY Result (Including Header Row)

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)
Inserting Two Blank Columns in QUERY Result (Including Header Row)

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)
Inserting a Blank Column in QUERY Result (Without Header Row)

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

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.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

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

6 COMMENTS

  1. Hi,

    @ Prashanth: Thank you for sharing your knowledge.

    I did not get, how {" "," "}/row(A2:A) adds 2 columns specifically the /row(A2:A) part with the / symbol.

    Thanks

    • Hi, Dinesh,

      To understand that, please follow the below steps.
      In two blank cells, for example, in cells A1 and B1, tap the space bar to get a space character in that cells.
      Now in cell C1 key in =ArrayFormula(A1:B1/row(A1:B1)).
      You will get two error values in C1 and D1. Just wrap that formula with IFERROR as below.
      =ArrayFormula(IFERROR(A1:B1/row(A1:B1)))

  2. Hi, could it possible for you to put the sample data into the tutorial as text so that we can quickly copy and paste it into Sheets to try for ourselves?

    • Hi, Helmanfrow,

      Thanks for your suggestion.

      In my latest tutorials, I am trying to include example sheets wherever necessary. I’ll try to update my earlier tutorials.

      The problem is, there are 1000+ posts, and it will be a time taking exercise for me to go through them all.

  3. Hello,

    Can we edit the blank column and place any number against the result of the query function, so that every time data gets change, that specific number comes against the resulted query.

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.